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.
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:
- 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.
- 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
-pwithout 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.cnffile (different files can also be used):
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-fileoption specifying the full path to the file:
- Make sure that the only account used for running MySQL is the Unix user account with read or write privileges in the database directories.
- Avoid running MySQL or MariaDB as the
rootuser of the system – if you do so, any user with the
FILEprivilege could create or modify any files on the server as
- Avoid giving anyone except the
rootuser access to the user table in the
- Do not choose common passwords or passwords from the dictionary when creating MySQL users or any users in general – consider using a password manager.
- 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.
- 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.
- Do not grant far-reaching privileges to users who do not need them: for example, do not grant
SUPERprivileges to everyone. To read more about
SUPERprivileges, take a look at the MySQL documentation.
- The amount of connections pertained to a single MySQL account can be controlled by altering the
However, the security of MySQL does not end with securing MySQL accounts. The following things should also not be ignored:
- Have a look into how the MySQL access privilege system works, use the
REVOKEstatements 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 GRANTSstatement and
REVOKEprivileges that are not necessary.
- Consider using a firewall and putting MySQL behind it.
- Protect your web applications that run MySQL with an encrypted connection by using SSL – MySQL supports internal SSL connections.
- Encrypt your binary log files and relay log files: in MySQL, encryption of these files can be enabled when the system variable
binlog_encryptionis set to
- Consider making the plugin directory read-only to the server or setting the
secure_file_privvariable to a directory where
SELECTwrites 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.
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.