mysql语句查询用户权限过程详解

在mysql中，如何查看一个用户被授予了那些权限呢？ 授予用户的权限可能分全局层级权限、数据库层级权限、表层级别权限、列层级别权限、子程序层级权限。具体分类如下：

全局层级

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。grant all on *.*和revoke all on *.*只授予和撤销全局权限。

数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。grant all on db_name.*和revoke all on db_name.*只授予和撤销数据库权限。

表层级

表权限适用于一个给定表中的所有列。这些权限存储在mysql.tables_priv表中。grant all on db_name.tbl_name和revoke all on db_name.tbl_name只授予和撤销表权限。

列层级

列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用revoke时，您必须指定与被授权列相同的列。

子程序层级

create routine, alter routine, execute和grant权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了create routine外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。

1：那么我们来创建一个测试账号test，授予全局层级的权限。如下所示：

mysql> grant select,insert on *.* to test@'%' identified by 'test';
query ok, 0 rows affected (0.01 sec)
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
mysql> 

那么可以用下面两种方式查询授予test的权限。如下所示：

mysql> show grants for test;
+--------------------------------------------------------------------------------------------------------------+
| grants for test@% |
+--------------------------------------------------------------------------------------------------------------+
| grant select, insert on *.* to 'test'@'%' identified by password '*94bdcebe19083ce2a1f959fd02f964c7af4cfc29' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from mysql.user where user='test'\g;
*************************** 1. row ***************************
host: %
user: test
password: *94bdcebe19083ce2a1f959fd02f964c7af4cfc29
select_priv: y
insert_priv: y
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: 
x509_issuer: 
x509_subject: 
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: 
password_expired: n
1 row in set (0.04 sec)
error: 
no query specified
mysql> 

2：那么我们来创建一个测试账号test，授予数据库层级的权限。如下所示：

mysql> drop user test;
query ok, 0 rows affected (0.00 sec)
mysql> grant select,insert,update,delete on mydb.* to test@'%' identified by 'test';
query ok, 0 rows affected (0.01 sec)
mysql> 
mysql> select * from mysql.user where user='test'\g; --可以看到无任何授权。
mysql> select * from mysql.db where user='test'\g;
*************************** 1. row ***************************
host: %
db: mydb
user: test
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.04 sec)
error: 
no query specified
mysql> 
mysql> show grants for test;
+-----------------------------------------------------------------------------------------------------+
| grants for test@% |
+-----------------------------------------------------------------------------------------------------+
| grant usage on *.* to 'test'@'%' identified by password '*94bdcebe19083ce2a1f959fd02f964c7af4cfc29' |
| grant select, insert, update, delete on `mydb`.* to 'test'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> 

3：那么我们来创建一个测试账号test，授予表层级的权限。如下所示：

mysql> drop user test;
query ok, 0 rows affected (0.00 sec)
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
mysql> grant all on mydb.kkk to test@'%' identified by 'test';
query ok, 0 rows affected (0.01 sec)
mysql> 
mysql> show grants for test;
+-----------------------------------------------------------------------------------------------------+
| grants for test@% |
+-----------------------------------------------------------------------------------------------------+
| grant usage on *.* to 'test'@'%' identified by password '*94bdcebe19083ce2a1f959fd02f964c7af4cfc29' |
| grant all privileges on `mydb`.`kkk` to 'test'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.tables_priv\g;
*************************** 1. row ***************************
host: %
db: mydb
user: test
table_name: kkk
grantor: root@localhost
timestamp: 0000-00-00 00:00:00
table_priv: select,insert,update,delete,create,drop,references,index,alter,create view,show view,trigger
column_priv: 
1 row in set (0.01 sec)
error: 
no query specified
mysql> 

4：那么我们来创建一个测试账号test，授予列层级的权限。如下所示：

mysql> drop user test;
query ok, 0 rows affected (0.00 sec)
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
mysql> grant select (id, col1) on mydb.test1 to test@'%' identified by 'test';
query ok, 0 rows affected (0.01 sec)
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
mysql> 
mysql> select * from mysql.columns_priv;
+------+------+------+------------+-------------+---------------------+-------------+
| host | db | user | table_name | column_name | timestamp | column_priv |
+------+------+------+------------+-------------+---------------------+-------------+
| % | mydb | test | test1 | id | 0000-00-00 00:00:00 | select |
| % | mydb | test | test1 | col1 | 0000-00-00 00:00:00 | select |
+------+------+------+------------+-------------+---------------------+-------------+
2 rows in set (0.00 sec)
mysql> show grants for test;
+-----------------------------------------------------------------------------------------------------+
| grants for test@% |
+-----------------------------------------------------------------------------------------------------+
| grant usage on *.* to 'test'@'%' identified by password '*94bdcebe19083ce2a1f959fd02f964c7af4cfc29' |
| grant select (id, col1) on `mydb`.`test1` to 'test'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> 

5：那么我们来创建一个测试账号test，授子程序层级的权限。如下所示：

mysql> drop procedure if exists prc_test;
query ok, 0 rows affected (0.00 sec)
mysql> delimiter //
mysql> create procedure prc_test()
-> begin
-> select * from kkk;
-> end //
query ok, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> grant execute on procedure mydb.prc_test to test@'%' identified by 'test';
query ok, 0 rows affected (0.01 sec)
mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
mysql> 
mysql> show grants for test;
+-----------------------------------------------------------------------------------------------------+
| grants for test@% |
+-----------------------------------------------------------------------------------------------------+
| grant usage on *.* to 'test'@'%' identified by password '*94bdcebe19083ce2a1f959fd02f964c7af4cfc29' |
| grant execute on procedure `mydb`.`prc_test` to 'test'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.procs_priv where user='test';
+------+------+------+--------------+--------------+----------------+-----------+---------------------+
| host | db | user | routine_name | routine_type | grantor | proc_priv | timestamp |
+------+------+------+--------------+--------------+----------------+-----------+---------------------+
| % | mydb | test | prc_test | procedure | root@localhost | execute | 0000-00-00 00:00:00 |
+------+------+------+--------------+--------------+----------------+-----------+---------------------+
1 row in set (0.00 sec)
mysql> 

所以，如果需要查看用户被授予的权限，就需要从这五个层级来查看被授予的权限。从上到下或从小到上，逐一检查各个层级被授予的权限。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。