35.3. Filesystem Security

Under multiuser systems such as Unix, all components of a MySQL installation should be owned by a login account with proper administrative privileges, to protect it against unauthorized access by users that are not responsible for database administration. The installation should be accessible to other users only to the extent necessary. To achieve this objective, set up a dedicated login account to use for administering MySQL and give that account ownership of the relevant files. An additional benefit of setting up this account is that you can use it to run the MySQL server, rather than running the server from the Unix root account. A server that has the privileges of the root login account has more filesystem access than necessary and constitutes a security risk.

This section 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 are not 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.

• Files that only the server should be able to access should be owned by the mysql account and readable only by it. This includes any option files that contain replication passwords, and the master.info replication file on slave servers, which also contains a replication password.

• 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. Files that must be accessible to other users include the Unix socket file, 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 doing so 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. To protect an installation that has the files located elsewhere, make the appropriate substitutions to the pathnames shown in the commands.

Run the following commands as root to set everything in and under the 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 installation 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 restrict access by other users. They also make the installation directory and bin directory where the client programs are installed accessible to but not modifiable to other users, and make the libexec directory (where the server is located) 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 

Each MySQL user on Unix that has a personal option file (~/.my.cnf) should be made aware that the file should be inaccessible to other users, to prevent exposure of any MySQL passwords stored in the file. Each user can issue the following command to set the option file access mode properly:

 shell> chmod u=rw,go-rwx ~/.my.cnf 

On Windows, the following measures enable you to increase the security of your MySQL installation:

• Use NTFS as the filesystem on the volume where MySQL is installed. NTFS supports access controls and allows data encryption. If you're considering enabling encryption, however, remember that although it increases security, it also lowers performance. Because of this tradeoff, you probably don't want to enable encryption if the server is used for high-volume database processing.

• Create a limited-privilege Windows account to use for running MySQL. Remove permissions on the MySQL installation directory and its contents except for this account.