Securing the Initial MySQL Accounts

Part of the MySQL installation process is to set up the


mysql

database that contains the grant tables:

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

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

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.

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

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.

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:

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:

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:

Anonymous Account Password Assignment

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

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 host name 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.

Anonymous Account Removal

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

shell> mysql -u root
mysql> DROP USER '';

The DROP statement applies both to Windows and to Unix.

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

root Account Password Assignment

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 SET PASSWORD statements. Be sure to encrypt the password using the PASSWORD() function.

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 host name that you used when you assigned the anonymous account passwords.

If the user table contains an account with User and Host values of 'root' and '127.0.0.1', use an additional SET PASSWORD statement to set that account's password:

mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');

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"

The mysqladmin method of setting the root account passwords does not set the password for the 'root'@'127.0.0.1' account. To do so, use SET PASSWORD as shown earlier.

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

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.

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

Note

If you forget your root password after setting it up, Section B.1.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.5.2, “Adding User Accounts to MySQL”.

User Comments

Posted by Stefan Haflidason on September 6 2004 9:17am

[Delete] [Edit]

If you're having trouble using the above statements, try the following syntax:

mysql> SET PASSWORD FOR username@"host" = PASSWORD('newpwd');

Posted by Tom Zimmermann on October 13 2004 1:52am

[Delete] [Edit]

I had the same problem as above, and wound up using the following command to set the root password for both accounts simultaneously:

mysql> UPDATE mysql.user SET Password = PASSWORD('newpassword')
mysql> WHERE User = 'root';

Posted by [name withheld] on November 3 2004 11:56am

[Delete] [Edit]

When you use the mysqladmin command to set up the root password for your MySQL database you have to be careful.

In my user database MySQL placed two root users, one for localhost and one for the SaxionSecurity host (which is the hostname of the Linux system). When I executed the 'mysqladmin -u root password �new_password�' command the password for the localhost was changed, but the SaxionSecurity host still had no password. So every user with shell access to my linux system could still login as root user in MySQL with the command 'mysql -h SaxionSecurity -u root'.

When I installed MySQL with apt under debian I did not have this extra root user, so I don't know when this could be a problem (in the above case I user a static binary). But you can just check your user table in your mysql database to see if you have this combination of hostname and root user.

Also for the paranoia sysadmins this might be useful. After you add a new user or change a password make sure you clean the command history, either in a file like .bash_history or .mysql_history.

Greetings,

Matt

Posted by [name withheld] on June 14 2005 6:14pm

[Delete] [Edit]

As far as I can tell, all of the methods provided for password assignment are potentially insecure in that passwords are likely to be visible in the shell history buffer (if mysqladmin is used), or the query history buffer maintained locally by the mysql client. As a result, anyone gaining access to the files containing these buffers could gain access to your database(s). Therefore, it is important to clear these out anytime MySQL passwords are set or changed.

I am using 4.1.x. Things may be different in 5. It's certainly an aspect that should be addressed at some point.

If I am overlooking something obvoius, please excuse my ignorance and feel free to comment.

Best regards

Joe

Posted by Abel Lopez on September 21 2005 4:11am

[Delete] [Edit]

Wow, after installing the RPM of 4.1 and not being able to do anything like the docs state, my DBA friend told me to delete the db and reinstall, This tottally worked. Steps involved:

cd /var/lib/mysql
rm -rf mysql/
mkdir mysql
mysql_install_db
chown -R mysql:mysql mysql

Now, when I run mysqladmin -u root password 'newpassword', it finally works.

Posted by Amelia Colarco on October 4 2005 5:29pm

[Delete] [Edit]

For those of you trying to use MySQL with PHP, the way they have you set the password here doesn't work. The solution can be found later in the document, but it seems appropriate to add it here because it can save some heartache later on. If you have PHP 4 or older, and MySQL 4.1 or newer, you have to use 16-bin encription, as PHP 4 does not support mysql at 32-bit.

After some digging it turns out the fix is EASY. Just use OLD_PASSWORD('yourpasswordhere') instead of PASSWORD('yourpasswordhere')

Problem solved! Of course this is less secure. This is by far the easiest fix, but information on other fixes can be found later on in the documentation, and on http://www.php.net if you'd like to learn more.

Posted by ralph cook on October 6 2005 7:30pm

[Delete] [Edit]

I got here by scouting the table of contents; I was trying to set the root password and add a user and ran into trouble immediately. I had used the Windows installer, so my root password was set when I ran that. I should have remembered, but didn't. I think this text could mention how things are different if you used the installer.

Also, unless I'm doing something wrong with my select statements, it only sets the one user, no remote users, no anonymous user.

Thanks for all the help, everybody. I managed to glean how to add a user and his correct encrypted password solely by reading the comments.

rc

Posted by Fausto Rodriguez Zapata on November 18 2005 1:28pm

[Delete] [Edit]

For Mac OS X Tiger 10.4.3

After installing the mysql-debug-5.0.15-osx10.4-powerpc.pkg package
and running the mysql server through installed preference pane I got the following error when I tried to set the anonymous passwords:

shell> mysql -u root
Access denied for user 'root'@'localhost' (using password: NO)

So I first followed the directions in A.4.1. How to Reset the Root Password
to reset the root password and then I modified the anonymous and root passwords as in this section.

Posted by Joe Dougherty on November 20 2005 3:44pm

[Delete] [Edit]

One thing that may drive you nuts, especially if you're old like me and don't see things as clearly as you used to...

When setting a password for the anonymous accounts, make sure you don't use double quotes (") in front if the @. Two single ticks will work, because then everything matches. ;-)

This is especially important for those reading these instructions with a smaller font. They might get fooled into using the wrong symbols/keys. I can't tell you how many times this caught me.

Posted by Yosemite Sam on April 24 2006 2:40pm

[Delete] [Edit]

My Notes

Accounts

Anonymous user
============
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.

Windows
============
Creates one root account with local connect only.

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.

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.

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_.

Add New User
=====================
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

Setting Passwords No FLUSH PRIVILEGES required
====================================================
*Anonymous

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

*Root

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

Using mysqladmin

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

Anonymous Account Removal
================================
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;

Root Account Removal
================================
If you prefer to remove the anonymous accounts instead, do so as follows:

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

List hosts
==================
SELECT Host, User FROM mysql.user;

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

Give all rights
==================
GRANT ALL PRIVILEGES ON *.* TO 'dbAdmin'@'localhost' IDENTIFIED BY 'newpasswd' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'dbAdmin'@'%' IDENTIFIED BY 'newpasswd' WITH GRANT OPTION;

Add your own comment.

News	See also	Recommeded Links	Setting initial root password	Resetting Root Password	Securing the Initial MySQL Accou	FAQs	Reference
Apache	MS Access to mySql conversion	PHP integration	ACID Installation (includes MySQL)	Comparison with other databases	Resetting root password on MySQL	Humor	Etc