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  |

+--------------------+--------+

12.3.1 Connection Request Checking

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'
```

12.3.2 Statement Privilege Checking

Each time the server receives a statement from a client, it checks the client's privileges to see whether it's allowed to execute the statement. For example, if you issue an UPDATE statement, you must possess the UPDATE privilege for each of the columns to be updated.

The server checks privileges in an additive fashion from the global level to the column-specific level. To check a statement, the server determines which privileges the statement requires, and then assesses whether the client possesses them by proceeding successively through the grant tables.

First, the server checks the client's global privileges in the user table. If these are sufficient, the server executes the statement. If the global privileges are not sufficient, the server adds any database-specific privileges indicated for the client in the db table and checks again. If the combined privileges are sufficient, the server executes the statement. Otherwise, it continues as necessary, checking the table-specific and column-specific privileges in the tables_priv and columns_priv tables. If, after checking all the grant tables, the client does not have sufficient privileges, the server refuses to execute the statement.

12.3.3 Resource Limit Checking

For an account that has resource limits, the server applies them to access control as follows:

If the client has a limit on the number of times per hour it can connect to the server, that limit applies in the first stage of access control, when the server determines whether to accept the client connection.
If the client has a limit on the number of queries or updates per hour it can issue, those limits apply in the second stage of access control. The server checks the limits for each query received before checking whether the client has the proper privileges to execute it.

12.3.4 Disabling Client Access Control

The --skip-grant-tables option tells the server not to use the grant tables to control client access. This option has the following effects:

You can connect from anywhere with no password, and you have full privileges to do anything. That's convenient if you've forgotten the root password and need to reset it because you can connect without knowing the password. On the other hand, because anyone else can connect, running the server in this mode is dangerous. To prevent remote clients from connecting over TCP/IP, you might want to use the --skip-networking option as well. Clients then can connect only from the local host using a Windows-named pipe or a Unix socket file.
--skip-grant-tables disables the GRANT, REVOKE, and SET PASSWORD statements. These statements require the in-memory copies of the grant tables, which aren't set up when you skip use of the tables. To make a change to the grant tables while those statements are inoperative, you must update them directly. Alternatively, when you connect to the server, you can issue a FLUSH PRIVILEGES statement to cause the server to read the tables. That will enable GRANT, REVOKE, and SET PASSWORD. (Note that if you also started the server with the --skip-networking option, you'll still need to restart it without that option to cause it to listen for TCP/IP connections again.)

< Day Day Up >