怎么在MySQL中查询用户权限？针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

介绍两种查看MySQL用户权限的两种方法

1、 使用MySQL grants命令

mysql>showgrantsforusername@localhost;+---------------------------------------------------------------------+|Grantsforroot@localhost|+---------------------------------------------------------------------+|GRANTALLPRIVILEGESON*.*TO'root'@'localhost'WITHGRANTOPTION|+---------------------------------------------------------------------+

需要注意的是：

● username和ip的组合需要是在mysql.user表中存在的，具体可以通过 select * from mysql.user 命令查看

● ip地址如果是通配符格式需要加引号，例如：show grants for root@'172.%';

2、 使用MySQL select命令

mysql>select*frommysql.userwhereuser='root'andhost='localhost'\G;***************************1.row***************************Host:localhostUser:rootPassword:**********************Select_priv:YInsert_priv:YUpdate_priv:YDelete_priv:YCreate_priv:YDrop_priv:YReload_priv:YShutdown_priv:YProcess_priv:YFile_priv:YGrant_priv:YReferences_priv:YIndex_priv:YAlter_priv:YShow_db_priv:YSuper_priv:YCreate_tmp_table_priv:YLock_tables_priv:YExecute_priv:YRepl_slave_priv:YRepl_client_priv:YCreate_view_priv:YShow_view_priv:YCreate_routine_priv:YAlter_routine_priv:YCreate_user_priv:YEvent_priv:YTrigger_priv:YCreate_tablespace_priv:Yssl_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.01sec)

知识点扩展：

我们来创建一个测试账号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><br>

我们来创建一个测试账号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><br>

关于怎么在MySQL中查询用户权限问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。