在mysql中我们一般分两类用户，一种是业务用户，一种是管理员账号。 平时创建账号的时候，可以分为两大类，一类是业务系统的账号，基于具体的数据库上面做的操作。一类是管理员账号，会涉及到 像 mysql、information_schema、performance_schema 系统数据库的管理， 用户做统计、分析，管理等等。 1.创建一个新的数据库。模拟业务数据库。
mysql> create database testdb ; Query OK, 1 row affected (0.00 sec)
2.分别创建基于业务的用户和基于管理员用户的所有权限all privileges 2.1授权grant all privileges
mysql> grant all privileges on testdb.* to test@'%' identified by 'testdb'; Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to admin@'%' identified by 'admindb'; Query OK, 0 rows affected (0.00 sec)
2.2检查权限check all privileges
mysql> show grants for test@'%'; +-----------------------------------------------------------------------------------------------------+ | Grants for test@'%' | +-----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*62048FFECC477DB7138C2CBCF04AAD3E0397A913' | | GRANT ALL PRIVILEGES ON `testdb`.* TO 'test'@'%' | +-----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show grants for admin@'%'; +--------------------------------------------------------------------------------------------------------------+ | Grants for admin@'%' | +--------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*7FG43WE11171F6BD1E6B6DEF0B70B72B40698453' | +--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
分析 从上面看到显示的都会all privilges，实际看不出来什么，所以我们可以反向考虑. 我回收一个基本的select 权限。看看剩余的权限都有哪些。 为啥这样呢。可以把all privileges 看成一个整体，拿走一个就不是整体了那就会把其余的全部列出来展现。 2.3回收权限revoke select
mysql> revoke select on testdb.* from 'test'@'%' ; Query OK, 0 rows affected (0.00 sec) mysql> revoke select on *.* from 'admin'@'%' ; Query OK, 0 rows affected (0.00 sec)
2.4检查回收完毕后的权限check all privileges again
mysql> show grants for test@'%'; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for test@'%'; | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*62048FFECC477DB7138C2CBCF04AAD3E0397A913' | | GRANT 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 `testdb`.* TO 'test'@'%' | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show grants for admin@'%'; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for admin@'%' | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT 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 ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*7FG43WE11171F6BD1E6B6DEF0B70B72B40698453' | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
我们简单整理对比一下： 整理下如下 1. 基于业务用户的 all privileges 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 2. 基于管理员用户的all privileges 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, RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER, CREATE TABLESPACE 结论 对比之后发现，基于管理员(*.*) 的所有权限比基于业务库(testdb.*) 上的所有权限多出了一下权限 RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER, CREATE TABLESPACE
