此操作的SUPER特权 [英] SUPER privilege(s) for this operation

问题描述

我使用cpanel(databases -> mySQL@ Databases -> add new user)在共享服务器中创建数据库和用户navid，然后为用户navid选择ALL PRIVILEGES. 遇到此错误时，我正在导入mydatabase.sql.

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.

我该如何解决错误?存储过程在localhost中工作正常.

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

什么是超级特权?

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 

推荐答案

来自文档(我的重点):

SUPER特权使帐户可以使用CHANGE MASTER TO，KILL 或mysqladmin kill杀死属于其他帐户的线程(您 可以随时杀死自己的线程)，清除二进制日志，配置 使用SET GLOBAL修改全局系统变量， mysqladmin debug命令，启用或禁用日志记录，执行 即使启用了read_only系统变量，也会更新，启动和 停止从属服务器上的复制，任何帐户的规范 在存储的程序和视图的DEFINER属性中，并启用 您连接一次(一次)，即使连接限制由 已达到max_connections系统变量.

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.

由于您已经是数据库的navid，因此无需在存储过程中设置DEFINER属性；添加此行会导致错误显示.如果删除此语句，将创建您的过程，并且不会出现权限错误.

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.

如果要为其他用户设置存储过程，则只需要设置DEFINER，默认情况下，存储过程将获得相同的安全上下文

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:

所有存储的程序(过程，函数和触发器)和视图 可以具有一个名为MySQL帐户的DEFINER属性. 如果 存储的程序或视图定义中省略了DEFINER属性， 默认帐户是创建对象的用户.

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.

这篇关于此操作的SUPER特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！