This may be a simple question and may not be the right place to ask but I couldn't find any useful information from Google. Here is my question.
When creating users in mysql using grant all privileges on mydb.* to 'mydb'@'%' identified by 'password';
, the user is getting super privilege. I used to create all my mysql users in this manner so, setting read-only=1 in my slave machine have no effect as the super user can write with read-only option set. So my question here is, using the above command for creating users is dangerous? If yes, what is the correct/safe method? Or I have to manually revoke the super privilege?
Best Answer
I have a surprise for you.
This command
does not give away the SUPER privilege. How do I know this ?
SUPER is a global grant privilege. Global grants exist in the table
mysql.user
Notice it has a column called
super_priv
.If you run the command
SHOW GRANTS FOR mydb.'%';
, it will echo two lines. The first line will sayGRANT USAGE ...
. When you seeUSAGE
, this tells you that all global privileges in themysql.user
table from that user are 'N'. To prove this, run this command:That being said, you are probably wondering, what privileges are granted for
grant all privileges on mydb.* to 'mydb'@'%' identified by 'password';
?Look carefully at the command. You are granting all privileges to the user for the
mydb
database. Where are database-level grants stored ? That's right, you guessed it. It's in the tablemysql.db
, which looks like this:Please notice that the column
super_priv
does not exist inmysql.db
Now, run this command
and see for yourself.
Now, do you have any global users ?
This will give you a list of users that currently have the SUPER privilege.
If you want to manually revoke that privilege from every user except
root
, login as root and do this:SUMMARY
Do not worry about any database-level users. They do not possess the SUPER privilege and never will.