How to grant super privilege to the user?

Question

I have created a user and given privileges to the user1.

`grant all privileges on db1.* to user1@'%' with grant option;

Am using mysql workbench to import dumps to my database. While importing dumps to database db1,error occurs stating that

ERROR 1227 (42000) at line 49: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

In that dumps all tables are imported successfully but the error occurs while importing routines to the database. Is there anything wrong with the privilege, I have given to the user1. Please advice.

Answer 1

In a politically correct sense, what you just asked for is impossible. Why ?

The SUPER privilege is a global privilege, not a database level privilege.

When you created the user with

grant all privileges on db1.* to user1@'%' with grant option;

you populated the table mysql.user with user='user1' and host='%'. All other columns (global privileges) were defaulted to 'N'. One of those columns is Super_priv. Here is the table:

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    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.00 sec)

mysql>

Super_priv appears right after Show_db_priv.

The database level privileges got populated into mysql.db. Here is it:

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)

mysql>

Notice that Super_priv does not exist in mysql.db.

To visualize this in pure SQL terms, login as user1 and run SHOW GRANTS; The output will have two lines:

• GRANT USAGE ON *.* TO user1@'%' ...
• GRANT ALL PRIVILEGES ON db1.* TO user1@'%' ...

There is a hack you can try but I normally would not recommend it.

STEP 01) Login to mysql as root@localhost (should have all privs)

STEP 02) Run this query

UPDATE mysql.user SET Super_Priv='Y' WHERE user='user1' AND host='%';

STEP 03) Run this query

FLUSH PRIVILEGES;

That theoretically should work. Then, user1 may work (I make no guarantees).

UPDATE 2014-12-19 15:24 EST

Metafaniel just asked

Great explanation, thanks. However if you do not reccomend that way to solve the problem, then what other way is the best one to gran a user this Super_priv?? Thanks! – Metafaniel

Since a user with only DB access cannot have SUPER, the only thing one can do is change the DEFINER manually in the dump. The basic idea would be to mysqldump the routines alone to a text file. Then, edit the definer to user1@'%'. Then, you should be able to reload.

• Oct 02, 2011 : Can mysqldump dump triggers and procedures?
• Jul 25, 2011 : DROP PROCEDURE IF EXISTS not included in mysqldump
• Jul 25, 2011 : Dump only the Stored Procedures in MySQL

Same Thing for Views

• Mar 23, 2012 : MySQL Views Authorization
• Jul 26, 2011 : Modify DEFINER on Many Views

Answer 2

I had the exact same problem. What I did was:

GRANT SUPER ON *.* TO user1@localhost

And problem solved.

CAVEAT: Is there any danger in granting SUPER privileges to a user?

Answer 3

"Access denied" error during the import process might be due to the privileges mismatch for the DEFINERs between the database exported and the database going to be imported. I faced the same situation and below command works well for me to import the database successfully.

cat db1.sql | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -u user1 -p db1