12.1.1 Securing MySQL at the Filesystem Level - MySQL

Under multiuser systems such as Unix, all components of a MySQL installation should be owned by a login account with proper administrative privileges. The installation should be accessible to other users only to the extent necessary.

This chapter assumes the existence of such an administrative account and that both its username and group name are mysql. However, the details of creating login accounts vary per version of Unix and are outside the scope of the exam, so they aren't discussed here. Consult the documentation for your operating system.

To have a secure MySQL installation, the following conditions should be satisfied:

• Every MySQL-related directory and file should have its user and group ownerships set to mysql. This includes the MySQL programs, the database directories and files, and the log, status, and configuration files.

  An allowable alternative to having everything owned by the mysql user is that some program and library directories and files may be owned by root. (The principle to follow is that anything the server might need to modify cannot be owned by root.)

• No files should be set to be readable by any user other than mysql except for those that client programs run by other users need to access. This includes global option files, error message files, language files, and character set files.

• In most cases, it's reasonable for client programs and other utilities to be world- executable so that other users with login accounts on the system can run them. Under certain conditions, you might want to restrict access to allow only a subset of the users on the machine to run MySQL programs.

• After you've established the proper filesystem access so that the mysql login account owns the relevant directories and files, the MySQL server should be run using this account. This is important because mysql is a regular login account that has no special filesystem privileges.

  The server should not be run as the system root user. There are many reasons for this; one is that there are operations performed by the server that involve reading or writing files in the server host filesystem. (For example, LOAD DATA INFILE and SELECT ... INTO OUTFILE do so.) Running the server as root is a bad idea because that gives it root privileges and vastly increases the extent of the filesystem that the server can access or modify.

• The server program need not be executable to anyone other than mysql. Its access privileges can be set accordingly.

The following sample procedure shows how to secure the directories and files of a MySQL installation. Before using this procedure, stop the server if it's running. Also, note that some operations must be done from a privileged login account, so you'll need root login access to perform them. The chown and chgrp commands should be run as the system root user because only root can assign directory and file ownership. After directories and files have been set to be owned by mysql, you can set their access permissions by running chmod as either root or mysql.

The procedure assumes that the MySQL base installation directory is /usr/local/mysql. An installation that has the files located elsewhere can be protected by making the appropriate substitutions to the pathnames shown in the commands.

Run the following commands as root to set everything in and under the base installation directory to be owned by user mysql and group mysql:

shell> chown -R mysql /usr/local/mysql
shell> chgrp -R mysql /usr/local/mysql

Then restrict access to the base directory so that only the mysql user has permission to make changes, and so that its subdirectories are accessible only as necessary by other users. The following commands can be run either as mysql or root:

shell> chmod u=rwx,go=rx /usr/local/mysql
shell> chmod u=rwx,go=rx /usr/local/mysql/bin
shell> chmod u=rwx,go-rwx /usr/local/mysql/libexec
shell> chmod -R go-rwx /usr/local/mysql/data

These commands give complete access to the mysql user but restricted access to other users. They also make the base directory and bin directory where the client programs are installed accessible but not modifiable to other users, and make the libexec directory (where the server is installed) and the data directory inaccessible to other users.

You should also protect the global option file, /etc/my.cnf, if it exists. The mysql user should own it and have read/write access to it, but other users need only read access:

shell> chown mysql /etc/my.cnf
shell> chgrp mysql /etc/my.cnf
shell> chmod u=rw,go=r /etc/my.cnf

Before starting the server, you should arrange to have it execute with the privileges of the mysql login account. This can be done either by starting the server while logged in as mysql, or by starting it as root with a --user=mysql option to instruct it to change user from root to mysql during its startup sequence. (It's allowable to start the server as root, but if you do, you should use a --user option to tell the server to change user to the mysql account and give up its special root privileges. Otherwise, the server continues to execute as root, which is dangerous.)

If you have the server set to start automatically during the system boot sequence, the system invokes the server as root and does not allow you to specify any options on the command line. To reliably start the server as the mysql user, it's best to put the --user option in an option file so that the server always uses it whether you start the server manually or automatically. One way to do so is to place the following lines in /etc/my.cnf:

[mysqld]
user=mysql