MySQL 在mysql中创建触发器的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1404107/
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
Permissions for creating a trigger in mysql
提问by Mohammad Mirzadeh
mysql ver is 5.0.67
mysql 版本是 5.0.67
my trigger code is :
我的触发代码是：
DELIMITER //
CREATE TRIGGER upd_price BEFORE UPDATE ON product_price
FOR EACH ROW BEGIN
INSERT INTO update_price_log (product_id, product_price_old, product_price_new) values(OLD.product_id, OLD.product_price, NEW.product_price);
END
//
DELIMITER ;
error is:
错误是：
#1227 - Access denied; you need the SUPER privilege for this operation
#1227 - 访问被拒绝；您需要此操作的 SUPER 权限
my server, is not cpanel! it is : DirectAdmin Web Control Panel.
我的服务器，不是cpanel！它是：DirectAdmin Web 控制面板。
how can i create SUPER privilege and create my trigger?
我如何创建超级特权并创建我的触发器？
回答by Phill Pafford
You must have MySQL Root privileges to set the SUPER permission for a user.
您必须具有 MySQL Root 权限才能为用户设置 SUPER 权限。
GRANT SUPER
ON '<database>'.'<tablename/*>'
TO '<username>'@'<host/connection/ip/%>';
回答by Jeff_Alieffson
I tried to give SUPER privilede on database.table level, but it fails, so I did
我试图在 database.table 级别上给予 SUPER 特权，但它失败了，所以我做到了
grant super on *.* to 'my_user'@'localhost';
then created my triggers. But don't forget to revoke it
然后创建了我的触发器。但是不要忘记撤销它
revoke super on *.* from 'my_user'@'localhost';
Because it's a dangerous privilege to leave it granted in internet database
因为将其保留在 Internet 数据库中是一种危险的特权
回答by alkree
Another way to create trigger without SUPER privilege by adding the following line to my.cnf or my.ini log_bin_trust_function_creators = 1
通过将以下行添加到 my.cnf 或 my.ini 来创建没有 SUPER 权限的触发器的另一种方法 log_bin_trust_function_creators = 1
回答by artfulrobot
Another way around this bug, if acceptable, is to disable binary logging.
I have done this because I am not using a replication server, nor am I doing backups from the binlogs for recovery. But it might not be appropriate in your situation.
我这样做是因为我没有使用复制服务器，也没有从二进制日志进行备份以进行恢复。但它可能不适合您的情况。
To do it, just comment out log_binand log_bin_indexlines in your /etc/mysql/my.cnf
要做到这一点，只是注释掉log_bin和log_bin_index线在你的/etc/mysql/my.cnf
Now trigger privilege works fine without SUPER.
现在触发器特权在没有 SUPER 的情况下也能正常工作。
