(mysql.info) connection-access
Info Catalog
(mysql.info) connecting
(mysql.info) privilege-system
(mysql.info) request-access
5.8.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:
* 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
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).
request-access, 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.0 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.
password-hashing, discusses password encryption further.
The following table shows how various combinations of `Host' and `User'
values in the `user' table apply to incoming connections.
`Host' *Value* `User' *Allowable Connections*
*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 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 information-functions.)
It returns a value in `USER_NAME@HOST_NAME' format that indicates the
`User' 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.
Info Catalog
(mysql.info) connecting
(mysql.info) privilege-system
(mysql.info) request-access
automatically generated byinfo2html