Unlocking MySQL Database Security: Best Practices

MySQL is one of the most popular open-source databases that runs on a variety of platforms. It is relatively easy to configure, simple firewall and shows good performance characteristics even under significant load but it still has a wide variety of security-relevant configuration issues.

MySQL Database Security Best Practices

The very best practice of security management is to be paranoid and anticipate an attack any minute from every direction, but if you adopt some precautionary measures, it won’t be such a hard work. Following guidelines will help you to substantially reduce the surface of possible threats.

First of all, read security guidelines at http://dev.mysql.com/doc/refman/5.7/en/security.html and check for updates regularly. Several serious vulnerabilities have been found recently for MySQL RDBMS which have freely available exploits. Take advantage of updates that add new features and more importantly, fix security flaws.

Regularly monitor vulnerability databases. Always be aware of newly found threats to your system.

Operating System

1. Turn off unnecessary daemons and services. The fewer components attackers can access the less is the chance of them finding a flaw that can be used to gain access to the system. By keeping the host configuration simple you reduce the effort needed to manage the system and mitigate the risk of security omissions.
2. Ensure that MySQL users cannot access files outside of a limited set of directories. MySQL data files should not be read by any users except for root or administrator accounts.
3. Disable or restrict remote access. In case you need your MySQL to be accessed remotely, configure GRANT statement, which is used to set up the user, to require SSL.
4. Make sure that no user other than MySQL user can read MySQL configuration and log files. Files my.cnf, my.ini and master.info commonly have unencrypted usernames and passwords. If there is a query log file, it is likely to contain passwords as well. Some MySQL configuration files can also contain plaintext usernames and passwords. Ensure that these files are protected from unwanted users.
5. Run MySQL with the –chroot option. It provides an excellent mitigation to the power of the file privilege. Chroot is used to restrict file access by a process to a given directory. Even with the chroot option, an attacker that gains file privilege will be able to read all MySQL data and probably still be able to execute UDF’s.
6. Regularly clear your .mysql_history file or permanently link it to /dev/null. By default on Unix systems, you will find a .mysql_history file in your home directory. It contains a log with all the queries that you’ve typed into the MySQL command-line client. Perform the following command to clear the history:

cat /dev/null > ~/.mysql_history

Users

After configuring the operating system, you need to build a privilege model and assign user accounts.

1. During the initial setup phase, remove all non-root MySQL users, so that you know where you stand in terms of users that have access to the database. The best way is to start from ground zero and build up users as you need them.
2. Rename root username and change the password using different numbers and characters. You can change administrator’s username with the following command in MySQL console: mysql> RENAME USER root TO new_name;
3. Don’t give account privileges that they don’t really need, especially File_priv, Grant_priv, and Super_priv. Consider creating a separate MySQL account that your application can use for interaction with the filesystem within MySQL. Keep in mind that this user will have access to all MySQL data, including password hashes.
4. If it is possible, create a MySQL user for each web application or for each role within each web application. Within this application assign user privileges only for required commands. It can seem tedious but makes sense when it comes to establishing a comprehensive security system.
5. In case remote connections are enabled, specify REQUIRE SSL in the GRANT statement used to set up the user. Some exploit scripts don’t work will not work as they don’t have SSL support. Moreover, SSL protocol ensures the confidentiality of a passport response sequence. You can also establish restrictions based on a client-side certificate that is used to authenticate with SSL. Another helpful security measure, knowledge of a password won’t be enough, a specified certificate will also be required.
6. Don’t give anyone access to the mysql.user.table (except for the users with root privileges).

MySQL Configuration

1. Disable LOAD DATA LOCAL INFILE command. It is a construction that helps to import local files into a table, it has a peculiarity that under certain circumstances can lead to retrieval of /etc/passwd file content. Exploit for this has been freely available since 2013. Add set-variable=local-infile=0 to the my.cnf file.
2. Get rid of any unused UDF. UDFs also pose threats to database security. If you see unused UDFs in mysql.func table, remove.
3. If you are using only local connections and there is no need for remote hosts to connect to MySQL, disable TCP/IP connections via the –skip-networking option.
4. Remove the test database. There is a test database by default that can be accessed by anyone. Remove it or restrict privileges.
5. Remove Anonymous accounts. Do not leave any users with blank passwords.

   You can find anonymous users with this command:

   select * from mysql.user where user=””;
6. Make sure that MySQL traffic is encrypted.
7. Enable logging via –log option. According to MySQL documentation “general query log” is a debugging feature, but you can also use it as a security measure. It logs successful connections and executed queries. By default the query log is disabled, you can turn it on using the –log option.

Bear in mind that query logs and error logs are a source of information to an attacker as well. Ensure that the log file is visible only to the administrator or root account of the system. Also, it doesn’t log results of the queries and retrieved data, but here are special database activity monitoring solutions for that matter.

Regularly monitor query logs and search for SQL injection attacks and use of the load_file, infile and outfile filesystem syntax.

Install antivirus and antispam software. Deploy a firewall to control incoming and outgoing network traffic and protect from attacks.

Comprehensive MySQL database security system is built by combining a huge amount of unobtrusive configuration changes. Every detail is important. There is no way to guarantee 100% security, but pursuance of maximum protection is a must in the era of cybercrimes.