6.2.2 Privilege System Grant Tables

Normally, you manipulate the contents of the grant tables in the mysql database indirectly by using statements such as 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.

These mysql database tables contain grant information:

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

Note

Modifications to tables in the mysql database normally are made by the server in response to statements such as CREATE USER, GRANT, or CREATE PROCEDURE. Direct modification of these tables using statements such as INSERT, UPDATE, or DELETE is not encouraged. The server is free to ignore rows that become malformed as a result of such modifications.

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
 UserDb
 PasswordUser
Privilege columnsSelect_privSelect_priv
 Insert_privInsert_priv
 Update_privUpdate_priv
 Delete_privDelete_priv
 Index_privIndex_priv
 Alter_privAlter_priv
 Create_privCreate_priv
 Drop_privDrop_priv
 Grant_privGrant_priv
 Create_view_privCreate_view_priv
 Show_view_privShow_view_priv
 Create_routine_privCreate_routine_priv
 Alter_routine_privAlter_routine_priv
 Execute_privExecute_priv
 Trigger_privTrigger_priv
 Event_privEvent_priv
 Create_tmp_table_privCreate_tmp_table_priv
 Lock_tables_privLock_tables_priv
 References_privReferences_priv
 Reload_priv 
 Shutdown_priv 
 Process_priv 
 File_priv 
 Show_db_priv 
 Super_priv 
 Repl_slave_priv 
 Repl_client_priv 
 Create_user_priv 
 Create_tablespace_priv 
Security columnsssl_type 
 ssl_cipher 
 x509_issuer 
 x509_subject 
 plugin 
 authentication_string 
Resource control columnsmax_questions 
 max_updates 
 max_connections 
 max_user_connections 

As of MySQL 5.5.7, the mysql.user table has plugin and authentication_string columns for storing authentication plugin information.

If the plugin column for an account row is empty, the server authenticates the account using either the mysql_native_password or mysql_old_password plugin, depending on whether the password hash value in the Password column used native hashing or the older pre-4.1 hashing method. Clients must match the password in the Password column of the account row.

If an account row names a plugin in the plugin column, the server uses it to authenticate connection attempts for the account. Whether the plugin uses the value in the Password column is up to the plugin.

Prior to MySQL 5.5.11, the length of the plugin column was 60 characters. This was increased to 64 characters in MySQL 5.5.11 for compatibility with the mysql.plugin table's name column. (Bug #11766610, Bug #59752)

During the second stage of access control, the server performs request verification to make sure 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
 DbDb
 UserUser
 Table_nameTable_name
  Column_name
Privilege columnsTable_privColumn_priv
 Column_priv 
Other columnsTimestampTimestamp
 Grantor 

The Timestamp and Grantor columns are set to the current timestamp and the CURRENT_USER value, respectively. However, they are unused and are discussed no further here.

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
 Db
 User
 Routine_name
 Routine_type
Privilege columnsProc_priv
Other columnsTimestamp
 Grantor

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 currently are unused and are discussed no further here.

The proxies_priv table was added in MySQL 5.5.7 and records information about proxy users. It has these columns:

Scope columns in the grant tables contain strings. They are declared as shown here; the default value for each is the empty string.

Table 6.6 Grant Table Scope Column Types

Column NameType
Host, Proxied_hostCHAR(60)
User, Proxied_userCHAR(16)
PasswordCHAR(41)
DbCHAR(64)
Table_nameCHAR(64)
Column_nameCHAR(64)
Routine_nameCHAR(64)

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

In the user, db, and host tables, each privilege is listed 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.

In the tables_priv, columns_priv, and procs_priv tables, the privilege columns are declared 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'

Administrative privileges (such as RELOAD or SHUTDOWN) are specified only in the user table. 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, to determine whether you can perform an administrative operation, the server need consult only the user table.

The FILE privilege also is specified only in the user table. It is not an administrative privilege as such, but your ability to read or write files on the server host is independent of the database you are accessing.

The mysqld 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's privileges, it is a good idea to verify that the changes set up privileges the way you want. To check the privileges for a given account, use the SHOW GRANTS statement (see Section 13.7.5.22, “SHOW GRANTS Syntax”). 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';