2.10.4 Securing the Initial MySQL Accounts

The MySQL installation process involves initializing the data directory, including the mysql database containing the grant tables that define MySQL accounts. For details, see Section 2.10, “Postinstallation Setup and Testing”.

This section describes how to assign passwords to the initial accounts created during the MySQL installation procedure, if you have not already done so.

Note

On Windows, you can also perform the process described in this section during installation with MySQL Installer (see Section 2.3.3, “MySQL Installer for Windows”). On all platforms, the MySQL distribution includes mysql_secure_installation, a command-line utility that automates much of the process of securing a MySQL installation. MySQL Workbench is available on all platforms, and also offers the ability to manage user accounts (see Chapter 30, MySQL Workbench ).

Passwords may have already been assigned under these circumstances:

• Installation On Windows performed using MySQL Installer give you the option of assigning passwords.

• Installation on Linux using a server RPM or Debian distribution from Oracle. See instructions given in Section 2.5.5, “Installing MySQL on Linux Using RPM Packages from Oracle”, Section 2.5.1, “Installing MySQL on Linux Using the MySQL Yum Repository”, Section 2.5.6, “Installing MySQL on Linux Using Debian Packages from Oracle”, or Section 2.5.3, “Installing MySQL on Linux Using the MySQL APT Repository” for how to find the randomly-generated password (for RPM installations) or how to set the password (for Debian installations).

• As of MySQL 5.7.6, if you initialized the data directory manually using mysqld --initialize, a random password should have been generated for the initial account; see Section 2.10.1.1, “Initializing the Data Directory Manually Using mysqld” for details.

The mysql.user grant table defines the initial MySQL user accounts and their access privileges. Current versions of MySQL 5.7 create only a 'root'@'localhost' account, but for earlier versions, there might be multiple accounts such as described here:

• Some accounts have the user name root. These are superuser accounts that have all privileges and can do anything. If these root accounts have empty passwords, anyone can connect to the MySQL server as root without a password and be granted all privileges.

  • On Windows, root accounts are created that permit connections from the local host only. Connections can be made by specifying the host name localhost, the IP address 127.0.0.1, or the IPv6 address ::1. If the user selects the Enable root access from remote machines option during installation, the Windows installer creates another root account that permits connections from any host.

  • On Unix, each root account permits connections from the local host. Connections can be made by specifying the host name localhost, the IP address 127.0.0.1, the IPv6 address ::1, or the actual host name or IP address.

  • The 'root'@'localhost' account also has a row in the mysql.proxies_priv table that enables granting the PROXY privilege for ''@'', that is, for all users and all hosts. This enables root to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. See Section 6.3.10, “Proxy Users”.

• If accounts for anonymous users were created, these have an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.

  • On Windows, there is one anonymous account that permits connections from the local host. Connections can be made by specifying a host name of localhost.

  • On Unix, each anonymous account permits connections from the local host. Connections can be made by specifying a host name of localhost for one of the accounts, or the actual host name or IP address for the other.

Checking Which Accounts Exist

Start the server if it is not running. For instructions, see Section 2.10.2, “Starting the Server”.

Assuming that no root password has been assigned, you should be able to connect to the server as root without one:

shell> mysql -u root

Once connected, determine which accounts exist in the mysql.user table and whether their passwords are empty:

• As of MySQL 5.7.6, use this statement:

  mysql> SELECT User, Host, HEX(authentication_string) FROM mysql.user;

  The statement uses HEX() because passwords stored in the authentication_string column might contain binary data that does not display well.

• Before MySQL 5.7.6, use this statement:

  mysql> SELECT User, Host, Password FROM mysql.user;

The SELECT statement results can vary depending on your version of MySQL and installation method. The following example output includes several root and anonymous-user accounts, none of which have passwords:

+------+--------------------+----------+
| User | Host               | Password |
+------+--------------------+----------+
| root | localhost          |          |
| root | myhost.example.com |          |
| root | 127.0.0.1          |          |
| root | ::1                |          |
|      | localhost          |          |
|      | myhost.example.com |          |
+------+--------------------+----------+

If the output on your system shows any accounts with empty passwords, your MySQL installation is unprotected until you do something about it:

• Assign a password to each MySQL root account that does not have one.

• To prevent clients from connecting as anonymous users without a password, either assign a password to each anonymous account or remove the accounts.

In addition, some installation methods create a test database and add rows to the mysql.db table that permit all accounts to access that database and other databases with names that start with test_. This is true even for accounts that otherwise have no special privileges such as the default anonymous accounts. This is convenient for testing but inadvisable on production servers. Administrators who want database access restricted only to accounts that have permissions granted explicitly for that purpose should remove these mysql.db table rows.

The following instructions describe how to set up passwords for the initial MySQL accounts, first for any root accounts, then for anonymous accounts. The instructions also cover how to remove anonymous accounts, should you prefer not to permit anonymous access at all, and describe how to remove permissive access to test databases.

Replace new_password in the examples with the password that you want to use. Replace host_name with the name of the server host. You can determine this name from the output of the SELECT statement shown earlier. For the output shown, host_name is myhost.example.com.

Note

For additional information about setting passwords, see Section 6.3.6, “Assigning Account Passwords”. If you forget your root password after setting it, see Section B.5.3.2, “How to Reset the Root Password”.

To set up additional accounts, see Section 6.3.2, “Adding User Accounts”.

You might want to defer setting the passwords until later, to avoid the need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for production purposes.

Assigning root Account Passwords

To assign a password to an account, connect to the server as root using the mysql client and issue the appropriate SQL statement:

• As of MySQL 5.7.6, use ALTER USER:

  mysql> ALTER USER user IDENTIFIED BY 'new_password';

• Before 5.7.6, use SET PASSWORD:

  mysql> SET PASSWORD FOR user = PASSWORD('new_password');

The following instructions use ALTER USER. If your version of MySQL is older than 5.7.6, substitute equivalent SET PASSWORD statements.

To assign the 'root'@'localhost' account a password, connect to the server as root:

shell> mysql -u root

Then issue an ALTER USER statement:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Issue a similar ALTER USER statement for any other root account present in your mysql.user table that has no password. (Vary the host name appropriately.)

After an account has been assigned a password, you must supply that password whenever you connect to the server using the account. For example, to shut down the server with mysqladmin, use this command:

shell> mysqladmin -u root -p shutdown
Enter password: (enter root password here)

The mysql commands in the following instructions include a -p option based on the assumption that you have assigned the root account password using the preceding instructions and must specify that password when connecting to the server.

Assigning Anonymous Account Passwords

In MySQL 5.7, installation methods that create anonymous accounts tend to be for early versions for which ALTER USER cannot be used to assign passwords. Consequently, the instructions in this section use SET PASSWORD.

To assign the ''@'localhost' anonymous account a password, connect to the server as root:

shell> mysql -u root -p
Enter password: (enter root password here)

Then issue a SET PASSWORD statement:

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

Issue a similar SET PASSWORD statement for any other anonymous account present in your mysql.user table that has no password. (Vary the host name appropriately.)

Removing Anonymous Accounts

If you prefer to remove any anonymous accounts rather than assigning them passwords, use DROP USER. To drop the ''@'localhost' account, connect to the server as root:

shell> mysql -u root -p
Enter password: (enter root password here)

Then issue a DROP USER statement:

mysql> DROP USER ''@'localhost';

Issue a similar DROP USER statement for any other anonymous account that you want to drop. (Vary the host name appropriately.)

Securing Test Databases

Some installation methods create a test database and set up privileges for accessing it. If that is true on your system, the mysql.db table will contain rows that permit access by any user to the test database and other databases with names that start with test_. (These rows have an empty User column value, which for access-checking purposes matches any user name.) This means that such databases can be used even by accounts that otherwise possess no privileges. If you want to remove any-user access to test databases, do so as follows:

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';
mysql> FLUSH PRIVILEGES;

The FLUSH statement causes the server to reread the grant tables. Without it, the privilege change remains unnoticed by the server until you restart it.

With the preceding change, only users who have global database privileges or privileges granted explicitly for the test database can use it. However, if you prefer that the database not exist at all, drop it:

mysql> DROP DATABASE test;