Client Access Control with MySQL (Page 1 of 5 )
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).
12.3 Client Access Control
This section describes how the server uses account information in the grant tables to control which clients may connect and what they may do after connecting.
There are two stages of client access control:
In the first stage, a client attempts to connect and the server either accepts or rejects the connection. For the attempt to succeed, some entry in the user table must match the host from which a client connects, the username, and the password.
In the second stage (which occurs only if a client has already connected successfully), the server checks every query it receives from the client to see whether the client has sufficient privileges to execute it.
The server matches a client against entries in the grant tables based on the host from which the client connects and the username the client provides. However, it's possible for more than one record to match:
Host values in grant tables may be specified as patterns containing wildcard values. If a grant table contains entries for myhost.example.com, %.example.com, %.com, and %, all of them match a client who connects from myhost.example.com.
Patterns are not allowed for User values in grant table entries, but a username may be given as an empty string to specify an anonymous user. The empty string matches any username and thus effectively acts as a wildcard.
When the Host and User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list. Sorting takes place as follows:
In the Host column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.example.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.
In the User column, nonblank usernames sort ahead of blank usernames. That is, nonanonymous users sort ahead of anonymous users.
The server performs this sorting at startup. It reads the grant tables into memory, sorts them, and uses the in-memory copies for access control.
Suppose that the user table contains the following values in the Host and User columns:
+--------------------+--------+
| Host | User |
+--------------------+--------+
| localhost | |
| % | james |
| %.example.com | jen |
| %.com | jobril |
| localhost | jon |
| myhost.example.com | james |
+--------------------+--------+
When the server reads the grant tables into memory, it sorts the user table records as follows:
localhost and myhost.example.com are literal values, so they sort ahead of the other Host values that contain pattern characters. The Host values that contain pattern characters sort from most specific to least specific.
The two entries that have localhost in the Host column are ordered based on the User values. The entry with the nonblank username sorts ahead of the one with the blank username.
The sorting rules result in entries that are ordered like this:
+--------------------+--------+
| Host | User |
+--------------------+--------+
| localhost | jon |
| localhost | |
| myhost.example.com | james |
| %.example.com | jen |
| %.com | jobril |
| % | james |
+--------------------+--------+
Next: 12.3.1 Connection Request 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 
