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)
The solution is to delete the anonymous (Any) user!
I also faced the same issue on a server setup by someone else. I normally don't choose to create an anonymous user upon installing MySQL, so hadn't noticed this. Initially I logged in as "root" user and created a couple of "normal" users (aka users with privileges only on dbs with their username as prefix), then logged out, then went on to verify the first normal user. I couldn't log in. Neither via phpMyAdmin, nor via shell. Turns out, the culprit is this "Any" user.
A related problem in my case was trying to connect using :
Whitespace IS apparently allowed between the -u #uname# but NOT between the -p and #password#
Therefore needed:
Otherwise with white-space between -p mypass mysql takes 'mypass' as the db name
It's a difference between:
and
Check it:
The command
access implicit to 'bill'@'localhost' and NOT to 'bill'@'%'.
There are no permissions for 'bill'@'localhost'
you get the error:
solving the problem:
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).I had the same problem, but in my case the solution was solved by the comment by eggyal. I had an anonymous user as well, but removing it didn't solve the problem. The 'FLUSH PRIVILEGES' command worked though.
The surprising thing to me about this was that I created the user with MySQL Workbench and I would have expected that to perform all of the necessary functions to complete the task.
Not sure if anyone else will find this helpful, but I encountered the same error and searched all over for any anonymous users...and there weren't any. The problem ended up being that the user account was set to "Require SSL" - which I found in PHPMyAdmin by going to User Accounts and clicking on Edit Privileges for the user. As soon as I unchecked this option, everything worked as expected!