Securing MySQL - dobar clanak za pocetnike i one malo starije

Securing MySQL

Thursday August 19, 2004 (03:01 PM GMT)

By: Mike Peters

Thanks to its speed and stability, MySQL has earned a place on millions of servers worldwide. MySQL has a simple and effective security mechanism, but administrators must perform a few additional tasks to make a default installation truly secure. The measures we'll talk about below will enable you to better secure your database, but be sure to secure the underlying operating system too.

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/httpd/tmp/. /chroot/mysql/tmp/mysql.sock and /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 --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. This should create a backup 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 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.