将触发器授予架构不起作用

Question

我需要为用户授予在mysql中导入mysql工作台备份的整个模式的TRIGGER权限。

我尝试过：

grant trigger ON `schemaname`.* TO `user`@`localhost`

但在导入时，会出现用户没有权限的错误。

ERROR 1142 (42000) at line 53: TRIGGER command denied to user 'user'@'localhost' for table 'table'

我试图给用户TRIGGER访问该表的权限——这是有效的，但当然只针对该表，对于其他表仍然会出现错误。

有没有任何方法可以在不分别授予用户对每个表的权限的情况下，授予用户对架构的触发器权限？

Answer 1

来自MySQL文档

In MySQL 5.0 CREATE TRIGGER requires the SUPER privilege.

因此，您需要将SUPER权限授予用户。在导入时，会出现类似"创建触发器…"的命令，这会引发一个错误。

在导入文件中检查MySQL版本和定义器值是否有触发器。

编辑：对于5.1版本，请遵循MySQL文档，上面写着：

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。尝试删除定义器，然后尝试导入。希望它能起作用。。。

Answer 2

在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.

设置全局变量并重新打开会话我可以插入触发