Cannot log in with created user in mysql

Question

Using this command

GRANT ALL PRIVILEGES ON *.* to brian@'%' identified by 'password';

I try to login with:

 mysql -u brian -ppassword

The error is:

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

I am doing this as root and I did try to flush privileges.

I tried this with countless users but it does not seem to work. I can create a user with no password and login works. Command line and from phpmyadmin

Answer 1

You need to explicitly add user@localhost. '%' does not match localhost.

Answer 2

You definatly used

... identified by 'password';

and not

... identified by password 'password';

as the latter expects a password hash value rather than the plaintext password. This would explain why you're unable to login with any of the passwords you set.

Answer 3

I usually do it in 2 commands:

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
GRANT ALL ON *.* TO 'user1'@'localhost';

Doesn't usually give me any trouble

Answer 4

Anything different happen if you specify the host?

mysql -ubrian -ppassword -hlocalhost

mysql -ubrian -ppassword -h127.0.0.1

Try checking:

SELECT user, host, password, PASSWORD('password') FROM mysql.user

And make sure the password in the db and the result of the PASSWORD function are the same?

Answer 5

No network connection is made when you do:

mysql -u brian -ppassword # with no -h

Instead, a socket connection is made. Socket connects do not match the host wildcard '%'. They only match the host 'localhost' as a security measure. You can force a network connection like this:

mysql -u brian -ppassword -h `hostname`

That works most of the time, but I have seen a few systems where it did not. If it works for you, it is a good way to avoid maintaining pairs of grants for each user ('user'@'%' and 'user'@'localhost')

Answer 6

The wildcard hostname is only matching TCP connections, not the socket connection used by localhost; you'll need an entry for % and for localhost.

The reason it's working without a password is probably because you have different credentials set in ~/.my.cnf

Answer 7

I hate when little niggling crap stops you dead in your tracks. I dropped an rebuilt my dev db many times before with no problem. When I got this error today, I was stumped. Thanks for the info!

Answer 8

James:

My first reaction on reading your note of Mar 15 was "Get out of here! If that were true, I'd know it!"

Wrong. I didn't know it, and you've just educated me to something I can't believe I'd not already known. I've run into the problem before a couple times, and every time, I appear to have accidentally solved it, by doing what you suggested above, but not understanding why I had to do so.

Thanks very much for the wisdom.