MySQL查询用户权限的方法总结
介绍两种查看MySQL用户权限的两种方法
1、使用MySQLgrants命令
mysql>showgrantsforusername@localhost; +---------------------------------------------------------------------+ |Grantsforroot@localhost| +---------------------------------------------------------------------+ |GRANTALLPRIVILEGESON*.*TO'root'@'localhost'WITHGRANTOPTION| +---------------------------------------------------------------------+
需要注意的是：
●username和ip的组合需要是在mysql.user表中存在的，具体可以通过select*frommysql.user命令查看
●ip地址如果是通配符格式需要加引号，例如：showgrantsforroot@'172.%';
2、使用MySQLselect命令
mysql>select*frommysql.userwhereuser='root'andhost='localhost'\G; ***************************1.row*************************** Host:localhost User:root Password:********************** Select_priv:Y Insert_priv:Y Update_priv:Y Delete_priv:Y Create_priv:Y Drop_priv:Y Reload_priv:Y Shutdown_priv:Y Process_priv:Y File_priv:Y Grant_priv:Y References_priv:Y Index_priv:Y Alter_priv:Y Show_db_priv:Y Super_priv:Y Create_tmp_table_priv:Y Lock_tables_priv:Y Execute_priv:Y Repl_slave_priv:Y Repl_client_priv:Y Create_view_priv:Y Show_view_priv:Y Create_routine_priv:Y Alter_routine_priv:Y Create_user_priv:Y Event_priv:Y Trigger_priv:Y Create_tablespace_priv:Y 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 1rowinset(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:MyDB User:test Table_name:kkk Grantor:root@localhost Timestamp:0000-00-0000:00:00 Table_priv:Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,CreateView,Showview,Trigger Column_priv: 1rowinset(0.01sec) ERROR: Noqueryspecified mysql>
我们来创建一个测试账号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>
到此这篇关于MySQL查询用户权限的方法总结的文章就介绍到这了,更多相关两种MySQL查询用户权限的方法内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。