MySQL查看用户权限（附带实例）

授予用户权限以后，可以使用如下命令查看当前用户拥有的权限：

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

例如，当前用户是 root，查看 root 用户拥有的权限，结果如下所示：

mysql> SHOW GRANTS FOR CURRENT_USER;
+--------------------------------------------------------------------------------------+
| Grants for root@%                                                                    |
+--------------------------------------------------------------------------------------+
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 'root'@'%' WITH GRANT OPTION
+--------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

可以使用如下命令查看某个用户拥有的全局权限：

SHOW GRANTS FOR 'user'@'host';

例如，查看 root 用户拥有的全局权限，SQL 语句如下所示，查询结果和上面的查询结果是一致的：

mysql> SHOW GRANTS FOR 'root'@'%';
+--------------------------------------------------------------------------------------+
| Grants for root@%                                                                    |
+--------------------------------------------------------------------------------------+
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 'root'@'%' WITH GRANT OPTION
+--------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

使用如下命令查看某个用户对某张表拥有的操作权限。在查看表权限之前，先将表 demo.test 的所有权限授予 zhang3 用户：

#MySQL 8.0 以前的版本
mysql> GRANT ALL PRIVILEGES ON demo.test TO zhang3'%' IDENTIFIED BY '1234567';
#MySQL 8.0
mysql> GRANT ALL PRIVILEGES ON demo.test to zhang3'%';

可以使用如下 SQL 语句查看用户对表拥有的操作权限：

mysql> SELECT Host,Db,User,Table_name,Grantor FROM mysql.tables_priv;
+------------+-----------+---------------+------------+-------------------+
| Host       | Db        | User          | Table_name | Grantor           |
+------------+-----------+---------------+------------+-------------------+
| localhost  | mysql     | mysql.session | user       | boot@             |
| %          | demo      | zhang3        | test       | root@172.16.210.1 |
| localhost  | sys       | mysql.sys     | sys_config | root@localhost    |
+------------+-----------+---------------+------------+-------------------+
6 rows in set (0.00 sec)

可以使用如下 SQL 语句查看 zhang3 用户对数据库拥有的操作权限。从结果中可以看到，zhang3 用户对数据库没有任何操作权限：

mysql> SELECT * FROM mysql.user WHERE User='zhang3'\G;
*************************** 1. row ***************************
       Host: %
       User: zhang3
  Select_priv: N
  Insert_priv: N
  Update_priv: N
  Delete_priv: N
  Create_priv: N
  Drop_priv: N
  Reload_priv: N
  Shutdown_priv: N
  Process_priv: N
  File_priv: N
  Grant_priv: N
  References_priv: N
  Index_priv: N
  Alter_priv: N
  Show_db_priv: N
  Super_priv: N
  Create_tmp_table_priv: N
  Lock_tables_priv: N
  Execute_priv: N
  Repl_slave_priv: N
  Repl_client_priv: N
  Create_view_priv: N
  Show_view_priv: N
  Create_routine_priv: N
  Alter_routine_priv: N
  Create_user_priv: N
  Event_priv: N
  Trigger_priv: N
  Create_tablespace_priv: N
  ssl_type:
  ssl_cipher: NULL
  x509_issuer: NULL
  x509_subject: NULL
  max_questions: 0
  max_updates: 0
  max_connections: 0
  max_user_connections: 0
  plugin: caching_sha2_password
  authentication_string: *A9A0055F400V,6PQHzhDGpT9A0022TSPwnmSQVymeYcK3EEqLq104LiADA
  password_expired: N
  password_last_changed: 2021-12-06 17:04:12
  password_lifetime: NULL
  account_locked: N
  Create_role_priv: N
  Drop_role_priv: N
  Password_reuse_history: NULL
  Password_reuse_time: NULL
  Password_require_current: NULL
  User_attributes: NULL
1 row in set (0.00 sec)

执行如下 SQL 语句再看一下，可以看到 zhang3 用户对数据库 demo 拥有 SELECT、INSERT、UPDATE 和 DELETE 权限，如果希望用户只对某个数据库拥有操作权限，则需要先将 user 表中对应的字段值设置为 N，再在 db 表中设置对应数据库的操作权限。

mysql> SELECT * FROM mysql.db WHERE User='zhang3'\G;
*************************** 1. row ***************************
       Host: %
       Db: demo
       User: zhang3
  Select_priv: Y
  Insert_priv: Y
  Update_priv: Y
  Delete_priv: Y
  Create_priv: N
  Drop_priv: N
  Grant_priv: N
  References_priv: N
  Index_priv: N
  Alter_priv: N
  Create_tmp_table_priv: N
  Lock_tables_priv: N
  Create_view_priv: N
  Show_view_priv: N
  Create_routine_priv: N
  Alter_routine_priv: N
  Execute_priv: N
  Event_priv: N
  Trigger_priv: N
1 row in set (0.00 sec)