《Mysql必讀MySQL驗(yàn)證用戶權(quán)限的方法》要點(diǎn)：
本文介紹了Mysql必讀MySQL驗(yàn)證用戶權(quán)限的方法，希望對您有用。如果有疑問，可以聯(lián)系我們。
MYSQL入門知識歸納
MYSQL入門因?yàn)镸ySQL是使用User和Host兩個字段來確定用戶身份的,這樣就帶來一個問題,就是一個客戶端到底屬于哪個host.
如果一個客戶端同時匹配幾個Host,對用戶的確定將按照下面的優(yōu)先級來排
MYSQL入門當(dāng)你登錄mysql服務(wù)器之后,你可以使用user()和current_user()來檢查你登陸的用戶.
MYSQL入門當(dāng)你登錄服務(wù)器并執(zhí)行MySQL的命令時,系統(tǒng)將檢查你當(dāng)前的用戶(current_user)是否有權(quán)限進(jìn)行當(dāng)前操作.
MYSQL入門測試過程
創(chuàng)建3個用戶名相同,HOST和權(quán)限都不同的USER
MYSQL入門
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)
MYSQL入門從另外一個機(jī)器登陸過來
MYSQL入門
[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)
MYSQL入門明確的user,host,進(jìn)行精確匹配,找到用戶為'bruce'@'10.20.0.232'
刪除掉這個用戶再登陸
MYSQL入門
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)
MYSQL入門此時匹配的用戶是bruce@%
然后把這個用戶也刪除,再登陸
MYSQL入門
[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)
MYSQL入門此時匹配的是''@'%' 用戶
MYSQL入門對于空用戶,默認(rèn)有對test或test開頭的數(shù)據(jù)庫有權(quán)限.
MYSQL入門以上就是MySQL驗(yàn)證用戶權(quán)限的方法,希望對大家的學(xué)習(xí)有所啟發(fā).
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/3463.html
