MySQL 此操作的超级权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18479418/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SUPER privilege(s) for this operation
提问by navid
I create my database and user navidin my shared server with cpanel(databases -> mySQL@ Databases -> add new user),and then selected ALL PRIVILEGES for user navid.
I was importing mydatabase.sqlwhen I was confronted with this error.
我navid使用cpanel( databases -> mySQL@ Databases -> add new user)在我的共享服务器中创建了我的数据库和用户，然后为 user 选择了 ALL PRIVILEGES navid。mydatabase.sql当我遇到这个错误时，我正在导入。
how do i fix the error? store procedure worked fine in localhost.
我该如何修复错误？存储过程在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
回答by Burhan Khalid
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.
SUPER 权限使一个帐户可以使用 CHANGE MASTER TO、KILL 或 mysqladmin kill 来杀死属于其他帐户的线程（您始终可以杀死自己的线程）、PURGE BINARY LOGS、使用 SET GLOBAL 修改全局系统变量的配置更改、mysqladmin调试命令，启用或禁用日志记录，即使启用了 read_only 系统变量也执行更新，在从服务器上启动和停止复制，在存储的程序和视图的 DEFINER 属性中指定任何帐户，并使您能够连接（一次）甚至如果达到由 max_connections 系统变量控制的连接限制。
Since you are already navidto the database, you do not need to set the DEFINERattribute 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.
既然你已经navid到了数据库，你就不需要DEFINER在你的存储过程中设置属性；添加此行会导致错误显示。如果您删除此语句，您的过程将被创建并且您不会收到权限错误。
You only need to set DEFINERif 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:
您只需要设置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.
所有存储的程序（过程、函数和触发器）和视图都可以具有命名 MySQL 帐户的 DEFINER 属性。如果从存储的程序或视图定义中省略 DEFINER 属性，则默认帐户是创建对象的用户。
回答by d586
I got the problem too and fixed it thus in MySQL Workbench. In my case it is because the "Send to SQL Editor > Create Statement" has extra stuff in there that prevents from being used without modification.
我也遇到了这个问题，因此在 MySQL Workbench 中修复了它。就我而言，这是因为“发送到 SQL 编辑器 > 创建语句”中有额外的内容，可以防止未经修改就使用。
Something like this:
像这样的东西：
CREATE ALGORITHM=UNDEFINED DEFINER=schemax@localhostSQL
SECURITY DEFINER VIEW viewNameAS SELECT ....
CREATE ALGORITHM=UNDEFINED DEFINER= schemax@ localhostSQL SECURITY DEFINER VIEW viewNameAS SELECT ....
Change it to this:
改成这样：
CREATE VIEW viewNameAS SELECT ....
创建视图为viewName选择 ....
Seems to work now, no need to update permissions. **I am the only user of my database..
现在似乎可以工作了，无需更新权限。**我是我的数据库的唯一用户..
回答by Mr Angry
I had the same issue - on my local dev server it was fine but on my hosted server (through PHPMyAdmin) it gave me the above error.
我遇到了同样的问题 - 在我的本地开发服务器上它很好，但在我的托管服务器上（通过 PHPMyAdmin）它给了我上述错误。
Removing the definer attribute seemed to be the easiest way to fix the problem if you're happy with the definer being the current user.
如果您对定义者是当前用户感到满意，那么删除定义者属性似乎是解决问题的最简单方法。
回答by Bashir Noori
just remove the DEFINERattribute and with it's value, example:
只需删除DEFINER属性及其值，例如：
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW ...
回答by Marco Marsala
Don't rely on default values, if emptying the definer field in phpMyAdmin or other programs won't solve, check the definer, should not be username@localhostunless explicitly needed, but username@%.
不要依赖默认值，如果在 phpMyAdmin 或其他程序中清空定义器字段无法解决，请检查定义器，username@localhost除非明确需要，否则应该不是，而是username@%.
