The Basics of MySQL Security

If you have ever built a website, chances are you took care of security. Securing web applications does have its own caveats though – did you take care of the security of the database? In this post we will look at the options that can make MySQL perform at the very best of its ability security-wise. Keep in mind that this is not a very comprehensive guide (certain topics such as backup security etc. will not be touched) and some of the aspects discussed here are not even limited to MySQL, but it should provide some insight into the security of this relational database management system.

Steps to Secure Your MySQL Instances

MySQL uses security-based Access Control Lists (ACLs) for all operations that users attempt to perform – that’s why accounts are one of the key parts of securing it. To keep your MySQL accounts secure, follow these steps:

1. Require all MySQL accounts to have a password. While such a thing may seem basic, it should not be overlooked – if your MySQL accounts do not have a password, anyone can connect to them.
2. Never provide a MySQL password over the command line. For example, a query like this should never be executed:
   mysql -u user -ppassword db_name
   You should avoid running queries like the above because on some systems the password might become visible to system status commands that can be invoked by other users – the command ps, for example, displays information about running processes.
   You can censor the password by only typing -p without the actual password (you will be prompted for the password afterwards), but there’s a way to avoid the password being provided in such a way altogether – on Unix, you can provide your password in a my.cnf file (different files can also be used):
   [client]
password=password
   You should also set the file access mode to 400 or 600 to make the file inaccessible to anyone but yourself.
   To use the file from a command line, use the --defaults-file option specifying the full path to the file:
   mysql --defaults-file=/var/lib/mysql/my.cnf
3. Make sure that the only account used for running MySQL is the Unix user account with read or write privileges in the database directories.
4. Avoid running MySQL or MariaDB as the root user of the system – if you do so, any user with the FILE privilege could create or modify any files on the server as root.
5. Avoid giving anyone except the root user access to the user table in the mysql database.
6. Do not choose common passwords or passwords from the dictionary when creating MySQL users or any users in general – consider using a password manager.
7. Never store plain text passwords in the database – use a one-way hashing algorithm such as BCrypt, and, if you have a lot of users, consider using salts to make password cracking harder when dealing with a huge amount of hashes.
8. Do not trust any input provided to your web application by the user – by doing so you will protect your database against SQL injection attacks.
9. Do not grant far-reaching privileges to users who do not need them: for example, do not grant PROCESS or SUPER privileges to everyone. To read more about GRANT and SUPER privileges, take a look at the MySQL documentation.
10. The amount of connections pertained to a single MySQL account can be controlled by altering the max_user_connections variable in mysqld.

However, the security of MySQL does not end with securing MySQL accounts. The following things should also not be ignored:

1. Have a look into how the MySQL access privilege system works, use the GRANT and REVOKE statements to give and take away privileges from MySQL users, only grant as much privileges as absolutely necessary and never grant them to all of the hosts – regularly check which accounts have access to what using the SHOW GRANTS statement and REVOKE privileges that are not necessary.
2. Consider using a firewall and putting MySQL behind it.
3. Protect your web applications that run MySQL with an encrypted connection by using SSL – MySQL supports internal SSL connections.
4. Encrypt your binary log files and relay log files: in MySQL, encryption of these files can be enabled when the system variable binlog_encryption is set to ON.
5. Consider making the plugin directory read-only to the server or setting the secure_file_priv variable to a directory where SELECT writes can run safely – by doing so you will avoid the scenario of a user writing executable code to a file in the plugin directory using SELECT … INTO DUMPFILE.

Summary

In order to ensure the security of your MySQL installation you have to take some steps that are not limited to MySQL and can be applied to pretty much all kinds of software. These steps include requiring all MySQL accounts to have a password, not providing the MySQL password over the command line, avoiding to grant far-reaching privileges etc.

If you’ve followed all (or most) of the steps outlined above, your database should be well on the way to a more secure future.