partial_revokes 系统变量控制是否可以对帐户设置权限限制。默认情况下，partial_revokes 是禁用的，尝试部分撤销全局权限会产生错误：The partial_revokes system variable controls whether privilege restrictions can be placed on accounts. By default, partial_revokes is disabled and attempts to partially revoke global privileges produce an error:

mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT ON *.* TO u1;
mysql> REVOKE INSERT ON world.* FROM u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'

要允许 REVOKE 操作，请启用 partial_revokes：To permit the REVOKE operation, enable partial_revokes:

SET PERSIST partial_revokes = ON;

SET PERSIST 为正在运行的 MySQL 实例设置一个值。它还会保存该值，使其在随后的服务器重启中保留。要更改正在运行的 MySQL 实例的值而不让其在随后的重启中保留，请使用 GLOBAL 关键字而不是 PERSIST。参见 第 15.7.6.1 节，“SET 变量赋值语法”。SET PERSIST sets a value for the running MySQL instance. It also saves the value, causing it to carry over to subsequent server restarts. To change the value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword rather than PERSIST. See Section 15.7.6.1, “SET Syntax for Variable Assignment”.

启用 partial_revokes 后，部分撤销将成功：With partial_revokes enabled, the partial revoke succeeds:

mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+

SHOW GRANTS 会在其输出中将部分撤销列为 REVOKE 语句。结果显示 u1 拥有全局 SELECT 和 INSERT 权限，但 INSERT 无法在 world 模式的表上使用。也就是说，u1 对 world 表的访问仅限于读取。SHOW GRANTS lists partial revokes as REVOKE statements in its output. The result indicates that u1 has global SELECT and INSERT privileges, except that INSERT cannot be exercised for tables in the world schema. That is, access by u1 to world tables is read only.

服务器通过在 mysql.user 系统表中实施部分撤销来记录权限限制。如果一个账户有部分撤销，其 User_attributes 列的值会有一个 Restrictions 属性：The server records privilege restrictions implemented through partial revokes in the mysql.user system table. If an account has partial revokes, its User_attributes column value has a Restrictions attribute:

mysql> SELECT User, Host, User_attributes->>'$.Restrictions'
       FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';
+------+------+------------------------------------------------------+
| User | Host | User_attributes->>'$.Restrictions'                   |
+------+------+------------------------------------------------------+
| u1   | %    | [{"Database": "world", "Privileges": ["INSERT"]}] |
+------+------+------------------------------------------------------+

部分撤销操作受以下条件约束：Partial revoke operations are subject to these conditions:

如前所述，模式级权限的部分撤销会在 SHOW GRANTS 输出中显示为 REVOKE 语句。这与 SHOW GRANTS 表示“普通”模式级权限的方式有所不同：As mentioned previously, partial revokes of schema-level privileges appear in SHOW GRANTS output as REVOKE statements. This differs from how SHOW GRANTS represents “plain” schema-level privileges:

当用户授予权限时，授予者在该权限上的任何限制都会被受权者继承，除非受权者已经拥有该权限且没有限制。考虑以下两个用户，其中一个拥有全局 SELECT 权限：When a user grants a privilege, any restriction the grantor has on the privilege is inherited by the grantee, unless the grantee already has the privilege without the restriction. Consider the following two users, one of whom has the global SELECT privilege:

CREATE USER u1, u2;
GRANT SELECT ON *.* TO u2;

假设一个管理用户 admin 拥有一个全局但部分被撤销的 SELECT 权限：Suppose that an administrative user admin has a global but partially revoked SELECT privilege:

mysql> CREATE USER admin;
mysql> GRANT SELECT ON *.* TO admin WITH GRANT OPTION;
mysql> REVOKE SELECT ON mysql.* FROM admin;
mysql> SHOW GRANTS FOR admin;
+------------------------------------------------------+
| Grants for admin@%                                   |
+------------------------------------------------------+
| GRANT SELECT ON *.* TO `admin`@`%` WITH GRANT OPTION |
| REVOKE SELECT ON `mysql`.* FROM `admin`@`%`          |
+------------------------------------------------------+

如果 admin 全局授予 SELECT 给 u1 和 u2，每个用户的结果会有所不同：If admin grants SELECT globally to u1 and u2, the result differs for each user:

如果 GRANT 语句包含 AS user 子句，则应用的权限限制是该子句指定的用户/角色组合的权限限制，而不是执行该语句的用户的权限限制。有关 AS 子句的详细信息，请参阅 第 15.7.1.6 节，“GRANT 语句”。If a GRANT statement includes an AS user clause, the privilege restrictions applied are those on the user/role combination specified by the clause, rather than those on the user who executes the statement. For information about the AS clause, see Section 15.7.1.6, “GRANT Statement”.

对账户授予的新权限的限制将被添加到该账户现有的限制中：Restrictions on new privileges granted to an account are added to any existing restrictions for that account:

mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u1;
mysql> REVOKE INSERT ON mysql.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------------------------+
| Grants for u1@%                                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
| REVOKE INSERT ON `mysql`.* FROM `u1`@`%`                |
+---------------------------------------------------------+
mysql> REVOKE DELETE, UPDATE ON db2.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------------------------+
| Grants for u1@%                                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
| REVOKE UPDATE, DELETE ON `db2`.* FROM `u1`@`%`          |
| REVOKE INSERT ON `mysql`.* FROM `u1`@`%`                |
+---------------------------------------------------------+

特权限制的聚合适用于在特权被部分明确撤销时（如刚才所示），以及当限制从执行语句的用户或在 AS user 子句中提到的用户隐式继承时。Aggregation of privilege restrictions applies both when privileges are partially revoked explicitly (as just shown) and when restrictions are inherited implicitly from the user who executes the statement or the user mentioned in an AS user clause.

如果一个账户在某个模式上有权限限制：If an account has a privilege restriction on a schema:

如果一个账户在全局权限上有限制，下列任何操作都可以移除该限制：If an account has a restriction on a global privilege, the restriction is removed by any of these actions:

考虑一个用户 u1，他在全局拥有多个权限，但在 INSERT、UPDATE 和 DELETE 上有一些限制：Consider a user u1 who holds several privileges globally, but with restrictions on INSERT, UPDATE and DELETE:

mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u1;
mysql> REVOKE INSERT, UPDATE, DELETE ON mysql.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+----------------------------------------------------------+
| Grants for u1@%                                          |
+----------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%`  |
| REVOKE INSERT, UPDATE, DELETE ON `mysql`.* FROM `u1`@`%` |
+----------------------------------------------------------+

从没有限制的账户全局授予权限给 u1 会移除权限限制。例如，要移除 INSERT 限制：Granting a privilege globally to u1 from an account with no restriction removes the privilege restriction. For example, to remove the INSERT restriction:

mysql> GRANT INSERT ON *.* TO u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------------------------+
| Grants for u1@%                                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
| REVOKE UPDATE, DELETE ON `mysql`.* FROM `u1`@`%`        |
+---------------------------------------------------------+

在模式级别授予权限给 u1 会移除权限限制。例如，要移除 UPDATE 限制：Granting a privilege at the schema level to u1 removes the privilege restriction. For example, to remove the UPDATE restriction:

mysql> GRANT UPDATE ON mysql.* TO u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------------------------+
| Grants for u1@%                                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
| REVOKE DELETE ON `mysql`.* FROM `u1`@`%`                |
+---------------------------------------------------------+

撤销全局权限会移除该权限，包括其上的任何限制。例如，要移除 DELETE 限制（但会移除所有 DELETE 访问权限）：Revoking a global privilege removes the privilege, including any restrictions on it. For example, to remove the DELETE restriction (at the cost of removing all DELETE access):

mysql> REVOKE DELETE ON *.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+-------------------------------------------------+
| Grants for u1@%                                 |
+-------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO `u1`@`%` |
+-------------------------------------------------+

如果一个账户在全局和模式级别都有某个权限，你必须在模式级别撤销两次才能实现部分撤销。假设 u1 拥有这些权限，其中 INSERT 权限在全局和 world 模式下都有：If an account has a privilege at both the global and schema levels, you must revoke it at the schema level twice to effect a partial revoke. Suppose that u1 has these privileges, where INSERT is held both globally and on the world schema:

mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT ON *.* TO u1;
mysql> GRANT INSERT ON world.* TO u1;
mysql> SHOW GRANTS FOR u1;
+-----------------------------------------+
| Grants for u1@%                         |
+-----------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
| GRANT INSERT ON `world`.* TO `u1`@`%`   |
+-----------------------------------------+

撤销 INSERT 在 world 上，会撤销模式级别的权限（SHOW GRANTS 不再显示模式级别的 GRANT 语句）：Revoking INSERT on world revokes the schema-level privilege (SHOW GRANTS no longer displays the schema-level GRANT statement):

mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+-----------------------------------------+
| Grants for u1@%                         |
+-----------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
+-----------------------------------------+

再次撤销 INSERT 在 world 上，会对全局权限执行部分撤销（SHOW GRANTS 现在包括一个模式级别的 REVOKE 语句）：Revoking INSERT on world again performs a partial revoke of the global privilege (SHOW GRANTS now includes a schema-level REVOKE statement):

mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+

为了为某些模式提供账户访问权限而不影响其他模式，部分撤销提供了一种替代方法，相比于显式授予模式级访问权限而不授予全局权限的方法。两种方法各有优缺点。To provide access to accounts for some schemas but not others, partial revokes provide an alternative to the approach of explicitly granting schema-level access without granting global privileges. The two approaches have different advantages and disadvantages.

授予模式级权限而不授予全局权限：Granting schema-level privileges and not global privileges:

结合全局权限和部分撤销：Granting global privileges in conjunction with partial revokes:

使用显式模式级授权的方法对于访问仅限于少数模式的账户更为方便。使用部分撤销权限的方法对于访问几乎所有模式但排除少数模式的账户更为方便。The approach that uses explicit schema-level grant is more convenient for accounts for which access is limited to a few schemas. The approach that uses partial revokes is more convenient for accounts with broad access to all schemas except a few.

一旦启用，partial_revokes 如果任何账户存在权限限制，则无法禁用。如果存在这样的账户，禁用 partial_revokes 将失败：Once enabled, partial_revokes cannot be disabled if any account has privilege restrictions. If any such account exists, disabling partial_revokes fails:

要在存在限制时禁用 partial_revokes，必须先移除这些限制：To disable partial_revokes when restrictions exist, the restrictions first must be removed:

在移除所有权限限制后，可以禁用部分撤销：After all privilege restrictions are removed, it is possible to disable partial revokes:

SET PERSIST partial_revokes = OFF;

在复制场景中，如果在任何主机上启用了partial_revokes，则必须在所有主机上启用。否则，用于部分撤销全局权限的REVOKE语句在所有进行复制的主机上不会产生相同的效果，可能导致复制不一致或错误。In replication scenarios, if partial_revokes is enabled on any host, it must be enabled on all hosts. Otherwise, REVOKE statements to partially revoke a global privilege do not have the same effect for all hosts on which replication occurs, potentially resulting in replication inconsistencies or errors.

当启用 partial_revokes 时，GRANT 语句的扩展语法会被记录在二进制日志中，包括发出该语句的当前用户及其当前活动角色。如果以这种方式记录的用户或角色在从服务器上不存在，复制应用线程将在该 GRANT 语句处停止并报错。请确保所有在复制源服务器上发出或可能发出 GRANT 语句的用户账户在从服务器上也存在，并且拥有与源服务器上相同的角色集合。When partial_revokes is enabled, an extended syntax is recorded in the binary log for GRANT statements, including the current user that issued the statement and their currently active roles. If a user or a role recorded in this way does not exist on the replica, the replication applier thread stops at the GRANT statement with an error. Ensure that all user accounts that issue or might issue GRANT statements on the replication source server also exist on the replica, and have the same set of roles as they have on the source.