Client Access Control with MySQL - 12.3.1 Connection Request Checking
(Page 2 of 5 )
When a client attempts to connect, the server matches the sorted records to the client using the Host values first and the User values second:
If jon connects from the local host, the entry with localhost and jon in the Host and User columns matches first.
If james connects from localhost, the two entries with localhost in the Host column match the host, and the entry with the blank User value matches any username. Therefore, that's the first entry that matches both the client hostname and username. (The entry with % in the Host column matches localhost as well, but the server doesn't consider it in this case because it has already found a matching record.)
On the other hand, if james connects from pluto.example.com instead, the first entry that matches the hostname has a Host value of %.example.com. That entry's username doesn't match, so the server continues looking. The same thing happens with the entry that has a Host value of %.com: The hostname matches but the username does not. Finally, the entry with a Host value of % matches and the username matches as well.
When you attempt to determine which grant table record the server will find as the best match for a client, remember to take the sort order into account. In particular, the fact that Host matching is done before User matching leads to a property that might be surprising unless you're aware of it. Consider again the case where james connects from the local host. There are two entries with james in the User column, but neither is the first match. Host matching takes place first, so on that basis the entry that matches first is the anonymous-user entry: localhost matches the host from which james connects, and the blank User value matches any username. This means that when james connects from the local host, he will be treated as an anonymous user, not as james.
When you connect successfully to the server, the USER() function returns the username you specified and the client host from which you connected. The CURRENT_USER() function returns the username and hostname values from the User and Host columns of the user table record the server used to authenticate you. The two values may be different. If james connects from the local host, USER() and CURRENT_USER() have these values:
mysql> SELECT USER(), CURRENT_USER();
+-----------------+----------------+
| USER() | CURRENT_USER() |
+-----------------+----------------+
| james@localhost | @localhost |
+-----------------+----------------+
The username part of CURRENT_USER() is empty. This occurs because the server authenticates james as an anonymous user.
If james connects from pluto.example.com instead, USER() and CURRENT_USER() have these values:
mysql> SELECT USER(), CURRENT_USER();
+-------------------------+----------------+
| USER() | CURRENT_USER() |
+-------------------------+----------------+
| james@pluto.example.com | james@% |
+-------------------------+----------------+
Here the host part of CURRENT_USER() is % because the server authenticates james using the user table entry that has % as the Host value.
For connection attempts that the server denies, an error message results:
If the client attempts to connect from a host for which there is no record in the user table with a matching Host value, the error is
Host 'host_name' is not allowed to connect to
this MySQL server
If connections from the client host are allowed by one or more user table records, but no match can be found for the User and Password values, the error is
"Access denied for user:
'user_name'@'host_name'
Next: 12.3.2 Statement Privilege Checking >>
More MySQL Articles
More By Sams Publishing
|
 This article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 6 
