User Creation and Lack of Privileges

Question

Is there a difference between

grant usage on databasename.* to 'username'@'localhost' identified by 'password' 

and

 grant usage on *.* to 'username'@'localhost' identified by 'password' 

and

 create user 'username'@'localhost' identified by 'password' 

Assuming the user does not exist yet? I understand that each of them creates a user with a password and does not provide the user with any rights.

If the user already exists, I expect some differences:

• grant -statements will change the password to 'password'
• create user -statement will fail

Answer 1

All these statements do the same - they create a new user without any privileges. First and second, they do this using the GRANT statement, where USE means - NO PRIVILEGES.

But there is a difference between the statements of GRANT + USAGE and CREATE USER:

 grant usage on *.* to 'username1'@'localhost'; -- when password is not specified 

throws an error in case of NO_AUTO_CREATE_USER sql mode.

From the link - NO_AUTO_CREATE_USER Prevent automatic creation of GRANT for new users, if otherwise, this will happen if a non-empty password is specified.

You are right about GRANT and CREATE USER statements when a user exists; CREATE USER gives an error, but in this case, if you want to change the password, you can use SET PASSWORD , for example. -

 SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new password');