HomeMySQL Page 2 - Client Access Control with MySQL
12.3.1 Connection Request Checking - MySQL
In our third and final article covering MySQL security, you will learn about client access control. There are exercises included (with answers) so you can test yourself on what you learned. This article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).
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: