5.7.5. Access Control, Stage 1: Connection Verification
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.
Your identity is based on two pieces of information:
Identity checking is performed using the three
user table scope columns
(Host, User, and
Password). The server accepts the connection
only if the Host and User
columns in some user table row match the
client hostname and username and the client supplies the
password specified in that row.
Host values in the user
table 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
Host column values. These have the same
meaning as for pattern-matching operations performed with
the LIKE operator. For example, a
Host value of '%'
matches any hostname, whereas a value of
'%.mysql.com' matches any host in the
mysql.com domain.
-
For Host values 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@'192.58.197.0/255.255.255.0';
This allows david to connect from any
client host having an IP number client_ip
for which the following condition is true:
client_ip & netmask = host_ip
That is, for the GRANT statement just
shown:
client_ip & 255.255.255.0 = 192.58.197.0
IP numbers that satisfy this condition and can connect to
the MySQL server are those in the range from
192.58.197.0 to
192.58.197.255.
Note: The netmask can only be used to tell the server to use
8, 16, 24, or 32 bits of the address. Examples:
192.0.0.0/255.0.0.0: anything on the
192 class A network
192.168.0.0/255.255.0.0: anything on
the 192.168 class B network
192.168.1.0/255.255.255.0: anything
on the 192.168.1 class C network
192.168.1.1: only this specific IP
The following netmask (28 bits) will not work:
192.168.0.1/255.255.255.240
-
A blank Host value in a
db table row means that its privileges
should be combined with those in the row in the
host table that matches the client
hostname. The privileges are combined using an AND
(intersection) operation, not OR (union).
Section 5.7.6, “Access Control, Stage 2: Request Verification”, discusses use of the
host table further.
A blank Host value in the other grant
tables is the same as '%'.
Because you can use IP wildcard values in the
Host column (for example,
'144.155.166.%' to match every host on a
subnet), someone could 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 never matches the
Host column of the grant tables. An IP
wildcard value can match only IP numbers, not hostnames.
In the User column, wildcard characters are
not allowed, but you can specify a blank value, which matches
any name. If the user table row that matches
an incoming connection has a blank username, 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 username is used for all further access checking for the
duration of the connection (that is, during Stage 2).
The 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.
Non-blank Password values in the
user table 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 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.) 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 non-administrative users read access to
tables in the mysql database.
MySQL 5.1 employs the stronger authentication
method (first implemented in MySQL 4.1) that has better password
protection during the connection process than in earlier
versions. It is secure even if TCP/IP packets are sniffed or the
mysql database is captured.
Section 5.7.9, “Password Hashing as of MySQL 4.1”, discusses password
encryption further.
The following table shows how various combinations of
Host and User values in
the user table apply to incoming connections.
It is possible for the client hostname and username 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 from thomas.loc.gov by
fred.
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 user table
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
hostname and username.
To see how this works, suppose that the user
table looks like this:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
When the server reads the table into memory, it orders the rows
with the most-specific Host values first.
Literal hostnames and IP numbers are the most specific. The
pattern '%' means “any host” and
is least specific. 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 the
user table just shown, 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 localhost by jeffrey,
two of the rows from the table match: the one with
Host and User values of
'localhost' and '', and
the one with values of '%' and
'jeffrey'. The 'localhost'
row appears first in sorted order, so that is the one the server
uses.
Here is another example. Suppose that 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 by jeffrey from
thomas.loc.gov is matched by the first row,
whereas a connection by jeffrey from
whitehouse.gov is matched by the second.
It is a common misconception to think that, for a given
username, all rows that explicitly name that user are 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 row
containing 'jeffrey' as the
User column value, but by the row with no
username. As a result, jeffrey is
authenticated as an anonymous user, even though he specified a
username 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.10.3, “Information Functions”.) It
returns a value in
user_name@host_nameUser and
Host values from the matching
user table row. Suppose that
jeffrey connects and issues the following
query:
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
is treating jeffrey as an anonymous user.
Another thing you can do to diagnose authentication problems is
to print out the user table and sort it by
hand to see where the first match is being made.
