MySQL Security Best Practices (Hardening MySQL Tips)

Introduction

The MySQL database has become the world's most popular open source database because of its consistent fast performance, high reliability and ease of use. MySQL is used on every continent – yes, even in Antarctica! – by individuals, Web developers, as well as many of the world's largest and fastest-growing organizations such as industry leaders Yahoo!, Alcatel-Lucent, Google, Nokia, YouTube and others to save time and money powering their high-volume websites, business-critical systems, and packaged software.

As most products do, MySQL comes "ready-to-work" out of the box. Usually, security is not a major consideration when installing this kind of product. Often, the most important issue is to get it up and running as quickly as possible so that the organization can benefit. This document is intended as a quick security manual to help you bring an installed MySQL database server into conformity with best security practices.

Syntax explanation

This paper contains code examples that can either be executed in the operation system console, sent to the database via the MySQL console or added to configuration files. Code snippets are denoted by a gray background. Please refer to the surrounding context for more precise instructions.

Green SQL

GreenSQL is an Open Source Database Firewall created under a GPL license. It works as a reverse proxy and protects your database from numerous kinds of attacks that target database servers and the sensitive data that they store. GreenSQL was originally created with MySQL in mind. Following GreenSQL's wide acceptance, support for other database engines was added. Because of its database orientation, GreenSQL defends your data at the very point at which other types of protection products often fail – preventing your data's destruction or leakage just before it happens.

1. Secure your server

Many known attacks are possible only once physical access to a machine has been acquired. For this reason, it is best to have the application server and the database server on different machines. If this is not possible, greater care must be taken; otherwise, by executing remote commands via an application server, an attacker may be able to harm your database even without permissions. For this reason, any service running on the same machine as the database should be granted the lowest possible permission that will still allow the service to operate.

Do not forget to install the whole security package: Antivirus and Antispam, Firewall, and all of the security packages recommended by your operating system’s vendor. In addition, do not forget to spend 10 minutes thinking of your server's physical location – in the wrong location, your server can be stolen, flooded, or harmed by wild animals or running children. Consider performing some operating system hardening procedures, such as the following:

Make sure to:

• Install Antivirus and Antispam software
• Configure the operating system’s firewall
• Consider the safety of your server's physical location
• Install the services you intend the machine to run
• Harden the production server and services
• Disable unnecessary services
• Follow services vendors’ recommendations regarding patches and updates needed for the safe and secure operation of their services

2. Disable or restrict remote access

Consider whether MySQL will be accessed from the network or only from its own server.

If remote access is used, ensure that only defined hosts can access the server. This is typically done through TCP wrappers, iptables, or any other firewall software or hardware available on the market.

To restrict MySQL from opening a network socket, the following parameter should be added in the [mysqld] section of my.cnf or my.ini:

skip-networking

The file is located in the "C:\Program Files\MySQL\MySQL Server 5.1" directory on the Windows operating system or "/etc/my.cnf" or "/etc/mysql/my.cnf" on Linux.

This line disables the initiation of networking during MySQL startup. Please note that a local connection can still be established to the MySQL server.

Another possible solution is to force MySQL to listen only to the localhost by adding the following line in the [mysqld] section of my.cnf

bind-address=127.0.0.1

You may not be willing to disable network access to your database server if users in your organization connect to the server from their machines or the web server installed on a different machine. In that case, the following restrictive grant syntax should be considered:

mysql> GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

3. Disable the use of LOCAL INFILE

The next change is to disable the use of the "LOAD DATA LOCAL INFILE" command, which will help to prevent unauthorized reading from local files. This is especially important when new SQL Injection vulnerabilities in PHP applications are found.

In addition, in certain cases, the "LOCAL INFILE" command can be used to gain access to other files on the operating system, for instance "/etc/passwd", using the following command:

mysql> LOAD DATA LOCAL INFILE '/etc/passwd' INTO TABLE table1

Or even simpler:

mysql> SELECT load_file("/etc/passwd")

To disable the usage of the "LOCAL INFILE" command, the following parameter should be added in the [mysqld] section of the MySQL configuration file.

set-variable=local-infile=0

4. Change root username and password

The default administrator username on the MySQL server is "root". Hackers often attempt to gain access to its permissions. To make this task harder, rename "root" to something else and provide it with a long, complex alphanumeric password.

To rename the administrator’s username, use the rename command in the MySQL console:

mysql> RENAME USER root TO new_user;

The MySQL "RENAME USER" command first appeared in MySQL version 5.0.2. If you use an older version of MySQL, you can use other commands to rename a user:

mysql> use mysql;
mysql> update user set user="new_user" where user="root";
mysql> flush privileges;

To change a user’s password, use the following command-line command:

mysql> SET PASSWORD FOR 'username'@'%hostname' = PASSWORD('newpass');

It is also possible to change the password using the "mysqladmin" utility:

shell> mysqladmin -u username -p password newpass

5. Remove the "test" database

MySQL comes with a "test" database intended as a test space. It can be accessed by the anonymous user, and is therefore used by numerous attacks.

To remove this database, use the drop command as follows:

mysql> drop database test;

Or use the "mysqladmin" command:

shell> mysqladmin -u username -p drop test

6. Remove Anonymous and obsolete accounts

The MySQL database comes with some anonymous users with blank passwords. As a result, anyone can connect to the database To check whether this is the case, do the following:

mysql> select * from mysql.user where user="";

In a secure system, no lines should be echoed back. Another way to do the same:

mysql> SHOW GRANTS FOR ''@'localhost';
mysql> SHOW GRANTS FOR ''@'myhost';

If the grants exist, then anybody can access the database and at least use the default database "test". Check this with:

shell> mysql -u blablabla

To remove the account, execute the following command:

mysql> DROP USER "";

The MySQL "DROP USER" command is supported starting with MySQL version 5.0. If you use an older version of MySQL, you can remove the account as follows:

mysql> use mysql;
mysql> DELETE FROM user WHERE user="";
mysql> flush privileges;

7. Lower system privileges

A very common database security recommendation is to lower the permissions given to various parties. MySQL is no different. Typically, when developers work, they use the system's maximum permission and give less consideration to permission principles than we might expect. This practice can expose the database to significant risk.

* Any new MySQL 5.x installation already installed using the correct security measures.

To protect your database, make sure that the file directory in which the MySQL database is actually stored is owned by the user "mysql" and the group "mysql".

shell>ls -l /var/lib/mysql

In addition, ensure that only the user "mysql" and "root" have access to the directory /var/lib/mysql.

The mysql binaries, which reside under the /usr/bin/ directory, should be owned by "root" or the specific system "mysql" user. Other users should not have write access to these files.

shell>ls -l /usr/bin/my*

8. Lower database privileges

Operating system permissions were fixed in the preceding section. Now let’s talk about database permissions. In most cases, there is an administrator user (the renamed "root") and one or more actual users who coexist in the database. Usually, the "root" has nothing to do with the data in the database; instead, it is used to maintain the server and its tables, to give and revoke permissions, etc.

On the other hand, some user ids are used to access the data, such as the user id assigned to the web server to execute "select\update\insert\delete" queries and to execute stored procedures. In most cases, no other users are necessary; however, only you, as a system administrator can really know your application’s needs.

Only administrator accounts need to be granted the SUPER / PROCESS /FILE privileges and access to the mysql database. Usually, it is a good idea to lower the administrator’s permissions for accessing the data.

Review the privileges of the rest of the users and ensure that these are set appropriately. This can be done using the following steps.

mysql> use mysql;

[Identify users]

mysql> select * from users;

[List grants of all users]

mysql> show grants for ‘root’@’localhost’;

The above statement has to be executed for each user ! Note that only users who really need root privileges should be granted them.

Another interesting privilege is "SHOW DATABASES". By default, the command can be used by everyone having access to the MySQL prompt. They can use it to gather information (e.g., getting database names) before attacking the database by, for instance, stealing the data. To prevent this, it is recommended that you follow the procedures described below.

• Add " --skip-show-database" to the startup script of MySQL or add it to the MySQL configuration file
• Grant the SHOW DATABASES privilege only to the users you want to use this command

To disable the usage of the "SHOW DATABASES" command, the following parameter should be added in the [mysqld] section of the /etc/my.cnf:

[mysqld]
skip-show-database

9. Enable Logging

If your database server does not execute many queries, it is recommended that you enable transaction logging, by adding the following line to [mysqld] section of the /etc/my.cnf file:

[mysqld]
log =/var/log/mylogfile

This is not recommended for heavy production MySQL servers because it causes high overhead on the server.

In addition, verify that only the "root" and "mysql" ids have access to these logfiles (at least write access).

Error log

Ensure only "root" and "mysql" have access to the logfile "hostname.err". The file is stored in the mysql data directory. This file contains very sensitive information such as passwords, addresses, table names, stored procedure names and code parts. It can be used for information gathering, and in some cases, can provide the attacker with the information needed to exploit the database, the machine on which the database is installed, or the data inside it.

MySQL log

Ensure only "root" and "mysql" have access to the logfile "*logfileXY". The file is stored in the mysql data directory.

10. Change the root directory

A chroot on Unix operating systems is an operation that changes the apparent disk root directory for the current running process and its children. A program that is re-rooted to another directory cannot access or name files outside that directory, and the directory is called a "chroot jail" or (less commonly) a "chroot prison".

By using the chroot environment, the write access of the MYSQL processes (and child processes) can be limited, increasing the security of the server.

Ensure that a dedicated directory exists for the chrooted environment. This should be something like: /chroot/mysql In addition, to make the use of the database administrative tools convenient, the following parameter should be changed in the [client] section of MySQL configuration file:

[client]
socket = /chroot/mysql/tmp/mysql.sock

Thanks to that line of code, there will be no need to supply the mysql, mysqladmin, mysqldump etc. commands with the --socket=/chroot/mysql/tmp/mysql.sock parameter every time these tools are run.

11. Remove History

During the installation procedures, there is a lot of sensitive information that can assist an intruder to assault a database. This information is stored in the server’s history and can be very helpful if something goes wrong during the installation. By analyzing the history files, administrators can figure out what has gone wrong and probably fix things up. However, these files are not needed after installation is complete.

We should remove the content of the MySQL history file (~/.mysql_history), where all executed SQL commands are stored (especially passwords, which are stored as plain text):

cat /dev/null > ~/.mysql_history

12. Patch your systems

Consult you operation system’s vendor for security and performance updates: use windows update on windows, apt-get or yum on (Debian) systems, Red Hat update Agent on Red hat and so on.

Windows : http://www.windowsupdate.com/

Redhat : http://www.redhat.com/docs/manuals/RHNetwork/ref-guide/3.6/ch-up2date.html

Debian: http://www.cyberciti.biz/faq/rhel-centos-fedora-linux-yum-command-howto/ or https://help.ubuntu.com/8.04/serverguide/C/apt-get.html

MacOS: http://support.apple.com/kb/HT1338

If you are using any kind of virtualization platform, consult your platform vendor for security issues, patches and recommendations.