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.

1. 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

2. 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';

3. 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 = '';

4. 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';

5. 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;

6. 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:

• ENCODE() and DECODE()

• DES_ENCRYPT() and DES_DECRYPT()

• AES_ENCRYPT() and AES_DECRYPT()

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.

Please check back next week for the continuation of this article.