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)

  從另外一個機器登陸過來

• [[email protected] ~]# mysql -ubruce -p -h10.20.0.231Enter password: Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server 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 licenseType ‘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)[[email protected] ~]# mysql -ubruce -p -h10.20.0.231Enter password: Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 6Server 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 licenseType ‘help;‘ or ‘\h‘ for help. Type‘\c‘to clear the current inputstatement.MySQL [(none)]>show grants;+-----------------------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                                    |+-----------------------------------------------------------------------------------------------------------------------+| 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] | [email protected]%        |+-------------------+----------------+1 row in set (0.00 sec)

  此時匹配的使用者是[email protected]%
  然後把這個使用者也刪除，再登陸

• [[email protected] ~]# mysql -ubruce -p -h10.20.0.231Enter password: Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server 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 licenseType ‘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開頭的資料庫有許可權

http://www.cnblogs.com/zuoxingyu/p/4935428.html

MYSQL-使用者權限的驗證過程(轉)