| Bug #39489 | Cannot create or drop triggers without SUPER privilege | ||
|---|---|---|---|
| Submitted: | 16 Sep 2008 19:45 | Modified: | 7 Jan 2011 19:45 |
| Reporter: | Dean Ellis | Email Updates: | |
| Status: | In progress | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S1 (Critical) |
| Version: | 5.1 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[16 Sep 2008 19:45]
Dean Ellis
[16 Sep 2008 22:33]
MySQL Verification Team
Thank you for the bug report.
[23 Oct 2008 19:33]
Omer Barnir
When binary logging is enabled the server must be running with the --log-bin-trust-function-creators option turned on in order to allow trigger creation without super (i.e. trigger permission).
[23 Oct 2008 19:42]
Dean Ellis
Just to be clear, log-bin-trust-function-creators is intended to circumvent two restrictions: that one have SUPER privilege in order to create routines and that one explicitly state that the routine is DETERMINISTIC. 1) "CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. (Before MySQL 5.1.6, this statement requires the SUPER privilege.)" 2) "CREATE TRIGGER does not have an optional DETERMINISTIC characteristic, so triggers are assumed to be always deterministic." In this context, then, log-bin-trust-function-creators has no meaning for triggers.
[23 Oct 2008 19:49]
Omer Barnir
To clarify, I did not say say 'log-bin-trust-function-creators' should be needed for triggers, the design might be wrong, what I said is that current design requires it and if specified allows for the trigger creation without the SUPER privilege. As such, IMHO, this should not be set as 'critical'
[22 Mar 2009 14:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/69982 2823 Alfranio Correia 2009-03-22 BUG#39489 Cannot create or drop triggers without SUPER privilege If the binlog was active, a user should define a function as deterministic to create it. On the other hand, a trigger don't have this option and a user should have the SUPER privilige to do so. To easy the creation, the replica could be configured with the option log-bin-trust-function-creators thus eliminating the need for a special option and privilege. These requirements were used to avoid database inconsistencies between the master and slave. With row and mixed-based format these requirements are not necessary as in the former case, DML operations are logged as rows and in latter, any unsafe operation is switched to row format. To fix this bug, the deterministic option, SUPER privilige or log-bin-trust-function-creators are not checked when the row or mixed-based is set.
[27 Mar 2009 15:37]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/70727 2823 Alfranio Correia 2009-03-27 BUG#39489 Cannot create or drop triggers without SUPER privilege If the binlog was active, a user should define a function as deterministic to create it. On the other hand, a trigger don't have this option and a user should have the SUPER privilige to do so. To easy the creation, the replica could be configured with the option log-bin-trust-function-creators thus eliminating the need for a special option and privilege. These requirements were used to avoid database inconsistencies between the master and slave. With row and mixed-based format these requirements are not necessary as in the former case, DML operations are logged as rows and in latter, any unsafe operation is switched to row format. To fix this bug, the deterministic option, SUPER privilige or log-bin-trust-function-creators are not checked when the row or mixed-based is set.
[31 Mar 2009 13:32]
Lars Thalmann
SUMMARY ======= We don't want to make the server less safe, thus propose to only fix this bug after proper execution-time checks have been added. BACKGROUND ========== Even if the manual states that "triggers are assumed to be deterministic", they can in fact be non-deterministic and contain e.g. calls to UUID. As this is a fact, it still makes sense to restrict users to not be allowed to create TRIGGERS that cause the binlog to be inconsistent with the database. PRINCIPLE 1: Keeping the log consistent per default =================================================== The server is currently safe for functions/triggers (i.e. statements involving functions are correctly logged) per default (i.e. when --trust-function-creators is disabled) for non-super users. We should not make the server unsafe per default for non-super users. PRINCIPLE 2: Don't trust that format don't change ================================================= Checking functions at creation time with respect to binlog format is not safe, since the binlog format can be changed dynamically later and the functions are not being auto-removed. SUGGESTIONS =========== 1. The patch proposed in this bug report violate principle 2, so it should *not* be pushed. 2. The documentation should be updated to clearly say that --trust-function-creators can be safely enabled for the server provided that one is always using the server in MIXED or ROW mode. 3. Tests should be added to ensure that the text in decision 2 is correct, i.e. that in ROW and MIXED format every execution (of functions and triggers) is checked for whether a (potentially unsafe) function/trigger is being executed. 4. There is no need to add options to made STATEMENT safe, we just keep it for backwards compatibility and that will just be confusing. 5. If people are interested in making STATEMENT mode safe (and thus error on the execution of any unsafe statement e.g. when it contains non-determinsitic functions, triggers), then a good way forward is to create a new format, e.g. named "STATEMENT-STRICT". In this format, we would need to check every execution. 6. In the long run, after we have introduced proper execution-time checks (and a STATEMENT-STRICT mode), we can deprecate --trust-function-creators CONCLUSIONS =========== Changing state to 'verified' and removing old patch approvals.
[11 Jul 2009 1:36]
John Smiley
You can't have it both ways. #2 says that it's safe to set trust_function_creators (now log_bin_trust_function_creators) = 1 if binlog_format is MIXED or ROW and #3 says that more testing needs to be done to prove #2. In any case, if recoverability is a requirement, there are is no such thing as a safe procedure/function/trigger. Anyone can declare their code to be deterministic and MySQL will accept it as so. Until this is enforced at runtime, this is a big chink in the armor.
[23 Mar 2010 4:37]
Roel Van de Paar
See bug #52127
[21 Jan 2011 6:51]
Valeriy Kravchuk
Bug #59531 was marked as a duplicate of this one.
