Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why SUPER privileges are disabled when binary logging option is on?

Tags:

mysql

mariadb

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): You do not have the SUPER Privilege and Binary Logging is Enabled"

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.

Does anybody have an answer on this?

Thank you.

like image 731
Volodymyr Litovka Avatar asked May 31 '19 06:05

Volodymyr Litovka


1 Answers

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

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.

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)

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.

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

like image 89
Vikash Pathak Avatar answered Sep 30 '22 19:09

Vikash Pathak