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:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.1 OS:Any
Assigned to: Alfranio Correia CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R4 (High) / E3 (Medium)

[16 Sep 2008 19:45] Dean Ellis
Description:
Cannot successfully create or drop triggers without SUPER privilege if binary logging is enabled.

How to repeat:
CREATE TABLE t1 ( a int );
CREATE TRIGGER g1 BEFORE INSERT ON t1 FOR EACH ROW SET new.a=new.a+1;
DROP TRIGGER g1;

The CREATE and DROP report:

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)

Suggested fix:
n/a
[16 Sep 2008 22:33] Miguel Solorzano
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.