CODEWITHSUNDEEP
mysqlsqlprivileges
Matt Fenwick
Matt Fenwick

Reputation: 49085

creating a user and granting no privileges

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:

Upvotes: 6

Views: 7137

Answers (1)

Devart
Devart

Reputation: 121922

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');

Upvotes: 3

Related Questions