Stored procedure creation fails

Overview

During a database import, a stored procedure may be included in the backup. Although the backup is complete, restoring it within phpMyAdmin, the control panel, or mysql CLI fails with a similar error:

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

Cause

Included in the procedure definition is a DEFINER clause. DEFINER clauses require SUPER privileges, which also permit the user access to set critical database configuration parameters, terminate users, and change replication settings. Naturally, these cannot be accessed by users for security reasons.

Solution

Remove DEFINER subclause from CREATEPROCEDURE, for example:

CREATE DEFINER=`myuser`@`localhost` PROCEDURE `some_proc`(user INT, fingerprint VARCHAR(64))

becomes:

CREATE PROCEDURE `some_proc`(user INT, fingerprint VARCHAR(64))

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.