Introduction

MySQL is one of the most popular databases on the Internet and it is often used in conjunction with PHP.

This article describes the basic steps which should be performed in order to secure a MySQL database against both local and remote attacks. In order to achieve the highest possible level of security, the installation and configuration of MySQL should be performed in accordance with the following security requirements:

* MySQL database must be executed in a chrooted environment;
* MySQL processes must run under a unique UID/GID that is not used by any other system process;
* Only local access to MySQL will be allowed;
* MySQL root’s account must be protected by a hard to guess password;
* The administrator’s account will be renamed;
* Anonymous access to the database (by using the nobody account) must be disabled;
* All sample databases and tables must be removed.

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

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. Next, we have to build the password database as follows (this applies only to FreeBSD):

cd /chroot/mysql/etc
pwd_mkdb -d /chroot/mysql/etc passwords
rm -rf /chroot/mysql/etc/master.passwd

As in case of the Apache web server, we have to create a special device file /dev/null:

ls -al /dev/null
crw-rw-rw- 1 root sys 2, 2 Jun 21 18:31 /dev/null
mknod /chroot/mysql/dev/null c 2 2
chown root:sys /chroot/mysql/dev/null
chmod 666 /chroot/mysql/dev/null

We must also copy the mysql database, which contains grant tables created during MySQL installation:

cp -R /usr/local/mysql/var/ /chroot/mysql/usr/local/mysql/var
chown -R mysql:mysql /chroot/mysql/usr/local/mysql/var

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.

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.

Configure the database server in compliance with our security requirements.

The main configuration file of mysql 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 PHP 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

An enhancement to this would be to add the directive bind-address=127.0.0.1 in your MySQL server configuration, to force MySQL to bind to the IP address of the local machine and thus ensure that only users on the same system can connect to MySQL.

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

Change admin 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 &

and 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/db
Next, we must 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 admin name
It is also 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

Finally, we should also 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

Remove wildcards in the grant tables

The MySQL access control system works through a series of so-called grant tables, which make it possible to define access levels for each user at the database, table or column level. While these tables do allow administrators to set blanket permissions for a user or set of tables using wildcards, doing so is inherently dangerous because a hacker could use a single compromised account to gain access to other parts of the system. For this reason, be precise when assigning users privileges and always ensure that users have only as much access as they need to perform their tasks.

Use the SHOW PRIVILEGES command for each user account to audit your grant tables and see if the use of wildcard permissions is appropriate.

Check the permissions of configuration files

Both individual users and server administrators store their user account passwords in their per-user MySQL options file. However, this password is stored in plain-text within the file and can easily be read. Therefore, it’s important to ensure that such per-user configuration files are not viewable by other users of the system, and are stored in non-public locations. Ideally, you’d want the per-user configuration to be stored in the user’s home directory with permissions 0600.

Encrypt client-server transmissions

An important issue in the MySQL (and any) client-server architecture involves the security of data being transmitted over the network. If client-server transactions take place in plaintext, it is possible for a hacker to “sniff” the data packets being transmitted and thus gain access to sensitive information. You can close this hole either by enabling SSL in your MySQL configuration, or by using a secure shell utility like OpenSSH to create a secure encrypted “tunnel” for your data to pass through. Encrypting your client-server connection in this manner makes it extremely hard for unauthorised users to read the data going back and forth.

Actively monitor the MySQL access log

MySQL comes with a number of different log files, which keep track of client connections, queries and server errors. Of these, the most important is the general query log, which logs each client connection and disconnection with a timestamp, and also records each query executed by a client.

my.cnf security tweaks.

–allow-suspicious-udfs

This option controls whether user-defined functions that have only an xxx symbol for the main function can be loaded. By default, the option is off and only UDFs that have at least one auxiliary symbol can be loaded; this prevents attempts at loading functions from shared object files other than those containing legitimate UDFs.

–local-infile[={0|1}]

If you start the server with –local-infile=0, clients cannot use LOCAL in LOAD DATA statements.

–old-passwords

Force the server to generate short (pre-4.1) password hashes for new passwords. This is useful for compatibility when the server must support older client programs.

–safe-show-database (OBSOLETE)

In previous versions of MySQL, this option caused the SHOW DATABASES statement to display the names of only those databases for which the user had some kind of privilege.

–safe-user-create

If this option is enabled, a user cannot create new MySQL users by using the GRANT statement unless the user has the INSERT privilege for the mysql.user table or any column in the table. If you want a user to have the ability to create new users that have those privileges that the user has the right to grant, you should grant the user the following privilege:

GRANT INSERT(user) ON mysql.user TO ‘user_name’@'host_name’;

This ensures that the user cannot change any privilege columns directly, but has to use the GRANT statement to give privileges to other users.

–secure-auth

Disallow authentication for accounts that have old passwords.
The mysql client also has a –secure-auth option, which prevents connections to a server if the server requires a password in old format for the client account.

–secure-file-priv=path

This option limits the effect of the LOAD_FILE() function and the LOAD DATA and SELECT … INTO OUTFILE statements to work only with files in the specified directory.

–skip-grant-tables

This option causes the server not to use the privilege system at all. This gives anyone with access to the server unrestricted access to all databases. You can cause a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload command from a system shell, or by issuing a MySQL FLUSH PRIVILEGES statement. This option also suppresses loading of user-defined functions (UDFs).

–skip-name-resolve

Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost.

–skip-networking

Do not allow TCP/IP connections over the network. All connections to mysqld must be made via Unix socket files.

Article Authored by Sibu

Author, Sibu, is a Sr.Systems Engineer with SupportPRO. Sibu specializes in Windows and Linux servers. SupportPRO offers 24X7 technical support services to Web hosting companies and service providers.