creating a user and granting no 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 that the user does not already exist? My understanding is that each of these creates a user with a password, and grants no privileges to the user.

If the user does already exist, then I expect some differences:

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

Answer 1

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

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

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

will throw an error in case of NO_AUTO_CREATE_USER sql mode.

From the reference - NO_AUTO_CREATE_USER Prevent GRANT from automatically creating new users if it would otherwise do so, unless a non-empty password also is specified.

You are right about the GRANT and CREATE USER statements when user exists; CREATE USER will throw an error, but in this case, if you want to change the password, you can use SET PASSWORD statement, e.g. -

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