MySQL Bugs: #52127: Inconsistencies between how TRIGGERS work, and what the manual states

Bug #52127	Inconsistencies between how TRIGGERS work, and what the manual states
Submitted:	17 Mar 2010 8:28	Modified:	15 Apr 2010 23:48
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.42, 5.1, 5.6.99	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Results from 'how to repeat':

Summary Table (OK=Trigger creation worked, E=Error):
		
BINLOG						Off	On	Off 	On
log_bin_trust_function_creators			Off	Off	On	On

trigger with DEFINER + ALL GRANT (no SUPER) 	E1227	E1419	E1227	E1227	(User 1-3)
trigger with DEFINER + SUPER, TRIGGER GRANT	OK	OK	OK	OK	(User 4)
trigger without DEFINER + ALL GRANT (no SUPER) 	OK	E1419	OK	OK	(User 1-3)
trigger without DEFINER + SUPER, TRIGGER GRANT	OK	OK	OK	OK	(User 4)

Note that the 'ALL' GRANT includes TRIGGER. So, the first and third line above can really be read as 'TRIGGER and everything else (except SUPER)', the second and fourth line as 'only TRIGGER + SUPER but nothing else'.

http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html
'If binary logging is not enabled, log_bin_trust_function_creators does not apply and SUPER is not required for function creation.' = not correct, as per the above
'If binary logging is not enabled, log_bin_trust_function_creators does not apply.' = correct

http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html
'The current conditions on the use of stored functions in MySQL 5.1 can be summarized as follows. These conditions do not apply to stored procedures or Event Scheduler events and they do not apply unless binary logging is enabled.
 To create or alter a stored function, you must have the SUPER privilege, in addition to the CREATE ROUTINE or ALTER ROUTINE privilege that is normally required.
 [...] Triggers are similar to stored functions, so the preceding remarks regarding functions also apply to triggers'
Line 1=not correct, as per line 3 (last result) above (binary logging is on, creation succeeds without SUPER)
Line 2=not correct, as per line 1 (first and third result) above (binary logging is off, yet still fails without SUPER)

http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html
'CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. (Before MySQL 5.1.6, this statement requires the SUPER privilege.)' = not correct, as per line 1 above (this was tested on 5.1.42)

How to repeat:
--- As root
DROP DATABASE IF EXISTS trig;
CREATE DATABASE trig;
USE trig;
DROP TRIGGER IF EXISTS trig1;
DROP TRIGGER IF EXISTS trig2;
DROP TABLE IF EXISTS tst1;
DROP TABLE IF EXISTS tst2;
CREATE TABLE tst1 (id int);
CREATE TABLE tst2 (id int);
INSERT INTO tst1 VALUES (1);
DROP USER user1; /* likely error, no if exists */
DROP USER user2; /* likely error, no if exists */
DROP USER user3; /* likely error, no if exists */
DROP USER user4; /* likely error, no if exists */
GRANT TRIGGER ON trig.tst1 TO user1;
GRANT TRIGGER ON trig.tst2 TO user1;
GRANT ALL ON trig.tst1 TO user2;
GRANT ALL ON trig.tst2 TO user2;
GRANT ALL ON trig.* TO user3;
GRANT TRIGGER,SUPER ON *.* TO user4;
SHOW GRANTS FOR user1;
SHOW GRANTS FOR user2;
SHOW GRANTS FOR user3;
SHOW GRANTS FOR user4;

--- As user1, user2, user3, user4
mysql -uuser1 -P5142 etc.

USE trig;
SHOW TRIGGERS; /* Make sure there's no triggers from previous testing, otherwise re-run
root section above */

DELIMITER //
CREATE DEFINER=`root`@`127.0.0.1` TRIGGER trig1 AFTER INSERT ON tst1
FOR EACH ROW
BEGIN
 INSERT INTO tst2 VALUES (NEW.id);
END //
DELIMITER ;
INSERT INTO tst1 VALUES (2);
SELECT * FROM tst2;

DELIMITER //
CREATE TRIGGER trig2 AFTER UPDATE ON tst1
FOR EACH ROW
BEGIN
 INSERT INTO tst2 VALUES (NEW.id);
END //
DELIMITER ;
UPDATE tst1 SET id=3 LIMIT 1;
SELECT * FROM tst2;

Suggested fix:
Find out why there are inconsistencies. Then fix relevant code or update the manual to reflect.

There are likely more inconsistencies than the ones above, but have not done a full scan (yet).

Detailed test results:

--- user1 / binlog turned OFF / log_bin_trust_function_creators OFF
--- user2 / binlog turned OFF / log_bin_trust_function_creators OFF
--- user3 / binlog turned OFF / log_bin_trust_function_creators OFF
trig1: ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation
trig2: Query OK, 0 rows affected (0.01 sec)

--- user4 / binlog turned OFF / log_bin_trust_function_creators OFF
trig1: Query OK, 0 rows affected (0.01 sec)
trig2: Query OK, 0 rows affected (0.01 sec)

--- user1 / binlog turned ON / log_bin_trust_function_creators OFF
--- user2 / binlog turned ON / log_bin_trust_function_creators OFF
--- user3 / binlog turned ON / log_bin_trust_function_creators OFF
trig1: 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)
trig2: 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)

--- user4 / binlog turned ON / log_bin_trust_function_creators OFF
trig1: Query OK, 0 rows affected (0.01 sec)
trig2: Query OK, 0 rows affected (0.01 sec)

--- user1 / binlog turned OFF / log_bin_trust_function_creators ON
--- user2 / binlog turned OFF / log_bin_trust_function_creators ON
--- user3 / binlog turned OFF / log_bin_trust_function_creators ON
trig1: ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation
trig2: Query OK, 0 rows affected (0.01 sec)

--- user4 / binlog turned OFF / log_bin_trust_function_creators ON
trig1: Query OK, 0 rows affected (0.01 sec)
trig2: Query OK, 0 rows affected (0.01 sec)

--- user1 / binlog turned ON / log_bin_trust_function_creators ON
--- user2 / binlog turned ON / log_bin_trust_function_creators ON
--- user3 / binlog turned ON / log_bin_trust_function_creators ON
trig1: ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation
trig2: Query OK, 0 rows affected (0.01 sec)

--- user4 / binlog turned ON / log_bin_trust_function_creators ON
trig1: Query OK, 0 rows affected (0.01 sec)
trig2: Query OK, 0 rows affected (0.01 sec)

Summary as per the table above.

Thank you for the report.

Inconsistencies verified as described: one still need SUPER privilege independently of log_bin_trust_function_creators. It is good to replace "SUPER is not required" to "SUPER is not required unless you need ..." Or add a note about SUPER sometimes required.

If, regardless of log_bin_trust_function_creators, SUPER is still required to create triggers, that would appear to be a bug. The TRIGGER privilege was created to *avoid* having to grant SUPER to users who need to create triggers.

Paul,

SUPER required only for cases described at  http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html:

----<q>----
 If you specify the DEFINER clause, these rules determine the legal DEFINER user values:

    * If you do not have the SUPER privilege, the only legal user value is your own
account, either specified literally or by using CURRENT_USER. You cannot set the definer
to some other account.
    * If you have the SUPER privilege, you can specify any syntactically legal account
name. If the account does not actually exist, a warning is generated. 
----</q>----

o The first two documentation items listed were not addressed.
o For the last documentation item, how about the following instead:

CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. Furthermore, the SUPER privilege is required if a DEFINER clause is specified in the trigger, or if binary logging is enabled and log_bin_trust_function_creators is turned off (even when no DEFINER clause is specified). (Before MySQL 5.1.6, there is no TRIGGER privilege and this statement requires the SUPER privilege in all cases.)

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

For:
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html
'The current conditions on the use of stored functions in MySQL 5.1 can be summarized as
follows. These conditions do not apply to stored procedures or Event Scheduler events
and
they do not apply unless binary logging is enabled.
 To create or alter a stored function, you must have the SUPER privilege, in addition to
the CREATE ROUTINE or ALTER ROUTINE privilege that is normally required.

Changed second part to:
To create or alter a stored function, you must have the SUPER
privilege, in addition to the CREATE ROUTINE or ALTER ROUTINE
privilege that is normally required. (Depending on the DEFINER value
in the function definition, SUPER might be required regardless of
whether binary logging is enabled. See Section 12.1.15, "CREATE
PROCEDURE and CREATE FUNCTION Syntax".) 

For:
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html
'If binary logging is not enabled, log_bin_trust_function_creators does not apply and
SUPER is not required for function creation.' = not correct, as per the above
'If binary logging is not enabled, log_bin_trust_function_creators does not apply.' =
correct

Changed (both instances) to:
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. 

For:
 [...] Triggers are similar to stored functions, so the preceding remarks regarding
functions also apply to triggers'

With the preceding changes, that statement needs no changes.

For:
http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html
'CREATE TRIGGER requires the TRIGGER privilege for the table associated with the
trigger.
(Before MySQL 5.1.6, this statement requires the SUPER privilege.)'

Changed to:
CREATE TRIGGER requires the TRIGGER privilege for the table 
associated with the trigger. The statement might also require the
SUPER privilege, depending on the DEFINER value (as described later
in this section), or if binary logging is enabled (as described in in
Section 19.6, "Binary Logging of Stored Programs"). (Before MySQL
5.1.6, there is no TRIGGER privilege and this statement requires the
SUPER privilege in all cases.)