CODEWITHSUNDEEP
mysql
Byeonggil Park
Byeonggil Park

Reputation: 137

what is the meaning of "%" between when you create user and giving grant in mysql

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?

Upvotes: 1

Views: 540

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562701

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.

Upvotes: 3

Related Questions