mysql 撤销删除_权限全局授权部分撤销【8.0.16】

在 8.0前，需要对一个用户授权除一个库以外的所有库的话，就得向所有的库都grants一遍，如：

需要授权某个用户，除了mysql库的权限

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| mysql              |
| aaa                |
| bbb                |
| ccc                |
| ddd                |
+--------------------+

8.0.16前

每个库授权一次

grant all on aaa.* to u1;
grant all on bbb.* to u1;
grant all on ccc.* to u1;
grant all on ddd.* to u1;

如果使用全局授权再回收的话是不行的

grant all on *.* to u1;

mysql> REVOKE ALL ON mysql.* FROM u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'

mysql> show grants for u1;
+-----------------------------------------+
| Grants for u1@%                         |
+-----------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'u1'@'%' |
+-----------------------------------------+

8.0.16后支持回收部分全局权限

8.0.16后可以授予全局权限后再回收

mysql> CREATE USER u1 IDENTIFIED with mysql_native_password BY '123456';

mysql> SHOW GRANTS FOR u1;
+----------------------------------------------------------------------------------------------------------------+
| Grants for u1@%|
+----------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `u1`@`%`|
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `u1`@`%` |
+----------------------------------------------------------------------------------------------------------------+

# 设置 partial_revokes = 1,要启用全局部分回收要开启次参数,开启后若使用了全局部分权限回收功能，参数无法关闭

mysql> set global partial_revokes = 1;

# 回收mysql库的权限
mysql> REVOKE ALL ON mysql.* FROM u1;

# 标记了权限被回收
mysql> SHOW GRANTS FOR u1;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for u1@%|
+-------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `u1`@`%`|
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `u1`@`%` |
| REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* FROM `u1`@`%`|
+-------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec

也可以针对某些权限做回收 REVOKE INSERT, UPDATE, DELETE ON mysql.* FROM u1;

禁用部分撤销

partial_revokes 参数开启后，只要有部分撤销的用户授权存在，则无法关闭，若要禁用这个参数，需要将之前的授权做修改

1.确定哪些帐户有部分权限授权：

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

2.对于每个这样的帐户，删除其权限限制。假设上一步显示帐户 u1具有以下限制：

[{"Database":  "world",  "Privileges":  ["INSERT",  "DELETE"]

限制删除可以通过多种方式完成：

• 全局授予特权，不受限制：

GRANT  INSERT,  DELETE  ON  *.*  TO u1;

• 在架构级别授予特权：

GRANT  INSERT,  DELETE  ON world.*  TO u1;

• 全局撤消特权（假设不再需要它们）：

REVOKE  INSERT,  DELETE  ON  *.*  FROM u1;

• 删除帐户本身（假设不再需要该帐户）：

DROP USER u1;

删除所有特权限制后，可以禁用部分撤销

SET global partial_revokes = OFF;

注意

注意：在复制方案中，如果主机启用了 partial_revokes，那么所有的机器都要启用，否则可能导致主从不一致

参考

6.2.11 Account Categories​dev.mysql.com 6.2.12 Privilege Restriction Using Partial Revokes​dev.mysql.com