MySQL has an advanced but non-standard security/privilege system. This section describes how it works.
Anyone using MySQL on a computer connected to the Internet should read this chapter to avoid mistakes people do.
Under "security" we mean that our site, not only MySQL is protected against all types of applicable attacks: eavesdropping, altering, playback and Denial of Service. We do not cover all aspects of availability and fault tolerance here.
There are some security logic in MySQL: Access control lists (ACL-s) and SSL encrypted connections but much more is depending on overall usage of MySQL. Also most of this chapter isn't MySQL dependant at all. Same rules apply for most applications.
When you running a site, designing software or just doing something with MySQL then try to follow these rules:
GRANT/REVOKEcommands are for restricting access to MySQL. Do not grant anyone for more than is must. Never grant all hosts to do something. Checklist:
mysql -u root. If you granted a connection without asking password, then this is bad.
SHOW GRANTSand check who is having access and to what.
MD5()or other one-way hashing function.
; DROP ALL DATABASES ;into form? Checklist:
quotemodifiers (?) for query streams.
tcpdump -l -i eth0 -w - src or dst port 3306 | strings(This works under linux and should work with small modifications under another systems). Warning: If you do not see data this doesn't actually always mean that it is encrypted. If you need high security you should consult with security expert.
When you connect to a MySQL server, you should normally use a password. The password is not transmitted in clear text over the connection.
All other information is transferred as text that can be read by anyone that
is able to watch the connection. If you are concerned about this, you can
use the compressed protocol (in MySQL 3.22 and above) to make things
much harder. To make things even more secure you should install
(see http://www.cs.hut.fi/ssh). With this, you can get an encrypted
TCP/IP connection between a MySQL server and a MySQL
To make a MySQL system secure, you should strongly consider the following suggestions:
mysql -u other_user db_nameif
other_userhas no password. It is common behavior with client/server applications that the client may specify any user name. You can change the password of all users by editing the
mysql_install_dbscript before you run it, or only the password for the MySQL
rootuser like this:
shell> mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql> FLUSH PRIVILEGES;
mysqldcan be run as any user. You can also create a new Unix user
mysqlto make everything even more secure. If you run
mysqldas another Unix user, you don't need to change the
rootuser name in the
usertable, because MySQL user names have nothing to do with Unix user names. You can edit the
mysql.serverscript to start
mysqldas another Unix user. Normally this is done with the
sucommand. For more details, see section 18.8 How to run MySQL as a normal user.
rootuser in the
mysql.serverscript, make sure this script is readable only by
mysqldruns as is the only user with read/write privileges in the database directories.
mysqladmin processlistshows the text of the currently executing queries, so any user who is allowed to execute that command might be able to see if another user issues an
UPDATE user SET password=PASSWORD('not_secure')query.
mysqldsaves an extra connection for users who have the process privilege, so that a MySQL
rootuser can log in and check things even if all normal connections are in use.
mysqlddaemon! To make this a bit safer, all files generated with
SELECT ... INTO OUTFILEare readable to everyone, and you can't overwrite existing files. The file privilege may also be used to read any file accessible to the Unix user that the server runs as. This could be abused, for example, by using
LOAD DATAto load `/etc/passwd' into a table, which can then be read with
mysqldshould make hostnames safe. In any case, you should be very careful about using hostname values that contain wildcards!
mysqld options affect security:
gethostbyname()system call are checked to make sure they resolve back to the original hostname. This makes it harder for someone on the outside to get access by simulating another host. This option also adds some sanity checks of hostnames. The option is turned off by default in MySQL 3.21 since it sometimes takes a long time to perform backward resolutions. MySQL 3.22 caches hostnames and has this option enabled by default.
Hostcolumn values in the grant tables must be IP numbers or
mysqldmust be made via Unix sockets. This option is unsuitable for systems that use MIT-pthreads, because the MIT-pthreads package doesn't support Unix sockets.
The primary function of the MySQL privilege system is to authenticate a user connecting from a given host, and to associate that user with select, insert, update and delete privileges on a database.
Additional functionality includes the ability to have an anonymous user and
to grant privileges for MySQL-specific functions such as
DATA INFILE and administrative operations.
There are several distinctions between the way user names and passwords are used by MySQL, and the way they are used by Unix or Windows:
--useroptions. This means that you can't make a database secure in any way unless all MySQL user names have passwords. Anyone may attempt to connect to the server using any name, and they will succeed if they specify any name that doesn't have a password.
ENCRYPT()functions in section 7.4.12 Miscellaneous functions.
MySQL client programs generally require that you specify connection
parameters when you want to access a MySQL server: the host you want
to connect to, your user name and your password. For example, the
mysql client can be started like this (optional arguments are enclosed
between `[' and `]'):
shell> mysql [-h host_name] [-u user_name] [-pyour_pass]
Alternate forms of the
-p options are
--password=your_pass. Note that there is no space between
--password= and the password following it.
Note: Specifing a password on the command line is not secure!
Any user on your system may then find out your password by typing a command
ps auxww. See section 4.15.4 Option files.
mysql uses default values for connection parameters that are missing
from the command line:
Thus, for a Unix user
joe, the following commands are equivalent:
shell> mysql -h localhost -u joe shell> mysql -h localhost shell> mysql -u joe shell> mysql
Other MySQL clients behave similarly.
On Unix systems, you can specify different default values to be used when you make a connection, so that you need not enter them on the command line each time you invoke a client program. This can be done in a couple of ways:
[client]section of the `.my.cnf' configuration file in your home directory. The relevant section of the file might look like this:
[client] host=host_name user=user_name password=your_passSee section 4.15.4 Option files.
MYSQL_HOST. The MySQL user name can be specified using
USER(this is for Windows only). The password can be specified using
MYSQL_PWD(but this is insecure; see next section).
If connection parameters are specified in multiple ways, values specified on the command line take precedence over values specified in configuration files and environment variables, and values in configuration files take precedence over values in environment variables.
It is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed below, along with an assessment of the risks of each method:
--password=your_passoption on the command line. This is convenient but insecure, since your password becomes visible to system status programs (such as
ps) that may be invoked by other users to display command lines. (MySQL clients typically overwrite the command line argument with zeroes during their initialization sequence, but there is still a brief interval during which the value is visible.)
--passwordoption (with no
your_passvalue specified). In this case, the client program solicits the password from the terminal:
shell> mysql -u user_name -p Enter password: ********The client echoes `*' characters to the terminal as you enter your password so that onlookers cannot see it. It is more secure to enter your password this way than to specify it on the command line because it is not visible to other users. However, this method of entering a password is suitable only for programs that you run interactively. If you want to invoke a client from a script that runs non-interactively, there is no opportunity to enter the password from the terminal.
[client]section of the `.my.cnf' file in your home directory:
[client] password=your_passIf you store your password in `.my.cnf', the file should not be group or world readable or writable. Make sure the file's access mode is
600. See section 4.15.4 Option files.
MYSQL_PWDenvironment variable, but this method must be considered extremely insecure and should not be used. Some versions of
psinclude an option to display the environment of running processes; your password will be in plain sight for all to see if you set
MYSQL_PWD. Even on systems without such a version of
ps, it is unwise to assume there is no other method to observe process environments.
All in all, the safest methods are to have the client program prompt for the password or to specify the password in a properly-protected `.my.cnf' file.
Privilege information is stored in the
columns_priv tables in the
database (that is, in the database named
mysql). The MySQL
server reads the contents of these tables when it starts up and under the
circumstances indicated in section 6.11 When privilege changes take effect.
The names used in this manual to refer to the privileges provided by MySQL are shown below, along with the table column name associated with each privilege in the grant tables and the context in which the privilege applies:
|create|| ||databases, tables or indexes|
|drop|| ||databases or tables|
|grant|| ||databases or tables|
|references|| ||databases or tables|
|reload|| ||server administration|
|shutdown|| ||server administration|
|process|| ||server administration|
|file|| ||file access on server|
The select, insert, update and delete privileges allow you to perform operations on rows in existing tables in a database.
SELECT statements require the select privilege only if they
actually retrieve rows from a table. You can execute certain
statements even without permission to access any of the databases on the
server. For example, you could use the
mysql client as a simple
mysql> SELECT 1+1; mysql> SELECT PI()*2;
The index privilege allows you to create or drop (remove) indexes.
The alter privilege allows you to use
The create and drop privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables.
Note that if you grant the drop privilege for the
database to a user, that user can drop the database in which the
MySQL access privileges are stored!
The grant privilege allows you to give to other users those privileges you yourself possess.
The file privilege gives you permission to read and write files on
the server using the
LOAD DATA INFILE and
SELECT ... INTO
OUTFILE statements. Any user to whom this privilege is granted can read or
write any file that the MySQL server can read or write.
The remaining privileges are used for administrative operations, which are
performed using the
mysqladmin program. The table below shows which
mysqladmin commands each administrative privilege allows you to
|Privilege||Commands permitted to privilege holders|
reload command tells the server to reread the grant tables. The
refresh command flushes all tables and opens and closes the log
flush-privileges is a synonym for
reload. The other
flush-* commands perform functions similar to
refresh but are
more limited in scope, and may be preferable in some instances. For example,
if you want to flush just the log files,
flush-logs is a better choice
shutdown command shuts down the server.
processlist command displays information about the threads
executing within the server. The
kill command kills server threads.
You can always display or kill your own threads, but you need the
process privilege to display or kill threads initiated by other
It is a good idea in general to grant privileges only to those users who need them, but you should exercise particular caution in granting certain privileges:
mysqldatabase can be used to change passwords and other access privilege information. (Passwords are stored encrypted, so a malicious user cannot simply read them. However, with sufficient privileges, that same user can replace a password with a different one.)
There are some things that you cannot do with the MySQL privilege system:
The MySQL privilege system ensures that all users may do exactly the things that they are supposed to be allowed to do. When you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. The system grants privileges according to your identity and what you want to do.
MySQL considers both your hostname and user name in identifying you
because there is little reason to assume that a given user name belongs to
the same person everywhere on the Internet. For example, the user
bill who connects from
whitehouse.gov need not be the same
person as the user
bill who connects from
MySQL handles this by allowing you to distinguish users on different
hosts that happen to have the same name: you can grant
bill one set
of privileges for connections from
whitehouse.gov, and a different set
of privileges for connections from
MySQL access control involves two stages:
The server uses the
host tables in the
mysql database at both stages of access control. The fields in these
grant tables are shown below:
|Table name|| || || |
|Scope fields|| || || |
| || || |
| || |
|Privilege fields|| || || |
| || || |
| || || |
| || || |
| || || |
| || || |
| || || |
| || || |
| || || |
For the second stage of access control (request verification), the server
may, if the request involves tables, additionally consult the
columns_priv tables. The fields in these
tables are shown below:
|Table name|| || |
|Scope fields|| || |
| || |
| || |
| || |
|Privilege fields|| || |
|Other fields|| || |
Each grant table contains scope fields and privilege fields.
Scope fields determine the scope of each entry in the tables, i.e., the
context in which the entry applies. For example, a
user table entry
User values of
'bob' would be used for authenticating connections made to the server
bob from the host
thomas.loc.gov. Similarly, a
table entry with
Db fields of
'reports' would be used when
bob connects from the host
thomas.loc.gov to access the
reports database. The
tables contain scope fields indicating tables or table/column combinations
to which each entry applies.
For access-checking purposes, comparisons of
Host values are
Table_name values are case sensitive.
Column_name values are case insensitive in MySQL 3.22.12
Privilege fields indicate the privileges granted by a table entry, that is, what operations can be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. The rules used to do this are described in section 6.10 Access control, stage 2: Request verification.
Scope fields are strings, declared as shown below; the default value for each is the empty string:
| || (|
all privilege fields are declared as
ENUM('N','Y') -- each can have a
'Y', and the default value is
columns_priv tables, the privilege
fields are declared as
|Table name||Field name||Possible set elements|
| || |
| || |
| || |
Briefly, the server uses the grant tables like this:
usertable scope fields determine whether to allow or reject incoming connections. For allowed connections, the privilege fields indicate the user's global (superuser) privileges.
hosttables are used together:
dbtable scope fields determine which users can access which databases from which hosts. The privilege fields determine which operations are allowed.
hosttable is used as an extension of the
dbtable when you want a given
dbtable entry to apply to several hosts. For example, if you want a user to be able to use a database from several hosts in your network, leave the
Hostvalue empty in the user's
dbtable entry, then populate the
hosttable with an entry for each of those hosts. This mechanism is described more detail in section 6.10 Access control, stage 2: Request verification.
columns_privtables are similar to the
dbtable, but are more fine-grained: they apply at the table and column level rather than at the database level.
Note that administrative privileges (reload, shutdown,
etc.) are specified only in the
user table. This is because
administrative operations are operations on the server itself and are not
database-specific, so there is no reason to list such privileges in the
other grant tables. In fact, only the
user table need
be consulted to determine whether or not you can perform an administrative
The file privilege is specified only in the
user table, too.
It is not an administrative privilege as such, but your ability to read or
write files on the server host is independent of the database you are
mysqld server reads the contents of the grant tables once, when it
starts up. Changes to the grant tables take effect as indicated in
section 6.11 When privilege changes take effect.
When you modify the contents of the grant tables, it is a good idea to make
sure that your changes set up privileges the way you want. For help in
diagnosing problems, see section 6.15 Causes of
Access denied errors. For advice on security issues,
section 6.2 How to make MySQL secure against crackers.
diagnostic tool is the
mysqlaccess script, which Yves Carlier has
provided for the MySQL distribution. Invoke
--help option to find out how it works.
mysqlaccess checks access using only the
host tables. It does not check table- or column-level
When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether or not you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, then enters stage 2 and waits for requests.
Your identity is based on two pieces of information:
Identity checking is performed using the three
user table scope fields
Password). The server accepts the
connection only if a
user table entry matches your hostname and user
name, and you supply the correct password.
Values in the
user table scope fields may be specified as follows:
Hostvalue may be a hostname or an IP number, or
'localhost'to indicate the local host.
'%'matches any hostname. A blank
Hostvalue is equivalent to
'%'. Note that these values match any host that can create a connection to your server!
Userfield, but you can specify a blank value, which matches any name. If the
usertable entry that matches an incoming connection has a blank user name, the user is considered to be the anonymous user (the user with no name), rather than the name that the client actually specified. This means that a blank user name is used for all further access checking for the duration of the connection (that is, during stage 2).
Passwordfield can be blank. This does not mean that any password matches, it means the user must connect without specifying a password.
Password values represent encrypted passwords.
MySQL does not store passwords in plaintext form for anyone to see.
Rather, the password supplied by a user who is attempting to connect is
encrypted (using the
PASSWORD() function) and compared to the
already-encrypted version stored in the
user table. If they match,
the password is correct.
The examples below show how various combinations of
User values in
user table entries apply to incoming
| ||Connections matched by entry|
| || |
| || Any user, connecting from |
| || |
| ||Any user, connecting from any host|
| || |
| || |
| || |
| || |
Since you can use IP wildcard values in the
Host field (e.g.,
'144.155.166.%' to match every host on a subnet), there is the
possibility that someone might try to exploit this capability by naming a
144.155.166.somewhere.com. To foil such attempts, MySQL
disallows matching on hostnames that start with digits and a dot. Thus, if
you have a host named something like
1.2.foo.com, its name will never
Host column of the grant tables. Only an IP number can
match an IP wildcard value.
An incoming connection may be matched by more than one entry in the
user table. For example, a connection from
fred would be matched by several of the entries just shown above. How
does the server choose which entry to use if more than one matches? The
server resolves this question by sorting the
user table after reading
it at startup time, then looking through the entries in sorted order when a
user attempts to connect. The first matching entry is the one that is used.
user table sorting works as follows. Suppose the
looks like this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +-----------+----------+-
When the server reads in the table, it orders the entries with the
Host values first (
'%' in the
means ``any host'' and is least specific). Entries with the same
value are ordered with the most-specific
User values first (a blank
User value means ``any user'' and is least specific). The resulting
user table looks like this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | localhost | root | ... | localhost | | ... | % | jeffrey | ... | % | root | ... +-----------+----------+-
When a connection is attempted, the server looks through the sorted entries
and uses the first match found. For a connection from
jeffrey, the entries with
'localhost' in the
match first. Of those, the entry with the blank user name matches both the
connecting hostname and user name. (The
'%'/'jeffrey' entry would
have matched, too, but it is not the first match in the table.)
Here is another example. Suppose the
user table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | % | jeffrey | ... | thomas.loc.gov | | ... +----------------+----------+-
The sorted table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | thomas.loc.gov | | ... | % | jeffrey | ... +----------------+----------+-
A connection from
jeffrey is matched by the
first entry, whereas a connection from
jeffrey is matched by the second.
A common misconception is to think that for a given user name, all entries
that explicitly name that user will be used first when the server attempts to
find a match for the connection. This is simply not true. The previous
example illustrates this, where a connection from
jeffrey is first matched not by the entry containing
User field value, but by the entry with no user name!
If you have problems connecting to the server, print out the
table and sort it by hand to see where the first match is being made.
Once you establish a connection, the server enters stage 2. For each request
that comes in on the connection, the server checks whether you have
sufficient privileges to perform it, based on the type of operation you wish
to perform. This is where the privilege fields in the grant tables come into
play. These privileges can come from any of the
columns_priv tables. The grant
tables are manipulated with
See section 7.26
REVOKE syntax. (You may find it helpful to refer to
section 6.8 How the privilege system works, which lists the fields present in each of the grant
user table grants privileges that are assigned to you on a global
basis and that apply no matter what the current database is. For example, if
user table grants you the delete privilege, you can
delete rows from any database on the server host! In other words,
user table privileges are superuser privileges. It is wise to grant
privileges in the
user table only to superusers such as server or
database administrators. For other users, you should leave the privileges
user table set to
'N' and grant privileges on a
database-specific basis only, using the
host tables grant database-specific privileges.
Values in the scope fields may be specified as follows:
Dbfields of either table.
Hostvalue in the
dbtable means ``any host.'' A blank
Hostvalue in the
dbtable means ``consult the
hosttable for further information.''
Hostvalue in the
hosttable means ``any host.''
Dbvalue in either table means ``any database.''
Uservalue in either table matches the anonymous user.
host tables are read in and sorted when the server
starts up (at the same time that it reads the
user table). The
db table is sorted on the
fields, and the
host table is sorted on the
scope fields. As with the
user table, sorting puts the most-specific
values first and least-specific values last, and when the server looks for
matching entries, it uses the first match that it finds.
columns_priv tables grant table- and
column-specific privileges. Values in the scope fields may be specified as
Hostfield of either table.
Hostvalue in either table means ``any host.''
Column_namefields cannot contain wildcards or be blank in either table.
columns_priv tables are sorted on
User fields. This is similar to
db table sorting, although since only the
Host field may
contain wildcards, the sorting is simpler.
The request verification process is described below. (If you are familiar with the access-checking source code, you will notice that the description here differs slightly from the algorithm used in the code. The description is equivalent to what the code actually does; it differs only to make the explanation simpler.)
For administrative requests (shutdown, reload, etc.), the
server checks only the
user table entry, since that is the only table
that specifies administrative privileges. Access is granted if the entry
allows the requested operation and denied otherwise. For example, if you
want to execute
mysqladmin shutdown but your
user table entry
doesn't grant the shutdown privilege to you, access is denied
without even checking the
host tables. (Since they
Shutdown_priv column, there is no need to do so.)
For database-related requests (insert, update, etc.), the
server first checks the user's global (superuser) privileges by looking in
user table entry. If the entry allows the requested operation,
access is granted. If the global privileges in the
user table are
insufficient, the server determines the user's database-specific privileges
by checking the
dbtable for a match on the
Userare matched to the connecting user's hostname and MySQL user name. The
Dbfield is matched to the database the user wants to access. If there is no entry for the
User, access is denied.
dbtable entry and its
Hostfield is not blank, that entry defines the user's database-specific privileges.
Hostfield is blank, it signifies that the
hosttable enumerates which hosts should be allowed access to the database. In this case, a further lookup is done in the
hosttable to find a match on the
Dbfields. If no
hosttable entry matches, access is denied. If there is a match, the user's database-specific privileges are computed as the intersection (not the union!) of the privileges in the
hosttable entries, i.e., the privileges that are
'Y'in both entries. (This way you can grant general privileges in the
dbtable entry and then selectively restrict them on a host-by-host basis using the
After determining the database-specific privileges granted by the
host table entries, the server adds them to the global privileges
granted by the
user table. If the result allows the requested
operation, access is granted. Otherwise, the server checks the user's
table and column privileges in the
tables and adds those to the user's privileges. Access is allowed or denied
based on the result.
Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:
global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges
It may not be apparent why, if the global
user entry privileges are
initially found to be insufficient for the requested operation, the server
adds those privileges to the database-, table- and column-specific privileges
later. The reason is that a request might require more than one type of
privilege. For example, if you execute an
INSERT ... SELECT
statement, you need both insert and select privileges.
Your privileges might be such that the
user table entry grants one
privilege and the
db table entry grants the other. In this case, you
have the necessary privileges to perform the request, but the server cannot
tell that from either table by itself; the privileges granted by both entries
must be combined.
host table can be used to maintain a list of ``secure'' servers.
At TcX, the
host table contains a list of all machines on the local
network. These are granted all privileges.
You can also use the
host table to indicate hosts that are not
secure. Suppose you have a machine
public.your.domain that is located
in a public area that you do not consider secure. You can allow access to
all hosts on your network except that machine by using
+--------------------+----+- | Host | Db | ... +--------------------+----+- | public.your.domain | % | ... (all privileges set to 'N') | %.your.domain | % | ... (all privileges set to 'Y') +--------------------+----+-
Naturally, you should always test your entries in the grant tables (e.g.,
mysqlaccess) to make sure your access privileges are actually
set up the way you think they are.
mysqld starts, all grant table contents are read into memory and
become effective at that point.
Modifications to the grant tables that you perform using
SET PASSWORD are noticed by the server immediately.
If you modify the grant tables manually (using
etc.), you should execute a
FLUSH PRIVILEGES statement or run
mysqladmin flush-privileges to tell the server to reload the grant
tables. Otherwise your changes will have no effect until
you restart the server.
When the server notices that the grant tables have been changed, existing client connections are affected as follows:
Global privilege changes and password changes take effect the next time the client connects.
After installing MySQL, you set up the initial access privileges by
See section 4.7.1 Quick installation overview.
scripts/mysql_install_db script starts up the
server, then initializes the grant tables to contain the following set
rootuser is created as a superuser who can do anything. Connections must be made from the local host. Note: The initial
rootpassword is empty, so anyone can connect as
rootwithout a password and be granted all privileges.
'test'or starting with
'test_'. Connections must be made from the local host. This means any local user can connect and be treated as the anonymous user.
Note: The default privileges are different for Win32. See section 4.12.4 Running MySQL on Win32.
Since your installation is initially wide open, one of the first things you
should do is specify a password for the MySQL
root user. You can do this as follows (note that you specify the
password using the
shell> mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql> FLUSH PRIVILEGES;
You can in MySQL 3.22 and above use the
SET PASSWORD statement:
shell> mysql -u root mysql mysql> SET PASSWORD FOR root=PASSWORD('new_password');
Another way to set the password is by using the
shell> mysqladmin -u root password new_password
Note that if you update the password in the
user table directly using
the first method, you must tell the server to reread the grant tables (with
FLUSH PRIVILEGES), since the change will go unnoticed otherwise.
root password has been set, thereafter you must supply that
password when you connect to the server as
You may wish to leave the
root password blank so that you don't need
to specify it while you perform additional setup or testing, but be sure to
set it before using your installation for any real production work.
scripts/mysql_install_db script to see how it sets up
the default privileges. You can use this as a basis to see how to
add other users.
If you want the initial privileges to be different than those just described
above, you can modify
mysql_install_db before you run it.
To recreate the grant tables completely, remove all the `*.frm',
`*.MYI' and `*.MYD' files in the directory containing the
mysql database. (This is the directory named `mysql' under
the database directory, which is listed when you run
--help.) Then run the
mysql_install_db script, possibly after
editing it first to have the privileges you want.
NOTE: For MySQL versions older than 3.22.10, you should NOT
delete the `*.frm' files. If you accidentally do this, you should
copy them back from your MySQL distribution before running
You can add users two different ways: by using
or by manipulating the MySQL grant tables directly. The
preferred method is to use
GRANT statements, because they are
more concise and less error-prone.
The examples below show how to use the
mysql client to set up new
users. These examples assume that privileges are set up according to the
defaults described in the previous section. This means that to make changes,
you must be on the same machine where
mysqld is running, you must
connect as the MySQL
root user, and the
root user must
have the insert privilege for the
mysql database and the
reload administrative privilege. Also, if you have changed the
root user password, you must specify it for the
You can add new users by issuing
shell> mysql --user=root mysql mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'something' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'something' WITH GRANT OPTION; mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost; mysql> GRANT USAGE ON *.* TO dummy@localhost;
GRANT statements set up three new users:
'something'to do so. Note that we must issue
GRANTstatements for both
monty@"%". If we don't add the entry with
localhost, the anonymous user entry for
localhostthat is created by
mysql_install_dbwill take precedence when we connect from the local host, because it has a more specific
Hostfield value and thuse comes earlier in the
usertable sort order.
localhostwithout a password and who is granted the reload and process administrative privileges. This allows the user to execute the
mysqladmin flush-*commands, as well as
mysqladmin processlist. No database-related privileges are granted. They can be granted later by issuing additional
USAGEprivilege type allows you to set up a user with no privileges. It is assumed that you will grant database-specific privileges later.
You can also add the same user access information directly by issuing
INSERT statements and then telling the server to reload the grant
shell> mysql --user=root mysql mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('something'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') mysql> INSERT INTO user VALUES('%','monty',PASSWORD('something'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') mysql> INSERT INTO user SET Host='localhost',User='admin', Reload_priv='Y', Process_priv='Y'; mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy',''); mysql> FLUSH PRIVILEGES;
Depending on your MySQL version, you may have to use a different
'Y' values above (versions prior to 3.22.11 had fewer
privilege columns). For the
admin user, the more readable extended
INSERT syntax that is available starting with 3.22.11 is used.
Note that to set up a superuser, you need only create a
entry with the privilege fields set to
host table entries are necessary.
The privilege columns in the
user table were not set explicitly in the
INSERT statement (for the
dummy user), so those columns
are assigned the default value of
'N'. This is the same thing that
GRANT USAGE does.
The following example adds a user
custom who can connect from hosts
whitehouse.gov. He wants
to access the
bankaccount database only from
expenses database only from
customer database from all three hosts. He wants
to use the password
stupid from all three hosts.
To set up this user's privileges using
GRANT statements, run these
shell> mysql --user=root mysql mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO custom@localhost IDENTIFIED BY 'stupid'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON expenses.* TO firstname.lastname@example.org IDENTIFIED BY 'stupid'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.* TO custom@'%' IDENTIFIED BY 'stupid';
To set up the user's privileges by modifying the grant tables directly,
run these commands (note the
FLUSH PRIVILEGES at the end):
shell> mysql --user=root mysql mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','custom',PASSWORD('stupid')); mysql> INSERT INTO user (Host,User,Password) VALUES('server.domain','custom',PASSWORD('stupid')); mysql> INSERT INTO user (Host,User,Password) VALUES('whitehouse.gov','custom',PASSWORD('stupid')); mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv) VALUES ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv) VALUES ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv) VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y'); mysql> FLUSH PRIVILEGES;
The first three
INSERT statements add
user table entries that
custom to connect from the various hosts with the given
password, but grant no permissions to him (all privileges are set to the
default value of
'N'). The next three
INSERT statements add
db table entries that grant privileges to
custom for the
customer databases, but only
when accessed from the proper hosts. As usual, when the grant tables are
modified directly, the server must be told to reload them (with
FLUSH PRIVILEGES) so that the privilege changes take effect.
If you want to give a specific user access from any machine in a given
domain, you can issue a
GRANT statement like the following:
mysql> GRANT ... ON *.* TO myusername@"%.mydomainname.com" IDENTIFIED BY 'mypassword';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername', PASSWORD('mypassword'),...); mysql> FLUSH PRIVILEGES;
You can also use
mysql_webadmin and even
xmysql to insert, change and update values in the grant tables.
You can find these utilities at the MySQL
The examples in the preceding sections illustrate an important principle:
when you store a non-empty password using
statements, you must use the
PASSWORD() function to encrypt it. This
is because the
user table stores passwords in encrypted form, not as
plaintext. If you forget that fact, you are likely to attempt to set
passwords like this:
shell> mysql -u root mysql mysql> INSERT INTO user (Host,User,Password) VALUES('%','jeffrey','biscuit'); mysql> FLUSH PRIVILEGES;
The result is that the plaintext value
'biscuit' is stored as the
password in the
user table. When the user
jeffrey attempts to
connect to the server using this password, the
mysql client encrypts
PASSWORD() and sends the result to the server. The server
compares the value in the
user table (which is the plaintext value
'biscuit') to the encrypted password (which is not
'biscuit'). The comparison fails and the server rejects the
shell> mysql -u jeffrey -pbiscuit test Access denied
Since passwords must be encrypted when they are inserted in the
INSERT statement should have been specified like this
mysql> INSERT INTO user (Host,User,Password) VALUES('%','jeffrey',PASSWORD('biscuit'));
You must also use the
PASSWORD() function when you use
mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');
If you set passwords using the
GRANT ... IDENTIFIED BY statement
mysqladmin password command, the
is unnecessary. They both take care of encrypting the password for you,
so you would specify a password of
'biscuit' like this:
mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';
shell> mysqladmin -u jeffrey password biscuit
PASSWORD() does not perform password encryption in the same way
that Unix passwords are encrypted. You should not assume that if your Unix
password and your MySQL password are the same,
will result in the same encrypted value as is stored in the Unix password
See section 6.4 MySQL user names and passwords.
If you encounter
Access denied errors when you try to connect to the
MySQL server, the list below indicates some courses of
action you can take to correct the problem:
mysql_install_dbscript after installing MySQL, to set up the initial grant table contents? If not, do so. See section 6.12 Setting up the initial MySQL privileges. Test the initial privileges by executing this command:
shell> mysql -u root testThe server should let you connect without error. You should also make sure you have a file `user.MYD' in the MySQL database directory. Ordinarily, this is `PATH/var/mysql/user.MYD', where
PATHis the pathname to the MySQL installation root.
shell> mysql -u root mysqlThe server should let you connect because the MySQL
rootuser has no password initially. Since that is also a security risk, setting the
rootpassword is something you should do while you're setting up your other MySQL users. If you try to connect as
rootand get this error:
Access denied for user: '@unknown' to database mysqlthis means that you don't have an entry in the
usertable with a
Usercolumn value of
mysqldcannot resolve the hostname for your client. In this case, you must restart the server with the
--skip-grant-tablesoption and edit your `/etc/hosts' or `\windows\hosts' file to add a entry for your host.
mysql_fix_privilege_tablesscript? If not, do so. The structure of the grant tables changed with MySQL 3.22.11 when the
GRANTstatement became functional.
UPDATEstatement) and your changes seem to be ignored, remember that you must issue a
FLUSH PRIVILEGESstatement or execute a
mysqladmin flush-privilegescommand to cause the server to reread the tables. Otherwise your changes have no effect until the next time the server is restarted. Remember that after you set the
rootpassword, you won't need to specify it until after you flush the privileges, because the server still won't know you've changed the password yet!
mysqlddaemon with the
--skip-grant-tablesoption. Then you can change the MySQL grant tables and use the
mysqlaccessscript to check whether or not your modifications have the desired effect. When you are satisfied with your changes, execute
mysqladmin flush-privilegesto tell the
mysqldserver to start using the new grant tables. Note: Reloading the grant tables overrides the
--skip-grant-tablesoption. This allows you to tell the server to begin using the grant tables again without bringing it down and restarting it.
mysql -u user_name db_nameor
mysql -u user_name -pyour_pass db_name. If you are able to connect using the
mysqlclient, there is a problem with your program and not with the access privileges. (Notice that there is no space between
-pand the password; you can also use the
--password=your_passsyntax to specify the password.)
PASSWORD()function if you set the password with the
SET PASSWORDstatements. The
PASSWORD()function is unnecessary if you specify the password using the
GRANT ... INDENTIFIED BYstatement or the
mysqladmin passwordcommand. See section 6.14 How to set up passwords.
localhostis a synonym for your local hostname, and is also the default host to which clients try to connect if you specify no host explicitly. However, connections to
localhostdo not work if you are running on a system that uses MIT-pthreads (
localhostconnections are made using Unix sockets, which are not supported by MIT-pthreads). To avoid this problem on such systems, you should use the
--hostoption to name the server host explicitly. This will make a TCP/IP connection to the
mysqldserver. In this case, you must have your real hostname in
usertable entries on the server host. (This is true even if you are running a client program on the same host as the server.)
Access deniederror when trying to connect to the database with
mysql -u user_name db_name, you may have a problem with the
usertable. Check this by executing
mysql -u root mysqland issuing this SQL statement:
mysql> SELECT * FROM user;The result should include an entry with the
Usercolumns matching your computer's hostname and your MySQL user name.
Access deniederror message will tell you who you are trying to log in as, the host from which you are trying to connect, and whether or not you were using a password. Normally, you should have one entry in the
usertable that exactly matches the hostname and user name that were given in the error message.
usertable that matches that host:
Host ... is not allowed to connect to this MySQL serverYou can fix this by using the command line tool
mysql(on the server host!) to add a row to the
usertable for the user/hostname combination from which you are trying to connect. If you are not running MySQL 3.22 and you don't know the IP number or hostname of the machine from which you are connecting, you should put an entry with
Hostcolumn value in the
usertable and restart
--logoption on the server machine. After trying to connect from the client machine, the information in the MySQL log will indicate how you really did connect. (Then replace the
usertable entry with the actual hostname that shows up in the log. Otherwise, you'll have a system that is insecure.)
mysql -u root testworks but
mysql -h your_hostname -u root testresults in
Access denied, then you may not have the correct name for your host in the
usertable. A common problem here is that the
Hostvalue in the user table entry specifies an unqualified hostname, but your system's name resolution routines return a fully-qualified domain name (or vice-versa). For example, if you have an entry with host
usertable, but your DNS tells MySQL that your hostname is
'tcx.subnet.se', the entry will not work. Try adding an entry to the
usertable that contains the IP number of your host as the
Hostcolumn value. (Alternatively, you could add an entry to the
usertable with a
Hostvalue that contains a wildcard--for example,
'tcx.%'. However, use of hostnames ending with `%' is insecure and is not recommended!)
mysql -u user_name testworks but
mysql -u user_name other_db_namedoesn't work, you don't have an entry for
other_db_namelisted in the
mysql -u user_name db_nameworks when executed on the server machine, but
mysql -u host_name -u user_name db_namedoesn't work when executed on another client machine, you don't have the client machine listed in the
usertable or the
Access denied, remove from the
usertable all entries that have
Hostvalues containing wildcards (entries that contain `%' or `_'). A very common error is to insert a new entry with
'some user', thinking that this will allow you to specify
localhostto connect from the same machine. The reason that this doesn't work is that the default privileges include an entry with
''. Since that entry has a
'localhost'that is more specific than
'%', it is used in preference to the new entry when connecting from
localhost! The correct procedure is to insert a second entry with
'some_user', or to remove the entry with with
Access to database deniedIf the entry selected from the
dbtable has an empty value in the
Hostcolumn, make sure there are one or more corresponding entries in the
hosttable specifying which hosts the
dbtable entry applies to. If you get the error when using the SQL commands
SELECT ... INTO OUTFILEor
LOAD DATA INFILE, your entry in the
usertable probably doesn't have the file privilege enabled.
Access deniedwhen you run a client without any options, make sure you haven't specified an old password in any of your option files! See section 4.15.4 Option files.
mysqlddaemon with a debugging option (for example,
--debug=d,general,query). This will print host and user information about attempted connections, as well as information about each command issued. See section G.1 Debugging a MySQL server.
mysqldump mysqlcommand. As always, post your problem using the
mysqlbugscript. In some cases you may restart
--skip-grant-tablesto be able to run
Go to the first, previous, next, last section, table of contents.