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:
-
The host from which you connect
-
Your MySQL user name
Identity checking is performed using the three user
table scope fields
(Host
, User
and 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:
-
A
Host
value may be a hostname or an IP number, or 'localhost'
to indicate the local host.
-
You can use the wildcard characters `%' and `_' in the
Host
field.
-
A
Host
value of '%'
matches any hostname. A blank Host
value is equivalent to '%'
. Note that these values match any
host that can create a connection to your server!
-
Wildcard characters are not allowed in the
User
field, but you can
specify a blank value, which matches any name. If the user
table
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).
-
The
Password
field can be blank. This does not mean that any password
matches, it means the user must connect without specifying a password.
Non-blank 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 Host
and
User
values in user
table entries apply to incoming
connections:
Host value | User value | Connections matched by entry
|
'thomas.loc.gov' | 'fred' | fred , connecting from thomas.loc.gov
|
'thomas.loc.gov' | '' | Any user, connecting from thomas.loc.gov
|
'%' | 'fred' | fred , connecting from any host
|
'%' | '' | Any user, connecting from any host
|
'%.loc.gov' | 'fred' | fred , connecting from any host in the loc.gov domain
|
'x.y.%' | 'fred' | fred , connecting from x.y.net , x.y.com ,x.y.edu , etc. (this is probably not useful)
|
'144.155.166.177' | 'fred' | fred , connecting from the host with IP address 144.155.166.177
|
'144.155.166.%' | 'fred' | fred , connecting from any host in the 144.155.166 class C subnet
|
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
host 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
match the 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 thomas.loc.gov
by
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 user
table
looks like this:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
When the server reads in the table, it orders the entries with the
most-specific Host
values first ('%'
in the Host
column
means ``any host'' and is least specific). Entries 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). The resulting
sorted 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 localhost
by
jeffrey
, the entries with 'localhost'
in the Host
column
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 thomas.loc.gov
by jeffrey
is matched by the
first entry, whereas a connection from whitehouse.gov
by
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 thomas.loc.gov
by
jeffrey
is first matched not by the entry containing 'jeffrey'
as the User
field value, but by the entry with no user name!
If you have problems connecting to the server, print out the user
table and sort it by hand to see where the first match is being made.