MySQL is a popular open source database management system. It includes a large feature set, developed in close collaboration with users who have been using the solution since 1995. MySQL powers many popular web applications such as Facebook, Netflix, Uber, Airbnb, Shopify and Booking.com. WordPress and many other website management platforms also use MySQL.

More advanced users face different challenges when using MySQL. Here you will learn the solution to a problem that can occur when importing a database that also contains SQL Routines– stored procedures, functions or triggers.

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

Table of contents

• What is a MySQL routine?
• Problem with importing MySQL routines
  • DEFINER – owner of the stored routine
  • Why can I have a problem when importing MySQL routines?
• Solution for successful database import

What is a MySQL routine?

A MySQL routine is a stored set of SQL statements that a user can execute by calling the routine name. Routines are stored in the database and are used to perform specific tasks and operations, simplifying data manipulation work.

There are three types of MySQL routines:

• Stored Procedures: these are groups of SQL statements that are executed by calling the procedure name. They usually accept parameters and return a result.
• Stored Functions: These are a set of routines that accept input parameters, execute some logic, and then return a value.
• Triggers: These are a type of routine that is automatically triggered when certain events occur, for example when inserting, updating or deleting data in a table.

Problem with importing MySQL routines

When a user does not have “root” access to the server and wants to import a MySQL database that includes stored routines, a problem may occur that causes the import of the database to fail.

During the import process, the following warning occurs:

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

Before we describe the solution that will allow importing a database with stored routines, we need to clarify the role of the MySQL routine manager or the DEFINER value.

DEFINER – the owner of the stored routine

DEFINER represents the user or account that owns the stored routine. When creating a routine, you can therefore specify which user will be assigned as DEFINER. When the routine executes SQL statements, it is executed with the rights of the defined user, which can affect data access and the execution of certain operations.

The use of the DEFINER value is important in two respects:

• Privileges: If a stored routine uses the privileges of a defined user, it will execute with the privileges of that user. This may affect access to tables, views, functions or other data sources in the database.
• Resource protection: The use of the DEFINER value allows better control over who can execute a specific stored routine and with which privileges. By defining a user, you can therefore protect individual data sources.

An example of using DEFINER when creating a stored procedure in MySQL:

DELIMITER //
CREATE PROCEDURE NameProcedure(IN param1 INT)
    SQL SECURITY DEFINER
BEGIN
    SELECT * FROM table WHERE column = param1;
END //
DELIMITER ;

In the above example we have used SQL SECURITY DEFINER, which means that the stored procedure will execute SQL statements with the rights of the defined user. This has helped to increase the security and control over the execution of the stored routines.

Why can I have a problem when importing MySQL?

The DEFINER value specifies the MySQL user that will be used when checking access privileges at runtime – for routines with the SQL SECURITY DEFINER feature.

The name of this user can be checked using the BASH command:

grep DEFINER export.sql

When we start a new session in the cPanel, the name of the temporary MySQL user we use to log in to phpMyAdmin is automatically changed. If the username value is different from the cPanel username, this will cause a problem when importing the SQL statement.

Example SQL statement:

/*!50003 CREATE*/ /*!50017 DEFINER=`cpses_dbhmxbj8s2`@`localhost`*/ /*!50003 TRIGGER `...` AFTER INSERT ON `...` FOR EACH ROW BEGIN

All cPanel usernames are structured in the format cpses_db...@localhost, where dots are replaced by random characters ( hmxbj8s2 in the example above), so you need to log in to the cPanel control panel with the username and password for that cPanel account.

The same problem can occur when the MySQL database is transferred between accounts or platforms.

Solution for successful database import

Here are two ways to fix a problem with importing a database that includes stored routines.

Option 1: Value DEFINER= ... value is removed from the MySQL output by using the following BASH command:

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i export.sql

Option 2: Change or correct the value DEFINER= ... in the MySQL output to the user name (for the example shown above: cpses_dbhmxbj8s2) using the BASH command below:

sed 's/cpses_dbhmxbj8s2/user_name_cPanel/g' -i export.sql

Now we can import the SQL statement again – the database will be imported successfully.