MySQL 8.4 Release Notes
MySQL 融入了用户账户类别的概念，基于 SYSTEM_USER 权限。MySQL incorporates the concept of user account categories, based
on the SYSTEM_USER
privilege.
MySQL 融入了用户账户类别的概念，根据是否拥有 SYSTEM_USER 权限来区分系统用户和普通用户：MySQL incorporates the concept of user account categories, with
system and regular users distinguished according to whether they
have the SYSTEM_USER privilege:
拥有
SYSTEM_USER权限的用户是系统用户。A user with theSYSTEM_USERprivilege is a system user.没有
SYSTEM_USER权限的用户是普通用户。A user without theSYSTEM_USERprivilege is a regular user.
SYSTEM_USER 权限会影响特定用户可以对哪些账户应用其他权限，以及该用户是否受到其他账户的保护：The SYSTEM_USER privilege has an
effect on the accounts to which a given user can apply its other
privileges, as well as whether the user is protected from other
accounts:
系统用户可以修改系统账户和普通账户。也就是说，具有在普通账户上执行某项操作的适当权限的用户，通过拥有
SYSTEM_USER权限，也能够在系统账户上执行该操作。系统账户只能由具有适当权限的系统用户修改，普通用户不能修改。A system user can modify both system and regular accounts. That is, a user who has the appropriate privileges to perform a given operation on regular accounts is enabled by possession ofSYSTEM_USERto also perform the operation on system accounts. A system account can be modified only by system users with appropriate privileges, not by regular users.拥有适当权限的普通用户可以修改普通账户，但不能修改系统账户。普通账户可以被拥有适当权限的系统用户和普通用户修改。A regular user with appropriate privileges can modify regular accounts, but not system accounts. A regular account can be modified by both system and regular users with appropriate privileges.
如果用户拥有对常规账户执行某个操作的适当权限，SYSTEM_USER 使该用户也能够对系统账户执行该操作。SYSTEM_USER 并不意味着任何其他权限，因此执行某个账户操作的能力仍然依赖于是否拥有其他所需的权限。例如，如果用户可以将 SELECT 和 UPDATE 权限授予常规账户，那么拥有 SYSTEM_USER 后，用户也可以将 SELECT 和 UPDATE 授予系统账户。If a user has the appropriate privileges to perform a given
operation on regular accounts,
SYSTEM_USER enables the user to
also perform the operation on system accounts.
SYSTEM_USER does not imply any
other privilege, so the ability to perform a given account
operation remains predicated on possession of any other required
privileges. For example, if a user can grant the
SELECT and
UPDATE privileges to regular
accounts, then with SYSTEM_USER
the user can also grant SELECT
and UPDATE to system accounts.
系统账户与普通账户之间的区别使得通过保护拥有 SYSTEM_USER 特权的账户免受未拥有该特权账户的影响，从而能够更好地控制某些账户管理问题。例如，CREATE USER 特权不仅允许创建新账户，还可以修改和删除现有账户。如果没有系统用户的概念，拥有 CREATE USER 特权的用户可以修改或删除任何现有账户，包括 root 账户。系统用户的概念使得对 root 账户（本身是系统账户）的修改可以仅由系统用户进行。拥有 CREATE
USER 特权的普通用户仍然可以修改或删除现有账户，但只限于普通账户。The distinction between system and regular accounts enables
better control over certain account administration issues by
protecting accounts that have the
SYSTEM_USER privilege from
accounts that do not have the privilege. For example, the
CREATE USER privilege enables not
only creation of new accounts, but modification and removal of
existing accounts. Without the system user concept, a user who
has the CREATE USER privilege can
modify or drop any existing account, including the
root account. The concept of system user
enables restricting modifications to the root
account (itself a system account) so they can be made only by
system users. Regular users with the CREATE
USER privilege can still modify or drop existing
accounts, but only regular accounts.
SYSTEM_USER 权限影响以下操作：The SYSTEM_USER privilege affects
these operations:
账户操作。Account manipulation.
账户操作包括创建和删除账户、授予和撤销权限、更改账户认证特性如凭证或认证插件，以及更改其他账户特性如密码过期策略。Account manipulation includes creating and dropping accounts, granting and revoking privileges, changing account authentication characteristics such as credentials or authentication plugin, and changing other account characteristics such as password expiration policy.
使用账户管理语句（例如
CREATE USER和GRANT）操作系统账户，需要SYSTEM_USER权限。为了防止账户以这种方式修改系统账户，可以通过不授予其SYSTEM_USER权限，使其成为普通账户。（但是，为了完全保护系统账户免受普通账户的影响，还必须对普通账户拒绝修改mysql系统模式的权限。参见 保护系统账户免受普通账户操作。）TheSYSTEM_USERprivilege is required to manipulate system accounts using account-management statements such asCREATE USERandGRANT. To prevent an account from modifying system accounts this way, make it a regular account by not granting it theSYSTEM_USERprivilege. (However, to fully protect system accounts against regular accounts, you must also withhold modification privileges for themysqlsystem schema from regular accounts. See Protecting System Accounts Against Manipulation by Regular Accounts.)终止当前会话及其内正在执行的语句。Killing current sessions and statements executing within them.
要终止以
SYSTEM_USER权限执行的会话或语句，您自己的会话必须具有SYSTEM_USER权限，以及任何其他所需权限（CONNECTION_ADMIN或已弃用的SUPER权限）。To kill a session or statement that is executing with theSYSTEM_USERprivilege, your own session must have theSYSTEM_USERprivilege, in addition to any other required privilege (CONNECTION_ADMINor the deprecatedSUPERprivilege).如果将服务器设置为离线模式的用户没有
SYSTEM_USER权限，那么已经连接的拥有SYSTEM_USER权限的客户端用户也不会被断开。不过，这些用户在服务器处于离线模式时无法发起新的连接，除非他们也拥有CONNECTION_ADMIN或SUPER权限。只有他们现有的连接不会被终止，因为终止连接需要拥有SYSTEM_USER权限。If the user that puts a server in offline mode does not have theSYSTEM_USERprivilege, connected client users who have theSYSTEM_USERprivilege are also not disconnected. However, these users cannot initiate new connections to the server while it is in offline mode, unless they have theCONNECTION_ADMINorSUPERprivilege as well. It is only their existing connection that is not terminated, because theSYSTEM_USERprivilege is required to do that.为存储对象设置
DEFINER属性。Setting theDEFINERattribute for stored objects.要将存储对象的
DEFINER属性设置为具有SYSTEM_USER权限的帐户，您必须拥有SYSTEM_USER权限，以及任何其他所需权限。To set theDEFINERattribute for a stored object to an account that has theSYSTEM_USERprivilege, you must have theSYSTEM_USERprivilege, in addition to any other required privilege.指定强制角色。Specifying mandatory roles.
具有
SYSTEM_USER权限的角色不能列在mandatory_roles系统变量的值中。A role that has theSYSTEM_USERprivilege cannot be listed in the value of themandatory_rolessystem variable.在 MySQL Enterprise Audit 的审计日志过滤器中覆盖 “abort” 项。Overriding “abort” items in MySQL Enterprise Audit’s audit log filter.
具有该
SYSTEM_USER权限的帐户会自动被分配该AUDIT_ABORT_EXEMPT权限，因此即使审计日志过滤器中的“中止”项会阻止查询，来自该帐户的查询仍会被执行。具有该SYSTEM_USER权限的帐户因此可以在审计配置错误后用来重新获得系统访问权限。参见第 8.4.5 节，“MySQL 企业审计”.Accounts with theSYSTEM_USERprivilege are automatically assigned theAUDIT_ABORT_EXEMPTprivilege, so that queries from the account are always executed even if an “abort” item in the audit log filter would block them. Accounts with theSYSTEM_USERprivilege can therefore be used to regain access to a system following an audit misconfiguration. See Section 8.4.5, “MySQL Enterprise Audit”.
在服务器中执行的会话被区分为系统会话或普通会话，类似于系统用户和普通用户之间的区分：Sessions executing within the server are distinguished as system or regular sessions, similar to the distinction between system and regular users:
拥有
SYSTEM_USER权限的会话是系统会话。A session that possesses theSYSTEM_USERprivilege is a system session.不拥有
SYSTEM_USER权限的会话是普通会话。A session that does not possess theSYSTEM_USERprivilege is a regular session.
常规会话只能执行常规用户允许的操作。系统会话还能够执行仅系统用户允许的操作。A regular session is able to perform only operations permitted to regular users. A system session is additionally able to perform operations permitted only to system users.
会话拥有的权限是直接授予其基础账户的权限，加上当前会话中所有已激活角色授予的权限。因此，一个会话可能是系统会话，因为其账户已直接获得 SYSTEM_USER 权限，或者因为会话已激活了拥有 SYSTEM_USER 权限的角色。授予账户但在会话中未激活的角色，不会影响会话权限。The privileges possessed by a session are those granted directly
to its underlying account, plus those granted to all roles
currently active within the session. Thus, a session may be a
system session because its account has been granted the
SYSTEM_USER privilege directly,
or because the session has activated a role that has the
SYSTEM_USER privilege. Roles
granted to an account that are not active within the session do
not affect session privileges.
因为激活和停用角色可能会更改会话所拥有的权限，所以一个会话可能会从普通会话变为系统会话，反之亦然。如果会话激活或停用具有 SYSTEM_USER 权限的角色，则会立即在该会话中进行普通会话和系统会话之间的适当更改：Because activating and deactivating roles can change the
privileges possessed by sessions, a session may change from a
regular session to a system session or vice versa. If a session
activates or deactivates a role that has the
SYSTEM_USER privilege, the
appropriate change between regular and system session takes
place immediately, for that session only:
如果普通会话激活具有
SYSTEM_USER权限的角色，则该会话将成为系统会话。If a regular session activates a role with theSYSTEM_USERprivilege, the session becomes a system session.如果系统会话停用拥有
SYSTEM_USER权限的角色，该会话将变为普通会话，除非其他拥有SYSTEM_USER权限的角色仍然处于活动状态。If a system session deactivates a role with theSYSTEM_USERprivilege, the session becomes a regular session, unless some other role with theSYSTEM_USERprivilege remains active.
这些操作对现有会话没有影响：These operations have no effect on existing sessions:
如果将
SYSTEM_USER权限授予或从一个账户撤销，该账户的现有会话在普通会话和系统会话之间不会发生变化。授予或撤销操作仅影响该账户随后连接的会话。If theSYSTEM_USERprivilege is granted to or revoked from an account, existing sessions for the account do not change between regular and system sessions. The grant or revoke operation affects only sessions for subsequent connections by the account.在会话中调用的存储对象执行的语句，将以父会话的系统或普通状态执行，即使该对象的
DEFINER属性指定了一个系统账户。Statements executed by a stored object invoked within a session execute with the system or regular status of the parent session, even if the objectDEFINERattribute names a system account.
由于角色激活仅影响会话而不影响账户，将具有 SYSTEM_USER 权限的角色授予普通账户，并不能保护该账户免受普通用户的影响。该角色仅保护在激活角色的账户中的会话，并且仅保护该会话不被普通会话终止。Because role activation affects only sessions and not accounts,
granting a role that has the
SYSTEM_USER privilege to a
regular account does not protect that account against regular
users. The role protects only sessions for the account in which
the role has been activated, and protects the session only
against being killed by regular sessions.
账户操作包括创建和删除账户、授予和撤销权限、更改账户认证特性如凭证或认证插件，以及更改其他账户特性如密码过期策略。Account manipulation includes creating and dropping accounts, granting and revoking privileges, changing account authentication characteristics such as credentials or authentication plugin, and changing other account characteristics such as password expiration policy.
账户操作可以通过两种方式进行：Account manipulation can be done two ways:
通过使用账户管理语句，例如
CREATE USER和GRANT。这是推荐的方法。By using account-management statements such asCREATE USERandGRANT. This is the preferred method.通过直接修改授权表，使用诸如
INSERT和UPDATE之类的语句。这种方法不推荐，但对于在包含授权表的mysql系统模式上具有相应权限的用户是可行的。By direct grant-table modification using statements such asINSERTandUPDATE. This method is discouraged but possible for users with the appropriate privileges on themysqlsystem schema that contains the grant tables.
为了完全保护系统账户不被特定账户修改，应将其设置为普通账户，并且不要赋予其修改 mysql 架构的权限：To fully protect system accounts against modification by a given
account, make it a regular account and do not grant it
modification privileges for the mysql schema:
使用账户管理语句操作系统账户需要
SYSTEM_USER权限。为了防止账户通过这种方式修改系统账户，应将其设置为普通账户，即不授予SYSTEM_USER权限。这也包括不将SYSTEM_USER权限授予该账户被授予的任何角色。TheSYSTEM_USERprivilege is required to manipulate system accounts using account-management statements. To prevent an account from modifying system accounts this way, make it a regular account by not grantingSYSTEM_USERto it. This includes not grantingSYSTEM_USERto any roles granted to the account.对
mysql架构的权限允许通过直接修改授权表来操作系统账户，即使修改账户是普通账户。为了限制普通账户未经授权直接修改系统账户，请不要将对mysql架构的修改权限授予该账户（或授予该账户的任何角色）。如果普通账户必须具有适用于所有架构的全局权限，可以通过使用部分撤销施加的权限限制来防止对mysql架构的修改。请参见 第 8.2.12 节，“使用部分撤销进行权限限制”。Privileges for themysqlschema enable manipulation of system accounts through direct modification of the grant tables, even if the modifying account is a regular account. To restrict unauthorized direct modification of system accounts by a regular account, do not grant modification privileges for themysqlschema to the account (or any roles granted to the account). If a regular account must have global privileges that apply to all schemas,mysqlschema modifications can be prevented using privilege restrictions imposed using partial revokes. See Section 8.2.12, “Privilege Restriction Using Partial Revokes”.
Note
与撤销 SYSTEM_USER 权限不同，后者会阻止账户修改系统账户但不阻止修改普通账户，撤销 mysql 架构权限会阻止账户修改系统账户以及普通账户。这通常不是问题，因为如前所述，不建议直接修改授权表。Unlike withholding the
SYSTEM_USER privilege, which
prevents an account from modifying system accounts but not
regular accounts, withholding mysql schema
privileges prevents an account from modifying system accounts
as well as regular accounts. This should not be an issue
because, as mentioned, direct grant-table modification is
discouraged.
假设你想创建一个用户 u1，他在所有架构上拥有所有权限，但 u1 应该是一个普通用户，没有修改系统账户的能力。假设 partial_revokes 系统变量已启用，可按如下方式配置 u1：Suppose that you want to create a user u1 who
has all privileges on all schemas, except that
u1 should be a regular user without the
ability to modify system accounts. Assuming that the
partial_revokes system variable
is enabled, configure u1 as follows:
CREATE USER u1 IDENTIFIED BY 'password';
GRANT ALL ON *.* TO u1 WITH GRANT OPTION;
-- GRANT ALL includes SYSTEM_USER, so at this point
-- u1 can manipulate system or regular accounts
REVOKE SYSTEM_USER ON *.* FROM u1;
-- Revoking SYSTEM_USER makes u1 a regular user;
-- now u1 can use account-management statements
-- to manipulate only regular accounts
REVOKE ALL ON mysql.* FROM u1;
-- This partial revoke prevents u1 from directly
-- modifying grant tables to manipulate accounts
为了防止一个账户访问所有 mysql 系统模式，请撤销其在 mysql 模式上的所有权限，如上所示。也可以允许部分 mysql 模式访问，例如只读访问。以下示例创建了一个账户，该账户在所有模式上具有 SELECT、INSERT、UPDATE 和 DELETE 权限，但在 mysql 模式上仅具有 SELECT 权限：To prevent all mysql system schema access by
an account, revoke all its privileges on the
mysql schema, as just shown. It is also
possible to permit partial mysql schema
access, such as read-only access. The following example creates
an account that has SELECT,
INSERT, UPDATE, and
DELETE privileges globally for all schemas,
but only SELECT for the
mysql schema:
CREATE USER u2 IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u2;
REVOKE INSERT, UPDATE, DELETE ON mysql.* FROM u2;
另一种可能性是撤销所有 mysql 模式的权限，但授予对特定 mysql 表或列的访问权限。即使对 mysql 执行部分撤销，也可以做到。以下语句允许在 mysql 模式下对 u1 进行只读访问，但仅限于 db 表以及 user 表的 Host 和 User 列：Another possibility is to revoke all mysql
schema privileges but grant access to specific
mysql tables or columns. This can be done
even with a partial revoke on mysql. The
following statements enable read-only access to
u1 within the mysql
schema, but only for the db table and the
Host and User columns of
the user table:
CREATE USER u3 IDENTIFIED BY 'password';
GRANT ALL ON *.* TO u3;
REVOKE ALL ON mysql.* FROM u3;
GRANT SELECT ON mysql.db TO u3;
GRANT SELECT(Host,User) ON mysql.user TO u3;