MySQL/MariaDB: What is the difference between 'GRANT ... IDENTIFIED BY' vs 'CREATE USER' and then 'GRANT'?

user_5735139 0
Viewed 330

When setting up a DB I normally create a user with:

CREATE USER myuser@localhost IDENTIFIED BY 'pa$$w0rd';

and then give the user some permissions on a DB:

GRANT SELECT, INSERT, UPDATE ON dbname.* TO 'myuser'@'localhost' IDENTIFIED BY 'pa$$w0rd';

I see this two command sequence all over the place as the way to do this. However, I notice if I skip the CREATE USER command and start with GRANT the user seems to be created automatically and work fine. Is there a reason why CREATE USER should still be used before GRANT? Or is it just an old convention maybe for backwards compatibility?

mariadb database-permissions permissions mysql
2 Answers

MySQL used to support implicit creation of users merely by GRANTing privileges to them. This usage allowed GRANT to be idempotent, and replication-safe. For example when the user may or may not exist on a replica, GRANT would create the user if the user did not already exist. If the user did exist, then GRANT still works, and adds privileges if needed.

When GRANT was used in this way, it was optional to use an IDENTIFIED BY clause to set a password. If the user already exists, then IDENTIFIED BY is not needed because the user already has a password. If the user does not exist, then omitting the IDENTIFIED BY would implicitly create the user, but with no password (i.e. anyone can login as that user without entering a password).

This was considered a security risk. For example, if someone uses GRANT without IDENTIFIED BY, and makes a spelling mistake on the username, then one could accidentally create a new user with privileges, but no password.

GRANT ALL ON *.* TO 'ruhnett'@'%';  -- misspelled username creates new user

This would allow anyone to log in as the new user and gain privileged access.

So in MySQL 5.7, using GRANT to create users implicitly was deprecated.

CREATE USER ... IF NOT EXISTS syntax takes the place of idempotent user creation. This encourages developers to be more explicit about when they want to create a user versus when they want to grant privileges to an existing user.

Bill_Karwin_208600

What you're doing will not work in MySQL 8.0. The ability to create the user automatically in the GRANT command has been removed, you have to use CREATE USER first.

user_14918950
Related