GNU Info
(mysql.info)Connection access
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 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 wild-card characters `%' and `_' in the `Host'
field.
* A `Host' value of `'%'' matches any hostname.
* A blank `Host' value means that the privilege should be anded with
the entry in the `host' table that matches the given host name.
You can find more information about this in the next chapter.
* As of MySQL Version 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:
GRANT ALL PRIVILEGES on db.* to david@'192.58.197.0/255.255.255.0';
This will allow everyone to connect from an IP where the following
is true:
user_ip & netmask = host_ip.
In the above example all IP:s in the interval 192.58.197.0 -
192.58.197.255 can connect to the MySQL server.
* Wild-card 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). The encrypted password is then used
when the client/server is checking if the password is correct (This is
done without the encrypted password ever traveling over the
connection.) Note that 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 normal users read access to the tables
in the `mysql' database!
The examples below show how various combinations of `Host' and `User'
values in `user' table entries 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', 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
`'144.155.166.0/255.255.255.0''`'fred'' Same as previous example
Because you can use IP wild-card values in the `Host' field (for
example, `'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 wild-card 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.
automatically generated by info2www version 1.2.2.9