Movatterモバイル変換

Direct modification of grant tables using statements such asINSERT,UPDATE, orDELETE 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. To update the tables to the expected structure, perform the MySQL upgrade procedure. SeeChapter 3,Upgrading MySQL.

• Grant Table Overview

• The user and db Grant Tables

• The tables_priv and columns_priv Grant Tables

• The procs_priv Grant Table

• The proxies_priv Grant Table

• The global_grants Grant Table

• The default_roles Grant Table

• The role_edges Grant Table

• The password_history Grant Table

• Grant Table Scope Column Properties

• Grant Table Privilege Column Properties

• Grant Table Concurrency

Thesemysql database tables contain grant information:

For information about the differences between static and dynamic global privileges, seeStatic Versus Dynamic Privileges.)

In MySQL 8.0, grant tables use theInnoDB storage engine and are transactional. Before MySQL 8.0, grant tables used theMyISAM storage engine and were nontransactional. This change of grant table storage engine enables an accompanying change to the behavior of account-management statements such asCREATE USER orGRANT. 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:

In addition, a grant table may contain columns used for purposes other than scope or privilege assessment.

The server uses the grant tables in the following manner:

The server reads the contents of the grant tables into memory when it starts. You can tell it to reload the tables by issuing aFLUSH PRIVILEGES statement or executing amysqladmin flush-privileges ormysqladmin reload command. Changes to the grant tables take effect as indicated inSection 8.2.13, “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 theSHOW GRANTS statement. For example, to determine the privileges that are granted to an account with user name and host name values ofbob andpc84.example.com, use this statement:

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

To display nonprivilege properties of an account, useSHOW CREATE USER:

SHOW CREATE USER 'bob'@'pc84.example.com';

The server uses theuser anddb tables in themysql database at both the first and second stages of access control (seeSection 8.2, “Access Control and Account Management”). The columns in theuser anddb tables are shown here.

Theuser tableplugin andauthentication_string columns store authentication plugin and credential information.

The server uses the plugin named in theplugin column of an account row to authenticate connection attempts for the account.

Theplugin column must be nonempty. At startup, and at runtime whenFLUSH PRIVILEGES is executed, the server checksuser table rows. For any row with an emptyplugin column, the server writes a warning to the error log of this form:

[Warning] User entry 'user_name'@'host_name' has an empty pluginvalue. The user will be ignored and no one can login with this useranymore.

To assign a plugin to an account that is missing one, use theALTER USER statement.

Thepassword_expired column permits DBAs to expire account passwords and require users to reset their password. The defaultpassword_expired value is'N', but can be set to'Y' with theALTER 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 anALTER USER statement to establish a new account password.

password_last_changed is aTIMESTAMP column indicating when the password was last changed. The value is non-NULL only for accounts that use a MySQL built-in authentication plugin (mysql_native_password,sha256_password, orcaching_sha2_password). The value isNULL for other accounts, such as those authenticated using an external authentication system.

password_last_changed is updated by theCREATE USER,ALTER USER, andSET PASSWORD statements, and byGRANT statements that create an account or change an account password.

password_lifetime indicates the account password lifetime, in days. If the password is past its lifetime (assessed using thepassword_last_changed column), the server considers the password expired when clients connect using the account. A value ofN greater than zero means that the password must be changed everyN days. A value of 0 disables automatic password expiration. If the value isNULL (the default), the global expiration policy applies, as defined by thedefault_password_lifetime system variable.

account_locked indicates whether the account is locked (seeSection 8.2.20, “Account Locking”).

Password_reuse_history is the value of thePASSWORD HISTORY option for the account, orNULL for the default history.

Password_reuse_time is the value of thePASSWORD REUSE INTERVAL option for the account, orNULL for the default interval.

Password_require_current (added in MySQL 8.0.13) corresponds to the value of thePASSWORD REQUIRE option for the account, as shown by the following table.

User_attributes (added in MySQL 8.0.14) is a JSON-format column that stores account attributes not stored in other columns. As of MySQL 8.0.21, theINFORMATION_SCHEMA exposes these attributes through theUSER_ATTRIBUTES table.

TheUser_attributes column may contain these attributes:

If no attributes apply,User_attributes isNULL.

Example: An account that has a secondary password and partially revoked database privileges hasadditional_password andRestrictions attributes in the column value:

mysql> SELECT User_attributes FROM mysql.User WHERE User = 'u'\G*************************** 1. row ***************************User_attributes: {"Restrictions":                   [{"Database": "mysql", "Privileges": ["SELECT"]}],                  "additional_password": "hashed_credentials"}

To determine which attributes are present, use theJSON_KEYS() function:

SELECT User, Host, JSON_KEYS(User_attributes)FROM mysql.user WHERE User_attributes IS NOT NULL;

To extract a particular attribute, such asRestrictions, do this:

SELECT User, Host, User_attributes->>'$.Restrictions'FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';

Here is an example of the kind of information stored formulti_factor_authentication:

{  "multi_factor_authentication": [    {      "plugin": "authentication_ldap_simple",      "passwordless": 0,      "authentication_string": "ldap auth string",      "requires_registration": 0    },    {      "plugin": "authentication_fido",      "passwordless": 0,      "authentication_string": "",      "requires_registration": 1    }  ]}

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 theuser anddb grant tables, the server may also consult thetables_priv andcolumns_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.

TheTimestamp andGrantor columns are set to the current timestamp and theCURRENT_USER value, respectively, but are otherwise unused.

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

TheRoutine_type column is anENUM 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.

TheTimestamp andGrantor columns are unused.

Theproxies_priv table records information about proxy accounts. It has these columns:

For an account to be able to grant thePROXY privilege to other accounts, it must have a row in theproxies_priv table withWith_grant set to 1 andProxied_host andProxied_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 theproxies_priv table that enables granting thePROXY privilege for''@'', that is, for all users and all hosts. This enablesroot to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. SeeSection 8.2.19, “Proxy Users”.

Theglobal_grants table lists current assignments of dynamic global privileges to user accounts. The table has these columns:

Thedefault_roles table lists default user roles. It has these columns:

Therole_edges table lists edges for role subgraphs. It has these columns:

Thepassword_history table contains information about password changes. It has these columns:

Thepassword_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.

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.

Host andProxied_host values are converted to lowercase before being stored in the grant tables.

For access-checking purposes, comparisons ofUser,Proxied_user,authentication_string,Db, andTable_name values are case-sensitive. Comparisons ofHost,Proxied_host,Column_name, andRoutine_name values are not case-sensitive.

Theuser anddb tables list each privilege in a separate column that is declared asENUM('N','Y') DEFAULT 'N'. In other words, each privilege can be disabled or enabled, with the default being disabled.

Thetables_priv,columns_priv, andprocs_priv tables declare the privilege columns asSET 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.

Only theuser andglobal_grants tables specify administrative privileges, such asRELOAD,SHUTDOWN, andSYSTEM_VARIABLES_ADMIN. 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 theuser andglobal_grants tables to determine whether a user can perform an administrative operation.

TheFILE privilege also is specified only in theuser 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.

As of MySQL 8.0.22, to permit concurrent DML and DDL operations on MySQL grant tables, read operations that previously acquired row locks on MySQL grant tables are executed as non-locking reads. Operations that are performed as non-locking reads on MySQL grant tables include:

Statements that no longer acquire row locks when reading data from grant tables report a warning if executed while using statement-based replication.

When using -binlog_format=mixed, DML operations that read data from grant tables are written to the binary log as row events to make the operations safe for mixed-mode replication.

SELECT ... FOR SHARE statements that read data from grant tables report a warning. With theFOR SHARE clause, read locks are not supported on grant tables.

DML operations that read data from grant tables and are executed using theSERIALIZABLE isolation level report a warning. Read locks that would normally be acquired when using theSERIALIZABLE isolation level are not supported on grant tables.