12.1 Securing MySQL
Before you can consider a MySQL installation ready for general use, several security-related issues must be considered and addressed:
If you're running MySQL on a multiuser system, you should protect the directories and files that are part of the installation against unauthorized access by users who are not responsible for database administration. To do this, 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. The initial MySQL accounts that are set up during the MySQL installation procedure have no passwords. Some of these accounts have full privileges for server access and should be assigned passwords to prevent unauthorized clients from connecting to the server and gaining complete access to it.
The directories and files of a MySQL installation can be protected by changing their ownership and access permissions before running the server, but setting passwords for the MySQL root accounts can be done only while the server is running. Consequently, before starting the server and setting passwords, you should take any actions necessary to protect MySQL-related portions of the filesystem. If you set the passwords first, before protecting the files in which the grant tables are stored, it's possible for someone with direct filesystem access on the server host to replace the grant tables. This compromises your MySQL installation and undoes the effect of setting the passwords.
12.1.1 Securing MySQL at the Filesystem Level
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
12.1.2 Securing the Initial MySQL Accounts
The MySQL server controls client access using the mysql database, which contains several tables known as grant tables. Privileges listed in the grant tables are tied to accounts, each of which is defined by a username and a hostname. That is, a MySQL account depends not only on your username, but the client host from which you connect to the server.
The MySQL installation procedure sets up several initial accounts in the grant tables. These accounts have no passwords at first. You should assign passwords at least to those accounts that have administrative privileges. This is true no matter what platform you run MySQL on, whether Windows or Unix.
The accounts that the MySQL installation procedure creates in the mysql database are of two kinds:
Accounts with a username of root. These are superuser accounts that have full access to the server's capabilities. Anonymous accounts with a blank username. An anonymous account allows a client to connect with any username if it connects from the host listed in the account record. In most cases, anonymous users have limited privileges. These accounts can access the test database. They also can access other databases having names that begin with test (on Windows) or other databases having names that begin with test_ (on Unix). On Windows, the anonymous account for connecting from the local host actually is fully equivalent to a root account with respect to the privileges that it has, so it can access any database.
As already mentioned, none of the initial MySQL accounts have passwords. You should assign passwords immediately to at least the root accounts to prevent other people from connecting to the server as root and gaining complete control over it. On Windows, you should also either assign a password to the anonymous account that has superuser privileges or remove the account.
There are various ways to set up MySQL passwords:
Use GRANT statements Use SET PASSWORD statements Use mysqladmin password commands Modify the grant tables directly with UPDATE statements
Generally, it's preferable to use one of the first three methods and to avoid modifying the grant tables directly. For example, after installing MySQL, a simple procedure to protect the root accounts by assigning them passwords is to use these two mysqladmin password commands, where rootpass represents the password and host_name is the hostname of your machine:
shell> mysqladmin -u root password 'rootpass'
shell> mysqladmin -u root -h host_name password 'rootpass'
However, these commands will not take care of the anonymous accounts. The following procedure secures all the initial accounts. It also serves to demonstrate how modifying the grant tables directly can be useful.
On the server host, connect to the server as the MySQL root user to access the grant tables in the mysql database. Initially, because the accounts have no password, you can connect as follows without specifying a password option:
shell> mysql -u root mysql
Account names and passwords are stored in the user table of the mysql database. Modify the user table records for root to assign a password. The following statement represents this password as rootpass:
mysql> UPDATE user SET Password = PASSWORD('rootpass')
-> WHERE User = 'root';
If you want to assign passwords to the anonymous accounts, do so as follows, where anonpass represents the anonymous-user password:
mysql> UPDATE user SET Password = PASSWORD('anonpass')
-> WHERE User = '';
On Windows, one of the anonymous user accounts has root privileges. You should either assign it a password or remove it. To assign a password, use this statement:
mysql> UPDATE user SET Password = PASSWORD('rootpass')
-> WHERE User = '' AND Host = 'localhost';
To delete the account instead, use this statement:
mysql> DELETE FROM user WHERE User = '' AND Host = 'localhost';
If you want to see what effect the preceding operations have on the user table, issue this statement:
mysql> SELECT Host, User, Password FROM user;
Finally, flush the grant tables:
mysql> FLUSH PRIVILEGES;
The reason for flushing the grant tables is that the server makes access-control decisions based on in-memory copies of the grant tables. The FLUSH statement tells the server to create new in-memory copies from the on-disk tables that were changed by the previous steps.
After setting the root account passwords, you'll need to supply the rootpass password whenever you connect to the server with a username of root. Similarly, to connect using an anonymous-user account, you'll need to specify a password of anonpass.
On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. This script can do any of the following:
Set a password for the root account. Remove any remotely accessible root accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The result is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attackers. Remove the anonymous user accounts. Remove the test database. (If you remove the anonymous accounts, you might also want to remove the test database to which they have access.)
MySQL encrypts passwords in the grant tables using the PASSWORD() function. This function should be considered for use only for managing MySQL accounts, not for general user applications. One reason for this is that applications often require reversible (two-way) encryption, and PASSWORD() performs irreversible (one-way) encryption. Another reason that applications should avoid reliance on PASSWORD() is that its implementation might change. (In fact, it does change in MySQL 4.1.)
Other than the encryption of Password column values in the user table, the server performs no encryption on the contents of MySQL tables. For applications that work with data that must not be stored in unencrypted form, MySQL provides several pairs of functions to perform two-way encryption and decryption:
Cryptographically, AES_ENCRYPT() and AES_DECRYPT() can be considered the most secure of the pairs. DES_ENCRYPT() and DES_DECRYPT() can be used if SSL support is enabled. Other details can be found in the MySQL Reference Manual.
| 
