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:
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:
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:
The server performs this sorting when it starts. 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:
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 | +--------------------+--------+ 34.2.1. Connection Request CheckingWhen a client attempts to connect, the server matches the sorted records to the client using the Host values first and the User values second:
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:
34.2.2. Statement Privilege CheckingEach time the server receives a statement from a client, it checks the client's privileges to see whether it is 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. (For stored routines, the server checks the procs_priv table.) If, after checking all the grant tables, the client has insufficient privileges, the server refuses to execute the statement. 34.2.3. Resource Limit CheckingFor an account that has resource limits, the server applies them to access control as follows:
34.2.4. Disabling Client Access ControlThe --skip-grant-tables option tells the server not to use the grant tables to control client access. This option has the following effects:
|