Why is a "GRANT USAGE" created the first time I grant a user privileges?
Solution 1
As you said, in MySQL USAGE
is synonymous with "no privileges". From the MySQL Reference Manual:
The USAGE privilege specifier stands for "no privileges." It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.
USAGE
is a way to tell MySQL that an account exists without conferring any real privileges to that account. They merely have permission to use the MySQL server, hence USAGE
. It corresponds to a row in the `mysql`.`user`
table with no privileges set.
The IDENTIFIED BY
clause indicates that a password is set for that user. How do we know a user is who they say they are? They identify themselves by sending the correct password for their account.
A user's password is one of those global level account attributes that isn't tied to a specific database or table. It also lives in the `mysql`.`user`
table. If the user does not have any other privileges ON *.*
, they are granted USAGE ON *.*
and their password hash is displayed there. This is often a side effect of a CREATE USER
statement. When a user is created in that way, they initially have no privileges so they are merely granted USAGE
.
Solution 2
I was trying to find the meaning of GRANT USAGE on *.* TO
and found here. I can clarify that GRANT USAGE on *.* TO user IDENTIFIED BY PASSWORD password
will be granted when you create the user with the following command (CREATE
):
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
When you grant privilege with GRANT
, new privilege s will be added on top of it.
Solution 3
In addition mysql passwords when not using the IDENTIFIED BY
clause, may be blank values, if non-blank, they may be encrypted. But yes USAGE
is used to modify an account by granting simple resource limiters such as MAX_QUERIES_PER_HOUR
, again this can be specified by also
using the WITH clause, in conjuction with GRANT USAGE
(no privileges added) or GRANT ALL
, you can also specify GRANT USAGE
at the global level, database level, table level,etc....
Related videos on Youtube
John M Naglick
Updated on July 08, 2022Comments
-
John M Naglick almost 2 years
I'm new to the admin side of DBMS and was setting up a new database tonight (using MySQL) when I noticed this. After granting a user a privilege for the first time, another grant is created that looks like
GRANT USAGE on *.* TO user IDENTIFIED BY PASSWORD password
The documentation says that the
USAGE
privilege means "no privileges," so I'm inferring thats grants work hierarchically and perhaps a user must have some kind of privilege for all databases, so this serves as a catch all?I also dont understand why this line has an
IDENTIFIED BY
clause in it when the grant I created does not have one (mostly because I dont understand what purpose theIDENTIFIED BY
clause serves).Edit: Sorry for not stating this originally, the grants were
GRANT ALL PRIVILEGES ON database.* TO admin_user GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO user
-
Pentium10 over 14 yearscan you please tell us the grant you've run
-
-
Mircea Vutcovici about 9 yearsI think it is because they are allowed to authenticate. If you try with a wrong user or/and password, you can not authenticate. An user with USAGE privilege can run certain SQL commands like 'select 1+1' and 'show processlist'.
-
Čamo over 3 yearsWhat it means "on top of it"?
-
umläute almost 3 yearsit means "additionally". as in "new priviliges are added in additon to the those already granted by
GRANT USAGE
".