MySQL驗證用戶權限的方法_MySQL

來源：懂視網責編：小采時間：2020-11-09 20:08:52

MySQL驗證用戶權限的方法_MySQL

MySQL驗證用戶權限的方法_MySQL:知識歸納因為MySQL是使用User和Host兩個字段來確定用戶身份的，這樣就帶來一個問題，就是一個客戶端到底屬于哪個host。如果一個客戶端同時匹配幾個Host，對用戶的確定將按照下面的優先級來排基本觀點越精確的匹配越優先 Host列上，越是確定的Host越

推薦度：

點擊下載本文 文檔為doc格式

導讀MySQL驗證用戶權限的方法_MySQL:知識歸納因為MySQL是使用User和Host兩個字段來確定用戶身份的，這樣就帶來一個問題，就是一個客戶端到底屬于哪個host。如果一個客戶端同時匹配幾個Host，對用戶的確定將按照下面的優先級來排基本觀點越精確的匹配越優先 Host列上，越是確定的Host越

知識歸納

因為MySQL是使用User和Host兩個字段來確定用戶身份的，這樣就帶來一個問題，就是一個客戶端到底屬于哪個host。
如果一個客戶端同時匹配幾個Host，對用戶的確定將按照下面的優先級來排

基本觀點越精確的匹配越優先

Host列上，越是確定的Host越優先，[localhost, 192.168.1.1, wiki.yfang.cn] 優先于[192.168.%, %.yfang.cn]，優先于[192.%, %.cn]，優先于[%]

User列上，明確的username優先于空username。（空username匹配所有用戶名，即匿名用戶匹配所有用戶）

Host列優先于User列考慮

當你登錄mysql服務器之后，你可以使用user()和current_user()來檢查你登陸的用戶。

user() 返回你連接server時候指定的用戶和主機

current_user() 返回在mysql.user表中匹配到的用戶和主機，這將確定你在數據庫中的權限

當你登錄服務器并執行MySQL的命令時，系統將檢查你當前的用戶(current_user)是否有權限進行當前操作。

首先檢查user表中的全局權限，如果滿足條件，則執行操作

如果上面的失敗，則檢查mysql.db表中是否有滿足條件的權限，如果滿足，則執行操作

如果上面的失敗，則檢查mysql.table_priv和mysql.columns_priv(如果是存儲過程操作則檢查mysql.procs_priv)，如果滿足，則執行操作

如果以上檢查均失敗，則系統拒絕執行操作。

測試過程
創建3個用戶名相同，HOST和權限都不同的USER

mysql> grant select on *.* to ''@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select,createon *.* to 'bruce'@'10.20.0.232' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select,create,deleteon *.* to 'bruce'@'%' identified by'123';
Query OK, 0rows affected (0.00 sec)

從另外一個機器登陸過來

[root@brucetest7 ~]# mysql -ubruce -p -h10.20.0.231
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.20-log MySQL Community Server (GPL)
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome tomodify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type'\c'to clear the current inputstatement.
MySQL [(none)]> show grants;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for bruce@10.20.0.232 |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, CREATEON *.* TO 'bruce'@'10.20.0.232' IDENTIFIED BY PASSWORD'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row inset (0.00 sec)
MySQL [(none)]> select user(), current_user();
+-------------------+-------------------+
| user() | current_user() |
+-------------------+-------------------+
| bruce@10.20.0.232 | bruce@10.20.0.232 |
+-------------------+-------------------+
1 row in set (0.03 sec)

明確的user,host,進行精確匹配，找到用戶為'bruce'@'10.20.0.232'
刪除掉這個用戶再登陸

mysql> delete from mysql.userwhereuser='bruce'andhost='10.20.0.232';
Query OK, 1row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@brucetest7 ~]# mysql -ubruce -p -h10.20.0.231
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.20-log MySQL Community Server (GPL)
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome tomodify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type'\c'to clear the current inputstatement.

MySQL [(none)]>show grants;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for bruce@% |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, DELETE, CREATEON*.* TO 'bruce'@'%' IDENTIFIED BYPASSWORD'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row inset (0.00 sec)
MySQL [(none)]> select user(), current_user();
+-------------------+----------------+
| user() | current_user() |
+-------------------+----------------+
| bruce@10.20.0.232 | bruce@% |
+-------------------+----------------+
1 row in set (0.00 sec)

此時匹配的用戶是bruce@%
然后把這個用戶也刪除，再登陸

[root@brucetest7 ~]# mysql -ubruce -p -h10.20.0.231
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.20-log MySQL Community Server (GPL)
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome tomodify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c'to clear the current inputstatement.
MySQL [(none)]> show grants;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for @% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON*.* TO''@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATEROUTINE, EVENT, TRIGGER ON `test`.* TO''@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATETEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATEROUTINE, EVENT, TRIGGER ON `test\_%`.* TO''@'%' |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
MySQL [(none)]> select user(), current_user();
+-------------------+----------------+
| user() | current_user() |
+-------------------+----------------+
| bruce@10.20.0.232 | @% |
+-------------------+----------------+
1 row in set (0.00 sec)

此時匹配的是''@'%' 用戶

對于空用戶，默認有對test或test開頭的數據庫有權限。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com