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 section to avoid the most common security mistakes.
In discussing security, we emphasize the necessity of fully protecting the entire server host (not simply the MySQL server) 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.
MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.
When running MySQL, follow these guidelines whenever possible:
userTABLE IN THE
mysqlDATABASE! The encrypted password is the real password in MySQL. If you know the password listed in the
usertable for a given user, you can easily log in as that user if you have access to the host listed for that account.
REVOKEcommands are used for controlling access to MySQL. Do not grant any more privileges than necessary. Never grant privileges to all hosts. Checklist:
mysql -u root. If you are able to connect successfully to the server without being asked for a password, you have problems. Anyone can connect to your MySQL server as the MySQL
rootuser with full privileges! Review the MySQL installation instructions, paying particular attention to the item about setting a
SHOW GRANTSand check to see who has access to what. Remove those privileges that are not necessary using the
MD5()or another one-way hashing function.
nmap. MySQL uses port 3306 by default. This port should be inaccessible from untrusted hosts. Another simple way to check whether or not your MySQL port is open is to try the following command from some remote machine, where
server_hostis the hostname of your MySQL server:
shell> telnet server_host 3306If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open. If
telnetjust hangs or the connection is refused, everything is OK; the port is blocked.
; DROP DATABASE mysql;''. This is an extreme example, but large security leaks and data loss may occur as a result of hackers using similar techniques, if you do not prepare for them. Also remember to check numeric data. A common mistake is to protect only strings. Sometimes people think that if a database contains only publicly available data that it need not be protected. This is incorrect. At least denial-of-service type attacks can be performed on such databases. The simplest way to protect from this type of attack is to use apostrophes around the numeric constants:
SELECT * FROM table WHERE ID='234'rather than
SELECT * FROM table WHERE ID=234. MySQL automatically converts this string to a number and strips all non-numeric symbols from it. Checklist:
%27(`'') in the URL.
addslashes()function. As of PHP 4.0.3, a
mysql_escape_string()function is available that is based on the function of the same name in the MySQL C API.
quotemodifiers for query streams.
quote()method or use placeholders.
PreparedStatementobject and placeholders.
stringsutilities. For most cases, you can check whether or not MySQL data streams are unencrypted by issuing a command like the following:
shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings(This works under Linux and should work with small modifications under other systems). Warning: If you do not see data this doesn't always actually mean that it is encrypted. If you need high security, you should consult with a security expert.
When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication.
All other information is transferred as text that can be read by anyone
who is able to watch the connection. If you are concerned about this,
you can use the compressed protocol (in MySQL Version 3.22 and above)
to make things much harder. To make things even more secure you should use
ssh. You can find an open source
ssh client at
http://www.openssh.org, and a commercial
ssh client at
http://www.ssh.com. With this, you can get an encrypted TCP/IP
connection between a MySQL server and a MySQL client.
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;
rootuser. This is very dangerous, because any user with
FILEprivileges will be able to create files as
~root/.bashrc). To prevent this,
mysqldwill refuse to run as
rootunless it is specified directly using a
mysqldcan be run as an ordinary unprivileged user instead. 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. To start
mysqldas another Unix user, add a
userline that specifies the user name to the
[mysqld]group of the `/etc/my.cnf' option file or the `my.cnf' option file in the server's data directory. For example:
[mysqld] user=mysqlThis will cause the server to start as the designated user whether you start it manually or by using
mysql.server. For more details, see section 21.9 How to Run MySQL As a Normal User.
--skip-symlinkoption. This is especially important if you run
mysqldas root as anyone that has write access to the mysqld data directories could then delete any file in the system! See section 126.96.36.199 Using Symbolic Links for Tables.
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.
mysqldreserves 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 cannot 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 creating grant table entries using hostname values that contain wild cards!
mysqld options affect networking 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 pretending to be another host. This option also adds some sanity checks of hostnames. The option is turned off by default in MySQL Version 3.21 because sometimes it takes a long time to perform backward resolutions. MySQL Version 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.
SHOW DATABASESstatement doesn't return anything.
SHOW DATABASESreturns only those databases for which the user has some kind of privilege.
The primary function of the MySQL privilege system is to authenticate a user connecting from a given host, and to associate that user with privileges on a database such as select, insert, update and delete.
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. Note that even if the password is stored 'scrambled', and knowing your 'scrambled' password is enough to be able to connect to the MySQL server!
MySQL users and they privileges are normally created with the
GRANT command. See section 7.35
When you login to a MySQL server with a command line client you
should specify the password with
See section 6.6 Connecting to the MySQL Server.
mysql --user=monty --password=guess database_name
If you want the client to prompt for a password, you should use
--password without any argument
mysql --user=monty --password database_name
or the short form:
mysql -u monty -p database_name
Note that in the last example the password is NOT 'database_name'.
If you want to use the -p option to supply a password you should do like this:
mysql -u monty -pguess database_name
On some system the library call that MySQL uses to prompt for a password will automaticly cut the password to 8 characters. Internally MySQL doesn't have any limit for the length of the password.
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: Specifying 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.16.5 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.16.5 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 the next section). See section A 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:
mysql.usertable. Knowing the encrypted password for a user makes it possible to login as this user. The passwords are only scrambled so that one shouldn't be able to see the real password you used (if you happen to use a similar password with your other applications).
--password=your_passoption on the command line. This is convenient but insecure, because 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 `*' characters represent your password. 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. On some systems, you may even find that the first line of your script is read and interpreted (incorrectly) as your password!
[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.16.5 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. See section A Environment Variables.
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.
Information about user privileges is stored in the
columns_priv tables in the
mysql database (that is, in the database named
MySQL server reads the contents of these tables when it starts up
and under the circumstances indicated in section 6.12 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 re-read 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
users. See section 7.27
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:
SELECT. This includes the contents of all databases hosted by the server!
mysqldatabase can be used to change passwords and other access privilege information. (Passwords are stored encrypted, so a malicious user cannot simply read them to know the plain text password). If they can access the
mysql.userpassword column, they can use it to log into the MySQL server for the given user. (With sufficient privileges, the 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, that is, 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 Version
3.22.12 or later.
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.11 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, any privileges granted in the
usertable indicate the user's global (superuser) privileges. These privileges apply to all databases on the server.
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.11 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 levels 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.12 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.16 Causes of
Access denied Errors. For advice on security issues,
see 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.
Hostvalue means that the privilege should be anded with the entry in the
hosttable that matches the given host name. You can find more information about this in the next chapter.
Hostvalues specified as IP numbers, you can specify a netmask indicating how many address bits to use for the network number. For example:
GRANT ALL PRIVILEGES on db.* to david@'188.8.131.52/255.255.255.0';This will allow everyone to connect from an IP where the following is true:
user_ip & netmask = host_ip.In the above example all IP:s in the interval 184.108.40.206 - 220.127.116.11 can connect to the MySQL 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). The
encrypted password is then used when the client/server is checking if
the password is correct (This is done without the encrypted password
ever traveling over the connection.) Note that from MySQL's
point of view the encrypted password is the REAL password, so you should
not give anyone access to it! In particular, don't give normal users
read access to the tables in the
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|
| || |
| || |
| || |
| || |
| ||Same as previous example|
Because you can use IP wild-card values in the
Host field (for example,
'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 wild-card 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.35
REVOKE Syntax. (You may find it helpful to refer to
section 6.9 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 wild cards or be blank in either table.
columns_priv tables are sorted on
User fields. This is similar to
db table sorting, although the sorting is simpler because
Host field may contain wild cards.
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, because 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. (They
Shutdown_priv column, so 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
Userfields are 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, that is, 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 the entries
in both tables 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 (for
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 or
mysqladmin reload to tell the
server to reload the grant tables. Otherwise your changes will have no
effect until you restart the server. If you change the grant tables manually
but forget to reload the privileges, you will be wondering why your changes
don't seem to make any difference!
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.
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 without a password and be treated as the anonymous user.
NOTE: The default privileges are different for Windows. See section 4.13.4 Running MySQL on Windows.
Because 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 Version 3.22 and above, use the
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
Only users with write/update access to the
mysql database can change the
password for others users. All normal users (not anonymous ones) can only
change their own password with either of the above commands or with
SET PASSWORD=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 re-read the grant tables (with
FLUSH PRIVILEGES), because 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. However, 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 re-create 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 Version 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
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. See section 7.35
There is also a lot of contributed programs like
can be used to create and administrate users. See section D Contributed Programs.
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 'some_pass' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'some_pass' 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:
'some_pass'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 thus 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 refresh, and
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 create 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('some_pass'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'), '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 Version 3.22.11 had fewer
privilege columns). For the
admin user, the more readable extended
INSERT syntax that is available starting with Version 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 email@example.com IDENTIFIED BY 'stupid'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.* TO custom@'%' IDENTIFIED BY 'stupid';
The reason that we do to grant statements for the user 'custom' is that we want the give the user access to MySQL both from the local machine with Unix sockets and from the remote machine 'whitehouse.gov' over TCP/IP.
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 in the
Contrib directory of the MySQL
In most cases you should use
GRANT to set up your users/passwords,
so the following only applies for advanced users. See section 7.35
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 (the encrypted value of
'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
Passwords must be encrypted when they are inserted in the
table, so 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, that
PASSWORD() will result in the same encrypted value as is stored in the
Unix password file. See section 6.5 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 to set up the initial grant table contents? If not, do so. See section 6.13 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. That is also a security risk, so 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 an entry for your host.
shell> mysqladmin -u root -pxxxx ver Access denied for user: 'root@localhost' (Using password: YES)It means that you are using a wrong password. See section 6.15 Setting Up Passwords. If you have forgot the root password, you can restart
--skip-grant-tablesto change the password. You can find more about this option later on in this manual section. If you get the above error even if you haven't specified a password, this means that you a wrong password in some
my.inifile. See section 4.16.5 Option Files. You can avoid using option files with the
--no-defaultsoption, as follows:
shell> mysqladmin --no-defaults -u root ver
mysql_fix_privilege_tablesscript? If not, do so. The structure of the grant tables changed with MySQL Version 3.22.11 when the
GRANTstatement became functional.
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.15 Setting 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. For example if you get an error message that contains
Using password: NO, this means that you tried to login without an password.
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
hosttable for the user/hostname combination from which you are trying to connect and then execute
mysqladmin flush-privileges. If you are not running MySQL Version 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.) Another reason for this error on Linux is that you are using a binary MySQL version that is compiled with a different glibc version than the one you are using. In this case you should either upgrade your OS/glibc or download the source MySQL version and compile this yourself. A source RPM is normally trivial to compile and install, so this isn't a big problem.
shell> mysqladmin -u root -pxxxx -h some-hostname ver Access denied for user: 'root' (Using password: YES)This means that MySQL got some error when trying to resolve the IP to a hostname. In this case you can execute
mysqladmin flush-hoststo reset the internal DNS cache. See section 13.2.11 How MySQL uses DNS. Some permanent solutions are:
localhostif you are running the server and the client on the same machine.
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 wild card--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 wild cards (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
''. Because 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
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.16.5 Option Files.
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 re-read the privilege tables. Otherwise your changes have no effect until the next time the server is restarted. Remember that after you set the
rootpassword with an
UPDATEcommand, you won't need to specify it until after you flush the privileges, because the server won't know you've changed the password yet!
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. (Note that there is no space between
-pand the password; you can also use the
--password=your_passsyntax to specify the password. If you use the
-poption alone, MySQL will prompt you for the password.)
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.
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 I.1.2 Creating trace files.
mysqldump mysqlcommand. As always, post your problem using the
mysqlbugscript. See section 2.3 How to Report Bugs or Problems. In some cases you may need to restart
Go to the first, previous, next, last section, table of contents.