How to Fix MySQL Error 1227: Access Denied — SUPER Privilege Required

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation is raised when the current user attempts an operation that requires a high-privilege capability — most commonly SUPER, but also SYSTEM_VARIABLES_ADMIN, REPLICATION_SLAVE_ADMIN, SET_USER_ID, or others — that has not been granted. The error symbol is ER_SPECIFIC_ACCESS_DENIED_ERROR.

Impact

The failing statement is rejected immediately without any partial effect. The surrounding transaction is not automatically rolled back, but the specific operation that triggered the error does not execute. This error surfaces during schema migrations, replication setup, stored routine imports (such as mysqldump restores), or any attempt to change global system variables or definer attributes.

On managed cloud databases (Amazon RDS, Aurora, Cloud SQL, PlanetScale, Azure Database for MySQL), SUPER is intentionally withheld from all users, including the admin account. This makes error 1227 extremely common when restoring dumps created on a self-hosted instance or when running migration scripts that were authored against a root-level account.

Common Causes

1. Restoring a mysqldump that contains DEFINER clauses. Routines, views, and triggers exported with mysqldump embed DEFINER=root@localhost`` (or another privileged user). Importing as a different user triggers 1227 because only SUPER (or SET_USER_ID in MySQL 8.0+) holders may set an arbitrary definer.

2. Creating or altering a stored procedure/function/view/trigger with an explicit DEFINER. Any CREATE DEFINER=<other_user> PROCEDURE ... where the current user is not <other_user> and lacks SUPER/SET_USER_ID will fail.

3. Setting a global or restricted session variable. Some system variables (sql_log_bin, gtid_next, binlog_format, certain innodb_* variables) require SUPER or SYSTEM_VARIABLES_ADMIN to modify at runtime.

4. Replication commands. CHANGE MASTER TO, START SLAVE, STOP SLAVE, RESET SLAVE require REPLICATION_SLAVE_ADMIN (MySQL 8.0+) or SUPER (MySQL 5.7 and earlier).

5. Killing another user's query. KILL <thread_id> for a thread belonging to a different user requires SUPER (MySQL 5.7) or CONNECTION_ADMIN (MySQL 8.0+).

6. Using SET GLOBAL without the appropriate dynamic-privilege grant. MySQL 8.0 disaggregated many SUPER operations into fine-grained dynamic privileges. Code written for 5.7 that relied on SUPER may fail on 8.0 instances where only the more specific privilege was granted.

Troubleshooting and Resolution Steps

1. Check what privileges the current user holds.

   SHOW GRANTS FOR CURRENT_USER();
   

   Look for SUPER, SYSTEM_VARIABLES_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, REPLICATION_SLAVE_ADMIN, or CONNECTION_ADMIN depending on what operation you need.

2. Strip DEFINER clauses when restoring a dump on a managed database.

   Use sed to remove them before importing:

   sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' dump.sql > dump_clean.sql
   mysql -u admin -p mydb < dump_clean.sql
   

   Alternatively, use mysqldump --no-definer (available on Percona and some community builds) or pass --set-gtid-purged=OFF when the dump was taken from a GTID-enabled source.

3. Grant the required dynamic privilege (MySQL 8.0+) instead of requesting SUPER.

   On self-managed MySQL 8.0, grant only the specific privilege needed rather than the broad SUPER:

   -- To allow setting global variables:
   GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'appuser'@'%';
   
   -- To allow creating routines with an arbitrary DEFINER:
   GRANT SET_USER_ID ON *.* TO 'appuser'@'%';
   
   -- To allow replication commands:
   GRANT REPLICATION_SLAVE_ADMIN ON *.* TO 'appuser'@'%';
   

4. On Amazon RDS / Aurora, use the managed parameter group for global variable changes.

   RDS does not permit SET GLOBAL for most variables. Instead, modify the DB parameter group in the AWS console or via the AWS CLI:

   aws rds modify-db-parameter-group \
     --db-parameter-group-name my-param-group \
     --parameters "ParameterName=max_connections,ParameterValue=500,ApplyMethod=immediate"
   

5. On Cloud SQL (Google), use instance flags.

   Variable changes must be applied as instance flags through gcloud sql instances patch or the console — not via SQL.

6. Alter the DEFINER of existing routines after import.

   If routines were imported with incorrect definers and now fail at execution time, update them:

   -- List affected routines
   SELECT ROUTINE_NAME, DEFINER
   FROM information_schema.ROUTINES
   WHERE ROUTINE_SCHEMA = 'mydb';
   
   -- Update definer (requires SUPER or SET_USER_ID on self-managed MySQL)
   UPDATE mysql.proc
     SET definer = 'newuser@%'
   WHERE db = 'mydb' AND definer = 'root@localhost';
   FLUSH PRIVILEGES;
   

   On MySQL 8.0, use ALTER ... SQL SECURITY INVOKER to sidestep the definer issue entirely for views and routines where caller-context execution is acceptable:

   ALTER VIEW myview SQL SECURITY INVOKER AS SELECT ...;
   

Additional Information

• The SQLSTATE 42000 class covers syntax and access-rule violations; 1227 shares this class with permission-related errors such as 1044 (ER_DBACCESS_DENIED_ERROR) and 1142 (ER_TABLEACCESS_DENIED_ERROR).
• MySQL 8.0 introduced role-based access control and broke SUPER into over a dozen dynamic privileges (AUDIT_ADMIN, BINLOG_ADMIN, CONNECTION_ADMIN, ENCRYPTION_KEY_ADMIN, etc.). Always prefer the narrowest privilege on 8.0+.
• Some ORMs (Flyway, Liquibase) run schema migrations as the application user. If that user is not root, migrations that create triggers or stored procedures with implicit definers will fail with 1227.
• sql_log_bin = 0 (a common workaround in replication setups) requires SUPER on MySQL 5.7 and SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN on 8.0.
• When error 1227 occurs inside a mysqldump restore pipeline, the import process often continues after the error, leaving routines or triggers missing. Always check for errors after restoring a dump rather than assuming success.

Frequently Asked Questions

Why does error 1227 appear on RDS even though I'm using the master user account?

RDS master users are granted most administrative permissions but are explicitly denied SUPER because AWS uses that privilege internally for replication and instance management. For routine and view imports, strip DEFINER clauses from the dump as described above. For global variable changes, use the RDS parameter group.

I'm on MySQL 8.0. How do I know which dynamic privilege replaces SUPER for my use case?

Check the MySQL 8.0 reference for the specific statement failing. The error message often names the required privilege (e.g., you need (at least one of) the SYSTEM_VARIABLES_ADMIN privilege(s)). If it only says SUPER, look up the statement in the MySQL 8.0 privilege documentation — each formerly-SUPER operation now maps to a named dynamic privilege.

Can I grant SUPER to a user on a self-managed MySQL instance?

Yes, on a self-managed instance you can grant SUPER to any user from a root-level account:

GRANT SUPER ON *.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

On MySQL 8.0, prefer fine-grained dynamic privileges over SUPER where possible, as SUPER is considered legacy and may be removed in a future major version.

Why does the error mention a different privilege name than SUPER?

On MySQL 8.0+, the error message reflects the actual dynamic privilege the operation requires rather than the generic SUPER. For example, you may see REPLICATION_SLAVE_ADMIN for replication commands or SET_USER_ID for definer changes. Grant that specific privilege rather than SUPER.