mysql 新建的%用户无法本地（localhost）登录

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

问题背景：

  

 

 

 

 

 

 

 

数据库中已创建了smartwin@'%'的用户，当以smartwin登录时，总是出错（前提密码输入正确）：

 

而通过指定host地址登录则可以成功，

 

SELECTUSER(),CURRENT_USER();

　USER() 　　　　  　　reports how you attempted to authenticate in MySQL

　CURRENT_USER()　  reports how you were allowed to authenticate in MySQL from the mysql.user table

问题分析：

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]'@'%'. 'billy'@'localhost' does match 'billy'@'%', but would match (e.g.) ''@'localhost' beforehands.

解决反感：

　　1、删除mysql.user表中 ‘’@localhost记录;

　　　　> delete from mysql.user

　　　　　　where host='localhost' and user='';

　　2、创建bill@‘localhost’账户

　　　　> create user bill@'%' identified by 'YOUR PASSWORD' ;

　　　　> create user bill@'localhost' identified by 'YOUR PASSWORD' ;

　　　　> [grant all privileges on temp.* to bill@'%' ;]

　　　　> [grant all privileges on temp.* to bill@'localhost';]

　　　　> flush privileges;

　　

本地用户登录验证：

　　

远程用户登录验证：
　　

 参考文献：

http://www.cnblogs.com/nocode/archive/2012/07/21/2602379.html

http://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw/12568665#12568665