53

I created a user per the first command but cannot logon via localhost (linux). This link mysqldoc indicates that I need to create a second user by the same name, but using the syntax in the second block of commands.

mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY '4myuser';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON my_upload.* TO 'myuser'@'%' IDENTIFIED BY '4myuser';
Query OK, 0 rows affected (0.00 sec)

So I tried that as below, and it indeed worked. But are these two separate users? If I change the pw for one, will the other one sync, or are they truly separate users?

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY '4myuser';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON my_upload.* TO  'myuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
EdgeCase
  • 4,719
  • 16
  • 45
  • 73

5 Answers5

54

User@% would allow access from all locations. User@localhost would only allow access from localhost. They are two different users with two different passwords (though you can set them to the same password, but if you update one password the other will not auto-update)

Konerak
  • 39,272
  • 12
  • 98
  • 118
Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • 20
    When I create a user `User@%` I still don't seem to be able to login from localhost. Instead it seems I have to create a `User@localhost` user. This seems to go against the statement "User@% would allow access from all locations.", no? – User Apr 29 '14 at 23:45
  • 1
    I think it might be a windows-specific issue having to do with the hosts file: See this answer for possible solution: http://stackoverflow.com/a/2283806/155268 – User Apr 30 '14 at 00:08
  • 3
    More than likely, %@localhost has been denied access, and without a specific User@localhost grant, User will be denied access from localhost despite the User@% wildcard (which is processed later, due to have a less-specific host name; per the MySQL manual linked by Bjoern in a separate answer). – Kent Jun 11 '14 at 01:58
  • 1
    You state that User@% would allow access from all locations, but it seem to allow access from all locations except localhost. However, your answer could work with mysql version from 2012... – 9ilsdx 9rvj 0lo Jan 13 '17 at 11:01
  • @9ilsdx9rvj0lo I don't think this is the case, as I can find nowhere in MySQL where this fundamental behavior has changed in most recent versions. My guess is either privileges were not flushed, or perhaps there is a grant rule attached to `localhost` that is taking precedence since it is more specific than the wildcard. – Mike Brant Jan 13 '17 at 20:26
  • With a 'user'@'%' GRANT you will be able to login with mysql -u user -h 127.0.0.1 but not -h localhost. – zach Sep 21 '17 at 16:45
18

Basically yes, those are two different users with (possibly) different permissions and (possibly) different passwords.

  • myuser@% : User myuser, connecting from any host.
  • myuser@localhost : User myuser, connecting from localhost only.

A good read is the MySQL manual about connection access, it demonstrates how it works.

Bjoern
  • 15,934
  • 4
  • 43
  • 48
4

I encountered the same situation as described - Adding an entry for user@% was not working.

Yet adding an entry for the same user@localhost would start to work again though. This did seem unintuitive, given our understanding of the % pattern!

An issue identified by Kent above was that: We had an entry row in the users table for host:localhost but the user was blank. This apparently resolved to a rule for %@localhost which was being used as a match before my user@% rule.

Short answer - check for blank or wildcard usernames in your user table.

... I have no idea how that blank got there or if it was intentional.

dman
  • 1,089
  • 13
  • 9
1

Even if they would mean the same, or if one would include the other, they ARE indeed separate users!

13.7.1.3. GRANT Syntax

MySQL and Standard SQL Versions of GRANT

The biggest differences between the MySQL and standard SQL versions of GRANT are:

  • MySQL associates privileges with the combination of a host name and user name and not with only a user name.
Konerak
  • 39,272
  • 12
  • 98
  • 118
-1

The main difference between MySQL and SQL is that MySQL is a specific type of SQL database management system, while SQL is a standard language used to manage databases. MySQL is a relational database management system (RDBMS) that.

MEDISH SESAY
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 13 '23 at 06:10