Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

SQL Error (1227): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

dtavres's profile image dtavres posted 3 months ago in General Permalink

I've been searching and searching, and cannot find a solution for this.

I have a fresh, clean install of HeidiSQL 10.3.0.5771, connecting to a MySQL db, but when I try to simply import (load) a .sql file, I get this error:

"SQL Error (1227): Access denied; you need (at least one of) the SUPER privilege(s) for this operation"

When I try to add all the permissions for a user, I get this error:

"SQL Error (1045): Access denied for user 'xxxxx'@'%' (using password: YES)"

What in the world am I doing wrong?

2 attachment(s):
  • 1227
  • SuperUser
dtavres's profile image dtavres posted 3 months ago Permalink

After many HOURS of searching, reading, looking, I caught a friend who's a security guy, and he quickly figured it out.

Turns out that Amazon RDS does NOT give "SUPER USER" privileges to the master user (Search AWS Docs for "Master User Account Privileges") - AND - the "DEFINER" commands are what's causing the errors.

SO, REMOVING the "DEFINER" commands, then running the script, allows the dat to be imported (Search TecTut-com for "Solved – AWS RDS MySQL ERROR 1227 (42000) at line : Access denied")

I hope this helps others.

ansgar's profile image ansgar posted 3 months ago Permalink

Thanks for your feedback, glad you fixed it!

By the way, you can exclude DEFINER clauses when creating the export:

Description

Please login to leave a reply, or register at first.