MySQL user without SUPER privilege

Question

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?

Answer 1

I have a surprise for you.

This command

grant all privileges on mydb.* to 'mydb'@'%' identified by 'password';

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

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)

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 say GRANT USAGE .... When you see USAGE, this tells you that all global privileges in the mysql.user table from that user are 'N'. To prove this, run this command:

SELECT * FROM mysql.user WHERE user='mydb' AND host='%'\G

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 table mysql.db, which looks like this:

mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

Please notice that the column super_priv does not exist in mysql.db

Now, run this command

SELECT * FROM mysql.db WHERE user='mydb' AND host='%'\G

and see for yourself.

Now, do you have any global users ?

SELECT user,host FROM mysql.user WHERE super_priv='Y';

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:

UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;

SUMMARY

Do not worry about any database-level users. They do not possess the SUPER privilege and never will.

Answer 2

As a rule, you (as root) should only grant users the privileges that they need to perform the tasks that they have to perform. A simple tutorial is here. Docco available here, here and here.

It is definitely worthwhile making the effort to at least get the basics right - with privileges, if something goes wrong, you can immediately eliminate many possibilities as to the origin of the error. If everyone's a superuser, anybody can be responsible for anything. It's also good practice to follow err... good practice. If you do it systematically, then when it's an actual requirement, it's second nature. The correct/safe method is to do what I suggested - as much power as they need, but no more. Finally, you could script this for your users - REVOKE superuser powers and then GRANT them back on a per-need basis. SHOW GRANTS; will be of use.

In answer to your question, yes, it is potentially dangerous.