Securing a New MySQL Installation

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

• A mysql database that contains the grant tables

• 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 will be in their initial state that allows anyone to connect to the server without a password. This is insecure, so you should set up some passwords. 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 may run into the complication noted in the "Setting Up Passwords for a Second Server" section later in this chapter. 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, you can skip this section.

The examples in the following discussion use a MySQL server hostname of cobra.snake.net; change the instructions to use your own hostname. The examples also assume that your server is running, 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. They 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're useful for testing because 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, for Windows, there is an important exception that you may want to take action on, 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 root access to your server easily. 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 the "Managing MySQL User Accounts" section later in this chapter.)

Entries in the user table contain a Host value indicating where a user can connect from, and User and Password values indicating the name and password the user must give when connecting from that host. The user table also has a number of columns indicating what superuser privileges the account has, if any.

Under UNIX, the data directory is initialized during the installation procedure by running mysql_install_db, a script that sets up the grant tables in the mysql database. mysql_install_db initializes the user table as follows:

Host	User	Password	Superuser Privileges
localhost	root		All
cobra.snake.net	root		All
localhost			None
cobra.snake.net			None

These entries allow connections 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, from cobra.snake.net you can connect as root with the mysql program using either of the following 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 server without 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) specifies 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 accounts 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 the user named by the User value can connect from any host. Thus, the initial Windows user table entries specify accounts 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 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. It's also a good idea to establish a password for the local anonymous account to revoke its superuser privileges or perhaps to delete it entirely. The following discussion covers all three options.

Establishing Passwords for the Initial MySQL Accounts

This section describes the various methods for setting passwords for the root 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 reread the tables.) This section also suggests some options for dealing with the anonymous superuser account that is present initially in the user table on Windows.

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 represents the value to which you want to set the password. 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 %.)

A second way to set the passwords is to 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, change the 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 rereads 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, if you have MySQL 3.22.9 or later, is to issue a FLUSH PRIVILEGES statement, as shown in the preceding example. You can also use mysqladmin to reload the grant tables:

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

reload should work for any version of MySQL; flush-privileges is available as of MySQL 3.22.12. From now on, whenever I say "reload the grant tables," I mean you should use one of the three methods just shown; it doesn't matter which one. (Examples in the remaining part of this chapter generally use FLUSH PRIVILEGES.)

After 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 will be able to 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 on. For brevity, many of the examples in the rest of this chapter do not show the -u or -p options; I assume you'll add them as necessary whenever you connect to the server as root.

The user table at this stage still contains anonymous user entries that have no password. If you have no need for these entries, consider deleting them entirely. To do this, 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; 

If you leave the anonymous user accounts in place, remember that the local anonymous user has the same privileges as root on Windows, 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'; 

Another option for dealing with this account is to assign it a password; for example:

 mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('anonpass');  

An implication of leaving the anonymous users in place is that they result in the curious phenomenon described in the "A Privilege Puzzle" section in Chapter 12. But you can read that section another time, after you have read the more general background material later in this chapter that describes how to set up new accounts.

Setting Up Passwords for a Second Server

The preceding instructions assume that you want to establish 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 that when you attempt to connect to the new server without a password, it rejects the attempt with the following error:

 % mysql -u root  ERROR 1045: 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? What this usually indicates is that you have an option file set up that lists 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 for mysqladmin and for other MySQL programs as well.

Additional discussion on using several servers can be found in the "Running Multiple Servers" section later in this chapter.