1

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

2
  • Do they show that you've added them? SELECT * from mysql.user; Sep 11, 2009 at 17:21
  • They do show up in that select.
    – Brian G
    Sep 11, 2009 at 17:25

6 Answers 6

4

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

4
  • Are you sure? The documentation indicates that '%' will match any hostname dev.mysql.com/doc/refman/5.1/en/account-names.html Sep 11, 2009 at 18:09
  • 3
    Yes. localhost has special meaning in mysql it means you are connecting via the local socket. '%' matches any host arriving via a TCP connection. localhost is not the same as 127.0.0.1.
    – Craig
    Sep 11, 2009 at 19:02
  • Try changing the command you are using to "sql -u brian -ppassword --host=127.0.0.1"
    – Craig
    Sep 11, 2009 at 19:12
  • It's bitten us at work before. Add user@localhost should work.
    – sybreon
    Sep 12, 2009 at 3:36
1

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.

0

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

0

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?

0

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')

0

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

1
  • 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.
    – user62664
    Dec 6, 2010 at 9:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .