Working with the MySQL Access Privilege System - 4.4.5 Access Control, Stage 1: Connection Verification
(Page 4 of 7 )
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:
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 entry 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 Section 4.4.6, "Access Control, Stage 2: Request Verification."
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 entry 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 Section 4.4.9, "Password Hashing in MySQL 4.1."
The following examples show how various combinations of Host and User values in the user table 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, 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 entry 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 entry 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' entry 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 entry, 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 entry containing 'jeffrey' as the User column value, but by the entry 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 entry 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. 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.
Next: 4.4.6 Access Control, Stage 2: Request Verification >>
More MySQL Articles
More By Sams Publishing
|
 This article is excerpted from the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345). Check it out today at your favorite bookstore. Buy this book now.
|
|
