1

So basically what is the difference of '%' and 'localhost' when you create a user and give a grant to the created user.

what will happens when you create/grant a user with 'localhost' and '%' each

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
vs
CREATE USER 'user'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON db.* TO 'user'@'localhost';
vs
GRANT ALL PRIVILEGES ON db.* TO 'user'@'%';

it seems like if I create a user with 'localhost' it means I can only log in the user in the local server, not from remote. and in terms of the grant with 'localhost', is that mean that the user only can access to database from local server, not from remote, is that correct?

Byeonggil Park
  • 107
  • 3
  • 12
  • thanks but I know what % means. I just want to know the comparison of '%' and 'localhost' on creating a user and giving a grant – Byeonggil Park Dec 06 '19 at 15:36
  • I changed the question a bit. can you check? @pnorton – Byeonggil Park Dec 06 '19 at 15:37
  • Hi Byeonggil How do you read CREATE USER 'user'@'%' IDENTIFIED BY 'password'; ? have you tested it? –  Dec 06 '19 at 15:40
  • Yes, those are working fine. I want to know the accessibility depending on 'localhost' and '%' from the remote when I create a user and grant the user. it might be a silly question but it just from curiosity. – Byeonggil Park Dec 06 '19 at 15:47
  • 2
    Possible duplicate of https://stackoverflow.com/questions/11634084/are-users-user-and-userlocalhost-not-the-same – Bill Karwin Dec 06 '19 at 15:56
  • 1
    Reference: https://dev.mysql.com/doc/refman/5.7/en/account-names.html ... dances around the question that was asked. In MySQL, the two statements create two separate and distinct users, each with their own password and privileges. (`@'localhost'` is for connecting with Unix socket only. The `@'%'` allows connection from any IP address... – spencer7593 Dec 06 '19 at 15:58
  • thank you, bill, spencer, pnorton. those helped a lot – Byeonggil Park Dec 06 '19 at 16:03

1 Answers1

2

MySQL treats '%' as a wildcard matching any client who connects via TCP. So any hostname or any IP address, including 127.0.0.1

MySQL treats 'localhost' as a special case. It only matches a client who connects only via the UNIX socket. The UNIX socket is faster than TCP, but it only works locally.

If you want to create a user who can connect either from any host via TCP, or via the UNIX socket, you must actually create two users. It's up to you to give them the same passwords and grant them the same privileges. MySQL does not ensure that.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    My coworker found a weird case to make this even more confusing. The Go connector for MySQL resolves "localhost" to its IP address 127.0.0.1 before connecting. So it forces you to use TCP. Every other MySQL client library allows "localhost" to mean the UNIX socket. – Bill Karwin Dec 06 '19 at 16:08