#At file:///home/lb200670/mysql/17598/ based on revid:dlenev@stripped
3675 lars-erik.bjork@stripped 2009-10-27
Test for bug#17598 - privilege checks when trigger is executed.
Based on the description given by Sergei.
The test has four scenarios.
Scenario 1:
Definer has INSERT privileges, invoker has INSERT privileges.
This fails when doing an insert query:
query 'INSERT INTO bug17598.t1 VALUES (1), (2), (3)' failed: 1143: UPDATE command denied
to user 'u1'@'localhost' for column 'c1' in table 't1'
Scenario 2:
Definer does not have INSERT privileges, invoker has INSERT privileges.
This fails when doing an insert query:
query 'INSERT INTO bug17598.t1 VALUES (1), (2), (3)' failed: 1143: UPDATE command denied
to user 'u1'@'localhost' for column 'c1' in table 't1'
Scenario 3:
Definer has UPDATE privileges, invoker has UPDATE privileges:
This succeeds when doing an update query
Scenario 4:
Definer does not have UPDATE privileges, invoker has UPDATE privileges:
This fails when doing an update query:
query 'UPDATE bug17598.t1 SET c1 = 17' failed: 1143: UPDATE command denied to user
'u1'@'localhost' for column 'c1' in table 't1'
According to Sergei, all of these scenarios should succeed
@ mysql-test/r/trigger.result
The expected result.
@ mysql-test/t/trigger.test
The test.
modified:
mysql-test/r/trigger.result
mysql-test/t/trigger.test
=== modified file 'mysql-test/r/trigger.result'
--- a/mysql-test/r/trigger.result 2009-10-25 13:41:27 +0000
+++ b/mysql-test/r/trigger.result 2009-10-27 09:01:36 +0000
@@ -2222,4 +2222,95 @@ INSERT INTO t1 VALUES (1, 'example.com')
INSERT INTO t2 VALUES ('Yes', 1, NULL, 'spamfilter','scan_incoming');
DROP TRIGGER t2_ai;
DROP TABLE t1, t2, t3;
+#
+# Bug #17598: privilege checks when trigger is executed.
+#
+DROP DATABASE IF EXISTS bug17598;
+CREATE DATABASE bug17598;
+CREATE USER u1;
+CREATE USER u2;
+GRANT SELECT ON bug17598.* TO u1@localhost;
+CREATE TABLE bug17598.t1 (c1 int);
+GRANT TRIGGER ON bug17598.t1 TO u1@localhost;
+GRANT INSERT ON bug17598.t1 TO u2@localhost;
+# Scenario: The definer of the trigger has INSERT privileges.
+GRANT INSERT ON bug17598.t1 TO u1@localhost;
+# Open connection con1 for user u1.
+# Create a trigger that updates the NEW pseudovariable.
+CREATE TRIGGER bug17598.tr1 BEFORE INSERT ON bug17598.t1
+FOR EACH ROW
+SET NEW.c1 = 42;
+# Open a connection for user u2.
+INSERT INTO bug17598.t1 VALUES (1), (2), (3);
+# Change to connection default.
+SELECT * FROM bug17598.t1;
+c1
+42
+42
+42
+DROP TRIGGER bug17598.tr1;
+REVOKE INSERT on bug17598.t1 FROM u1@localhost;
+DELETE FROM bug17598.t1;
+# Scenario: The definer of the trigger does not have INSERT privileges.
+# Change to connection con1.
+# Create a trigger that updates the NEW pseudovariable.
+CREATE TRIGGER bug17598.tr1 BEFORE INSERT ON bug17598.t1
+FOR EACH ROW
+SET NEW.c1 = 42;
+# Change to connection con2.
+INSERT INTO bug17598.t1 VALUES (1), (2), (3);
+# Change to connection default.
+SELECT * FROM bug17598.t1;
+c1
+42
+42
+42
+DROP TRIGGER bug17598.tr1;
+DELETE FROM bug17598.t1;
+REVOKE INSERT ON bug17598.t1 FROM u2@localhost;
+GRANT UPDATE ON bug17598.t1 TO u2@localhost;
+# Scenario: The definer of the trigger has UPDATE privileges.
+# Insert some rows to be updated.
+INSERT INTO bug17598.t1 values (1), (2), (3);
+GRANT UPDATE ON bug17598.t1 TO u1@localhost;
+# Change to connection con1.
+# Create a trigger that updates the NEW pseudovariable.
+CREATE TRIGGER bug17598.tr1 BEFORE UPDATE ON bug17598.t1
+FOR EACH ROW
+SET NEW.c1 = 42;
+# Change to connection con2.
+UPDATE bug17598.t1 SET c1 = 17;
+# Change to connection default.
+SELECT * FROM bug17598.t1;
+c1
+42
+42
+42
+DROP TRIGGER bug17598.tr1;
+REVOKE UPDATE ON bug17598.t1 FROM u1@localhost;
+DELETE FROM bug17598.t1;
+# Scenario: The definer of the trigger does not have UPDATE privileges.
+# Insert some rows to be updated.
+INSERT INTO bug17598.t1 values (1), (2), (3);
+# Change to connection con1.
+# Create a trigger that updates the NEW pseudovariable.
+CREATE TRIGGER bug17598.tr1 BEFORE UPDATE ON bug17598.t1
+FOR EACH ROW
+SET NEW.c1 = 42;
+# Change to connection con2.
+UPDATE bug17598.t1 SET c1 = 17;
+# Change to connection default.
+SELECT * FROM bug17598.t1;
+c1
+42
+42
+42
+DROP TRIGGER bug17598.tr1;
+DELETE FROM bug17598.t1;
+REVOKE UPDATE ON bug17598.t1 FROM u2@localhost;
+REVOKE TRIGGER ON bug17598.t1 FROM u1@localhost;
+# Final cleanup
+DROP TABLE bug17598.t1;
+DROP USER u1, u2;
+DROP DATABASE bug17598;
End of 6.0 tests.
=== modified file 'mysql-test/t/trigger.test'
--- a/mysql-test/t/trigger.test 2009-10-25 13:41:27 +0000
+++ b/mysql-test/t/trigger.test 2009-10-27 09:01:36 +0000
@@ -2561,4 +2561,104 @@ INSERT INTO t2 VALUES ('Yes', 1, NULL, '
DROP TRIGGER t2_ai;
DROP TABLE t1, t2, t3;
+--echo #
+--echo # Bug #17598: privilege checks when trigger is executed.
+--echo #
+--disable_warnings
+DROP DATABASE IF EXISTS bug17598;
+--enable_warnings
+CREATE DATABASE bug17598;
+CREATE USER u1;
+CREATE USER u2;
+GRANT SELECT ON bug17598.* TO u1@localhost;
+CREATE TABLE bug17598.t1 (c1 int);
+
+GRANT TRIGGER ON bug17598.t1 TO u1@localhost;
+GRANT INSERT ON bug17598.t1 TO u2@localhost;
+
+--echo # Scenario: The definer of the trigger has INSERT privileges.
+GRANT INSERT ON bug17598.t1 TO u1@localhost;
+--echo # Open connection con1 for user u1.
+connect (con1,localhost,u1,,);
+--echo # Create a trigger that updates the NEW pseudovariable.
+CREATE TRIGGER bug17598.tr1 BEFORE INSERT ON bug17598.t1
+ FOR EACH ROW
+ SET NEW.c1 = 42;
+--echo # Open a connection for user u2.
+connect (con2,localhost,u2,,);
+INSERT INTO bug17598.t1 VALUES (1), (2), (3);
+--echo # Change to connection default.
+connection default;
+SELECT * FROM bug17598.t1;
+DROP TRIGGER bug17598.tr1;
+REVOKE INSERT on bug17598.t1 FROM u1@localhost;
+DELETE FROM bug17598.t1;
+
+--echo # Scenario: The definer of the trigger does not have INSERT privileges.
+--echo # Change to connection con1.
+connection con1;
+--echo # Create a trigger that updates the NEW pseudovariable.
+CREATE TRIGGER bug17598.tr1 BEFORE INSERT ON bug17598.t1
+ FOR EACH ROW
+ SET NEW.c1 = 42;
+--echo # Change to connection con2.
+connection con2;
+INSERT INTO bug17598.t1 VALUES (1), (2), (3);
+--echo # Change to connection default.
+connection default;
+SELECT * FROM bug17598.t1;
+DROP TRIGGER bug17598.tr1;
+DELETE FROM bug17598.t1;
+
+REVOKE INSERT ON bug17598.t1 FROM u2@localhost;
+GRANT UPDATE ON bug17598.t1 TO u2@localhost;
+
+--echo # Scenario: The definer of the trigger has UPDATE privileges.
+--echo # Insert some rows to be updated.
+INSERT INTO bug17598.t1 values (1), (2), (3);
+GRANT UPDATE ON bug17598.t1 TO u1@localhost;
+--echo # Change to connection con1.
+connection con1;
+--echo # Create a trigger that updates the NEW pseudovariable.
+CREATE TRIGGER bug17598.tr1 BEFORE UPDATE ON bug17598.t1
+ FOR EACH ROW
+ SET NEW.c1 = 42;
+--echo # Change to connection con2.
+connection con2;
+UPDATE bug17598.t1 SET c1 = 17;
+--echo # Change to connection default.
+connection default;
+SELECT * FROM bug17598.t1;
+DROP TRIGGER bug17598.tr1;
+REVOKE UPDATE ON bug17598.t1 FROM u1@localhost;
+DELETE FROM bug17598.t1;
+
+--echo # Scenario: The definer of the trigger does not have UPDATE privileges.
+--echo # Insert some rows to be updated.
+INSERT INTO bug17598.t1 values (1), (2), (3);
+--echo # Change to connection con1.
+connection con1;
+--echo # Create a trigger that updates the NEW pseudovariable.
+CREATE TRIGGER bug17598.tr1 BEFORE UPDATE ON bug17598.t1
+ FOR EACH ROW
+ SET NEW.c1 = 42;
+--echo # Change to connection con2.
+connection con2;
+UPDATE bug17598.t1 SET c1 = 17;
+--echo # Change to connection default.
+connection default;
+SELECT * FROM bug17598.t1;
+DROP TRIGGER bug17598.tr1;
+DELETE FROM bug17598.t1;
+
+REVOKE UPDATE ON bug17598.t1 FROM u2@localhost;
+REVOKE TRIGGER ON bug17598.t1 FROM u1@localhost;
+
+--echo # Final cleanup
+disconnect con1;
+disconnect con2;
+DROP TABLE bug17598.t1;
+DROP USER u1, u2;
+DROP DATABASE bug17598;
+
--echo End of 6.0 tests.
Attachment: [text/bzr-bundle] bzr/lars-erik.bjork@sun.com-20091027090136-10gr3r3bpq99vnwm.bundle
Thread |
---|
• bzr commit into mysql-6.0-codebase-bugfixing branch (lars-erik.bjork:3675)Bug#17598 | lars-erik.bjork | 27 Oct |