12.2.2 The Grant Tables - MySQL

Four grant tables in the mysql database contain most of the access control information used by the server. They contain information to indicate what the legal accounts are and the privileges held at each access level by each account:

• The user table contains a record for each account known to the server. The user record for an account lists its global privileges. It also indicates other information about the account, such as any resource limits it's subject to, and whether client connections that use the account must be made over a secure connection using the Secure Sockets Layer (SSL). Use of SSL connections is not covered on the Professional Exam.

• The db table lists database-specific privileges for accounts.

• The tables_priv table lists table-specific privileges for accounts.

• The columns_priv table lists column-specific privileges for accounts.

Every account must have a user table record because the server uses that table's contents when determining whether to accept or reject client connection attempts. An account also will have records in the other grant tables if it has privileges at other than the global level.

Each grant table has columns that identify which accounts its records apply to:

• The server decides whether a client can connect based on the Host, User, and Password columns of the user table. An account is defined by a hostname and username, so for a client to be able to connect, some record in the user table must match the host from which the client connects and the username given by the client. In addition, the client must provide the password listed in the matching record.

• After a client connects, the server determines its access privileges based on the Host and User columns of the user, db, tables_priv, and columns_priv tables. Any privileges enabled in the matching user table record may be used globally by the client. The privileges in the matching records of the other grant tables apply in more limited contexts. For example, privileges in a db table record apply to the database named in the record, but not to other databases.

Use of the grant tables for controlling what clients can do is discussed further in section 12.3, "Client Access Control."

There is also a fifth grant table named host that exists for historical reasons. It is not affected by the GRANT and REVOKE statements, so it's discussed no further here. For more information about the host table, see the MySQL Reference Manual.

The grant tables are stored as MyISAM tables. The MyISAM storage engine is always guaranteed to be enabled, which is not true for storage engines such as InnoDB and BDB.

As already mentioned, the server uses the information in the grant tables to determine whether to allow clients to connect, and to determine for every statement that a connected client issues whether the client has sufficient privileges to execute it. However, the server does not actually access the on-disk grant tables each time it needs to verify client access because that would result in a great deal of overhead. Instead, the server reads the grant tables into memory during its startup sequence and uses the in-memory copies to check client access.

The server refreshes its in-memory copies of the grant tables under the following conditions:

• You modify a user account in the on-disk tables by issuing a GRANT, REVOKE, or SET PASSWORD statement.

• You tell the server to reload the tables explicitly by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.