(mysql.info.gz) Default privileges
Info Catalog
(mysql.info.gz) Unix post-installation
(mysql.info.gz) Post-installation
2.9.3 Securing the Initial MySQL Accounts
-----------------------------------------
Part of the MySQL installation process is to set up the `mysql' database
containing 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
Unix post-installation.
The grant tables define the initial MySQL user accounts and their access
privileges. These accounts are set up as follows:
* Two accounts are created with a username of `root'. 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 for connecting from the
local host and the other allows connections from any host.
* 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 account, 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 passwords, 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 or 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 account, or the
actual hostname or IP number for the other. These accounts
have all privileges for the `test' database or 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 passwords to the
anonymous accounts or else remove them.
* You should assign passwords to the 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 any real production work.
To assign passwords to the anonymous accounts, you can use either `SET
PASSWORD' or `UPDATE'. In both cases, 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 will go 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;
This 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.
If you are using a server from a _very_ old version of MySQL, the
`mysqladmin' commands to set the password will fail with the message
`parse error near 'SET password''. The solution to this problem is to
upgrade the server to a newer version of MySQL.
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)
* If you forget your `root' password after setting it up, the
procedure for resetting it is covered in Resetting permissions.
To set up new accounts, you can use the `GRANT' statement. For
instructions, see Adding users.
Info Catalog
(mysql.info.gz) Unix post-installation
(mysql.info.gz) Post-installation
automatically generated byinfo2html