这篇文章将为大家详细讲解有关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>grantselect,inserton*.*totest@'%'identifiedby'test';QueryOK,0rowsaffected(0.01sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>

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

mysql>showgrantsfortest;+--------------------------------------------------------------------------------------------------------------+|Grantsfortest@%|+--------------------------------------------------------------------------------------------------------------+|GRANTSELECT,INSERTON*.*TO'test'@'%'IDENTIFIEDBYPASSWORD'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'|+--------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>select*frommysql.userwhereuser='test'\G;***************************1.row***************************Host:%User:testPassword:*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29Select_priv:YInsert_priv:YUpdate_priv:NDelete_priv:NCreate_priv:NDrop_priv:NReload_priv:NShutdown_priv:NProcess_priv:NFile_priv:NGrant_priv:NReferences_priv:NIndex_priv:NAlter_priv:NShow_db_priv:NSuper_priv:NCreate_tmp_table_priv:NLock_tables_priv:NExecute_priv:NRepl_slave_priv:NRepl_client_priv:NCreate_view_priv:NShow_view_priv:NCreate_routine_priv:NAlter_routine_priv:NCreate_user_priv:NEvent_priv:NTrigger_priv:NCreate_tablespace_priv:Nssl_type:ssl_cipher:x509_issuer:x509_subject:max_questions:0max_updates:0max_connections:0max_user_connections:0plugin:mysql_native_passwordauthentication_string:password_expired:N1rowinset(0.04sec)ERROR:Noqueryspecifiedmysql>

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

mysql>dropusertest;QueryOK,0rowsaffected(0.00sec)mysql>grantselect,insert,update,deleteonMyDB.*totest@'%'identifiedby'test';QueryOK,0rowsaffected(0.01sec)mysql>mysql>select*frommysql.userwhereuser='test'\G;--可以看到无任何授权。mysql>select*frommysql.dbwhereuser='test'\G;***************************1.row***************************Host:%Db:MyDBUser:testSelect_priv:YInsert_priv:YUpdate_priv:YDelete_priv:YCreate_priv:NDrop_priv:NGrant_priv:NReferences_priv:NIndex_priv:NAlter_priv:NCreate_tmp_table_priv:NLock_tables_priv:NCreate_view_priv:NShow_view_priv:NCreate_routine_priv:NAlter_routine_priv:NExecute_priv:NEvent_priv:NTrigger_priv:N1rowinset(0.04sec)ERROR:Noqueryspecifiedmysql>mysql>showgrantsfortest;+-----------------------------------------------------------------------------------------------------+|Grantsfortest@%|+-----------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'test'@'%'IDENTIFIEDBYPASSWORD'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'||GRANTSELECT,INSERT,UPDATE,DELETEON`MyDB`.*TO'test'@'%'|+-----------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)mysql>

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

mysql>dropusertest;QueryOK,0rowsaffected(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>grantallonMyDB.kkktotest@'%'identifiedby'test';QueryOK,0rowsaffected(0.01sec)mysql>mysql>showgrantsfortest;+-----------------------------------------------------------------------------------------------------+|Grantsfortest@%|+-----------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'test'@'%'IDENTIFIEDBYPASSWORD'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'||GRANTALLPRIVILEGESON`MyDB`.`kkk`TO'test'@'%'|+-----------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)mysql>select*frommysql.tables_priv\G;***************************1.row***************************Host:%Db:MyDBUser:testTable_name:kkkGrantor:root@localhostTimestamp:0000-00-0000:00:00Table_priv:Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,CreateView,Showview,TriggerColumn_priv:1rowinset(0.01sec)ERROR:Noqueryspecifiedmysql>

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

mysql>dropusertest;QueryOK,0rowsaffected(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>grantselect(id,col1)onMyDB.TEST1totest@'%'identifiedby'test';QueryOK,0rowsaffected(0.01sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>mysql>select*frommysql.columns_priv;+------+------+------+------------+-------------+---------------------+-------------+|Host|Db|User|Table_name|Column_name|Timestamp|Column_priv|+------+------+------+------------+-------------+---------------------+-------------+|%|MyDB|test|TEST1|id|0000-00-0000:00:00|Select||%|MyDB|test|TEST1|col1|0000-00-0000:00:00|Select|+------+------+------+------------+-------------+---------------------+-------------+2rowsinset(0.00sec)mysql>showgrantsfortest;+-----------------------------------------------------------------------------------------------------+|Grantsfortest@%|+-----------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'test'@'%'IDENTIFIEDBYPASSWORD'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'||GRANTSELECT(id,col1)ON`MyDB`.`TEST1`TO'test'@'%'|+-----------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)mysql>

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

mysql>DROPPROCEDUREIFEXISTSPRC_TEST;QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER//mysql>CREATEPROCEDUREPRC_TEST()->BEGIN->SELECT*FROMkkk;->END//QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;mysql>grantexecuteonprocedureMyDB.PRC_TESTtotest@'%'identifiedby'test';QueryOK,0rowsaffected(0.01sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>mysql>showgrantsfortest;+-----------------------------------------------------------------------------------------------------+|Grantsfortest@%|+-----------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'test'@'%'IDENTIFIEDBYPASSWORD'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'||GRANTEXECUTEONPROCEDURE`MyDB`.`prc_test`TO'test'@'%'|+-----------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)mysql>select*frommysql.procs_privwhereUser='test';+------+------+------+--------------+--------------+----------------+-----------+---------------------+|Host|Db|User|Routine_name|Routine_type|Grantor|Proc_priv|Timestamp|+------+------+------+--------------+--------------+----------------+-----------+---------------------+|%|MyDB|test|PRC_TEST|PROCEDURE|root@localhost|Execute|0000-00-0000:00:00|+------+------+------+--------------+--------------+----------------+-----------+---------------------+1rowinset(0.00sec)mysql>

关于mysql中怎么查询用户权限就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。