Author: Mike Peters
Installation
One key to better security is to run MySQL as its own user. Create such a user and group with the commands:
# 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 you 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 you know the database is
running you 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 that 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
Now create null device:
mknod /chroot/mysql/dev/null c 2 2 chown root:sys /chroot/mysql/dev/null chmod 666 /chroot/mysql/dev/null
Edit the password and groups files to remove any entries except for 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 you need to create a link to
mysql.sock: ln /chroot/mysql/tmp/mysql.sock. /chroot/mysql/tmp/mysql.sock and /chroot/httpd/tmp/
/chroot/httpd/tmp/
need to be on same filesystem. This needs to be done every time you start up the MySQL server.
An example startup script below will handle this.
To run MySQL in a chrooted environment as a user other than root, you need to install the
chrootuid program. Once you’ve installed chrootuid, test the server: chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &. This will run the 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 you 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, you 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, and DELETE 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 then
checks each statement to see if the user is allowed to perform the requested
action.
When creating new MySQL users, always give the users 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 goes 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 creates a MySQL user 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;
makes 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 that 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 usernames and passwords for all users, as well as the hosts from which users can connect. There are are also many fields specifying a wide range of privileges, such as SELECT, INSERT,
DELETE, FILE, and PROCESS. You should examine this table and the MySQL manual
yourself to become familiar with all 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 command 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 so that they become effective:
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, but some bear
special consideration. PROCESS and 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.
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 with system administrative rights or database administrative rights,
such as FILE, GRANT, ALTER, SHOW DATABASE, RELOAD, SHUTDOWN, PROCESS, and SUPER,
should not generally be given to accounts used by specific applications,
especially Web-based applications. Furthermore, accounts for specific
applications should have access only 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 point 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
In addition to MySQL privileges, there are a number of measures you 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 would then be capable of creating files as the root user.
Make sure that only the mysql user has read and write access to the database directory. Data in the database files can be viewed with any text editor, so any user with read or write access to the files could read or alter data, bypassing MySQL’s privileges.
The mysql command history is stored in $HOME/.mysql_history. This file may show 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 using MySQL only on the local machine — for example for PHP Web-based
applications — then in /chroot/mysql/etc/my.cnf add the line skip-networking to the [mysqld] section. This disables all TCP networking features of the MySQL
daemon.
You 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, MySQL administrative programs such as mysqladmin are not in the chroot environment 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 allow only trusted hosts access to MySQL’s port,
3306. Better still is to firewall off your MySQL server altogether and
allow access onlythrough a Secure Shell (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, but 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 you 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
Be sure 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 he is backing up, the SELECT privilege for those tables, and the RELOAD privilege, in order to execute FLUSH TABLES. You can backup a database 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. This should create a backup which is quick and
--add-locks --create-options --disable-keys --extended-insert --lock-tables
--quick --set-charset
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 to run
mysql -u user -p db_name < backup-file.sql. The -p option tells mysql to prompt for a password.
Server startup
The following script can be used to start 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
Using these procedures will reduce the risk of a potential breakin to
your database server. MySQL’s extensive privilege system allows you to protect
the data stored within the database. As always you should remain vigilant, and
be sure to apply patches and upgrades to your server as they become available.
Mike Peters is a freelance consultant and programmer and long-time Linux user.
