I'm granting the following rights to a user:
grant super on . to 'user'@'host';
does this include the rights on dbname given by:
grant all on dbname.* to 'user'@'host';
thank you
Mysql – Does thesql SUPER privilege include ALL
MySQLpermissionsuser-defined-type
Related Solutions
You can't avoid it. The restriction applies also to stored procedures. The only alternative would be to have the SUPER privilege, but I don't think it's possible in this case.
Moreover, you have to declare your function as DETERMINISTIC, telling explicitly that you're not going to modify data:
CREATE FUNCTION GetRootZoneId (zoneId INT) RETURNS INT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE _id, _parentId INT;
SELECT zoneId INTO _parentId;
my_loop: LOOP
SELECT id, parentId
INTO _id,_parentId
FROM Zone
WHERE id = _parentId;
IF _parentId IS NULL THEN
LEAVE my_loop;
END IF;
END LOOP my_loop;
RETURN _id;
END;
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.
Related Question
- MySQL – Stored Procedure Requiring SUPER Permission
- MySQL Security – Risks of Granting SUPER Privileges to a User
- Teradata 15: Failed [5315 : HY000] An owner referenced by user does not have SELECT WITH GRANT OPTION access to DBC.RoleMembersV.RoleName
- MariaDB: granting access to a DB before creating the DB
- Postgresql – How to grant Connect & Create Privilege on current Database in PostgreSQL
- SQL Server – Grant Permissions to All Databases Using Server Role
Best Answer
Please be careful not to assign SUPER to just anyone. Once a DB Server reaches the max_connections limit, only one DB Connection is allowed to login and that user must have the SUPER privilege. Otherwise, a DBA cannot login to perform major operations mentioned by @DTest.
As to the question, here is mysql.db from MySQL 5.0.45
Here is mysql.user from MySQL 5.0.45
As shown, SUPER is not a DB specific privilege. It is a DBA privilege.