How to Secure MySQL: Part One

Whenever application security is mentioned, people think of securing applications against some of the most frequent attacks such as injection, broken authentication, sensitive data exposure, and the like. However, while these attacks are prevalent, knowing how to protect your application from them alone will not be sufficient – especially when you’re running MySQL. Today we are going to look at a different side of security – we are going to look into how to properly secure MySQL.

As MySQL security is a pretty big thing, treat this post as the start of a series of posts regarding MySQL security measures. We will not cover everything, but this post should provide the foundation of some of MySQL’s security measures.

Why Do You Need to Secure MySQL?

First of all, we must answer the question why we would want to secure MySQL. After all, if we secure our application against the most prevalent threats, we should be safe, right? Well, yes and no.

You see, when you secure your application against certain attacks, you make sure that the attackers have a harder time penetrating the defenses of your application – in order to secure your database though, protecting against such attacks will not be sufficient. Securing your database might be the last straw that could save your application (and your database!) from destruction.

How Do I Secure MySQL?

Now, onto the burning question. How do you actually secure MySQL?

When thinking about the security of your MySQL instance(s), you should consider a wide range of possible options. Fortunately, some of those options are not even limited to MySQL meaning that they will be applicable in other scenarios too! We will start with the general things.

MySQL Security – General

When securing MySQL, keep in mind that it uses security-based Access Control Lists (ACLs) for operations performed by users (Access Control Lists are a list of permissions associated with an object). Here’s how to take care of a couple of the most basic security issues:

• Secure the initial MySQL account – this is very obvious, but you should ensure that the root account has a password. The root account may or may not have a password when MySQL is first installed – you can figure out what the password is by checking the error log, then change it to a stronger one if you wish. All of the other accounts should have passwords too.
• Never store passwords inside any MySQL databases in plain text – use a one-way hashing function like BCrypt.
• Do not grant any users access to the user table in the mysql database (the root user is an exception).

Also, familiarize yourself with Access Control and Account Management in MySQL. This subject deserves an entire book in and of itself, but the basic things you should know include:

• Controlling access in MySQL;
• Creating, altering, and removing users;
• Granting and revoking privileges to and from users;
• Knowing how to check what privileges are assigned;
• What account categories are;
• What reserved accounts are;
• What roles are;
• How password management works;
• How account locking works;
• Taking a glance into the security plugins offered by MySQL;
• Knowing how to secure MySQL backups.

As far as MySQL security is concerned, backups should also be covered.

Now, we will look into each of these options further.

Controlling Access in MySQL

• As already noted above, never give any accounts, except the root account, access to the user table in the mysql database;
• Ensure that all existing MySQL accounts use only the privileges that they absolutely need to perform their actions.

Creating, Altering, and Removing Users in MySQL

In MySQL, users can be created by running the CREATE USER query:

CREATE USER ‘demouser’@’localhost’ IDENTIFIED BY ‘password’;

​Users can be altered by running the ALTER USER query – the query allows you to do many different things including locking and unlocking accounts, requiring the account to connect using SSL, establish the maximum amount of connections per hour, discard old passwords, etc. Here’s a sample query that can change your own password:

ALTER USER USER() IDENTIFIED BY ‘password’;

​Users can be removed by running the DROP USER query:

DROP USER ‘demouser’@’localhost’;

Granting and Revoking Privileges to and from Users in MySQL

The GRANT statement must grant either privileges or roles. The ON statement is able to tell you whether privileges or roles will be granted. The following query grants privileges:

GRANT ALL ON demo_db.* TO ‘demouser’@’localhost’;

​The following query grants roles:

GRANT ‘demo_role’ TO ‘demouser’@’localhost’;

GRANT should respond with Query OK, 0 rows affected.

To revoke certain privileges from users, use the REVOKE statement (the hostname part of the account name defaults to “%”):

REVOKE SELECT ON *.* FROM ‘demouser’@’localhost’;

To revoke all privileges, REVOKE ALL can be used:

REVOKE ALL PRIVILEGES ON *.* FROM ‘demouser’@’localhost’;

You might want to issue a FLUSH PRIVILEGES; statement after performing the steps above.

Checking what Privileges are Assigned in MySQL

• To check what privileges are assigned, issue the SHOW GRANTS; statement.
• For every request that is issued, the server determines the operation that you want to perform, then checks whether your privileges are sufficient enough to perform the operation in question.
• The server uses the user and db tables in the mysql database to ensure access control.
• The user and global_grants tables grant global privileges.

The rest of the options will be covered in upcoming posts.

Summary

As far as MySQL security is concerned, you have a very wide range of options to choose from. The options include basic security measures that can be applicable to pretty much all applications, but some of the options are pretty specific to MySQL. Keep in mind that not all of the available options have been covered yet – they will be talked about in upcoming editions of the MySQL security series too.