快捷導(dǎo)航

MySQL驗(yàn)證用戶權(quán)限的方法

更新時(shí)間：2015年11月05日 10:37:24 作者：天堂

這篇文章主要介紹了MySQL驗(yàn)證用戶權(quán)限的方法，需要的朋友可以參考下

知識(shí)歸納

因?yàn)镸ySQL是使用User和Host兩個(gè)字段來確定用戶身份的，這樣就帶來一個(gè)問題，就是一個(gè)客戶端到底屬于哪個(gè)host。
如果一個(gè)客戶端同時(shí)匹配幾個(gè)Host，對(duì)用戶的確定將按照下面的優(yōu)先級(jí)來排

基本觀點(diǎn)越精確的匹配越優(yōu)先
Host列上，越是確定的Host越優(yōu)先，[localhost, 192.168.1.1, wiki.yfang.cn] 優(yōu)先于[192.168.%, %.yfang.cn]，優(yōu)先于[192.%, %.cn]，優(yōu)先于[%]
User列上，明確的username優(yōu)先于空username。（空username匹配所有用戶名，即匿名用戶匹配所有用戶）
Host列優(yōu)先于User列考慮

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

user() 返回你連接server時(shí)候指定的用戶和主機(jī)
current_user() 返回在mysql.user表中匹配到的用戶和主機(jī)，這將確定你在數(shù)據(jù)庫(kù)中的權(quán)限

當(dāng)你登錄服務(wù)器并執(zhí)行MySQL的命令時(shí)，系統(tǒng)將檢查你當(dāng)前的用戶(current_user)是否有權(quán)限進(jìn)行當(dāng)前操作。

首先檢查user表中的全局權(quán)限，如果滿足條件，則執(zhí)行操作
如果上面的失敗，則檢查mysql.db表中是否有滿足條件的權(quán)限，如果滿足，則執(zhí)行操作
如果上面的失敗，則檢查mysql.table_priv和mysql.columns_priv(如果是存儲(chǔ)過程操作則檢查mysql.procs_priv)，如果滿足，則執(zhí)行操作
如果以上檢查均失敗，則系統(tǒng)拒絕執(zhí)行操作。

測(cè)試過程
創(chuàng)建3個(gè)用戶名相同，HOST和權(quán)限都不同的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)

從另外一個(gè)機(jī)器登陸過來

[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,進(jìn)行精確匹配，找到用戶為'bruce'@'10.20.0.232'
刪除掉這個(gè)用戶再登陸

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)

此時(shí)匹配的用戶是bruce@%
然后把這個(gè)用戶也刪除，再登陸

[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)

此時(shí)匹配的是''@'%' 用戶

對(duì)于空用戶，默認(rèn)有對(duì)test或test開頭的數(shù)據(jù)庫(kù)有權(quán)限。

以上就是MySQL驗(yàn)證用戶權(quán)限的方法，希望對(duì)大家的學(xué)習(xí)有所啟發(fā)。

您可能感興趣的文章: