MySQLStudy之--MySQL普通用戶無法本地登陸
MySQL Study之--MySQL普通用戶無法本地登陸
在安裝完成MySQL後，我們通常添加擁有相應權限的普通用戶用來訪問數據庫。在使用用戶本地登錄數據庫的時候，經常會出現怎麼登錄也無法登錄的情況,但是從其它的mysql客戶端卻可以登錄。
故障現象：
[root@mysrv ~]# mysql -u root -poracle
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select version()\g
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.6.25-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)
創建用戶並授權：
mysql> grant all on prod.* to 'rose'@'%' identified by 'rose';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for rose;
+-----------------------------------------------------------------------------------------------------+
| Grants for rose@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rose'@'%' IDENTIFIED BY PASSWORD '*86F57026C60B8CE1038EFB3B9383EC573979A7BD' |
| GRANT ALL PRIVILEGES ON `prod`.* TO 'rose'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select user,host from user;
+-------+-----------+
| user | host |
+-------+-----------+
| jerry | % |
| rose | % |
| tom | % |
| tom1 | % |
| tom2 | % |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| jerry | localhost |
| root | localhost |
| scott | localhost |
| tom | localhost |
| | mysrv |
| root | mysrv |
+-------+-----------+
14 rows in set (0.00 sec)
用戶登陸：
[root@mysrv ~]# mysql -u rose -prose
ERROR 1045 (28000): Access denied for user 'rose'@'localhost' (using password: YES) ---登陸失敗！
[root@mysrv ~]# mysql -u rose -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use prod;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'prod'--
---在不用密碼的情況下可以登陸，但沒有權限訪問，應該是匿名用戶的身份 ！
遠程登陸： 
---遠程登陸成功！
一、登錄後查看mysql.user表的情況
可以看到，我的數據庫中有rose用戶和匿名用戶localhost;
mysql> show grants for rose;
+-----------------------------------------------------------------------------------------------------+
| Grants for rose@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rose'@'%' IDENTIFIED BY PASSWORD '*86F57026C60B8CE1038EFB3B9383EC573979A7BD' |
| GRANT ALL PRIVILEGES ON `prod`.* TO 'rose'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
二、在本機用rose用戶登錄，發現不用密碼可以登錄；
[root@mysql01 ~]# mysql -urose -p
Enter password:
mysql> select user(),current_user();
+----------------+----------------+
| user() | current_user() |
+----------------+----------------+
| rose@localhost | @localhost |
+----------------+----------------+
1 row in set (0.00 sec)
登錄成功了，使用USER()和CURRENT_USER()兩個函數查看所使用的用戶。
USER()函數返回你在客戶端登陸時指定的用戶名和主機名。
CURRENT_USER()函數返回的是MySQL使用授權表中的哪個用戶來認證你的登錄請求。
這裡發現，我使用'rose'@'localhost'這個賬戶登錄數據庫（因為在本地登陸時沒指定主機，默認是以localhost登錄），但是數據庫使用的是''@'localhost'這個賬戶來進行登錄認證，而''@'localhost'這個匿名用戶是沒有密碼的，因此我輸入空密碼登錄成功了。但是登錄後，所對應的用戶的匿名用戶。
一般在MySQL在安裝完畢後，我們使用mysql_install_db這個腳本生成授權表，會默認創建''@'localhost'這個匿名用戶。正是因為這個匿名用戶，影響了其他用戶從本地登錄的認證。
那麼MySQL是如何進行用戶身份認證呢？
一、當用戶從客戶端請求登陸時，MySQL將授權表中的條目與客戶端所提供的條目進行比較，包括用戶的用戶名，密碼和主機。授權表中的Host字段是可以使用通配符作為模式進行匹配的，如test.example.com, %.example.com, %.com和%都可以匹配test.example.com這個主機。授權表中的User字段不允許使用模式匹配，但是可以有一個空字符的用戶名代表匿名用戶，並且空字符串可以匹配所有的用戶名，就像通配符一樣。 當user表中的Host和User有多個值可以匹配客戶端提供的主機和用戶名時，MySQL將user表讀入內存，並且按照一定規則排序，按照排序規則讀取到的第一個匹配客戶端用戶名和主機名的條目對客戶端進行身份驗證。
二、排序規則：對於Host字段，按照匹配的精確程度進行排序，越精確的排序越前，例如當匹配test.example.com這個主機時, %.example.com比%.com更精確，而test.example.com比%.example.com更精確。對於User字段，非空的字符串用戶名比空字符串匹配的用戶名排序更靠前。 User和Host字段都有多個匹配值，MySQL使用主機名排序最前的條目，在主機名字段相同時再選取用戶名排序更前的條目。因此，如果User和Host字段都有多個匹配值，主機名最精確匹配的條目被用戶對用戶進行認證。
了解了這個認證流程，就知道為什麼server登錄失敗了。
使用GaMe在本機登錄數據時，不指定-h參數默認為localhost主機登錄，而在MySQL中有兩個匹配的條目：'rose'@'%' 和 ''@'localhost'
匿名用戶能夠匹配的原因上面說過，空字符串可以匹配所有的用戶名，就像通配符一樣。
根據MySQL認證時的排序規則，第一個條目的用戶名排序更前，第二個條目的主機名更精確，排序更前。
而MySQL會優先使用主機名排序第一的條目進行身份認證，因此''@'localhost'被用戶對客戶端進行認證。因此，只有使用匿名用戶的空密碼才能登錄進數據庫。就會出現下面的情況了。
解決的方法：刪除匿名用戶（僅僅為了安全也有這個必要）
為什麼root用戶不會受影響，而只有普通用戶不能從本地登錄？
因為mysql_install_db腳本會在授權表中生成'root'@'localhost'這個賬戶。同樣的，使用root登錄MySQL 時，'root'@'localhost'和''@'localhost'都能匹配登錄的賬戶，但是根據排序規則，主機名相同，而用戶名非空字符串優先，因此'root'@'localhost'這個條目的排序更靠前。使用root本地登錄是不會被匿名用戶遮蓋。
解決方法：
授權rose用戶本地登陸： mysql> grant all on prod.* to 'rose'@'localhost' identified by 'rose';
Query OK, 0 rows affected (0.01 sec)
從本地登陸： [root@mysrv ~]# mysql -u rose -prose
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use prod;
Database changed
mysql> show tables;
+----------------+
| Tables_in_prod |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 10 | tom |
| 20 | jerry |
| 30 | rose |
+------+-------+
3 rows in set (0.00 sec)
---登陸成功！
