20230429-MySQL如何查询all有哪些权限?

问题：

当执行grant all privileges on . to cjc@‘localhost’;时，all包含哪些权限？

测试：

MySQL 5.7.34

创建测试用户

create user cjc@'localhost' identified by "********";

授权

grant all privileges on *.* to cjc@'localhost';
flush privileges;

查看权限

show grants for cjc@'localhost';
+----------------------------------------------------+
| Grants for cjc@localhost |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'cjc'@'localhost' |
+----------------------------------------------------+
1 row in set (0.00 sec)

回收insert权限

revoke insert on *.* from cjc@'localhost';
flush privileges;

查看剩余的权限

show grants for cjc@'localhost'\G;

GRANT SELECT, 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 'cjc'@'localhost'

结论：

在MySQL 5.7.34版本下，all有如下权限：

SELECT, UPDATE, DELETE,INSERT, 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

###chenjuchao 20230429###
欢迎关注我的公众号《IT小Chen》