MYSQL 用户权限类型划分

在日常数据库管理中，最好不要直接用root去登录，root作为超级管理员，权限太高，管理员可以额外创建一些普通用户来管理数据库。

用户权限是通过GRANT命令来授权的，普通用户常见的一些权限类型划分，我大概整理成如下几种。

1、super用户权限（即root） all  WITH GRANT OPTION;

root作为超级管理员，拥有最高权限，直接授予ALL权限。

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'admin123' WITH GRANT OPTION;

2、高权限用户

相对于普通用户，这种类型用户的权限会更高一点。

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'testdb'@'%%' IDENTIFIED BY 'admin123' WITH GRANT OPTION;

3、普通用户

普通用户的权限又有4种类型，我将它划分为：只读权限、读写权限、DML权限、DDL权限。

a. 只读权限

1. GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'testdb'@'%%' IDENTIFIED BY 'admin123'

2. GRANT SELECT, LOCK TABLES, SHOW VIEW ON `testdb`.* TO 'testdb'@'%%'

3. GRANT SELECT ON `mysql`.`help_topic` TO 'testdb'@'%%'

4. GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'testdb'@'%%'

5. GRANT SELECT ON `mysql`.`proc` TO 'testdb'@'%%'

6. GRANT SELECT ON `mysql`.`time_zone` TO 'testdb'@'%%'

7. GRANT SELECT ON `mysql`.`time_zone_name` TO 'testdb'@'%%'

8. GRANT SELECT ON `mysql`.`time_zone_transition` TO 'testdb'@'%%'

9. GRANT SELECT ON `mysql`.`help_relation` TO 'testdb'@'%%'

10. GRANT SELECT ON `mysql`.`general_log` TO 'testdb'@'%%'

11. GRANT SELECT ON `mysql`.`help_category` TO 'testdb'@'%%'

12. GRANT SELECT ON `mysql`.`func` TO 'testdb'@'%%'

13. GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'testdb'@'%%'

14. GRANT SELECT ON `mysql`.`slow_log` TO 'testdb'@'%%'

15. GRANT SELECT ON `mysql`.`event` TO 'testdb'@'%%'

16. GRANT SELECT ON `mysql`.`help_keyword` TO 'testdb'@'%%'

b. 读写权限

1. GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'testdb'@'%%' IDENTIFIED BY 'admin123'

2. GRANT ALL PRIVILEGES ON `testdb`.* TO 'testdb'@'%%'

3. GRANT SELECT ON `mysql`.`help_topic` TO 'testdb'@'%%'

4. GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'testdb'@'%%'

5. GRANT SELECT ON `mysql`.`proc` TO 'testdb'@'%%'

6. GRANT SELECT ON `mysql`.`time_zone` TO 'testdb'@'%%'

7. GRANT SELECT ON `mysql`.`time_zone_name` TO 'testdb'@'%%'

8. GRANT SELECT ON `mysql`.`time_zone_transition` TO 'testdb'@'%%'

9. GRANT SELECT ON `mysql`.`help_relation` TO 'testdb'@'%%'

10. GRANT SELECT ON `mysql`.`general_log` TO 'testdb'@'%%'

11. GRANT SELECT ON `mysql`.`help_category` TO 'testdb'@'%%'

12. GRANT SELECT ON `mysql`.`func` TO 'testdb'@'%%'

13. GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'testdb'@'%%'

14. GRANT SELECT ON `mysql`.`slow_log` TO 'testdb'@'%%'

15. GRANT SELECT ON `mysql`.`event` TO 'testdb'@'%%'

16. GRANT SELECT ON `mysql`.`help_keyword` TO 'testdb'@'%%'

c. 只有DML权限

1. GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'testdb'@'%%' IDENTIFIED BY 'admin123'

2. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON `testdb`.* TO 'testdb'@'%%'

3. GRANT SELECT ON `mysql`.`help_topic` TO 'testdb'@'%%'

4. GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'testdb'@'%%'

5. GRANT SELECT ON `mysql`.`proc` TO 'testdb'@'%%'

6. GRANT SELECT ON `mysql`.`time_zone` TO 'testdb'@'%%'

7. GRANT SELECT ON `mysql`.`time_zone_name` TO 'testdb'@'%%'

8. GRANT SELECT ON `mysql`.`time_zone_transition` TO 'testdb'@'%%'

9. GRANT SELECT ON `mysql`.`help_relation` TO 'testdb'@'%%'

10. GRANT SELECT ON `mysql`.`general_log` TO 'testdb'@'%%'

11. GRANT SELECT ON `mysql`.`help_category` TO 'testdb'@'%%'

12. GRANT SELECT ON `mysql`.`func` TO 'testdb'@'%%'

13. GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'testdb'@'%%'

14. GRANT SELECT ON `mysql`.`slow_log` TO 'testdb'@'%%'

15. GRANT SELECT ON `mysql`.`event` TO 'testdb'@'%%'

16. GRANT SELECT ON `mysql`.`help_keyword` TO 'testdb'@'%%'

d. 只有DDL权限

1. GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'testdb'@'%%' IDENTIFIED BY 'admin123'

2. GRANT CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `testdb`.* TO 'testdb'@'%%'

3. GRANT SELECT ON `mysql`.`help_topic` TO 'testdb'@'%%'

4. GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'testdb'@'%%'

5. GRANT SELECT ON `mysql`.`proc` TO 'testdb'@'%%'

6. GRANT SELECT ON `mysql`.`time_zone` TO 'testdb'@'%%'

7. GRANT SELECT ON `mysql`.`time_zone_name` TO 'testdb'@'%%'

8. GRANT SELECT ON `mysql`.`time_zone_transition` TO 'testdb'@'%%'

9. GRANT SELECT ON `mysql`.`help_relation` TO 'testdb'@'%%'

10. GRANT SELECT ON `mysql`.`general_log` TO 'testdb'@'%%'

11. GRANT SELECT ON `mysql`.`help_category` TO 'testdb'@'%%'

12. GRANT SELECT ON `mysql`.`func` TO 'testdb'@'%%'

13. GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'testdb'@'%%'

14. GRANT SELECT ON `mysql`.`slow_log` TO 'testdb'@'%%'

15. GRANT SELECT ON `mysql`.`event` TO 'testdb'@'%%'

16. GRANT SELECT ON `mysql`.`help_keyword` TO 'testdb'@'%%'

这是我对数据库用户权限的分类总结，如有不对，欢迎指正。