SUPER privilege(s) for this operation

Question

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 1

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.