When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether 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, and then enters Stage 2 and waits for requests.
Credential checking is performed using the three
user table scope columns
Password). The server accepts the connection
only if the
columns in some
user table row match the client
host name and user name and the client supplies the password
specified in that row. The rules for permissible
User values are
given in Section 6.2.4, “Specifying Account Names”.
Your identity is based on two pieces of information:
The client host from which you connect
Your MySQL user name
User column value is nonblank, the user
name in an incoming connection must match exactly. If the
User value is blank, it matches any user name.
user table row that matches an incoming
connection has a blank user name, the user is considered to be an
anonymous user with no name, not a user with 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).
Password column can be blank. This is not a
wildcard and does not mean that any password matches. It means
that the user must connect without specifying a password. If the
server authenticates a client using a plugin, the authentication
method that the plugin implements may or may not use the password
Password column. In this case, it is
possible that an external password is also used to authenticate to
the MySQL server.
Password values in the
user table represent encrypted passwords. MySQL
does not store passwords as cleartext for anyone to see. Rather,
the password supplied by a user who is attempting to connect is
encrypted (using the
function). The encrypted password then is used during the
connection process when checking whether the password is correct.
This is done without the encrypted password ever traveling over
the connection. See Section 6.2.1, “Account User Names and Passwords”.
From MySQL's point of view, the encrypted password is the
real password, so you should never give
anyone access to it. In particular, do not give
nonadministrative users read access to tables in the
mysql system database.
The following table shows how various combinations of
Host values in the
user table apply to incoming connections.
||Any user, connecting from
||Any user, connecting from any host|
||Same as previous example|
It is possible for the client host name and user name of an
incoming connection to match more than one row in the
user table. The preceding set of examples
demonstrates this: Several of the entries shown match a connection
When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:
Whenever the server reads the
usertable into memory, it sorts the rows.
When a client attempts to connect, the server looks through the rows in sorted order.
The server uses the first row that matches the client host name and user name.
The server uses sorting rules that order rows with the
Host values first. Literal host
names and IP addresses are the most specific. (The specificity of
a literal IP address is not affected by whether it has a netmask,
198.51.100.0/255.255.255.0 are considered
equally specific.) The pattern
“any host” and is least specific. The empty string
'' also means “any host” but sorts
'%'. Rows with the same
Host value are ordered with the most-specific
User values first (a blank
User value means “any user” and is
least specific). For rows with equally-specific
User values, the
order is nondeterministic.
To see how this works, suppose that the
table looks like this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +-----------+----------+-
When the server reads the table into memory, it sorts the rows using the rules just described. The result after sorting looks like this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | localhost | root | ... | localhost | | ... | % | jeffrey | ... | % | root | ... +-----------+----------+-
When a client attempts to connect, the server looks through the
sorted rows and uses the first match found. For a connection from
jeffrey, two of
the rows from the table match: the one with
User values of
'', and the
one with values of
row appears first in sorted order, so that is the one the server
Here is another example. Suppose that the
table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | % | jeffrey | ... | h1.example.net | | ... +----------------+----------+-
The sorted table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | h1.example.net | | ... | % | jeffrey | ... +----------------+----------+-
A connection by
h1.example.net is matched by the first row,
whereas a connection by
jeffrey from any host
is matched by the second.
It is a common misconception to think that, for a given user
name, all rows that explicitly name that user are used first
when the server attempts to find a match for the connection.
This is not true. The preceding example illustrates this, where
a connection from
jeffrey is first matched not by the row
'jeffrey' as the
User column value, but by the row with no
user name. As a result,
authenticated as an anonymous user, even though he specified a
user name when connecting.
If you are able to connect to the server, but your privileges are
not what you expect, you probably are being authenticated as some
other account. To find out what account the server used to
authenticate you, use the
CURRENT_USER() function. (See
Section 12.16, “Information Functions”.) It returns a value in
format that indicates the
Host values from the matching
user table row. Suppose that
jeffrey connects and issues the following
mysql> SELECT CURRENT_USER(); +----------------+ | CURRENT_USER() | +----------------+ | @localhost | +----------------+
The result shown here indicates that the matching
user table row had a blank
User column value. In other words, the server
jeffrey as an anonymous user.
Another way to diagnose authentication problems is to print out
user table and sort it by hand to see where
the first match is being made.