Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

2.9.3. Securing the Initial MySQL Accounts

Part of the MySQL installation process is to set up the mysql database that contains the grant tables:

  • Windows distributions contain preinitialized grant tables that are installed automatically.

  • On Unix, the grant tables are populated by the mysql_install_db program. Some installation methods run this program for you. Others require that you execute it manually. For details, see Section 2.9.2, “Unix Post-Installation Procedures”.

The grant tables define the initial MySQL user accounts and their access privileges. These accounts are set up as follows:

  • Accounts with the username root are created. These are superuser accounts that can do anything. The initial root account passwords are empty, so anyone can connect to the MySQL server as rootwithout a password — and be granted all privileges.

    • On Windows, one root account is created; this account allows connecting from the local host only. The Windows installer will optionally create an account allowing for connections from any host only if the user selects the Enable root access from remote machines option during installation.

    • On Unix, both root accounts are for connections from the local host. Connections must be made from the local host by specifying a hostname of localhost for one of the accounts, or the actual hostname or IP number for the other.

  • Two anonymous-user accounts are created, each with an empty username. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.

    • On Windows, one anonymous account is for connections from the local host. It has all privileges, just like the root accounts. The other is for connections from any host and has all privileges for the test database and for other databases with names that start with test.

    • On Unix, both anonymous accounts are for connections from the local host. Connections must be made from the local host by specifying a hostname of localhost for one of the accounts, or the actual hostname or IP number for the other. These accounts have all privileges for the test database and for other databases with names that start with test_.

As noted, none of the initial accounts have passwords. This means that your MySQL installation is unprotected until you do something about it:

  • If you want to prevent clients from connecting as anonymous users without a password, you should either assign a password to each anonymous account or else remove the accounts.

  • You should assign a password to each MySQL root accounts.

The following instructions describe how to set up passwords for the initial MySQL accounts, first for the anonymous accounts and then for the root accounts. Replace “newpwd” in the examples with the actual password that you want to use. The instructions also cover how to remove the anonymous accounts, should you prefer not to allow anonymous access at all.

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

To assign passwords to the anonymous accounts, connect to the server as root and then either SET PASSWORD or UPDATE. In either case, be sure to encrypt the password using the PASSWORD() function.

To use SET PASSWORD on Windows, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd');

To use SET PASSWORD on Unix, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');

In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the name that is specified in the Host column of the non-localhost record for root in the user table. If you don't know what hostname this is, issue the following statement before using SET PASSWORD:

mysql> SELECT Host, User FROM mysql.user;

Look for the record that has root in the User column and something other than localhost in the Host column. Then use that Host value in the second SET PASSWORD statement.

The other way to assign passwords to the anonymous accounts is by using UPDATE to modify the user table directly. Connect to the server as root and issue an UPDATE statement that assigns a value to the Password column of the appropriate user table records. The procedure is the same for Windows and Unix. The following UPDATE statement assigns a password to both anonymous accounts at once:

shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
    ->     WHERE User = '';
mysql> FLUSH PRIVILEGES;

After you update the passwords in the user table directly using UPDATE, you must tell the server to re-read the grant tables with FLUSH PRIVILEGES. Otherwise, the change goes unnoticed until you restart the server.

If you prefer to remove the anonymous accounts instead, do so as follows:

shell> mysql -u root
mysql> DELETE FROM mysql.user WHERE User = '';
mysql> FLUSH PRIVILEGES;

The DELETE statement applies both to Windows and to Unix. On Windows, if you want to remove only the anonymous account that has the same privileges as root, do this instead:

shell> mysql -u root
mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User='';
mysql> FLUSH PRIVILEGES;

That account allows anonymous access but has full privileges, so removing it improves security.

You can assign passwords to the root accounts in several ways. The following discussion demonstrates three methods:

  • Use the SET PASSWORD statement

  • Use the mysqladmin command-line client program

  • Use the UPDATE statement

To assign passwords using SET PASSWORD, connect to the server as root and issue two SET PASSWORD statements. Be sure to encrypt the password using the PASSWORD() function.

For Windows, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd');

For Unix, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('newpwd');

In the second SET PASSWORD statement, replace host_name with the name of the server host. This is the same hostname that you used when you assigned the anonymous account passwords.

To assign passwords to the root accounts using mysqladmin, execute the following commands:

shell> mysqladmin -u root password "newpwd"
shell> mysqladmin -u root -h host_name password "newpwd"

These commands apply both to Windows and to Unix. In the second command, replace host_name with the name of the server host. The double quotes around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter.

You can also use UPDATE to modify the user table directly. The following UPDATE statement assigns a password to both root accounts at once:

shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
    ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

The UPDATE statement applies both to Windows and to Unix.

After the passwords have been set, you must supply the appropriate password whenever you connect to the server. For example, if you want to use mysqladmin to shut down the server, you can do so using this command:

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

Note: If you forget your root password after setting it up, Section A.4.1, “How to Reset the Root Password”, covers the procedure for resetting it.

To set up additional accounts, you can use the GRANT statement. For instructions, see Section 5.8.2, “Adding New User Accounts to MySQL”.


 
 
  Published under the terms of the GNU General Public License Design by Interspire