6.2.2 Privilege System Grant Tables

The mysql system database includes several grant tables that contain information about user accounts and the privileges held by them.

Normally, to manipulate the contents of grant tables, you modify them indirectly by using account-management statements such as CREATE USER, GRANT, and REVOKE to set up accounts and control the privileges available to each one. See Section 13.7.1, “Account Management Statements”. The discussion here describes the underlying structure of the grant tables and how the server uses their contents when interacting with clients.


Direct modification of grant tables using statements such as INSERT, UPDATE, or DELETE is discouraged. The server is free to ignore rows that become malformed as a result of such modifications.

These mysql database tables contain grant information:

Other tables in the mysql database do not hold grant information and are discussed elsewhere:

Each grant table contains scope columns and privilege columns:

The server uses the grant tables in the following manner:

The server uses the user, db, and host tables in the mysql database at both the first and second stages of access control (see Section 6.2, “The MySQL Access Privilege System”). The columns in the user and db tables are shown here. The host table is similar to the db table but has a specialized use as described in Section 6.2.5, “Access Control, Stage 2: Request Verification”.

Table 6.3 user and db Table Columns

Table Nameuserdb
Scope columnsHostHost
Privilege columnsSelect_privSelect_priv
Security columnsssl_type 
Resource control columnsmax_questions 

The Event_priv and Trigger_priv columns were added in MySQL 5.1.6.

During the second stage of access control, the server performs request verification to ensure that each client has sufficient privileges for each request that it issues. In addition to the user, db, and host grant tables, the server may also consult the tables_priv and columns_priv tables for requests that involve tables. The latter tables provide finer privilege control at the table and column levels. They have the columns shown in the following table.

Table 6.4 tables_priv and columns_priv Table Columns

Table Nametables_privcolumns_priv
Scope columnsHostHost
Privilege columnsTable_privColumn_priv
Other columnsTimestampTimestamp

The Timestamp and Grantor columns are unused.

For verification of requests that involve stored routines, the server may consult the procs_priv table, which has the columns shown in the following table.

Table 6.5 procs_priv Table Columns

Table Nameprocs_priv
Scope columnsHost
Privilege columnsProc_priv
Other columnsTimestamp

The Routine_type column is an ENUM column with values of 'FUNCTION' or 'PROCEDURE' to indicate the type of routine the row refers to. This column enables privileges to be granted separately for a function and a procedure with the same name.

The Timestamp and Grantor columns are set to the current timestamp and the CURRENT_USER value, respectively, but are otherwise unused.

Scope columns in the grant tables contain strings. The default value for each is the empty string. The following table shows the number of characters permitted in each column.

Table 6.6 Grant Table Scope Column Lengths

Column NameMaximum Permitted Characters

For access-checking purposes, comparisons of User, Password, Db, and Table_name values are case sensitive. Comparisons of Host, Column_name, and Routine_name values are not case sensitive.

The user, db, and host tables list each privilege in a separate column that is declared as ENUM('N','Y') DEFAULT 'N'. In other words, each privilege can be disabled or enabled, with the default being disabled.

The tables_priv, columns_priv, and procs_priv tables declare the privilege columns as SET columns. Values in these columns can contain any combination of the privileges controlled by the table. Only those privileges listed in the column value are enabled.

Table 6.7 Set-Type Privilege Column Values

Table NameColumn NamePossible Set Elements
tables_privTable_priv'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter', 'Create View', 'Show view', 'Trigger'
tables_privColumn_priv'Select', 'Insert', 'Update', 'References'
columns_privColumn_priv'Select', 'Insert', 'Update', 'References'
procs_privProc_priv'Execute', 'Alter Routine', 'Grant'

Only the user table specifies administrative privileges, such as RELOAD and SHUTDOWN. Administrative operations are operations on the server itself and are not database-specific, so there is no reason to list these privileges in the other grant tables. Consequently, the server need consult only the user table to determine whether a user can perform an administrative operation.

The FILE privilege also is specified only in the user table. It is not an administrative privilege as such, but a user's ability to read or write files on the server host is independent of the database being accessed.

The server reads the contents of the grant tables into memory when it starts. You can tell it to reload the tables by issuing a FLUSH PRIVILEGES statement or executing a mysqladmin flush-privileges or mysqladmin reload command. Changes to the grant tables take effect as indicated in Section 6.2.6, “When Privilege Changes Take Effect”.

When you modify an account, it is a good idea to verify that your changes have the intended effect. To check the privileges for a given account, use the SHOW GRANTS statement. For example, to determine the privileges that are granted to an account with user name and host name values of bob and pc84.example.com, use this statement:

SHOW GRANTS FOR 'bob'@'pc84.example.com';