这些 mysql 表包含授权信息：These mysql database tables contain grant information:

有关静态全局权限和动态全局权限之间差异的信息，请参见 静态与动态权限。For information about the differences between static and dynamic global privileges, see Static Versus Dynamic Privileges.)

授权表使用 InnoDB 存储引擎，并且是事务性的。每条语句要么对所有指定用户成功，要么在发生任何错误时回滚且不产生任何效果。Grant tables use the InnoDB storage engine and are transactional. Each statement 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:

服务器在启动时会将授权表的内容读取到内存中。你可以通过执行FLUSH PRIVILEGES语句，或者执行mysqladmin flush-privileges或mysqladmin reload命令来让它重新加载表。对授权表的更改会如第 8.2.13 节，“权限更改何时生效”中所述生效。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 8.2.13, “When Privilege Changes Take Effect”.

当您修改一个账户时，最好验证您的更改是否达到了预期效果。要检查给定账户的权限，请使用SHOW GRANTS语句。例如，要确定用户名和主机名分别为bob和pc84.example.com的账户所拥有的权限，请使用以下语句：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';

要显示账户的非权限属性，请使用SHOW CREATE USER：To display nonprivilege properties of an account, use SHOW CREATE USER:

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

服务器在访问控制的第一阶段和第二阶段都使用 mysql 数据库中的 user 和 db 表（参见 第 8.2 节，“访问控制和账户管理”）。user 和 db 表中的列如下所示。The server uses the user and db tables in the mysql database at both the first and second stages of access control (see Section 8.2, “Access Control and Account Management”). The columns in the user and db tables are shown here.

user 表中的 plugin 和 authentication_string 列存储认证插件和凭证信息。The user table plugin and authentication_string columns store authentication plugin and credential information.

服务器使用账户行中 plugin 列中指定的插件来验证该账户的连接尝试。The server uses the plugin named in the plugin column of an account row to authenticate connection attempts for the account.

plugin 列不能为空。在启动时，以及在执行 FLUSH PRIVILEGES 时，服务器会检查 user 表中的行。对于任何 plugin 列为空的行，服务器会在错误日志中写入如下形式的警告：The plugin column must be nonempty. At startup, and at runtime when FLUSH 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.

要为缺少插件的账户分配插件，请使用 ALTER USER 语句。To assign a plugin to an account that is missing one, use the ALTER USER statement.

password_expired 列允许数据库管理员使账户密码过期，并要求用户重置他们的密码。默认的 password_expired 值为 'N'，但可以通过 ALTER USER 语句将其设置为 'Y'。在账户密码过期后，该账户在随后的服务器连接中执行的所有操作都会导致错误，直到用户执行 ALTER USER 语句以设置新的账户密码。The 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 'Y' with the 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.

password_last_changed 是一个 TIMESTAMP 列，用于表示密码上次更改的时间。仅对于使用 MySQL 内置身份验证插件的账户（已弃用的 mysql_native_password、已弃用的 sha256_password 或 caching_sha2_password）此值才非 NULL。对于其他账户，例如使用外部身份验证系统认证的账户，此值为 NULL。password_last_changed is a TIMESTAMP 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 which is deprecated, sha256_password which is deprecated, or caching_sha2_password). The value is NULL for other accounts, such as those authenticated using an external authentication system.

password_last_changed 会被 CREATE USER、ALTER USER 和 SET PASSWORD 语句更新，也会被 GRANT 语句更新，这些语句会创建账户或更改账户密码。password_last_changed is updated by the CREATE USER, ALTER USER, and SET PASSWORD statements, and by GRANT statements that create an account or change an account password.

password_lifetime 表示账户密码的有效期（以天为单位）。如果密码已超过其有效期（通过 password_last_changed 列进行评估），当客户端使用该账户连接时，服务器会认为密码已过期。大于零的 N 值意味着密码必须每 N 天更改一次。值为 0 表示禁用自动密码过期。如果值为 NULL（默认值），则应用全局过期策略，该策略由 default_password_lifetime 系统变量定义。password_lifetime indicates the account password lifetime, in days. If the password is past its lifetime (assessed using the password_last_changed column), the server considers the password expired when clients connect using the account. A value of N 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 NULL (the default), the global expiration policy applies, as defined by the default_password_lifetime system variable.

account_locked 表示账户是否被锁定（见 第 8.2.20 节，“账户锁定”）。account_locked indicates whether the account is locked (see Section 8.2.20, “Account Locking”).

Password_reuse_history 是账户的 PASSWORD HISTORY 选项的值，或者对于默认历史为 NULL。Password_reuse_history is the value of the PASSWORD HISTORY option for the account, or NULL for the default history.

Password_reuse_time 是账户的 PASSWORD REUSE INTERVAL 选项的值，或者对于默认间隔为 NULL。Password_reuse_time is the value of the PASSWORD REUSE INTERVAL option for the account, or NULL for the default interval.

Password_require_current 对应账户的 PASSWORD REQUIRE 选项的值，如下表所示。Password_require_current corresponds to the value of the PASSWORD REQUIRE option for the account, as shown by the following table.

User_attributes 是一个 JSON 格式的列，用于存储未存储在其他列中的账户属性。INFORMATION_SCHEMA 通过 USER_ATTRIBUTES 表公开这些属性。User_attributes is a JSON-format column that stores account attributes not stored in other columns. The INFORMATION_SCHEMA exposes these attributes through the USER_ATTRIBUTES table.

User_attributes 列可能包含以下属性：The User_attributes column may contain these attributes:

如果没有适用的属性，User_attributes 为 NULL。If no attributes apply, User_attributes is NULL.

示例：一个拥有二级密码且部分撤销数据库权限的账户，其列值中包含 additional_password 和 Restrictions 属性：Example: An account that has a secondary password and partially revoked database privileges has additional_password and Restrictions 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"}

要确定存在哪些属性，请使用 JSON_KEYS() 函数：To determine which attributes are present, use the JSON_KEYS() function:

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

要提取特定属性，例如 Restrictions，可以这样做：To extract a particular attribute, such as Restrictions, do this:

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

以下是存储 multi_factor_authentication 的信息示例：Here is an example of the kind of information stored for multi_factor_authentication:

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

在访问控制的第二阶段，服务器会执行请求验证，以确保每个客户端对其发出的每个请求具有足够的权限。除了user和db授权表之外，对于涉及表的请求，服务器还可能查询tables_priv和columns_priv表。后者表在表和列级别提供了更精细的权限控制。它们具有如下表所示的列。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 and db 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.

Timestamp 和 Grantor 列分别设置为当前时间戳和 CURRENT_USER 值，但在其他方面未被使用。The Timestamp and Grantor columns are set to the current timestamp and the CURRENT_USER value, respectively, but are otherwise unused.

为了验证涉及存储例程的请求，服务器可能会查询 procs_priv 表，该表具有如下表中所示的列。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.

Routine_type 列是一个 ENUM 列，其值为 'FUNCTION' 或 'PROCEDURE'，用于指示该行所指的例程类型。此列允许对具有相同名称的函数和存储过程分别授予权限。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.

Timestamp 和 Grantor 列未被使用。The Timestamp and Grantor columns are unused.

proxies_priv 表记录有关代理账户的信息。它具有以下列：The proxies_priv table records information about proxy accounts. It has these columns:

为了使一个账户能够将 PROXY 权限授予其他账户，该账户必须在 proxies_priv 表中有一行，并且 With_grant 设置为 1，同时 Proxied_host 和 Proxied_user 设置为指示可以授予权限的账户或账户组。例如，在 MySQL 安装过程中创建的 'root'@'localhost' 账户在 proxies_priv 表中有一行，它允许为 ''@'' 授予 PROXY 权限，也就是对所有用户和所有主机。这使得 root 可以设置代理用户，并且可以将设置代理用户的权限委托给其他账户。参见 第 8.2.19 节，“代理用户”。For an account to be able to grant the PROXY privilege to other accounts, it must have a row in the proxies_priv table with With_grant set to 1 and Proxied_host 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 proxies_priv table that enables granting the PROXY privilege for ''@'', that is, for all users and all hosts. This enables root to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. See Section 8.2.19, “Proxy Users”.

global_grants 表列出了动态全局权限当前分配给用户帐户的情况。该表具有以下列：The global_grants table lists current assignments of dynamic global privileges to user accounts. The table has these columns:

default_roles 表列出了默认用户角色。它具有以下列：The default_roles table lists default user roles. It has these columns:

role_edges 表列出了角色子图的边。它有以下列：The role_edges table lists edges for role subgraphs. It has these columns:

password_history 表包含密码更改信息。它有以下列：The password_history table contains information about password changes. It has these columns:

password_history 表为每个账户累积足够数量的非空密码，以使 MySQL 能够检查账户密码历史长度和重用间隔。在发生密码更改尝试时，对超出两个限制的条目会自动进行裁剪。The 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.

如果一个账户被重命名，它的条目也会被重命名以匹配。如果一个账户被删除或者其认证插件被更改，它的条目将被移除。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 和 Proxied_host 的值在存储到授权表之前会被转换为小写。Host and Proxied_host values are converted to lowercase before being stored in the grant tables.

为了访问检查的目的，User、Proxied_user、authentication_string、Db 和 Table_name 值的比较是区分大小写的。Host、Proxied_host、Column_name 和 Routine_name 值的比较不区分大小写。For access-checking purposes, comparisons of User, Proxied_user, authentication_string, Db, and Table_name values are case-sensitive. Comparisons of Host, Proxied_host, Column_name, and Routine_name values are not case-sensitive.

user 和 db 表在各自的列中列出了每个权限，这些列声明为 ENUM('N','Y') DEFAULT 'N'。换句话说，每个权限可以被禁用或启用，默认是禁用的。The user and 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 disabled.

tables_priv、columns_priv 和 procs_priv 表将权限列声明为 SET 类型的列。这些列中的值可以包含该表控制的权限的任何组合。只有列值中列出的权限才是启用的。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.

只有 user 和 global_grants 表指定管理权限，例如 RELOAD、SHUTDOWN 和 SYSTEM_VARIABLES_ADMIN。管理操作是针对服务器本身的操作，而不是特定数据库的操作，因此没有必要在其他授权表中列出这些权限。因此，服务器只需要查询 user 和 global_grants 表，就可以确定用户是否可以执行管理操作。Only the user and global_grants tables specify administrative privileges, such as RELOAD, SHUTDOWN, and SYSTEM_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 the user and global_grants tables to determine whether a user can perform an administrative operation.

FILE 权限也仅在 user 表中指定。它本身不是管理权限，但用户在服务器主机上读取或写入文件的能力与所访问的数据库无关。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.

为了允许在 MySQL 授予表上同时进行 DML 和 DDL 操作，之前在 MySQL 授予表上获得行锁的读取操作将作为非锁定读取执行。在 MySQL 授予表上执行为非锁定读取的操作包括：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.

当使用-binlog_format=mixed时，从授权表读取数据的 DML 操作会以行事件的形式写入二进制日志，以确保操作在混合模式复制下的安全性。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语句读取授权表的数据时会报告警告。使用FOR SHARE 子句时，不支持对授权表加读锁。SELECT ... FOR SHARE statements that read data from grant tables report a warning. With the FOR SHARE clause, read locks are not supported on grant tables.

使用 SERIALIZABLE 隔离级别执行从授权表读取数据的 DML 操作会报告警告。通常在使用 SERIALIZABLE 隔离级别时会获取的读取锁在授权表上不受支持。DML operations that read data from grant tables and are executed using the SERIALIZABLE isolation level report a warning. Read locks that would normally be acquired when using the SERIALIZABLE isolation level are not supported on grant tables.