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.11.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 user name 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 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 host name of
localhost
for one of the accounts, or
the actual host name or IP number for the other.
Two anonymous-user accounts are created, each with an empty user name. 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 no global privileges.
(Before MySQL 5.1.16, it has all global 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 host name of
localhost
for one of the accounts, or
the actual host name 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.
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 Windows,
do this:
shell>mysql -u root
mysql>SET PASSWORD FOR ''@'localhost' = PASSWORD('
mysql>newpwd
');SET PASSWORD FOR ''@'%' = PASSWORD('
newpwd
');
To use SET PASSWORD
on Unix, do
this:
shell>mysql -u root
mysql>SET PASSWORD FOR ''@'localhost' = PASSWORD('
mysql>newpwd
');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. 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>DROP USER ''@'localhost';
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 Windows, do this:
shell>mysql -u root
mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('
mysql>newpwd
');SET PASSWORD FOR 'root'@'%' = PASSWORD('
newpwd
');
For Unix, do this:
shell>mysql -u root
mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('
mysql>newpwd
');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 "
shell>newpwd
"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)
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”.
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.