Securing a New MySQL Installation

Let's begin by covering an administrative task that you should perform immediately after installing MySQL: Making sure that the server cannot be used by just anybody. This is a matter of understanding the MySQL user accounts that are created during installation and setting up passwords for them.

The MySQL installation procedure sets up the server's data directory and populates it with two databases:

A mysql database containing the grant tables that control access by clients to the server
A test database that can be used for testing purposes

If you've just installed MySQL for the first time (for example, using the instructions in Appendix A, "Obtaining and Installing Software"), the grant tables in the mysql database contain accounts in their initial state that allow anyone to connect to the server without a password. This is insecure, so you should assign passwords to these accounts. (If you're upgrading MySQL by installing a newer version on top of an existing installation for which the grant tables are already set up, passwords may already have been assigned.) If you're setting up a second installation on a machine that already has MySQL installed in another location, you'll need to set up passwords for the new server. However, in this case, you might run into the complication noted in "Setting Up Passwords for a Second Server."

Note: Some MySQL installers give you the option of creating passwords during the installation procedure, but even if you've used one of them, the information here helps you better understand the initial MySQL user accounts. The following discussion assumes that you have not yet established any passwords.

For concreteness, the examples in the following discussion assume that you are using MySQL on a machine with a hostname of cobra.snake.net. Whenever you see that hostname in the instructions, substitute the name of your own server host. The examples assume that your MySQL server already has been started, because you'll need to connect to it.

How the Grant Tables Are Set Up Initially

The grant tables in the mysql database are set up during the MySQL installation procedure with two kinds of accounts:

Accounts that have a username of root. These are superuser accounts intended for administrative purposes. The root accounts have all privileges and can do anything, including deleting all your databases. (By the way, the fact that the MySQL and Unix superuser accounts each have the name root is coincidental. Each has exceptional privileges, but they have nothing to do with each other.)
Accounts that are associated with no username at all. These are "anonymous" accounts; they allow people to connect to the server without having accounts explicitly set up for them in advance. Anonymous users usually are given very few privileges, to limit the scope of what they can do. However, on Windows, there is an important exception for which you should take action, as described later.

Every account known to a MySQL server is listed in the user table of its mysql database, so that's where you'll find the initial root and anonymous accounts. None of these accounts have passwords initially, because it's expected that you'll supply your own. Therefore, one of your first acts in administering a MySQL installation should be to establish passwords, at least for the privileged accounts. Otherwise, unauthorized users can gain superuser access to your server easily by connecting as root. After you secure the initial accounts, you can proceed to set up other accounts to allow the members of your user community to connect to the server under names that you specify and with privileges appropriate for what those users should be allowed to do. Instructions for setting up new accounts are given in "Managing MySQL User Accounts."

Each entry (record) in the user table contains a Host value that indicates where a user can connect from, and User and Password values that indicate the name and password the user must give when connecting from that host. The user table also has a number of columns that indicate what superuser privileges each account has, if any.

Under Unix, the data directory is initialized during the installation procedure by the mysql_install_db script. If you install MySQL on Linux from RPM files on or Mac OS X using a DMG package, mysql_install_db is run for you automatically. Otherwise, you run it yourself. See Appendix A for details.

One purpose of mysql_install_db is to set up the grant tables in the mysql database. On a server host named cobra.snake.net, mysql_install_db initializes the user table with the following accounts:

Host
User
Password
Superuser Privileges
localhost
root

All
cobra.snake.net
root

All
localhost

None
cobra.snake.net

None

These account entries allow connections by client programs as follows:

The root entries allow you to connect to the local MySQL server, using a hostname of localhost or cobra.snake.net. For example, if you invoke the mysql program while logged in on cobra.snake.net, you can connect as root using either of these commands:
```
% mysql -h localhost -u root
% mysql -h cobra.snake.net -u root
```
As root, you have all privileges and can perform any operation.
The entries with blank User values are the anonymous accounts. They allow connections to the local MySQL server, using a hostname of localhost or cobra.snake.net, and without specifying any username:
```
% mysql -h localhost
% mysql -h cobra.snake.net
```
Anonymous users have no superuser privileges, but another grant table (the db table, not shown) contains privilege information that indicates that anonymous users can use the test database or any database having a name that begins with test_.

Under Windows, the data directory and the mysql database are included pre-initialized with the MySQL distribution, with account entries that are set up somewhat differently than those on Unix systems. The Windows user table entries look like this:

Host
User
Password
Superuser Privileges
localhost
root

All
%
root

All
localhost

All
%

None

In these entries, the Host value of % acts as a wildcard meaning that connections for the given user are allowed from any host. Thus, the initial Windows user table accounts allow connections as follows:

You can connect as root from the local host, or from any remote host. As root, you have all privileges and can perform any operation.
You can connect anonymously with no username. If you connect from the local host, you will have the same superuser privileges as root and can do anything. If you connect remotely from another host, you will have no superuser privileges. On Windows, the db table allows anonymous users access to the test database or any database having a name that begins with test.

For Windows, an important implication of the fact that one of the root accounts has % for a Host value is that anyone,anywhere can connect as root with no password. This leaves your MySQL server completely vulnerable, so you'll certainly want to lock down that account by giving it a password. In addition, the fact that the localhost anonymous account has the same privileges as root means that it's not sufficient to assign passwords just to the root accounts: You also should establish a password for the local anonymous account to revoke its superuser privileges, or perhaps delete it entirely. The following discussion covers all three options.

Note: Recent versions of MySQL for Windows might not contain the two accounts that have a Host value of %, in which case you need not deal with them in the following discussion.

Establishing Passwords for the Initial MySQL Accounts

This section describes the various methods of setting passwords for the root and anonymous-user accounts. Depending on the method you use, you may also need to tell the server to reload the grant tables so that it notices the change. The server performs access control using in-memory copies of the grant tables. For some methods of changing passwords in the user table, the server may not recognize that you've changed anything, so you must tell it explicitly to re-read the tables.

One way to establish passwords is to use the mysqladmin program:

% mysqladmin -h localhost -u root password "rootpass"
% mysqladmin -h cobra.snake.net -u root password "rootpass"

This works for both Unix and Windows. The word "password" in these commands is a literal word that indicates what you want mysqladmin to do (set a password), and rootpass indicates what you want the password to be. Both mysqladmin commands are necessary. The first sets the password for the root account associated with localhost and the second for the account associated with cobra.snake.net. (On Windows, the second command sets the password for the root account associated with the Host value of %.)

You can use either single quotes or double quotes in the mysqladmin command to quote the password on Unix, but you should use only double quotes on Windows. Windows command interpreters do not recognize single quotes as argument-quoting characters. If you use single quotes, they will become part of your password.

A second way to set the root passwords is to connect to the server as root and issue SET PASSWORD statements. Each statement names the User and Host values of the user table entry that you want to modify, in 'user_name'@'host_name' format. For Unix, connect to the server and change the root passwords like this:

% mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('rootpass');
mysql> SET PASSWORD FOR 'root'@'cobra.snake.net' = PASSWORD('rootpass');

For Windows, use a slightly different second statement, because one of the root accounts has a different Host value:

C:\> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('rootpass');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('rootpass');

Another way to assign passwords is to modify the user table directly. This works for any version of MySQL, and actually may be your only recourse if you have a really old version of MySQL that predates both mysqladmin password and SET PASSWORD. To set the password for both root entries at the same time, do the following:

% mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD('rootpass') WHERE User='root';
mysql> FLUSH PRIVILEGES;

If you use mysqladmin password or SET PASSWORD to change passwords, the server notices that you've made a change to the grant tables and automatically re-reads them to refresh its in-memory copy of the tables. If you use UPDATE to modify the user table directly, it's necessary to tell the server to reload the tables explicitly. One way to do so is to issue a FLUSH PRIVILEGES statement, as shown in the preceding example. You can also use either of these mysqladmin commands to reload the grant tables:

% mysqladmin -u root flush-privileges
% mysqladmin -u root reload

From now on, whenever I say "reload the grant tables," I mean you should use one of the methods just shown; it doesn't matter which one. Examples in the remaining part of this chapter generally use FLUSH PRIVILEGES.

Now that you have set the root password (and reloaded the grant tables if necessary), you'll need to specify your new password whenever you connect to the server as root:

% mysql -p -u root
Enter password: rootpass
mysql>

Another effect of setting the root password is that no one else can connect as root without knowing the password, which is really the point of the exercise.

The need to specify a password when connecting as root from this point on will be true not just for mysql, but also for programs like mysqladmin, mysqldump, and so forth. For brevity, many of the examples in later sections of this chapter do not show the -u or -p options; I assume that you'll add them as necessary whenever you connect to the server as root.

The user table at this stage still contains anonymous-user accounts that have no password. If you have no need for these accounts, I recommend that you delete them entirely. To do so, connect to the server as root (using your new password, of course), delete any rows from the user and db tables that have a blank User value, and reload the grant tables:

% mysql -p -u root
Enter password: rootpass
mysql> USE mysql;
mysql> DELETE FROM user WHERE User = '';
mysql> DELETE FROM db WHERE User = '';
mysql> FLUSH PRIVILEGES;

The main benefit of removing the anonymous-user accounts is that it significantly simplifies the task of setting up non-anonymous accounts. Otherwise, you will have to deal with the curious phenomenon described in "A Privilege Puzzle," in Chapter 12. You can read that section for the details behind this phenomenon.

If you prefer not to delete the anonymous-user accounts, you can assign passwords to them to make them more secure. The following commands connect to the server as root and set up a password, anonpass, for both anonymous accounts at once:

% mysql -p -u root
Enter password: rootpass
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD('anonpass') WHERE User='';
mysql> FLUSH PRIVILEGES;

If you leave the anonymous-user accounts in place on a Windows system, remember that the local anonymous user has the same privileges as root, which may be more access than you care to allow. To weaken that account to the same strength as the one for the remote anonymous user, revoke its superuser privileges by connecting to the server as root and issuing these statements:

mysql> REVOKE ALL ON *.* FROM ''@'localhost';
mysql> REVOKE GRANT OPTION ON *.* FROM ''@'localhost';

Setting Up Passwords for a Second Server

The preceding instructions assume that you're establishing passwords on a system that hasn't had MySQL installed on it before. However, if MySQL is already installed in one location and you're setting the passwords for a new server installed in a second location on the same machine, you may find when you attempt to connect to the new server without a password that it rejects the attempt with the following error:

% mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost'
(using password: YES)

Hm! Why did the server say it received a password, when you didn't specify one? This usually indicates that you have an option file set up that contains the password for accessing the previously installed server. mysql finds the option file and automatically uses the password listed there. To override that and explicitly specify "no password," use a -p option and press Enter when mysql prompts for the password:

% mysql -p -u root
Enter password:             just press Enter

You can use this strategy when you invoke mysqladmin or other MySQL client programs as well.

Additional discussion on using several servers can be found in "Running Multiple Servers."