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 SUPER. So, the first and third line above can really be read as 'TRIGGER and everything else', 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.
