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)
Best Answer
You probably have an anonymous user
''@'localhost'
or''@'127.0.0.1'
.As per the manual:
Hence, such an anonymous user would "mask" any other user like
'[any_username]'@'%'
when connecting fromlocalhost
.'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.Edit 2
Exact same setup, except I re-activated networking, and I now create an anonymous user
''@'localhost'
.Edit 3
Same situation as in edit 2, now providing the anonymous user's password.
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).