MySQL查询用户权限的方法总结

时间:2021-05-24

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

1、 使用MySQL grants命令

mysql> show grants for username@localhost;+---------------------------------------------------------------------+| Grants for root@localhost |+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |+---------------------------------------------------------------------+

需要注意的是:

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

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

2、 使用MySQL select命令

mysql> select * from mysql.user where user='root' and host='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: N1 row in set (0.01 sec)

知识点扩展:

我们来创建一个测试账号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: MyDBUser: testTable_name: kkkGrantor: root@localhostTimestamp: 0000-00-00 00:00:00Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,TriggerColumn_priv: 1 row in set (0.01 sec)ERROR: No query specifiedmysql> <br>

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

到此这篇关于MySQL查询用户权限的方法总结的文章就介绍到这了,更多相关两种MySQL查询用户权限的方法内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!

声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。

相关文章