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:
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:
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:
These account entries allow connections by client programs as follows:
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:
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:
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.
% 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');
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>
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
Additional discussion on using several servers can be found in "Running Multiple Servers."