|< Day Day Up >|
12.1 Securing MySQL
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.
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.
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:
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.
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.
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.
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.
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.
|< Day Day Up >|