bzr commit into mysql-6.0-codebase-bugfixing branch (lars-erik.bjork:3675) Bug#17598

[lars-erik.bjork]

#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/[e-mail]