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

Sometimes you restore a SQL dump in a MySQL database server and bump into the following error:

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

This happens when the SQL dump file contains DEFINER clause (to create specific database objects) with user account (a combination of user name and host name) other than yours and your account does not have the SUPER privilege granted.

Let us assume that:

• SQL dump file is: mysql_dump_file.sql
• Account that is specified with DEFINER clause in SQL dump file is: `pdxnnxxp1`@`%`
• Your MySQL User Account is: `your_user_name`@`your_host_name`

To resolve the issue you can:

1. Remove the DEFINER clause
• sed -i 's/DEFINER=`pdxnnxxp1`@`%`//g' mysql_dump_file.sql
2. Remove the DEFINER account with your account
• sed -i 's/`pdxnnxxp1`@`%`/`your_user_name`@`your_host_name`/g' mysql_dump_file.sql
• Replace your_user_name and your_host_name with actual values.

I have used the first method to resolve my issue.

NOTE: I have used this process successfully on a 2.3G SQL dump file. It will depend on the resources that you have configured for your machine.

NOTE: It is a good idea to make a copy of your SQL dump file before modifying it.

Repeat the process for each user account that you find in the DEFINER clause across the SQL dump file.

You can search the DEFINER clause in your SQL dump file as follows:

shell> grep DEFINER mysql_dump_file.sql

Sample Output Lines

CREATE DEFINER=`pdxnnxxp1`@`%` PROCEDURE `renewalactivationvalidator`()
CREATE DEFINER=`pdxnnxxp1`@`%` PROCEDURE `renewalinsertactivationcustomattributevalue`()
CREATE DEFINER=`pdxnnxxp1`@`%` PROCEDURE `renewalinsertactivationlicenseattribute`()

You can re-direct the output of the above command to a file for easy analysis.

shell> grep DEFINER mysql_dump_file.sql > mysql_dump_file_definer.log

Here is the story

All stored programs (procedures, functions, triggers, and events) and views can have a DEFINER attribute that names a MySQL account. If the DEFINER attribute is omitted from a stored program or view definition, the default account is the user who creates the object.

In addition, stored routines (procedures and functions) and views can have an SQL SECURITY characteristic with a value of DEFINER or INVOKER to specify whether the object executes in definer or invoker context. If the SQL SECURITY characteristic is omitted, the default is definer context.

Triggers and events have no SQL SECURITY characteristic and always execute in definer context. The server invokes these objects automatically as necessary, so there is no invoking user. 

MySQL uses the following rules to control which accounts a user can specify in an object DEFINER attribute:

• You can specify a DEFINER value other than your own account only if you have the SUPER privilege.
• If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account. 

More details at:  Access Control for Stored Programs and Views