MySQL 8使用部分撤销做权限限制

 

在 MySQL 8.0.16 之前，不可能授予全局权限的同时，排除某些schema；从 MySQL 8.0.16 开始，如果启用了 partial_revokes 系统变量，就可以做到这一点。具体来说，对于拥有全局权限的用户，partial_revokes 可以撤销特定schema的权限，同时保留其他schema的权限。这样施加的权限限制可能有助于管理具有全局权限但不应被允许访问某些schema的账户。例如，可以允许账户修改除 mysql 系统库之外的任何表。

partial_revokes 系统变量控制是否可以对账户设置权限限制。默认情况下，partial_revokes 被禁用，尝试部分撤销全局权限会产生错误：

mysql> show variables like '%partial_revokes%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | OFF   |
+-----------------+-------+
mysql> create user u@'%' identified by 'abcd';
mysql> grant select,insert on *.* to u@'%';
mysql> revoke insert on abce.* from u@'%';
ERROR 1141 (42000): There is no such grant defined for user 'u' on host '%'

 

需要开启partial_revokes 参数才可以：

mysql> set persist partial_revokes = on;
mysql> revoke insert on abce.* from u@'%';
mysql> show grants for u@'%';
+----------------------------------------+
| Grants for u@%                         |
+----------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u`@`%` |
| REVOKE INSERT ON `abce`.* FROM `u`@`%` |
+----------------------------------------+

部分撤销 schema 级别权限在 SHOW GRANTS 输出中显示为 REVOKE 语句。

mysql.user 系统表中记录通过部分撤销实施的权限限制。如果账户被部分撤销了权限，其 User_attributes 列值中就会有一个 Restrictions 属性：

mysql> SELECT User, Host, User_attributes->>'$.Restrictions' FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';
+------+------+--------------------------------------------------+
| User | Host | User_attributes->>'$.Restrictions'               |
+------+------+--------------------------------------------------+
| u    | %    | [{"Database": "abce", "Privileges": ["INSERT"]}] |
+------+------+--------------------------------------------------+
mysql> select * from mysql.user where user='u'\G
*************************** 1. row ***************************
                    Host: %
                    User: u
             Select_priv: Y
             Insert_priv: Y
             Update_priv: N
             Delete_priv: N
             Create_priv: N
               Drop_priv: N
             Reload_priv: N
           Shutdown_priv: N
            Process_priv: N
               File_priv: N
              Grant_priv: N
         References_priv: N
              Index_priv: N
              Alter_priv: N
            Show_db_priv: N
              Super_priv: N
   Create_tmp_table_priv: N
        Lock_tables_priv: N
            Execute_priv: N
         Repl_slave_priv: N
        Repl_client_priv: N
        Create_view_priv: N
          Show_view_priv: N
     Create_routine_priv: N
      Alter_routine_priv: N
        Create_user_priv: N
              Event_priv: N
            Trigger_priv: N
  Create_tablespace_priv: N
                ssl_type: 
              ssl_cipher: 0x
             x509_issuer: 0x
            x509_subject: 0x
           max_questions: 0
             max_updates: 0
         max_connections: 0
    max_user_connections: 0
                  plugin: caching_sha2_password
   authentication_string: $A$005$E
                                  P@ R4vNPCpngI?EoF4C2G.M5.1Eeqp6e3cIM7QS.XpVIintWrjndwzqw9
        password_expired: N
   password_last_changed: 2023-09-08 10:11:33
       password_lifetime: NULL
          account_locked: N
        Create_role_priv: N
          Drop_role_priv: N
  Password_reuse_history: NULL
     Password_reuse_time: NULL
Password_require_current: NULL
         User_attributes: {"Restrictions": [{"Database": "abce", "Privileges": ["INSERT"]}]}

 

部分撤销操作必须符合这些条件：
·可以使用部分撤销对不存在的 schema 施加限制，但前提是被撤销的权限是全局授予的。如果未在全局范围内授予权限，针对不存在的 schema 撤销权限会产生错误。
·部分撤销仅适用于 schema 级别。对于只在全局应用的权限（如 FILE 或 BINLOG_ADMIN），或表、列或常规权限，不能使用部分撤销。
·在权限分配中，启用 partial_revokes 会导致 MySQL 将 schema 名称中出现的未转义 _ 和 % SQL 通配符解释为字面字符，就像它们被转义为 \_ 和 \% 一样。由于这会改变 MySQL 解释权限的方式，因此在可能启用部分撤销的安装中，最好避免在权限分配中使用未转义的通配符。

如前所述，部分撤销 schema 级别权限在 SHOW GRANTS 输出中显示为 REVOKE 语句：
·当授予权限时，schema级别权限由其自身的 GRANT 语句表示：

mysql> CREATE USER u1;
mysql> GRANT UPDATE ON mysql.* TO u1;
mysql> GRANT DELETE ON world.* TO u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------+
| Grants for u1@%                       |
+---------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`        |
| GRANT UPDATE ON `mysql`.* TO `u1`@`%` |
| GRANT DELETE ON `world`.* TO `u1`@`%` |
+---------------------------------------+

部分撤销与显式授予schema权限
要为某些schema而非其他schema提供账户访问权限，部分撤销是明确授予schema级别访问权限而不授予全局权限的另一种方法。这两种方法各有利弊。

1.授予schema级别限而不授予全局权限：
·添加新schema：默认情况下，现有账户无法访问新的schema。对于应可访问新schema的任何账户，DBA必须授予schema级别访问权限。
·添加新账户：DBA必须为账户可访问的每个schema授予schema级别访问权限。

2.结合部分撤销授予全局权限：
·添加新schema：拥有全局权限的现有账户可访问新的schema。对于新的schema应无法访问的任何此类账户，DBA必须添加部分撤销。
·添加新账户：DBA必须授予全局权限，并对每个受限schema进行部分撤销。

对于访问权限仅限于几个schema的账户来说，使用显式schema级别授予的方法更方便。而使用部分撤销的方法则更适用于可广泛访问除少数schema外所有schema的账户。

禁用部分撤销特性
一旦开启该功能，任何一个账户有权限限制，都不能禁用部分撤销功能，否则会报错：
1.在启动的时候尝试禁用该功能，会在日志中报错，并开启部分撤销功能
2.在运行时尝试禁用该功能，会报错，且部分撤销功能保持原样

 

如果有账户有权限限制时，想关闭部分取消功能，必须先把账户的限制先取消。
1.找出哪些账户存在权限限制

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

2.移除权限限制
可以有多种方式：
(1)全局授权

grant insert on *.* to u@'%';

(2)schema级别授权

grant insert on abc.* to u@'%';
grant insert on abce.* to u@'%';

(3)全局取消权限

revoke insert on *.* to u@'%';

(4)删除账号

drop user u@'%';

 

3.移除权限限制后，禁用部分撤销

set persist partial_revokes = off;

 

部分撤销和复制
在复制环境中，如果任何一个节点开启了部分撤销，则所有节点都要开启部分撤销功能。否则可能会导致数据不一致。
开启了部分撤销功能后，grants语句的一个扩展语法记录会被写入binlog文件中：包括当前执行该命令的用户、以及他们当前活跃的角色。如果该用户或角色在从库不存在，复制的applier线程会停止grant语句并报错。
要确保所有执行grant的账户在主从节点上都存在，且他们要和主库一样，具有相同的角色。