Bug #17033 trigger privilege checks

• Submitted: 2 Feb 2006 9:48
• Modified: 6 Feb 2006 9:45
• Reporter: Sergei Golubchik
• Status: Won't fix
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.6
• CPU Architecture: Any

[2 Feb 2006 9:48] Sergei Golubchik

Description:
According to the standard the correct privilege requirements should be:

1. for CREATE TRIGGER - TRIGGER privilege and if OLD or NEW is accessed - SELECT privilege
2. for execution - TRIGGER privilege and either invoker or definer should have UPDATE trivilege (or INSERT - depending on the operation) for all affected columns.

Also, if trigger does not have enough privileges there should be a warning not an error, and the statement should still be executed, not aborted (only the trigger won't be executed)

How to repeat:
e.g.

mysql> show grants;
+-----------------------------------------+
| Grants for a@localhost                  |
+-----------------------------------------+
| GRANT TRIGGER ON *.* TO 'a'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> create trigger trg1 before insert on t1 for each row set @a:=new.a;
Query OK, 0 rows affected (0.01 sec)

---- it should've failed

as another user:

mysql> insert t1 values (1);
ERROR 1142 (42000): SELECT,UPDATE command denied to user 'a'@'localhost' for table 't1'

-- it should've succeed.

[6 Feb 2006 15:54] Dmitry Lenev

Hi!
Actually thanks for recent fixes of bug #15166 and bug #15196 current 5.1 behaves sligthly differently. I am pasting small script for mysqltest program that will demonstrate current behavior:

CREATE DATABASE mysqltest_db1;
CREATE USER mysqltest_dfn@localhost;
GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_dfn@localhost;
CREATE TABLE mysqltest_db1.t1 (id int, val int);
INSERT INTO mysqltest_db1.t1 VALUES (1,1), (2,2);

--connect (dfn_con,localhost,mysqltest_dfn,,mysqltest_db1)
SHOW GRANTS;
# Grants for mysqltest_dfn@localhost
# GRANT USAGE ON *.* TO 'mysqltest_dfn'@'localhost'
# GRANT TRIGGER ON `mysqltest_db1`.* TO 'mysqltest_dfn'@'localhost'

# Statement 1
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:= NEW.id;

# Statement 2
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.val:= 100;

--connection default

# Statement 3
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO mysqltest_db1.t1 VALUES (3, 3);
# ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'id' in table 't1'

# Statement 4
--error ER_COLUMNACCESS_DENIED_ERROR
UPDATE mysqltest_db1.t1 SET val= 10 WHERE id=2;
# ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'val' in table 't1'

# End of test

[13 May 2010 1:56] Roel Van de Paar

And current behavior is different again it seems

mysql> create trigger trg1 before insert on t1 for each row set @a:=new.a;
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)

...

mysql> CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:= NEW.id;
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)