22

I've created a database and a user, and allowed access via the following:

create user 'someuser'@'%' identified by 'password';
grant all privileges on somedb.* to 'someuser' with grant option;

however, when I try to connect to MySQL I get the following error:

$ mysql -u someuser -p
> Enter Password:
> ERROR 1045 (28000): Access denied for user 'someuser'@'localhost' (using password: YES)

If "%" is the wildcard, then wouldn't it also enable localhost? However, if I do not specify that I want to use a password, then I can connect just fine to the database, which makes no sense because I'm specifying a password when I created the user.

Improve this question

5 Answers 5

Reset to default
20

Try connecting with mysql -u someuser -p -h 127.0.0.1.

If you can connect without a password, either you have saved credentials in a .my.cnf or you have created an account that allows access without a password.

This comment from the mysql docs may also be related.

http://dev.mysql.com/doc/refman/5.1/en/access-denied.html

If you cannot figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain '%' or '_' characters). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this allows you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''.

Improve this answer
1
  • +1 including host definition & ~/.my.cnf reference
    – Andy
    Mar 15, 2010 at 23:20
9

I'm pretty sure you need the following:

grant all privileges on somedb.* to 'someuser'@'%' with grant option;

Your GRANT statement lacks a hostname declaration.

Improve this answer
1
  • A location should be specified: GRANT ALL ON somedb.* TO 'someuser'@'10.1.10.1';
    – tacotuesday
    Nov 21, 2012 at 20:17
7

If you are unable to connect to mysql using someuser@'%' where '%' is the wildcard for the hostname, then make sure you don't have ''@localhost entry in your user table. Confirm using the following SQL statement:

    mysql> SELECT * FROM user WHERE user='' AND host='localhost';

If ''@localhost is existing, then remove it by issuing the following SQL statement:

    mysql> DELETE FROM user WHERE user='' AND host='localhost';

then lastly

    FLUSH PRIVILEGES;

Now someuser@'%' will connect to the database.

Improve this answer
6
  • Granting 'username'@'localhost' privileges will not cause grants on the same user from a different location to not work.
    – tacotuesday
    Nov 21, 2012 at 20:11
  • how is this relevant? what does the '' user at 'localhost' cause?
    – Steve Buzonas
    Jan 29, 2015 at 23:27
  • 1
    @SteveBuzonas This is absolutely relevant. It provides a code example for the answer that Zoredache posted. Localhost is more specific than '%', so if you attempt to connect via localhost with a user that only has access at '%', the localhost entry is more specific, so mysql attempts to login at localhost, but expects an empty username and empty password. Since those are not the credentials provided, you get the access denied error. By removing these entries, it allows access for users at '%'.
    – bstakes
    Feb 6, 2015 at 14:33
  • @bstakes the default behavior of the mysql client is to use your shell username if you don't specify one. the question has a user in the example and in the error message. i was curious how a '' user would conflict with a named user. is '' a wildcard?
    – Steve Buzonas
    Feb 6, 2015 at 15:35
  • @SteveBuzonas '' is functioning like a wildcard with respect to localhost. From the MySQL Docs and shown in an answer above referencing the default that you mentioned: "Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''."
    – bstakes
    Feb 6, 2015 at 19:52
4

My understanding, and I'm prepared to be corrected on this, is that MySQL treats localhost separately to %. i.e. localhost is not included in the wildcard.

Improve this answer
4
  • The examples provided in dev.mysql.com/doc/refman/5.1/en/connection-access.html lead me to believe this may not be accurate. Do you have references otherwise?
    – Warner
    Mar 15, 2010 at 13:24
  • My understanding of this matter is based on others reporting the same issue, both in print and verbally, and resolving it by creating 2 users, using the "%" and "localhost". I can't recall ever actually seeing it officially documented though.
    – John Gardeniers
    Mar 15, 2010 at 21:37
  • 1
    This seems to be correct on Ubuntu 12.04 LTS at least
    – Dex
    May 31, 2013 at 2:09
  • What happened to me is that I had user@% and it didn't worked until I included user@localhost. Then I saw the answer stackoverflow.com/a/29421084/4850646 and realized that I had an anonymous user with host localhost. I removed all the anonymous users and was able to access from localhost for that user, even after removing user@localhost (and letting only user@%). It seems that because localhost is more specific than %, it tries first the localhost user, even if it is an anonymous user!
    – Lucas Basquerotto
    May 16, 2019 at 14:28
0

Have you run flush privileges; after creating the user? If you don't, changes to users/permissions won't take until after the server is restarted.

Then, check you don't have a '%'@'localhost' entry.

Improve this answer
1
  • 4
    using 'create user' and 'grant' automatically flushes privileges. You should only need to flush privileges if you are directly manipulating the mysql database.
    – Zoredache
    Mar 15, 2010 at 5:59

You must log in to answer this question.

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