MySQL Database Asset Protection Standards:

Introduction

MySQL is one of the most popular databases on the Internet and it is often used in conjunction with PHP. Besides its undoubted advantages such as easy of use and relatively high performance, MySQL offers simple but very effective security mechanisms. Unfortunately, the default installation of MySQL, and in particular the empty root password and the potential vulnerability to buffer overflow attacks, makes the database an easy target for attacks.

This article describes the basic steps which should be performed in order to secure a MySQL database against both local and remote attacks.

Chrooting the server

The first step of securing MySQL is to prepare the chrooted environment, in which the MySQL server will run.

Operating system

The methods presented should also apply on most modern UNIX and UNIX-like systems.

Prepare Chroot Environment

In order to prepare the chrooted environment, we must create the following directory structure:

mkdir -p /chroot/mysql/dev
mkdir -p /chroot/mysql/etc
mkdir -p /chroot/mysql/tmp
mkdir -p /chroot/mysql/var/tmp
mkdir -p /chroot/mysql/usr/local/mysql/libexec
mkdir -p /chroot/mysql/usr/local/mysql/share/mysql/english

Set Access Rights

The access rights to the above directories should be set as follows:

chown -R root:sys /chroot/mysql
chmod -R 755 /chroot/mysql
chmod 1777 /chroot/mysql/tmp

Create Directory Structure

Next, the following files have to be copied into the new directory structure:

cp /usr/local/mysql/libexec/mysqld /chroot/mysql/usr/local/mysql/libexec/
cp /usr/local/mysql/share/mysql/english/errmsg.sys /chroot/mysql/usr/local/mysql/share/mysql/english/
cp /etc/hosts /chroot/mysql/etc/
cp /etc/host.conf /chroot/mysql/etc/
cp /etc/resolv.conf /chroot/mysql/etc/
cp /etc/group /chroot/mysql/etc/
cp /etc/master.passwd /chroot/mysql/etc/passwords
cp /etc/my.cnf /chroot/mysql/etc/

Tighten Passwords and Groups

From the files: /chroot/mysql/etc/passwords and /chroot/mysql/etc/group we must remove all the lines except the mysql account and group.

Localization

If any language other than English will be used, we should copy the proper charsets from the /usr/local/mysql/share/mysql/charsets directory as well.

Test the Configuration

At this point MySQL is ready to run in the chrooted environment. We can test if it runs correctly by executing the following command:

chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &

If any error occurs, we should use the truss command or an alternative, such as ktrace/kdump, strace, etc. This will help us to determine and eliminate the cause of the problems.

Notice, that in order to run the mysqld process, the chrootuid program was used instead of chroot, as in case of Apache or PHP. The main difference is that chrootuid changes the owner of the executing process. In our example, mysqld is being executed in a chrooted environment, but the owner of the process is not root, but mysql user. The chrootuid is not installed by default in many operating systems and it may be necessary to download and install this program manually. The chrootuid software can be downloaded here.

Configuring the Server

The next step is to configure the database server in compliance with our security requirements. In case of default installation of MySQL, the main configuration file is /etc/my.cnf. In our case, however, because of running the server in a chrooted environment, we will use two configuration files: /chroot/mysql/etc/my.cnf and /etc/my.cnf. The first one will be used by MySQL server, and the latter will be used by MySQL tools (e.g. mysqladmin, mysql, mysqldump etc.). In both cases, some configuration changes will be required.

Disable Remote Access

The first change applies to the 3306/tcp port, on which MySQL listens by default. Because, according to the initial assumptions, the database will be used only by locally installed applications, we can freely disable listening on that port. This will limit possibilities of attacking the MySQL database by direct TCP/IP connections from other hosts. Local communication will be still possible throw the mysql.sock socket. In order to disable listening on the mentioned port, the following parameter should be added to the [mysqld] section of /chroot/mysql/etc/my.cnf:

skip-networking

If, for some reason, remote access to the database is still required (e.g. to perform remote data backup), the SSH protocol can be used as follows:

backuphost$ ssh mysqlserver /usr/local/mysql/bin/mysqldump -A > backup

Improve Local Security

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

For that purpose, the following parameter should be added in the [mysqld] section in /chroot/mysql/etc/my.cnf:
set-variable=local-infile=0

In addition, to make the use of the database administrative tools convenient, the following parameter should be changed in the [client] section of /etc/my.cnf:

socket = /chroot/mysql/tmp/mysql.sock

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

Change Administrator Password

One of the most important steps in securing MySQL is changing the database administrator's password, which is empty by default. In order to perform that, we should run MySQL (if it is not already running):

chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &

Change the administrator's password as follows:

/usr/local/mysql/bin/mysql -u root mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

It is good practice not to change passwords from the command line, for example, by using the "mysqladmin password" command. This is especially important when other users work on the server. In that case the password could be easily revealed, e.g. by using the "ps aux" command or reviewing history files (~/.history, ~/.bash_history etc), when improper access rights are set to them.

Remove Default Users and Database

Remove the sample database (test) and all accounts except the local root account:

mysql> drop database test;
mysql> use mysql;
mysql> delete from db;
mysql> delete from user where not (host="localhost" and user="root");
mysql> flush privileges;

This will prevent the database from establishing anonymous connections and -- irrespective of the skip-networking parameter in /chroot/mysql/etc/my.cnf -- remote connections as well.

Change Administrator Name

It is recommended to change the default name of administrator's account (root), to a different, harder to guess one. Such a change will make it difficult to perform brute-force and dictionary attacks on the administrator's password. In this case the intruder will have to guess not only the password, but first and foremost, the name of the administrator's account.

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

Remove History

Remove the content of the MySQL history file (~/.mysql_history), in which all executed SQL commands are being stored (especially passwords, which are stored as plain text):

cat /dev/null > ~/.mysql_history

Final Steps

At this point we can create all databases and accounts which will be used by specific PHP applications. It should be emphasized that these accounts should have access rights only to the databases which are used by the PHP applications. In particular, they should not have any access rights to the mysql database, nor any system or administrative privileges (FILE, GRANT, ALTER, SHOW DATABASE, RELOAD, SHUTDOWN, PROCESS, SUPER etc.).

At last, we should also create a shell script that will be used to run MySQL during operating system start up.

You may download the script here: Media:MySQL-CHROOT.txt

Summary

Applying the methods described in the article allows us to significantly increase the security of MySQL. By running the database in a chrooted environment, disabling listening on 3306/tcp port and applying strong passwords to users' accounts we can make the database immune to a many of the attacks that would be possible with the default installation.

Although no method will let us achieve 100% security, applying the outlined methods will at least limit attack possibilities from users who visit our web servers with unfair intentions.