- Newest
- Most votes
- Most comments
=>Check AWS RDS logs for any relevant entries around the time when the privilege gets revoked.
=>Review recent changes in your AWS RDS configuration, including parameter groups, security groups, and IAM policies.
=>Monitor the behaviour closely to identify any patterns or triggers that might be causing the privilege deletion every 10 minutes.
=>Contact AWS Support for assistance if you're unable to identify the cause on your own. They can provide additional insights and help you resolve the issue.
The same happens for us, too. We have an external mysqlimport process which worked for months and since yesterday we receive the message:
mysqlimport: Error: 1227 Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
We can fix it by setting SESSION_VARIABLES_ADMIN but after a few minutes the GRANT is gone, even with FLUSH PRIVILEGES;
We have experienced issues on one of our databases causing downtime on one of ours apps which requires calls to SET SESSION
when connections are initialised in a Django app.
We have tried granting the privileges to the user again but after a few minutes it is removed from the user like OP.
We have two MySQL 8 clusters and copies of the Django app are deployed to both but this issue only affects one of the database instances and not the other which makes me think this may be a bug in RDS. The affected instance does not have a failover instance but the unaffected instance does.
The same happened with me. I've 2 RDS MySQL instances. Each user is having SESSION_VARIABLES_ADMIN permission till 4th May, 2024. All of a sudden on one of the instance, I started getting error message - 1227 Access denied; you need (at least one of) the SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
. When I logged in using root user, the MySQL users permission was revoked. I assigned it back, however, after 5-10 minutes it was revoked again. I tried this multiple times. On the other hand, the other instance is working perfectly fine. Both of this have same parameters and configuration.
Our other instance is now experiencing the same problem. It seems like they've rolled out a change. We've had to expedite a fix to production for our Django app in order to work around this.
FYI: We have received feedback from AWS Support
( ... ) This is because RDS recently disabled granting administrative privileges such as SESSION_VARIABLES_ADMIN to improve the security of your database instances. We recommend modifying your application to move away from using the administrative privileges SESSION_VARIABLES_ADMIN. By refactoring your application to operate with RDS standard user permissions, you can enhance its resilience, maintainability, and alignment with best practices for secure application development. ( ...)
Hope this helps :)
I have a similar error in my Django application. I had given the MySQL user SYSTEM_VALIABLES_ADMIN permission, but AWS RDS changed their specs and this permission is reset every 5-10 minutes.
So I checked my Django configuration. I looked for where it was using the SYSTEM_VALIABLES_ADMIN permission and found the following code
DATABASES["default"]["OPTIONS"] = {
"init_command": "SET sql_mode='STRICT_TRANS_TABLES', innodb_strict_mode=1",
"charset": "utf8mb4",
}
SET sql_mode='STRICT_TRANS_TABLES', innodb_strict_mode=1
requires the SYSTEM_VALIABLES_ADMIN permission.
I have resolved the error by commenting out this init_command.
Instead, I set innodb_strict_mode=1 using the AWS RDS parameter group feature.
I hope this helps you all.
This issue seems to have resolved on its own. I am not seeing this issue from past 7-8 days.
Relevant content
- asked 2 years ago
- asked 10 months ago
- AWS OFFICIALUpdated 3 years ago
- AWS OFFICIALUpdated 3 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 3 years ago