Security in MySQL: Part One

Security is a critical part of any infrastructure – it’s even more so in the database world, where one step in the wrong direction can be the cause of system disruptions and downtime, customer dissatisfaction, and in the worst-case scenario – deaths.

As MySQL is one of the primary relational database management systems being used in the world today, it’s critical to understand how to go about properly the database infrastructure to keep data breaches away from the organization’s websites or, if they’ve happened already, to become a thing of the past.

Why secure MySQL?

First, the basics – why should you secure your MySQL infrastructure in the first place?

You see, chances are that both you and people that use software developed by you, whether knowingly or not, provide the website with data that is then processed by a database. Regardless of what the website is about, data is still a critical part of any database-backed infrastructure. Users inside of the database mean more exposure to a specific project, blog posts probably mean marketing, and products and customers inside of it usually mean revenue to your business.

However, the sad fact is that people usually think about securing their most critical infrastructure only when it’s too late to do so; once a data breach happens, everyone’s scrambling to save themselves from identity theft by using data breach search engines and archives and putting efforts into research related to how they should go about protecting their database in the future. As sad as it would be to state it, such efforts are often futile; if you don’t put efforts into database security before a thief comes knocking on the door, the data taken from a MySQL database will be exploited for identity theft purposes, sold (then reused for identity theft attacks), twisted in other ways, and finally, archived somewhere in a dark alley of black-hat hackers evaluating the strength of the next database – their next upcoming victim. Doesn’t sound like a very promising scenario, does it?

How to secure MySQL?

The security of MySQL infrastructure can be conveniently split into multiple categories:

• Access control.
• User security.
• The components and plugins that help keep MySQL safe.
• Security considerations and general security guidelines.
• Enterprise-level security controls.

In the list above, I have outlined seemingly few categories – however, if you think about it, the majority of security issues fall somewhere within their range from one side or another: improper access control is often the cause of SQL injection escalation, insecure user accounts might mean guessable passwords, missing security plugins mean missing out on the security features offered by MySQL which makes the job easier for an attacker.

I’m now going to go through these points one by one in detail. One article is probably not sufficient to cover all of these issues, though, so if something’s missing in this one, please head over to the following parts of this guide.

Access control in MySQL

Controlling access in MySQL is arguably the cornerstone of its security. After all, if access to every resource were so heavily restricted that nobody except the people who absolutely need it could access it, there would be many fewer issues related to data theft, right?

While there is a degree of truth in that statement because, with MySQL, everything gets a little more complex. In this RDBMS, access control usually entails several aspects, including, but not limited to the following:

• Properly setting up privileges for every user.
• Limiting a specific account.
• Account locking and unlocking.
• User activity control.
• Password security.

Properly setting up privileges is one of the most important aspects of access control. Simple GRANT and REVOKE statements can either give or, as the name suggests, revoke privileges to and from users. The proper assignment of privileges is said to be crucial for the security of any infrastructure backed by MySQL; the stronger strategy of assigning the privileges is, the less chance the attacker has to penetrate the defenses.

Privileges span multiple categories: they can be either administrative (meaning that they can enable users to control what operations MySQL performs and how it does it), they can apply to a specific database, or span database objects (i.e., things that are stored inside a database.) Some of the privileges are as follows:

Privilege	Explanation
ALL	Granting this privilege would, as the privilege itself suggests, grant ALL privileges to a specific user. Dangerous from a security point of view, but can be used in a local environment when testing features and whatnot.
CREATE	Enables a specific user to create databases and tables within them.
ALTER	Enables a user to alter a specific table – in other words, enables a user to change table structure.
DELETE or DROP	Enables a user to delete rows or drop entire tables and databases.

 

Privileges in MySQL also have a couple of brothers named roles. Roles are collections of privileges, and granting a specific role to a user means granting all of the privileges assigned to that role; it’s all pretty self-explanatory. Find the full list of privileges here, and for a more thorough explanation of what privileges are and what they do, head over to this part of the documentation.

It can be very useful to limit specific accounts, but such a feature is often overlooked. It’s possible to set a limit on the number of queries that can be issued every hour by a specific account, how many times an account can connect to the server, how many connections can a specific account make to the server at one moment, etc. The MAX_USER_CONNECTIONS variable defines how many connections a given account can make at the same time; the MAX_CONNECTIONS_PER_HOUR variable will define how many connections per hour can be made; MAX_UPDATES_PER_HOUR will set boundaries on how many UPDATE queries can be run in an hour, and so on. The usage of such variables will look similar to the following query (replace YOUR_VARIABLE with the variable you want to set.) Also, keep in mind that all variables set by the client revert back to normal if they’re not specified inside of my.cnf.

ALTER USER ‘your_user’@’localhost’ WITH [YOUR_VARIABLE] 0;

Accounts can also easily be locked (that prohibits any further activity from originating from them) by issuing a simple ACCOUNT LOCK command. It works when creating or modifying the account:

[CREATE USER / ALTER USER] demo_account IDENTIFIED BY ‘verystrongpasswordhere’ ACCOUNT LOCK;

You can check if a specific account is locked by issuing a query such as the one below which also tells on which which host it resides):

You can unlock an account by specifying ACCOUNT UNLOCK instead of ACCOUNT LOCK. That’s it!

Password security comes down to a couple of simple things as well: MySQL allows password expiration. You can set a “lifetime” for passwords by using a default_password_lifetime variable in my.cnf. You can also impose password reuse restrictions through the use of password_history, which defines how many previous passwords need to be “blacklisted” before being allowed to be used again, and password_reuse_interval, which prohibits using any passwords that were used within the last X days.

User security

Another thing closely related to access control and password security is the security of MySQL users themselves. The majority of the recommendations within this sphere can seem pretty basic at first glance, but when combined with the things mentioned above, they all can create a powerful force of security within MySQL. To further user account security within MySQL, consider not sending passwords over plain text and send them using SSL instead (use the REQUIRE SSL option when creating a user once SSL is configured on your server), avoid running MySQL as root (the root user has the FILE privilege which can cause the server to create files), and, of course, require all users to have strong passwords. I will elaborate on this in the next part of this series, but completing the outlined steps should be a good preventative to ensure that your database stays secure whatever happens.

Summary

I hope that this article has widened your horizons in the MySQL space. Follow the advice outlined in this blog post by tightening up your users and using proper access controls, and your database will surely ride fast on the security highway. However, that’s not it. To properly secure your database, you will also need to take care of plugins that help keep MySQL safe from attacks, keep an eye on enterprise-level security control, and follow general security guidelines whenever you’re working with it.

I hope that this article has been informational, and you will share it (or at least the knowledge contained within this article) with your friends to help them secure their MySQL instances. My next article will cover more ways to secure MySQL. Until next time, be sure to put what you’ve learned in practice.

 

The post Security in MySQL: Part One appeared first on Simple Talk.

from Simple Talk https://www.red-gate.com/simple-talk/databases/mysql/security-in-mysql-part-one/
via