将触发器授予架构不起作用
将触发器授予架构不起作用
提问于 2012-08-02 20:47:15
我需要给一个用户在mysql中的整个模式的触发器权限，以导入mysql工作台备份。
我尝试过：
grant trigger ON `schemaname`.* TO `user`@`localhost`但在导入时，会出现用户没有权限的错误。
ERROR 1142 (42000) at line 53: TRIGGER command denied to user 'user'@'localhost' for table 'table'我尝试给用户触发器权限访问该表--这是可行的，但当然只对该表有效，因为其他表仍然出现错误。
有没有办法在不单独给用户每个表的权限的情况下，给他一个模式的触发器权限？
回答 2
Stack Overflow用户
回答已采纳
发布于 2012-08-02 20:54:41
来自MySQL Docs
In MySQL 5.0 CREATE TRIGGER requires the SUPER privilege.因此，您需要为用户提供超级权限。导入时，会出现类似"Create Trigger...“的命令这会抛出一个错误。
在导入文件中检查触发器的MySQL版本和定义器的值。
编辑: 5.1版的，遵循MySQL docs，上面写着：
CREATE TRIGGER requires the TRIGGER privilege for the table associated with the
trigger. The statement might also require the SUPER privilege, depending on
the DEFINER value, as described later in this section. If binary logging is
enabled, CREATE TRIGGER might require the SUPER privilege, as described in
Section 19.7, “Binary Logging of Stored Programs”. (Before MySQL 5.1.6, there is
no TRIGGER privilege and this statement requires the SUPER privilege in all cases)
The DEFINER clause determines the security context to be used when checking access
privileges at trigger activation time.因此，您需要检查导入触发器的Definer值。它可能包含类似于：DEFINER = root的内容。尝试删除定义器，然后尝试导入。希望它能起作用。
Stack Overflow用户
发布于 2016-11-04 19:44:15
在MySQL文档中：
To relax the preceding conditions on function creation (that you must have the
SUPER privilege and that a function must be declared deterministic or to not
modify data), set the global log_bin_trust_function_creators system variable
to 1. By default, this variable has a value of 0, but you can change it like
this:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
You can also set this variable by using the
--log-bin-trust-function-creators=1
option when starting the server.设置全局变量并重新打开我能够插入触发器的会话
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11777747
复制相关文章
相似问题
腾讯云开发者
