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 is equivalent to '%'. Note that these values match
any host that can create a connection to your server!
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 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
'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.
No comments:
Post a Comment