Securing MySQL on Linux

How to install MySQL securely with Apache on a Linux server.

by Mike Peters, August 2004
(Originally published on Linux.com)

Introduction

MySQL is a very popular open source database. Due to its speed and stability it is used on millions of servers world wide. MySQL has a simple and effective security mechanism, however, many measures need to be taken to make a default installation secure. Whilst the measures described below will enable you to secure your database it is also important that you secure the underlying operating system as much as possible too.

Installation

It is important to run MySQL as its own user. In order to do so we need to create such a user and group.

# groupadd mysql
# useradd -c "MySQL Server" -d /dev/null -g mysql -s /bin/false mysql

Install MySQL in /usr/local/mysql

./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql \
    --with-unix-socket-path=/tmp/mysql.sock --with-mysqld-ldflags=-all-static
make
su
make install
strip /usr/local/mysql/libexec/mysqld
scripts/mysql_install_db
chown -R root /usr/local/mysql
chown -R mysql /usr/local/mysql/var
chgrp -R mysql /usr/local/mysql

The configure option --with-mysqld-user=mysql enables MySQL to run as the mysql user. The --with-mysqld-ldflags=-all-static option makes it easier to chroot MySQL.

Copy the example configuration file from the MySQL source, support-files/my-medium.cnf, to /etc/my.cnf and set the appropriate permissions, chmod 644 /etc/my.cnf.

Once we have MySQL installed, test the installation. Start MySQL with /usr/local/mysql/bin/mysqld_safe & and log on as the root user, mysql -u root. If you see the MySQL prompt we know the database is running we can proceed to chroot it. If the installation is not working examine the log files to find out what the problem is. Otherwise shutdown the server, usr/local/mysql/bin/mysqladmin -u root shutdown

Chrooting MySQL

First, create the necessary directory structure for the database.

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

Now set the correct directory permissions

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

Once the directories are set up, copy the server's files:

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 -r /usr/local/mysql/share/mysql/charsets /chroot/mysql/usr/local/mysql/share/mysql/
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/

Finally, copy the mysql databases which contain the grant tables storing the MySQL access privileges:

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

As with Apache, we need to create null device:

mknod /chroot/mysql/dev/null c 2 2
chown root:sys /chroot/mysql/dev/null
chmod 666 /chroot/mysql/dev/null

We need to edit the password and groups files to remove any entries bar the mysql user and group.

/etc/passwd:
mysql:x:12347:12348:MySQL Server:/dev/null:/bin/false

/etc/group:
mysql:x:12347:

In order for PHP to be able to access MySQL we need to create a link to mysql.sock, ln /chroot/mysql/tmp/mysql.sock /chroot/httpd/tmp/. /chroot/mysql/tmp/mysql.sock and /chroot/httpd/tmp/ need to be on same filesystem. This needs to be done every time we startup the MySQL server (the example startup script below handles this).

To run MySQL in a chrooted environment as a user other than root, we need the chrootuid program. Once we have installed chrootuid, test the server: chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &. This will run our server as the mysql user.

The MySQL root User and Default Accounts

The MySQL root user should not be confused with the system root user. By default, the MySQL root user has no password. You can check this with mysql -u root, if you get a mysql prompt, no root password is set. The first thing we should do is set a strong password for this user. Never give the system root password to the MySQL root user.

To set the initial root password open a mysql prompt, mysql -u root mysql, and enter the following:

mysql> UPDATE user SET Password=PASSWORD('new_password')
    ->             WHERE user='root';
mysql> FLUSH PRIVILEGES;

Don't forget to FLUSH PRIVILEGES; to make the privileges effective.

As well as setting the root password, we should remove anonymous accounts:

mysql> DELETE FROM user WHERE User = '';
mysql> FLUSH PRIVILEGES;

Alternatively set a password for the anonymous accounts:

mysql> UPDATE user SET Password = PASSWORD('new_password')
    ->     WHERE User = '';
mysql> FLUSH PRIVILEGES;

MySQL Privilege System and MySQL Users

The MySQL privilege system allows for authentication of users connecting from specific hosts. Authenticated users can be assigned privileges such as SELECT, INSERT, UPDATE, DELETE etc on a per database, table, column or host basis. When a user connects, MySQL first checks if that user is authorized to connect, based on the host and supplied password. If the user is allowed to connect, MySQL will then check each statement to see if the user is allowed to perform the requested action.

When creating new MySQL users, always give the user a strong password and never store passwords as plain text. Only allow the minimum amount of privileges for a user to accomplish a task and set those privileges on a per database basis. Some extra time spent planning what privileges to assign to users will go a long way in ensuring the security of your data.

You can create a new user with specific privileges using the GRANT statement. For example:

GRANT USAGE ON myapp.* TO 'someuser'@'localhost' IDENTIFIED BY 'some_pass';
FLUSH PRIVILEGES;

This statement will create a user MySQL named someuser who has access to all tables in the myapp database. The USAGE option sets all of the user's privileges to 'No', meaning you must enable specific privileges later. You may replace USAGE with a list of specific privileges. IDENTIFIED BY 'some_pass' sets the accounts password to 'some_pass', GRANT automatically encrypts the password for you. Finally, this user can only connect from localhost. FLUSH PRIVILEGES; is needed to make privilege changes effective.

MySQL access privileges are stored in the grant tables of the mysql database. You should never grant normal users privileges to edit entries in the mysql database. That right should be reserved for the root user. There are several tables in the mysql database which allow for a fine grained level of control over user privileges.

The user table is the most important of the MySQL grant tables. It contains the username and password for the user as well as the host from which a user can connect. There are are also many fields specifying a wide range of privileges such as SELECT, INSERT, DELETE, FILE, PROCESS. You should examine this table and the MySQL manual yourself to become familiar with all of the options available. Setting a value of 'N' for a field disables the privilege and 'Y' enables it.

You can change privileges using an SQL UPDATE query or the GRANT statement. If you are using SQL statements such as UPDATE or INSERT to update or set user passwords, be sure to use the PASSWORD() function to encrypt the password in the database. Finally, remember to FLUSH PRIVILEGES; for any changes you make to become effective. eg

UPDATE user SET Host='localhost', Password=PASSWORD('new_pass'), 
  Reload_priv='Y', Process_priv='Y' WHERE
User='admin';
FLUSH PRIVILEGES;

Of the different privileges, most are self-explanatory, however some bear special consideration. The PROCESS or SUPER should never be given to untrusted users. A user with these privileges may run mysqladmin processlist which shows a list of currently executing queries. This list could potentially reveal sensitive data such as passwords.

The FILE should also not be granted lightly. This privilege allows users to read and write files anywhere on the filesystem to which the mysqld process has access.

Privileges which system administrative rights or database administrative rights, such as FILE, GRANT, ALTER, SHOW DATABASE, RELOAD, SHUTDOWN, PROCESS, SUPER, should not generally be given to accounts used by specific applications, especially web based applications. Furthermore, accounts for specific applications should only have access to the databases related to that specific application.

The other tables in the mysql database give an even finer grained level of control over privileges:

db - controls the access of users to specific databases.

tables_priv - controls the access of users to specific tables.

columns_priv - controls the access of users to specific columns of a table.

hosts - specify the actions which can be performed from a particular host.

One final thing to note is that, if you don't completely trust your DNS, use IP numbers in grant tables in place of host names. This makes it more difficult to spoof hosts.

Local Security

There are a number of measures we need to take to improve security on the local machine. Most importantly, never run mysqld as root as, among other risks, any user with the FILE privilege will then be capable of creating files as the root user.

We should also make sure that only the mysql user has read write access to database directory. Data in the database files can easily be viewed with any text editor, therefore any user with read or write access to the files could read or alter data, by-passing MySQL's privileges.

The mysql command history is stored in $HOME/.mysql_history. This may show up sensitive information such as passwords. You should clear the file with echo > $HOME/.mysql_history. To prevent the file being written to in the future, link the .mysql_history files of administrative users to /dev/null, ln -s /dev/null .mysql_history

If you are only using MySQL on the local machine, for example, for PHP web based applications, in /chroot/mysql/etc/my.cnf add the line skip-networking to the [mysqld] section. This will disable all TCP networking features of the MySQL daemon.

We can also disable the use of the LOAD DATA LOCAL INFILE command which allows reading of local files and is potentially dangerous. Add the line set-variable=local-infile=0 to the [mysqld] section of /chroot/mysql/etc/my.cnf.

Finally, add the line socket = /chroot/mysql/tmp/mysql.sock to the [client] section of /etc/my.cnf. Notice that we are adding this line to /etc/my.cnf not /chroot/mysql/etc/my.cnf. This is because, while the MySQL server daemon will use /chroot/mysql/etc/my.cnf, our MySQL administrative programs such as mysqladmin are not in our chroot and will therefore read configuration from /etc/my.cnf.

Securing Remote Access

The most important step in securing remote access to your MySQL server is in having a firewall. Your firewall should only allow trusted hosts access to MySQL's port, 3306. Better still, is to firewall off your MySQL server altogether and only allow access through an SSH tunnel as described below.

Always use passwords for user accounts, even for trusted client programs. The password in a mysql connection is sent encrypted, however, in versions prior to 4.1.1 encryption was not particularly strong. In version 4.1.1 the encryption algorithm was much improved.

Even though the password is sent encrypted, data is sent as plain text. If you are connecting across an untrusted network, you should use an SSH encrypted tunnel. SSH tunneling allows us to connect to a MySQL server from behind a firewall, even when the MySQL port is blocked. To set up tunnel, use the command ssh ssh_server -L 5001:mysql_server:3306 sleep 99999. You need not have direct access to mysql_server, provided ssh_server does. Now you can connect to port 5001 on the local machine with your favorite database client and the connection will be forwarded silently to the remote machine in an encrypted ssh tunnel.

Backup

It is important to make regular backups of your databases. MySQL includes two utilities which make this easy, mysqlhotcopy and mysqldump.

To use mysqlhotcopy, a user needs access to the files for the tables that they are backing up, the SELECT privilege for those tables, and the RELOAD privilege (in order to execute FLUSH TABLES). A database can be backed up using mysqlhotcopy db_name [/path/to/backup_db_dir].

mysqldump supports more options and is especially useful for copying databases between servers, backing up multiple databases at once or making backups of the database structure only. Databases can be backed up using one of the following commands:

mysqldump [options] db_name [tables]
mysqldump [options] --databases DB1 [DB2 DB3...]
mysqldump [options] --all-databases

For example, you can back-up all your databases and compress them in one go with the command: date=`date -I`; mysqldump --opt --all-databases -u user --password="your_pass" | bzip2 -c > databasebackup-$date.sql.bz2

The --opt option is shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. This should create a back-up which is quick and easy to restore. In fact this option is enabled by default in versions 4.1 and later, you can disable it with --skip-opt.

To restore a database from a file created by mysqldump you just need mysql -u user -p db_name < backup-file.sql. The -p option will have mysql prompt for a password.

Server Startup

The following script can be used for starting your MySQL server.

#!/bin/sh

CHROOT_MYSQL=/chroot/mysql
CHROOT_PHP=/chroot/httpd
SOCKET=/tmp/mysql.sock
MYSQLD=/usr/local/mysql/libexec/mysqld
PIDFILE=/usr/local/mysql/var/`hostname`.pid
CHROOTUID=/usr/local/sbin/chrootuid

echo -n " mysql"

case "$1" in
start)
        rm -rf ${CHROOT_PHP}/${SOCKET}
        nohup ${CHROOTUID} ${CHROOT_MYSQL} mysql ${MYSQLD} >/dev/null 2>&1 &
        sleep 5 && ln ${CHROOT_MYSQL}/${SOCKET} ${CHROOT_PHP}/${SOCKET}
        ;;
stop)
        kill `cat ${CHROOT_MYSQL}/${PIDFILE}`
        rm -rf ${CHROOT_MYSQL}/${SOCKET}
        ;;
*)
        echo ""
        echo "Usage: `basename $0` {start|stop}" >&2
        exit 64
        ;;
esac

exit 0

Summary

The procedures we have seen will reduce the risk of a potential break in to our database server. MySQL's extensive privilege system allows us to protect the data stored within our database. As always we should remain diligent, and be sure to apply patches and upgrades to our server as and when they become available.