MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (使用密码: YES)

2022-01-30 00:00:00 access-denied mysql

首先让我提一下,我已经解决了许多建议的问题,但没有找到相关的答案.这就是我正在做的事情.

我已连接到我的 Amazon EC2 实例.我可以使用以下命令使用 MySQL root 登录:

mysql -u root -p

然后我用主机 % 创建了一个新的用户账单

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

授予用户账单所有权限:

使用授予选项将 *.* 上的所有权限授予 'bill'@'%';

然后我退出 root 用户并尝试使用 bill 登录:

mysql -u bill -p

输入了正确的密码并得到这个错误:

<块引用>

错误 1045 (28000):拒绝用户 'bill'@'localhost' 访问(使用密码:YES)

解决方案

你可能有一个匿名用户 ''@'localhost'''@'127.0.0.1'.

根据手册:

<块引用>

当可能有多个匹配时,服务器必须确定哪一个他们使用.它解决了这个问题如下:(...)

  • 当客户端尝试连接时,服务器会按排序顺序查看行[of table mysql.user].
  • 服务器使用与客户端主机名和用户名匹配的第一行.

(...)服务器使用排序规则将具有最具体的主机值优先的行排序.文字主机名[例如'localhost']和IP地址是最具体的.

因此,当从 localhost 连接时,这样的匿名用户会屏蔽"任何其他用户,例如 '[any_username]'@'%'.

'bill'@'localhost' 确实匹配 'bill'@'%',但会匹配(例如)''@'localhost' 提前.

推荐的解决方案是删除这个匿名用户(这通常是一件好事).

<小时>

以下编辑大多与主要问题无关.这些只是为了回答本主题其他评论中提出的一些问题.

编辑 1

通过套接字验证为 'bill'@'%'.

<上一页>root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass --socket=/tmp/mysql-5.5.sock欢迎使用 MySQL 监视器 (...)mysql> 选择用户,主机 FROM mysql.user;+--------+------------+|用户 |主持人 |+--------+------------+|账单 |% ||根 |127.0.0.1 ||根 |::1 ||根 |本地主机 |+--------+------------+4 行一组(0.00 秒)mysql> 选择用户(),当前用户();+----------------+----------------+|用户() |当前用户() |+----------------+----------------+|账单@localhost |账单@% |+----------------+----------------+一组中的 1 行(0.02 秒)mysql> 显示变量,如skip_networking";+-----------------+--------+|变量名 |价值 |+-----------------+--------+|跳过网络 |开 |+-----------------+--------+一组中的 1 行(0.00 秒)

编辑 2

完全相同的设置,除了我重新激活了网络,我现在创建了一个匿名用户 ''@'localhost'.

<上一页>root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql欢迎使用 MySQL 监视器 (...)mysql> CREATE USER ''@'localhost' IDENTIFIED BY 'anotherpass';查询正常,0 行受影响(0.00 秒)mysql>再见root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass --socket=/tmp/mysql-5.5.sock错误 1045 (28000):拒绝用户 'bill'@'localhost' 的访问(使用密码:是)root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass -h127.0.0.1 --protocol=TCP错误 1045 (28000):拒绝用户 'bill'@'localhost' 的访问(使用密码:是)root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass -hlocalhost --protocol=TCP错误 1045 (28000):拒绝用户 'bill'@'localhost' 的访问(使用密码:是)

编辑 3

与编辑 2 中的情况相同,现在提供匿名用户的密码.

<上一页>root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -panotherpass -hlocalhost欢迎使用 MySQL 监视器 (...)mysql> 选择用户(),当前用户();+----------------+----------------+|用户() |当前用户() |+----------------+----------------+|账单@localhost |@本地主机 |+----------------+----------------+一组中的 1 行(0.01 秒)

结论 1,来自编辑 1:可以通过套接字验证为 'bill'@'%'.

结论 2,来自编辑 2:一个人是通过 TCP 连接还是通过套接字连接对身份验证过程没有影响(除非一个人不能像其他人一样连接,但 'something'@'localhost' 通过显然是一个套接字).

结论 3,来自编辑 3:虽然我指定了 -ubil,但我已被授予匿名用户访问权限.这是因为上面建议的排序规则".请注意,在大多数默认安装中,存在一个无密码、匿名用户 (并且应该被固定/移除).

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).

相关文章