介紹兩種查看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: 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
1 row in set (0.01 sec)
知識點擴展：
我們來創(chuàng)建一個測試賬號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> br>
我們來創(chuàng)建一個測試賬號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查詢用戶權限的方法內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- MySQL 權限控制詳解
- mysql創(chuàng)建用戶并賦予用戶權限詳細操作教程
- Mysql修改存儲過程相關權限問題
- MySQL8.0設置遠程訪問權限的方法
- MySQL如何利用DCL管理用戶和控制權限
- MySQL創(chuàng)建用戶和權限管理的方法
- mysql用戶權限管理實例分析
- MYSQL開啟遠程訪問權限的方法
- MySQL給新建用戶并賦予權限最簡單的方法
- MySQL用戶與權限的管理詳解
- MySQL 權限控制細節(jié)分析
