Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A dump restore into RDS fails on a DEFINER clause and asks for SUPER

ErrorAccess denied; you need (at least one of) the SUPER privilege(s) for this operation

Short answer

The dump names another account as DEFINER on a view, trigger, routine or event. MySQL only lets you set a definer other than yourself with a special privilege, and the RDS master user does not hold SUPER. Remove the DEFINER clauses from the dump, or leave routines, triggers and events out and create them again as the master user.

  • Applies to Amazon RDS for MySQL 8.0.36 and later, MySQL 8.4 manual
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find the line the import stopped on and see which account the dump names. The count shows how many different definers you have to deal with.

    grep -o 'DEFINER=`[^`]*`@`[^`]*`' dump.sql | sort | uniq -c
  2. 2Match what you see, then jump to its fix

    1. 1The error comes at a CREATE VIEW, CREATE TRIGGER or CREATE PROCEDURE line that contains DEFINER=...The dump sets a DEFINER that is not the account running the importFix
    2. 2Tables import fine; the failure starts at the first stored routine, trigger or eventRoutines, triggers and events are part of a direct database-to-database pipeFix

What does the error look like?

  • Access denied; you need (at least one of) the SUPER privilege(s) for this operation
  • Access denied; you need (at least one of) the SUPER privilege(s) for this DEFINER operation

Cause 1 of 2

The dump sets a DEFINER that is not the account running the import

1Check

The MySQL manual says that without the SET_ANY_DEFINER privilege the only permitted definer is your own account, written literally or as CURRENT_USER. The RDS master user's privilege list has no SUPER and no SET_ANY_DEFINER, so any other definer in the dump is refused. Without a DEFINER clause the default definer is the user who runs the statement.

SHOW GRANTS FOR CURRENT_USER();

2Fix

Strip the clause while streaming the dump into RDS, so every object is owned by the master user. Then check the result in a copy first, because objects whose body relies on the old owner's privileges will run with the master user's.

sed -E 's/[[:space:]]DEFINER=`[^`]+`@`[^`]+`//g' dump.sql > dump-nodefiner.sql
mysql -h mydb.xxxx.us-east-1.rds.amazonaws.com -u admin -p < dump-nodefiner.sql

3Verify

The import now runs to the end, and the definer of each object is the master user.

SELECT table_name, definer FROM information_schema.views WHERE table_schema = 'app';

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

Routines, triggers and events are part of a direct database-to-database pipe

1Check

Amazon's import guide for RDS for MySQL tells you to leave these objects out of the dump and create them again by hand in the RDS database. Your mysqldump command line shows whether you included them (--routines, --triggers, --events).

mysqldump --help | grep -E 'routines|triggers|events'

2Fix

Dump without them, then run their CREATE statements as the master user.

mysqldump -u local_user --databases app --single-transaction \
  --routines=0 --triggers=0 --events=0 -p | \
  mysql -h mydb.xxxx.us-east-1.rds.amazonaws.com -u admin -p

3Verify

List the routines after you created them again.

SHOW PROCEDURE STATUS WHERE Db = 'app';

Confirmed by docs.aws.amazon.com

Did this fix it?

How do I stop it coming back?

  • Create views and routines in development with the same account that will own them in RDS.
  • Keep the dump step and the definer clean-up in one script, so nobody imports a raw dump.
  • Import into a throwaway RDS instance first when the dump comes from a hosting panel.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

Creating a trigger or function on RDS fails because binary logging is on

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

RDS never gives you the SUPER privilege, and MySQL asks for it when binary logging is on and you create a trigger or stored function.

2 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.