Securing the Initial MySQL Accounts

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

• 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.10.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 root — without a password — and be granted all privileges.
  • 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 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 account.

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.

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 two 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 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.9.2, “Adding New User Accounts to MySQL”.

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"

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.

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.

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

Previous / Next / Up / Table of Contents

User Comments

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

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

 

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';
 

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
 

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

 

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.
 

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.

 

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
 

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.
 

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.
 

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.

Etc

Society

Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers :   Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism  : The Iron Law of Oligarchy : Libertarian Philosophy

Quotes

War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda  : SE quotes : Language Design and Programming Quotes : Random IT-related quotes :  Somerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose Bierce :  Bernard Shaw : Mark Twain Quotes

Bulletin:

Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 :  Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method  : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law

History:

Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds  : Larry Wall  : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOS :  Programming Languages History : PL/1 : Simula 67 : C : History of GCC development :  Scripting Languages : Perl history   : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history

Classic books:

The Peter Principle : Parkinson Law : 1984 : The Mythical Man-Month :  How to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite

Most popular humor pages:

Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor

The Last but not Least Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D

The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the Softpanorama society. We do not warrant the correctness of the information provided or its fitness for any purpose. The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.

Last modified: March 12, 2019