每天感悟
当教化是为了一类人统治另一类人的时候，教化就变成了教育，当你感受到了某种无私的教育的时候，请注意你的思维是否已经被关进了监狱。MySQL 8 的8.10已经推出有一段时间了，但是一部分项目和管理者还是停留在MySQL 5.7 ,那么哪项知识在 MySQL 8 和 MySQL 5.7 有了差别，这就是今天我们要说的部分revoke.
mysql> create user 'part_user'@'%' identified by 'part';
Query OK, 0 rows affected (0.10 sec)
mysql> grant all on *.* to 'part_user'@'%';
Query OK, 0 rows affected (0.56 sec)
mysql>mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.57 sec)
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| app_user |
| app_user_1000 |
| orders |
| payments |
+----------------+
4 rows in set (0.01 sec)
mysql> revoke all on test.app_user from 'part_user'@'%';
ERROR 1147 (42000): There is no such grant defined for user 'part_user' on host '%' on table 'app_user'
mysql>
mysql>
mysql>从上面的部分，我们可以很清晰的看到一个问题，我对一个用户的赋值是all，但是我如果对于这个拥有所有权限的用户，要收回某一个表的权限是不可以的，这就是在8.016 之前的MySQL 在回收权限方面的一个无法做到的问题，这里赋予是全部，回收也是全部，部分回收是不可以的。
mysql> revoke all on *.* from 'part_user'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for part_user@'%';
+---------------------------------------+
| Grants for part_user@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO `part_user`@`%` |
+---------------------------------------+
1 row in set (0.00 sec)
mysql>以下是完整的建立用户和用户权限展示的部分
mysql> create user 'part_user'@'%' identified by 'part';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to part_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for part_user@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for part_user@% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 `part_user`@`%` |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `part_user`@`%` |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke all on *.* from 'part_user'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for part_user@'%';
+---------------------------------------+
| Grants for part_user@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO `part_user`@`%` |
+---------------------------------------+
1 row in set (0.00 sec)
mysql>在普通的MySQL 5.7 或 MySQL 8.016 之前的版本，或者没有开启partial_revokes 的情况下对于 grant all的 账号是不能进行相关部分数据库的权限的回收的。
mysql> REVOKE ALL privileges ON app_user FROM 'part_user'@'%';
ERROR 1147 (42000): There is no such grant defined for user 'part_user' on host '%' on table 'app_user'
mysql> REVOKE ALL privileges ON test.app_user FROM 'part_user'@'%';
ERROR 1147 (42000): There is no such grant defined for user 'part_user' on host '%' on table 'app_user'
mysql>
mysql>
mysql> REVOKE ALL privileges ON test.* FROM 'part_user'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql>现在在8.016后的MySQL可以进行相关的操作，这样的操作有什么用处看似好像没有什么特别的，实际上这对我们赋予一些权限的操作精准和速度都有了提升。
举例我们设置一个数据库管理员的账号，但是这样账号里面我们不希望他对MySQL 数据库里面 mysql 数据库进行访问。那么我们可以这样来做.
mysql> create user db_admin identified by 'db_admin';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to db_admin@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> set persist partial_revokes = on;
Query OK, 0 rows affected (0.01 sec)
mysql> revoke all on mysql.* from db_admin@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> show create user db_admin@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for db_admin@% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `db_admin`@`%` IDENTIFIED WITH 'mysql_native_password' AS '*B9AB3C42DFF5AB8E2EC42D3D16A387144C87E470' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.05 sec)
mysql> SHOW GRANTS FOR 'db_admin'@'%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for db_admin@% |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 `db_admin`@`%` |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `db_admin`@`%` |
| 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 `db_admin`@`%` |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)这个功能让MySQL 针对与一些账号的分配权限更加的方便，实际上说，这对于云上的MySQL数据库更加的友好，因为在云上是不可能给你最大权限的，但购买RDS的人对于MySQL 的管理权的执着，让权限赋予变得复杂，而现在的情况，则变得更加的简单。
