程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL驗證用戶權限的辦法

MySQL驗證用戶權限的辦法

編輯：MySQL綜合教程

MySQL驗證用戶權限的辦法。本站提示廣大學習愛好者：（MySQL驗證用戶權限的辦法）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL驗證用戶權限的辦法正文

常識歸結

由於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 [email protected]                       |
+-------------------------------------------------------------------------------------------------------------------------+
| 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() |
+-------------------+-------------------+
| [email protected] | [email protected] |
+-------------------+-------------------+
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() |
+-------------------+----------------+
| [email protected] | 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() |
+-------------------+----------------+
| [email protected] | @%    |
+-------------------+----------------+
1 row in set (0.00 sec)

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

關於空用戶，默許有對test或test開首的數據庫有權限。

以上就是MySQL驗證用戶權限的辦法，願望對年夜家的進修有所啟示。