當可能有多個匹配項時，服務器必須確定哪個匹配項他們使用.它解決了這個問題如下:(...)

• 當客戶端嘗試連接時，服務器按排序順序查看[表 mysql.user] 的行.
• 服務器使用與客戶端主機名和用戶名匹配的第一行.

(...)服務器使用排序規則，將具有最具體的 Host 值的行排在第一位.文字主機名[例如'localhost']和IP地址是最具體的.

因此，當從 localhost 連接時，這樣的匿名用戶會屏蔽"任何其他用戶，例如 '[any_username]'@'%'.

'bill'@'localhost' 確實匹配 'bill'@'%'，但會匹配(例如)''@'localhost' 事先.

推薦的解決方案是刪除此匿名用戶(無論如何，這通常是一件好事).

下面的編輯大多與主要問題無關.這些只是為了回答在此線程中的其他評論中提出的一些問題.

編輯 1

通過套接字驗證為 'bill'@'%'.

編輯 2

完全相同的設置，除了我重新激活網絡，我現在創建一個匿名用戶 ''@'localhost'.

編輯 3

與編輯 2 中的情況相同，現在提供匿名用戶的密碼.

結論 1，來自編輯 1:可以通過套接字驗證為 'bill'@'%'.

結論 2，來自編輯 2:無論是通過 TCP 連接還是通過套接字連接對身份驗證過程沒有影響(除了不能作為其他任何人連接，但 'something'@'localhost' 通過顯然是一個套接字).

結論 3，來自編輯 3:雖然我指定了 -ubill，但我已被授予匿名用戶訪問權限.這是因為上面建議的排序規則".請注意，在大多數默認安裝中，存在無密碼的匿名用戶(并且應該被固定/移除).

First let me mention that I've gone through many suggested questions and found no relevent answer. Here is what I'm doing.

I'm connected to my Amazon EC2 instance. I can login with MySQL root with this command:

mysql -u root -p

Then I created a new user bill with host %

CREATE USER 'bill'@'%' IDENTIFIED BY 'passpass';

Granted all the privileges to user bill:

grant all privileges on *.* to 'bill'@'%' with grant option;

Then I exit from root user and try to login with bill:

mysql -u bill -p

entered the correct password and got this error:

ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

You probably have an anonymous user ''@'localhost' or ''@'127.0.0.1'.

As per the manual:

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows: (...)

• When a client attempts to connect, the server looks through the rows [of table mysql.user] in sorted order.
• The server uses the first row that matches the client host name and user name.

(...) The server uses sorting rules that order rows with the most-specific Host values first. Literal host names [such as 'localhost'] and IP addresses are the most specific.

Hence, such an anonymous user would "mask" any other user like '[any_username]'@'%' when connecting from localhost.

'bill'@'localhost' does match 'bill'@'%', but would match (e.g.) ''@'localhost' beforehands.

The recommended solution is to drop this anonymous user (this is usually a good thing to do anyways).

Below edits are mostly irrelevant to the main question. These are only meant to answer some questions raised in other comments within this thread.

Edit 1

Authenticating as 'bill'@'%' through a socket.

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass --socket=/tmp/mysql-5.5.sock
    Welcome to the MySQL monitor (...)

    mysql> SELECT user, host FROM mysql.user;
    +------+-----------+
    | user | host      |
    +------+-----------+
    | bill | %         |
    | root | 127.0.0.1 |
    | root | ::1       |
    | root | localhost |
    +------+-----------+
    4 rows in set (0.00 sec)

    mysql> SELECT USER(), CURRENT_USER();
    +----------------+----------------+
    | USER()         | CURRENT_USER() |
    +----------------+----------------+
    | bill@localhost | bill@%         |
    +----------------+----------------+
    1 row in set (0.02 sec)

    mysql> SHOW VARIABLES LIKE 'skip_networking';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | skip_networking | ON    |
    +-----------------+-------+
    1 row in set (0.00 sec)

Edit 2

Exact same setup, except I re-activated networking, and I now create an anonymous user ''@'localhost'.

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql
    Welcome to the MySQL monitor (...)

    mysql> CREATE USER ''@'localhost' IDENTIFIED BY 'anotherpass';
    Query OK, 0 rows affected (0.00 sec)

    mysql> Bye

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
        --socket=/tmp/mysql-5.5.sock
    ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
        -h127.0.0.1 --protocol=TCP
    ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
        -hlocalhost --protocol=TCP
    ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

Edit 3

Same situation as in edit 2, now providing the anonymous user's password.

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -panotherpass -hlocalhost
    Welcome to the MySQL monitor (...)

    mysql> SELECT USER(), CURRENT_USER();
    +----------------+----------------+
    | USER()         | CURRENT_USER() |
    +----------------+----------------+
    | bill@localhost | @localhost     |
    +----------------+----------------+
    1 row in set (0.01 sec)

Conclusion 1, from edit 1: One can authenticate as 'bill'@'%'through a socket.

Conclusion 2, from edit 2: Whether one connects through TCP or through a socket has no impact on the authentication process (except one cannot connect as anyone else but 'something'@'localhost' through a socket, obviously).

Conclusion 3, from edit 3: Although I specified -ubill, I have been granted access as an anonymous user. This is because of the "sorting rules" advised above. Notice that in most default installations, a no-password, anonymous user exists (and should be secured/removed).

這篇關于MySQL ERROR 1045 (28000):用戶“bill"@“localhost"的訪問被拒絕(使用密碼:是)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！