ERROR 1227 (42000) Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege

Question

Hello all,

I have a sql file that is exported from RDS1 and I need to import this file to newly created RDS2. Exporting this file was seamless. However, I'm running to permission issues when running the import on RDS2. Any suggestion on how to fix the error and/or how to import the sql file to RDS2?

Export - works

```
mysqldump -h rds1 -P 3306 -u root -p --all-databases --set-gtid-purged=OFF > db1.sql 
```

Import - doesn't work
```
mysql -h rds2 -u root -p < db1.sql

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

Engine version: `8.0.mysql_aurora.3.08.0`

I have already tried the following solutions:

Solution 1: Use `--set-gtid-purged=OFF` and/or commented out these three lines from the .sql file

```
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 1;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
```

https://stackoverflow.com/questions/44015692/access-denied-you-need-at-least-one-of-the-super-privileges-for-this-define

Solution 2: Update the parameters 
https://stackoverflow.com/questions/11601692/mysql-amazon-rds-error-you-do-not-have-super-privileges but this didn't work and it is an older solution. Not sure if this still applies to version 8.

learner00 · Accepted Answer

I needed to copy an RDS DB from one AWS account to another.  I couldn't restore from snapshot initially because the database was encrypted through the default KMS keys.  So I tried taking a dump of the database and importing it into the new RDS and this is where I was having trouble.  The workaround that I found was to take a snapshot and copy the encrypted snapshot using a custom KMS keys that I can share with the new account and then restore the db using the custom KMS in the new account. This workaround ended up working for me.

YUTAKA_H · Answer

Thank you so much for asking the question.

To set a global system variable runtime value, use the `SET GLOBAL` statement, which requires the `SYSTEM_VARIABLES_ADMIN` privilege (or the deprecated `SUPER` privilege) [1]. However, Aurora MySQL 3 doesn't support `SYSTEM_VARIABLES_ADMIN` privilege for `rds_superuser_role` which is assigned to default super user [2].

Please simply delete line 14 and rerun the dump script.

I hope this might help.

[1] : [https://dev.mysql.com/doc/refman/8.0/en/system-variable-privileges.html](https://dev.mysql.com/doc/refman/8.0/en/system-variable-privileges.html)

[2] : [https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.MasterAccounts.html](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.MasterAccounts.html)

Gary Mclean · Answer

Check what action on line 14 is being performed. As per AI some actions are limited only to AWS

re:Post Agent · Answer

The error you're encountering is a common issue when importing data into Amazon RDS MySQL instances, especially after upgrading to MySQL 8. This error occurs because RDS restricts certain privileges for security reasons, including the SUPER and SYSTEM_VARIABLES_ADMIN privileges.

To resolve this issue, you can try the following approaches:

1. Modify your dump file:
   Open your db1.sql file and remove or comment out any statements that require SUPER or SYSTEM_VARIABLES_ADMIN privileges. This includes statements like SET @@GLOBAL.GTID_PURGED and other global variable settings.

2. Use the --set-gtid-purged=OFF option:
   You've already used this option during the export, which is good. Make sure to keep using it for future exports.

3. Grant necessary privileges:
   If possible, grant the SESSION_VARIABLES_ADMIN privilege to the user you're using for import. You can do this by running:
   GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'username'@'%';
   However, note that you might encounter another error if you don't have the GRANT OPTION privilege.

4. Use the RDS-specific stored procedure:
   Amazon RDS provides a stored procedure to set the GTID_PURGED value. You can try using this after importing your data:
   CALL mysql.rds_set_configuration('gtid_purged', '');

5. Check and adjust the DB cluster parameter group:
   Ensure that the gtid_mode parameter is set to OFF in the DB cluster parameter group associated with your Aurora cluster.

6. Create a new user with required privileges:
   If possible, create a new database user with the necessary privileges for the import operation.

If these solutions don't work, you might need to contact AWS Support for further assistance, as they may be able to provide more specific guidance based on your RDS configuration.

Remember to always backup your data before performing any import operations, and test the process in a non-production environment first if possible.  
**Sources**  
[Can't use mysqlimport since MySQL 8 upgrade due to insufficient privileges | AWS re:Post](https://repost.aws/questions/QUw2Kf2dCJQOiD4mqh99FGdg/can-t-use-mysqlimport-since-mysql-8-upgrade-due-to-insufficient-privileges)  
[cannot purge GTID for Aurora RDS mysql | AWS re:Post](https://repost.aws/questions/QUO2GYKH10TQmZJSGKSFqgOA/cannot-purge-gtid-for-aurora-rds-mysql)  
[Granting privileges for Importing data to AWS Lightsail MySQL | AWS re:Post](https://repost.aws/questions/QUFcGy1uw7SXKeFn5fsMYBAQ/granting-privileges-for-importing-data-to-aws-lightsail-mysql)  
[RDS MySQL Database Migration and Privilege Error | AWS re:Post](https://repost.aws/questions/QUQ2Ug1useSlKGI6JyJgc8cw/rds-mysql-database-migration-and-privilege-error)

ERROR 1227 (42000) Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege

Add your answer

Relevant content