启用二进制日志记录选项后，为什么禁用超级权限? [英] Why SUPER privileges are disabled when binary logging option is on?

问题描述

Internet上有很多关于如何启用SUPER特权的建议，以防万一有人遇到以下错误:

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

错误1419(HY000):您没有SUPER特权，并且启用了二进制日志记录"

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

如果我使用例如复制，是否存在一些复制问题?触发哪个修改数据库或其他?是否安全?如果没有，如果我将返回SUPER特权返回，在什么情况下会遇到什么问题?我认为此限制背后应有一些基本原理，但不了解是哪个.

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线程具有完全特权，它将执行危险语句.因此，函数调用对主节点和主节点具有不同的影响.从属并且不是复制安全的.

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权限，将发生错误:

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屋！