(mysql.info.gz) Connection access
Info Catalog
(mysql.info.gz) Connecting
(mysql.info.gz) Privilege system
(mysql.info.gz) Request access
5.5.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, then enters Stage 2 and waits for requests.
Your identity is based on two pieces of information:
* The client host from which you connect
* Your MySQL username
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
record match the client hostname and username, and the client supplies
the password specified in that record.
`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.
* As of MySQL 3.23, 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:
mysql> 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 that lie in the range from `192.58.197.0' to
`192.58.197.255'.
* A blank `Host' value in a `db' table record 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).
You can find more information about the `host' table in
Request access.
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 will never
match 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 not
give anyone access to it! In particular, don't give non-administrative
users read access to the tables in the `mysql' database!
From version 4.1 on, MySQL employs a stronger authentication method
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. Password encryption is discussed
further in Password hashing.
The following examples show how various combinations of `Host' and
`User' values in the `user' table apply to incoming connections:
`Host' *Value* `User' *Connections Matched by Entry*
*Value*
`'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', and so on.
(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
`'144.155.166.0/255.255.255.0''`'fred'' Same as previous example
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 entries.
* When a client attempts to connect, the server looks through the
entries 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 in the table, it orders the entries 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. 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). 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
entries and uses the first match found. For a connection from
`localhost' by `jeffrey', two of the entries in 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
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 row
containing `'jeffrey'' as the `User' column value, but by the row with
no username! As a result, `jeffrey' will be 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. It returns a value in
`USER_NAME@HOST_NAME' format that indicates the `User' and `Host'
values from the matching `user' table record. 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.
The `CURRENT_USER()' function is available as of MySQL 4.0.6.
Information functions. 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.
Info Catalog
(mysql.info.gz) Connecting
(mysql.info.gz) Privilege system
(mysql.info.gz) Request access
automatically generated byinfo2html