MySQL建立使用者後本地無法登入的原因

MySQL建立使用者後本地無法登入的原因

在安裝完成MySQL後，我們通常添加擁有相應許可權的普通使用者用來訪問資料庫。在使用使用者本地登入資料庫的時候，經常會出現怎麼登入也無法登入的情況,但是從其他的mysql用戶端卻可以登入。

[root@mysql01 ~]# mysql -userver -p123456
ERROR 1045 (28000): Access denied for user 'server'@'localhost' (using password: YES)

本地登入失敗；

[root@mysql02 ~]# mysql -h192.168.47.166 -userver -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.36-log Source distribution

遠程登入成功；

一、登入後查看mysql.user表的情況

image

可以看到，我的資料庫中有server使用者和匿名使用者localhost;
•server使用者的密碼是'server'
•匿名使用者的密碼為空白

二、在本機用server使用者登入，發現不用密碼可以登入；

[root@mysql01 ~]# mysql -userver -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.36-log Source distribution

Copyright (c) 2000, 2014, 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 USER(), CURRENT_USER();
+--------------------------+-----------------------+
| USER()                    | CURRENT_USER() |
+-------------------------+------------------------+
| server@localhost    | @localhost          |
+-------------------------+-------------------------+
1 row in set (0.00 sec)

登入成功了，使用USER()和CURRENT_USER()兩個函數查看所使用的使用者。
•USER()函數返回你在用戶端登陸時指定的使用者名稱和主機名稱。
•CURRENT_USER()函數返回的是MySQL使用授權表中的哪個使用者來認證你的登入請求。

這裡發現，我使用'server'@'localhost'這個賬戶登入資料庫（因為在本地登陸時沒指定主機，預設是以localhost登入），但是資料庫使用的是''@'localhost'這個賬戶來進行登入認證，而''@'localhost'這個匿名使用者是沒有密碼的，因此我輸入空密碼登入成功了。但是登入後，所對應的使用者的匿名使用者。

一般在MySQL在安裝完畢後，我們使用mysql_install_db這個指令碼產生授權表，會預設建立''@'localhost'這個匿名使用者。正是因為這個匿名使用者，影響了其他使用者從本地登入的認證。

那麼MySQL是如何進行使用者身份認證呢？

一、當使用者從用戶端請求登陸時，MySQL將授權表中的條目與用戶端所提供的條目進行比較，包括使用者的使用者名稱，密碼和主機。授權表中的Host欄位是可以使用萬用字元作為模式進行匹配的，如test.example.com, %.example.com, %.com和%都可以匹配test.example.com這個主機。授權表中的User欄位不允許使用模式比對，但是可以有一個Null 字元的使用者名稱代表匿名使用者，並且Null 字元串可以匹配所有的使用者名稱，就像萬用字元一樣。 當user表中的Host和User有多個值可以匹配用戶端提供的主機和使用者名稱時，MySQL將user表讀入記憶體，並且按照一定規則排序，按照定序讀取到的第一個匹配用戶端使用者名稱和主機名稱的條目對用戶端進行身分識別驗證。

二、定序：對於Host欄位，按照匹配的精確程度進行排序，越精確的排序越前，例如當匹配test.example.com這個主機時, %.example.com比%.com更精確，而test.example.com比%.example.com更精確。對於User欄位，非空的字串使用者名稱比Null 字元串匹配的使用者名稱排序更靠前。 User和Host欄位都有多個匹配值，MySQL使用主機名稱排序最前的條目，在主機名稱欄位相同時再選取使用者名稱排序更前的條目。因此，如果User和Host欄位都有多個匹配值，主機名稱最精確匹配的條目被使用者對使用者進行認證。

瞭解了這個認證流程，就知道為什麼server登入失敗了。

使用GaMe在本機登入資料時，不指定-h參數預設為localhost主機登入，而在MySQL中有兩個匹配的條目：'server'@'%'  和 ''@'localhost'

匿名使用者能夠匹配的原因上面說過，Null 字元串可以匹配所有的使用者名稱，就像萬用字元一樣。

根據MySQL認證時的定序，第一個條目的使用者名稱排序更前，第二個條目的主機名稱更精確，排序更前。

而MySQL會優先使用主機名稱排序第一的條目進行身份認證，因此''@'localhost'被使用者對用戶端進行認證。因此，只有使用匿名使用者的空密碼才能登入進資料庫。就會出現下面的情況了。

解決的方法：刪除匿名使用者（僅僅為了安全也有這個必要）

為什麼root使用者不會受影響，而只有普通使用者不能從本地登入？

因為mysql_install_db指令碼會在授權表中產生'root'@'localhost'這個賬戶。同樣的，使用root登入MySQL 時，'root'@'localhost'和''@'localhost'都能匹配登入的賬戶，但是根據定序，主機名稱相同，而使用者名稱非Null 字元串優先，因此'root'@'localhost'這個條目的排序更靠前。使用root本地登入是不會被匿名使用者遮蓋。

本文永久更新連結地址：