I received access denied; you need (at least one of) the super privilege(s) for this operation - What to do now?
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 operationIn 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 user1.
In case of access denied; you need (at least one of) the super privilege(s) for this operation - 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 visualise 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).Answer (1)
If you encounter the "Access Denied" error message stating that you require super privileges for the operation, you'll need to take specific actions to address it. Here's what you can do:
Check User Privileges: Firstly, ensure that your user account has the necessary privileges to perform the operation. Review the privileges required for the task you're attempting and verify that your user account has been granted those privileges.
Seek Assistance from a Super User: If you don't have the required privileges, reach out to a super user or administrator who does. They may be able to perform the operation on your behalf or grant you the necessary privileges.
Review System Documentation: Consult the system documentation or contact your system administrator to understand the super privileges required for the operation. They can provide guidance on how to obtain these privileges or alternative methods to accomplish your task.
Request Privilege Granting: If your role or responsibilities require regular access to perform the operation, consider requesting the necessary privileges from your system administrator. Provide justification for why you need these privileges and how they contribute to your job responsibilities.
Troubleshoot Authorization Issues: If you believe you should have the required privileges but are still encountering the error, investigate potential authorization issues. Check for any misconfigurations or conflicts in permissions settings that may be preventing you from accessing the required privileges.
Follow Security Protocols: Ensure that you adhere to security protocols and best practices when requesting or granting super privileges. These privileges often come with elevated access to sensitive data or system resources, so it's essential to handle them responsibly and securely.
By following these steps and addressing any privilege-related issues, you should be able to resolve the "Access Denied" error and proceed with the necessary operation.