navid navid - 17 days ago 10
MySQL Question

SUPER privilege(s) for this operation

I create my database and user

navid
in my shared server with
cpanel
(
databases -> mySQL@ Databases -> add new user
),and then selected ALL PRIVILEGES for user
navid
.
I was importing
mydatabase.sql
when I was confronted with this error.




how do i fix the error? store procedure worked fine in
localhost
.




what is SUPER privilege?

Error
SQL query:
DELIMITER $$--
-- Procedures
--
CREATE DEFINER = `navid`@`%` PROCEDURE `d_answer` ( OUT `sp_out` INT( 11 ) , IN `sp_id` INT( 11 ) ) NO SQL BEGIN DELETE FROM `tblname` WHERE `a_id` = sp_id;
SET sp_out = ROW_COUNT( ) ;
END$$

MySQL said: Documentation
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Answer

From the documentation (my emphasis):

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

Since you are already navid to the database, you do not need to set the DEFINER attribute in your stored procedure; adding this line is causing the error to show up. If you remove this statement, your procedure will be created and you won't get the permissions error.

You only need to set DEFINER if you are setting up the stored procedure for some other user, by default the stored procedure gets the same security context as the user that is creating it:

All stored programs (procedures, functions, and triggers) 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.

Comments