Pre-General Availability Draft: 2017-12-16
mysql system database includes several
grant tables that contain information about user accounts and the
privileges held by them. This section describes those tables. For
information about other tables in the system database, see
Section 5.3, “The mysql System Database”.
Normally, to manipulate the contents of grant tables, you modify
them indirectly by using account-management statements such as
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
DELETE is discouraged and done at
your own risk. The server is free to ignore rows that become
malformed as a result of such modifications.
For any operation that modifies a grant table, the server checks whether the table has the expected structure and produces an error if not. mysql_upgrade must be run to update the tables to the expected structure.
mysql database tables contain grant
user: User accounts, global privileges, and other non-privilege columns
global_grants: Assignments of dynamic global privileges to users; see Section 6.2.2, “Static Versus Dynamic Privileges”.
db: Database-level privileges
tables_priv: Table-level privileges
columns_priv: Column-level privileges
procs_priv: Stored procedure and function privileges
proxies_priv: Proxy-user privileges
default_roles: Default user roles
role_edges: Edges for role subgraphs
password_history: Password changes
In MySQL 8.0, grant tables use the
InnoDB storage engine and are transactional.
Before MySQL 8.0, grant tables used the
MyISAM storage engine and were
nontransactional. This change of grant table storage engine
enables an accompanying change to the behavior of
account-management statements such as
Previously, an account-management statement that named multiple
users could succeed for some users and fail for others. Now, each
statement is transactional and either succeeds for all named users
or rolls back and has no effect if any error occurs.
Each grant table contains scope columns and privilege columns:
Scope columns determine the scope of each row in the tables; that is, the context in which the row applies. For example, a
usertable row with
'bob'applies to authenticating connections made to the server from the host
h1.example.netby a client that specifies a user name of
bob. Similarly, a
dbtable row with
Dbcolumn values of
bobconnects from the host
h1.example.netto access the
columns_privtables contain scope columns indicating tables or table/column combinations to which each row applies. The
procs_privscope columns indicate the stored routine to which each row applies.
Privilege columns indicate which privileges a table row grants; that is, which operations it permits to be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. Section 6.2.7, “Access Control, Stage 2: Request Verification”, describes the rules for this.
The server uses the grant tables in the following manner:
usertable scope columns determine whether to reject or permit incoming connections. For permitted connections, any privileges granted in the
usertable indicate the user's global privileges. Any privileges granted in this table apply to all databases on the server.
global_grantstable lists current assignments of dynamic privileges to user accounts.
dbtable scope columns determine which users can access which databases from which hosts. The privilege columns determine the permitted operations. A privilege granted at the database level applies to the database and to all objects in the database, such as tables and stored programs.
columns_privtables are similar to the
dbtable, but are more fine-grained: They apply at the table and column levels rather than at the database level. A privilege granted at the table level applies to the table and to all its columns. A privilege granted at the column level applies only to a specific column.
procs_privtable applies to stored routines (procedures and functions). A privilege granted at the routine level applies only to a single procedure or function.
proxies_privtable indicates which users can act as proxies for other users and whether a user can grant the
PROXYprivilege to other users.
role_edgestables contain information about role relationships.
password_historytable retains previously chosen passwords to enable restrictions on password reuse. See Section 6.3.8, “Password Management”.
The server uses the
db tables in the
database at both the first and second stages of access control
(see Section 6.2, “The MySQL Access Privilege System”). The columns in the
db tables are shown
Table 6.4 user and db Table Columns
|Resource control columns|
authentication_string columns store
authentication plugin and credential information.
The server uses the plugin named in the
column of an account row to authenticate connection attempts for
plugin column must be nonempty. At startup,
and at runtime when
PRIVILEGES is executed, the server checks
user table rows. For any row with an empty
plugin column, the server writes a warning to
the error log of this form:
[Warning] User entry 'user_name'@'host_name' has an empty plugin value. The user will be ignored and no one can login with this user anymore.
password_expired column permits DBAs to
expire account passwords and require users to reset their
password. The default
password_expired value is
'N', but can be set to
ALTER USER statement.
After an account's password has been expired, all operations
performed by the account in subsequent connections to the server
result in an error until the user issues an
ALTER USER statement to establish a
new account password.
It is possible after password expiration to “reset” a password by setting it to its current value. As a matter of good policy, it is preferable to choose a different password. DBAs can enforce non-reuse by establishing an appropriate password-reuse policy. See Password Reuse Policy.
password_last_changed is a
TIMESTAMP column indicating when the password
was last changed. The value is non-
for accounts that use a MySQL built-in authentication plugin
caching_sha2_password). The value is
NULL for other accounts, such as those
authenticated using an external authentication system.
password_lifetime indicates the account
password lifetime, in days. If the password is past its lifetime
(assessed using the
column), the server considers the password expired when clients
connect using the account. A value of
greater than zero means that the password must be changed every
N days. A value of 0 disables automatic
password expiration. If the value is
default), the global expiration policy applies, as defined by the
account_locked indicates whether the account is
locked (see Section 6.3.12, “User Account Locking”).
Password_reuse_history is the value of the
PASSWORD HISTORY option for the account, or
NULL for the default history.
Password_reuse_time is the value of the
PASSWORD REUSE INTERVAL option for the account,
NULL for the default interval.
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
db grant tables,
the server may also consult the
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
Table 6.5 tables_priv and columns_priv Table Columns
columns are set to the current timestamp and the
CURRENT_USER value, respectively,
but are otherwise 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.6 procs_priv Table Columns
Routine_type column is an
ENUM column with values of
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.
columns are unused.
proxies_priv table records information
about proxy accounts. It has these columns:
For an account to be able to grant the
PROXY privilege to other accounts,
it must have a row in the
With_grant set to 1 and
Proxied_user set to indicate the account or
accounts for which the privilege can be granted. For example, the
'root'@'localhost' account created during MySQL
installation has a row in the
table that enables granting the
PROXY privilege for
''@'', that is, for all users and all hosts.
root to set up proxy users, as
well as to delegate to other accounts the authority to set up
proxy users. See Section 6.3.11, “Proxy Users”.
global_grants table lists current
assignments of dynamic privileges to user accounts. These
privileges are global. The table has these columns:
HOST: The user name and host name of the account to which the privilege is granted.
PRIV: The privilege name.
WITH_GRANT_OPTION: Whether the account can grant the privilege to other accounts.
default_roles table lists default user
roles. It has these columns:
USER: The account or role to which the default role applies.
DEFAULT_ROLE_USER: The default role.
role_edges table lists edges for role
subgraphs. It has these columns:
FROM_USER: The account that is granted a role.
TO_USER: The role that is granted to the account.
WITH_ADMIN_OPTION: Whether the account can grant the role to and revoke it from other accounts by using
WITH ADMIN OPTION.
password_history table contains information
about password changes. It has these columns:
User: The account for which the password change occurred.
Password_timestamp: The time when the password change occurred.
Password: The new password hash value.
password_history table accumulates a
sufficient number of nonempty passwords per account to enable
MySQL to perform checks against both the account password history
length and reuse interval. Automatic pruning of entries that are
outside both limits occurs when password-change attempts occur.
The empty password does not count in the password history and is subject to reuse at any time.
If an account is renamed, its entries are renamed to match. If an account is dropped or its authentication plugin is changed, its entries are removed.
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.7 Grant Table Scope Column Lengths
|Column Name||Maximum Permitted Characters|
For access-checking purposes, comparisons of
Table_name values are case sensitive.
Routine_name values are not case sensitive.
db 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
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
Table 6.8 Set-Type Privilege Column Values
|Table Name||Column Name||Possible Set Elements|
user table specifies administrative
privileges, such as
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.
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
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.8, “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
statement. For example, to determine the privileges that are
granted to an account with user name and host name values of
use this statement:
SHOW GRANTS FOR 'bob'@'pc84.example.com';
To display nonprivilege properties of an account, use
SHOW CREATE USER:
SHOW CREATE USER 'bob'@'pc84.example.com';