Fixing the MySQL DEFINER Error on Import (Error 1227 / Access Denied)
Views, triggers, and stored procedures fail to import with 'access denied; you need SUPER privilege'. Here's why, and three ways to fix it.
Your import dies on a CREATE VIEW or CREATE TRIGGER with: 'Access denied; you need (at least one of) the SUPER privilege(s)'. This is the DEFINER problem, and it's extremely common when moving a database between servers.
Why it happens
mysqldump records the user that created each view, trigger, or stored routine as a DEFINER clause:
CREATE DEFINER=`olduser`@`localhost` VIEW `active_orders` AS ...On the new server that user often doesn't exist, and only an account with SUPER privilege is allowed to create objects with a DEFINER that isn't itself. On shared/managed hosting you rarely have SUPER, so the import fails.
Fix 1: Strip the DEFINER clauses from the dump
The most portable fix. Remove every DEFINER=... clause so the objects are created as the importing user:
sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' dump.sql > dump_nodef.sql
mysql -u user -p your_database < dump_nodef.sqlFix 2: Export without DEFINER in the first place
If you control the export, there's no clean mysqldump flag, but you can pipe the output through sed at dump time:
mysqldump database_name | \
sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' > dump.sqlFix 3: Create the missing user (only if you have control)
On your own server, recreate the original definer account so the DEFINER clauses resolve:
CREATE USER 'olduser'@'localhost' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON your_database.* TO 'olduser'@'localhost';Even after the object is created, a view defined with SQL SECURITY DEFINER runs as the (possibly missing) definer at query time. If queries later fail, rewrite the view with SQL SECURITY INVOKER so it runs as whoever queries it.
Clean up a big dump before importing
Split the file into chunks so you can isolate and re-run just the views/triggers section after stripping DEFINER.
Open SQLSplitFrequently Asked Questions
How do I fix 'access denied; you need SUPER privilege' on import?
The dump contains DEFINER clauses pointing to a user that doesn't exist on the new server. Strip them with sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' before importing, or recreate the original user if you control the server.
What does the DEFINER clause do in a MySQL dump?
It records which account created a view, trigger, or stored routine, and which account it runs as. When that account is missing on the target server, MySQL refuses to create the object unless you have SUPER privilege.
Is it safe to remove DEFINER from a SQL dump?
Yes. Removing the DEFINER clause makes the importing user the definer, which is usually what you want when migrating. For views, also consider SQL SECURITY INVOKER so they run as the querying user.