为什么当二进制日志选项打开时超级权限被禁用? [英] Why SUPER privileges are disabled when binary logging option is on?

问题描述

互联网上有很多关于如何启用超级权限的建议，以防有人遇到以下错误:

there are lot of recommendations over the Internet on how to enable SUPER privileges in case if someone hit the following error:

"ERROR 1419 (HY000): 您没有超级权限并且启用了二进制日志记录"

"ERROR 1419 (HY000): You do not have the SUPER Privilege and Binary Logging is Enabled"

但我无法找到为什么 MySQL 在二进制日志记录选项打开时会禁用这些权限.

But I wasn't be able to find WHY MySQL disables these privileges when binary logging option is on.

如果我使用例如复制是否存在一些问题?修改数据库或其他东西的触发器?是否安全，如果没有，如果我将返回超级特权，我可以解决什么样的问题以及在什么情况下?我认为这个限制背后应该有一些理由，但不明白是哪一个.

Are there some issues with replication if I use e.g. triggers which modify DB or something else? Whether it's safe and, if no, what kind of issues and under which circumstances I can hit if I will return SUPER privileges back? I think there should be some rationale behind this restriction but don't understand which one.

有人对此有答案吗?

谢谢.

推荐答案

这是我在文档中找到的一些详细解释.希望这可以帮助您理解.

Here is some detailed explaination I had found in documentation. Hopefully this could help you to understand.

CREATE FUNCTION 和 INSERT 语句被写入二进制日志，所以从机将执行它们.因为从 SQL线程具有完全权限，它将执行危险语句.因此，函数调用对 master 和从属并且不是复制安全的.

The CREATE FUNCTION and INSERT statements are written to the binary log, so the slave will execute them. Because the slave SQL thread has full privileges, it will execute the dangerous statement. Thus, the function invocation has different effects on the master and slave and is not replication-safe.

为具有二进制日志记录的服务器防范这种危险启用，存储函数创建者必须具有 SUPER 权限，在除了通常所需的 CREATE ROUTINE 权限.同样，要使用 ALTER FUNCTION，您必须拥有 SUPER除了 ALTER ROUTINE 特权之外的特权.没有SUPER privilege，会报错:

To guard against this danger for servers that have binary logging enabled, stored function creators must have the SUPER privilege, in addition to the usual CREATE ROUTINE privilege that is required. Similarly, to use ALTER FUNCTION, you must have the SUPER privilege in addition to the ALTER ROUTINE privilege. Without the SUPER privilege, an error will occur:

ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)

如果您不想要求函数创建者拥有 SUPER权限(例如，如果所有用户都拥有 CREATE ROUTINE您的系统上的特权是经验丰富的应用程序开发人员)，设置全局 log_bin_trust_function_creators 系统变量为 1.你也可以使用启动服务器时的 --log-bin-trust-function-creators=1 选项.如果未启用二进制日志记录，log_bin_trust_function_creators不适用.SUPER 不是函数创建所必需的，除非，如前所述，函数中的 DEFINER 值定义需要它.

If you do not want to require function creators to have the SUPER privilege (for example, if all users with the CREATE ROUTINE privilege on your system are experienced application developers), set the global log_bin_trust_function_creators system variable to 1. You can also set this variable by using the --log-bin-trust-function-creators=1 option when starting the server. If binary logging is not enabled, log_bin_trust_function_creators does not apply. SUPER is not required for function creation unless, as described previously, the DEFINER value in the function definition requires it.

来源:https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html

这篇关于为什么当二进制日志选项打开时超级权限被禁用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！