MySQL ERROR 1045 (28000):用户“bill"@“localhost"的访问被拒绝(使用密码:是)
首先让我提一下,我已经浏览了许多建议的问题,但没有找到相关的答案.这就是我正在做的事情.
我已连接到我的 Amazon EC2 实例.我可以使用以下命令使用 MySQL root 登录:
mysql -u root -p
然后我用主机 % 创建了一个新的用户账单
CREATE USER 'bill'@'%' IDENTIFIED BY 'passpass';
授予用户 bill 的所有权限:
使用授权选项将*.*的所有权限授予'bill'@'%';
然后我从 root 用户退出并尝试使用 bill 登录:
mysql -u bill -p
输入正确的密码,但出现此错误:
<块引用>ERROR 1045 (28000): 用户 'bill'@'localhost' 访问被拒绝(使用密码:YES)
解决方案您可能有一个匿名用户 ''@'localhost'
或 ''@'127.0.0.1'代码>.
根据手册:
<块引用>当可能有多个匹配项时,服务器必须确定哪个匹配项他们使用.它解决了这个问题如下:(...)
- 当客户端尝试连接时,服务器按排序顺序查看[表 mysql.user] 的行.
- 服务器使用与客户端主机名和用户名匹配的第一行.
(...)服务器使用排序规则,将具有最具体的 Host 值的行排在第一位.文字主机名[例如'localhost']和IP地址是最具体的.
因此,当从 localhost
连接时,这样的匿名用户会屏蔽"任何其他用户,例如 '[any_username]'@'%'
.
'bill'@'localhost'
确实匹配 'bill'@'%'
,但会匹配(例如)''@'localhost'
事先.
推荐的解决方案是删除此匿名用户(无论如何,这通常是一件好事).
<小时>下面的编辑大多与主要问题无关.这些只是为了回答在此线程中的其他评论中提出的一些问题.
编辑 1
通过套接字验证为 'bill'@'%'
.
编辑 2
完全相同的设置,除了我重新激活网络,我现在创建一个匿名用户 ''@'localhost'
.
编辑 3
与编辑 2 中的情况相同,现在提供匿名用户的密码.
<前>root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -panotherpass -hlocalhost欢迎使用 MySQL 监视器 (...)mysql> SELECT USER(), CURRENT_USER();+----------------+----------------+|用户() |CURRENT_USER() |+----------------+----------------+|bill@localhost |@本地主机 |+----------------+----------------+1 行(0.01 秒)结论 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).
相关文章