Jinlong's Blog

备忘:MySQL创建用户中的坑

问题

MySQL数据库作业中需要创建多用户,并对其赋予相应的权限。于是我索性用create user语句创建了一个名为lijinlong的用户,如下:

1
2
create user lijinlong@"%" identified by "password";
flush privileges;

@后面使用通配符是因为想要这个账户在任何主机都可以登录。创建好账户之后退出数据库,重新用lijinlong的账号登录,发现就算密码输入对了仍然是Access denied。

1
2
3
$ mysql -u lijinlong -p
Enter password:
ERROR 1045 (28000): Access denied for user 'lijinlong'@'localhost' (using password: YES)

难道是lijinlong账户没有相应的权限,于是用root账户给账户lijinlong赋予了所有权限。

1
grant all privileges on *.* to lijinlong@"%";

重新登录,依然是Access denied。

解决

百思不得其解,于是去翻MySQL的用户手册,看到如下一段话。

The ‘finley’@’localhost’ account is necessary if there is an anonymous-user account for localhost. Without the ‘finley’@’localhost’ account, that anonymous-user account takes precedence when finley connects from the local host and finley is treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the ‘finley’@’%’ account and thus comes earlier in the user table sort order. (user table sorting is discussed in Section 6.2.4, “Access Control, Stage 1: Connection Verification”.)

传送门:http://dev.mysql.com/doc/refman/5.6/en/adding-users.html

原来是由于新建的账户host为”%”,原生的匿名账户比新建的lijinlong账户更加具体,所以lijinlong会被视为匿名账户。

1
grant all privileges on *.* to lijinlong@"localhost";

成功解决问题。
还有一种解决办法是直接删除掉无用的匿名账户。