# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not # supported in embedded server. So, this test should not be run on embedded # server. -- source include/not_embedded.inc ########################################################################### # # Tests for WL#2818: # - Check that triggers are executed under the authorization of the definer. # - Check DEFINER clause of CREATE TRIGGER statement; # - Check that SUPER privilege required to create a trigger with different # definer. # - Check that if the user specified as DEFINER does not exist, a warning # is emitted. # - Check that the definer of a trigger does not exist, the trigger will # not be activated. # - Check that SHOW TRIGGERS statement provides "Definer" column. # - Check that if trigger contains NEW/OLD variables, the definer must have # SELECT privilege on the subject table (aka BUG#15166/BUG#15196). # # Let's also check that user name part of definer can contain '@' symbol (to # check that triggers are not affected by BUG#13310 "incorrect user parsing # by SP"). # ########################################################################### # # Prepare environment. # DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; FLUSH PRIVILEGES; --disable_warnings DROP DATABASE IF EXISTS mysqltest_db1; --enable_warnings CREATE DATABASE mysqltest_db1; CREATE USER mysqltest_dfn@localhost; CREATE USER mysqltest_inv@localhost; GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; --connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con CREATE TABLE t1(num_value INT); CREATE TABLE t2(user_str TEXT); --disconnect wl2818_definer_con --connection default --echo --echo ---> connection: default GRANT INSERT, DELETE ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; GRANT INSERT, DELETE ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; # # Check that the user must have TRIGGER privilege to create a trigger. # --connection default --echo --echo ---> connection: default GRANT SUPER ON *.* TO mysqltest_dfn@localhost; --connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con --error ER_TABLEACCESS_DENIED_ERROR CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER()); --disconnect wl2818_definer_con # # Check that the user must have TRIGGER privilege to drop a trigger. # --connection default --echo --echo ---> connection: default GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; --connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER()); --disconnect wl2818_definer_con --connection default --echo --echo ---> connection: default REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; --connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con --error ER_TABLEACCESS_DENIED_ERROR DROP TRIGGER trg1; --disconnect wl2818_definer_con # # Check that the definer must have TRIGGER privilege to activate a trigger. # --connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con --error ER_TABLEACCESS_DENIED_ERROR INSERT INTO t1 VALUES(0); --disconnect wl2818_definer_con --connection default --echo --echo ---> connection: default GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; --connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con INSERT INTO t1 VALUES(0); # Cleanup for further tests. DROP TRIGGER trg1; DELETE FROM t1; DELETE FROM t2; --disconnect wl2818_definer_con --connection default --echo --echo ---> connection: default REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost; # # Check that triggers are executed under the authorization of the definer: # - create two tables under "definer"; # - grant all privileges on the test db to "definer"; # - grant all privileges on the first table to "invoker"; # - grant only select privilege on the second table to "invoker"; # - create a trigger, which inserts a row into the second table after # inserting into the first table. # - insert a row into the first table under "invoker". A row also should be # inserted into the second table. # --connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con CREATE TRIGGER trg1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER()); --connection default --echo --echo ---> connection: default # Setup definer's privileges. GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; # Setup invoker's privileges. GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO 'mysqltest_inv'@localhost; GRANT SELECT ON mysqltest_db1.t2 TO 'mysqltest_inv'@localhost; --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con use mysqltest_db1; INSERT INTO t1 VALUES(1); SELECT * FROM t1; SELECT * FROM t2; --connect (wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1) --connection wl2818_invoker_con --echo --echo ---> connection: wl2818_invoker_con use mysqltest_db1; INSERT INTO t1 VALUES(2); SELECT * FROM t1; SELECT * FROM t2; # # Check that if definer lost some privilege required to execute (activate) a # trigger, the trigger will not be activated: # - create a trigger on insert into the first table, which will insert a row # into the second table; # - revoke INSERT privilege on the second table from the definer; # - insert a row into the first table; # - check that an error has been risen; # - check that no row has been inserted into the second table; # --connection default --echo --echo ---> connection: default use mysqltest_db1; REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; --connection wl2818_invoker_con --echo --echo ---> connection: wl2818_invoker_con use mysqltest_db1; --error ER_TABLEACCESS_DENIED_ERROR INSERT INTO t1 VALUES(3); SELECT * FROM t1; SELECT * FROM t2; # # Check DEFINER clause of CREATE TRIGGER statement. # # - Check that SUPER privilege required to create a trigger with different # definer: # - try to create a trigger with DEFINER="definer@localhost" under # "invoker"; # - analyze error code; # - Check that if the user specified as DEFINER does not exist, a warning is # emitted: # - create a trigger with DEFINER="non_existent_user@localhost" from # "definer"; # - check that a warning emitted; # - Check that the definer of a trigger does not exist, the trigger will not # be activated: # - activate just created trigger; # - check error code; # --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con use mysqltest_db1; DROP TRIGGER trg1; # Check that SUPER is required to specify different DEFINER. --error ER_SPECIFIC_ACCESS_DENIED_ERROR CREATE DEFINER='mysqltest_inv'@'localhost' TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @new_sum = 0; --connection default --echo --echo ---> connection: default use mysqltest_db1; GRANT SUPER ON *.* TO mysqltest_dfn@localhost; --disconnect wl2818_definer_con --connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) --connection wl2818_definer_con --echo --echo ---> connection: wl2818_definer_con CREATE DEFINER='mysqltest_inv'@'localhost' TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @new_sum = 0; # Create with non-existent user. CREATE DEFINER='mysqltest_nonexs'@'localhost' TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW SET @new_sum = 0; # Check that trg2 will not be activated. # --error ER_SPECIFIC_ACCESS_DENIED_ERROR # # TODO: Due to the BUG#13198(SP executes if definer does not exist) the # following statement does not fail as it should. INSERT INTO t1 VALUES(6); # # Check that SHOW TRIGGERS statement provides "Definer" column. # SHOW TRIGGERS; # # Check that weird definer values do not break functionality. I.e. check the # following definer values: # - ''; # - '@'; # - '@abc@def@@'; # - '@hostname'; # - '@abc@def@@@hostname'; # DROP TRIGGER trg1; DROP TRIGGER trg2; CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; CREATE TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW SET @a = 2; CREATE TRIGGER trg3 BEFORE UPDATE ON t1 FOR EACH ROW SET @a = 3; CREATE TRIGGER trg4 AFTER UPDATE ON t1 FOR EACH ROW SET @a = 4; CREATE TRIGGER trg5 BEFORE DELETE ON t1 FOR EACH ROW SET @a = 5; --exec egrep -v '^definers=' $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG > $MYSQL_TEST_DIR/var/tmp/t1.TRG --exec echo "definers='' '@' '@abc@def@@' '@hostname' '@abcdef@@@hostname'" >> $MYSQL_TEST_DIR/var/tmp/t1.TRG --exec mv $MYSQL_TEST_DIR/var/tmp/t1.TRG $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG --echo SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; --echo SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; # # Cleanup # --connection default --echo --echo ---> connection: default DROP USER mysqltest_dfn@localhost; DROP USER mysqltest_inv@localhost; DROP DATABASE mysqltest_db1; ########################################################################### # # BUG#15166: Wrong update [was: select/update] permissions required to execute # triggers. # # BUG#15196: Wrong select permission required to execute triggers. # ########################################################################### # # Prepare environment. # DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; FLUSH PRIVILEGES; --disable_warnings DROP DATABASE IF EXISTS mysqltest_db1; --enable_warnings CREATE DATABASE mysqltest_db1; use mysqltest_db1; # Tables for tesing table-level privileges: CREATE TABLE t1(col CHAR(20)); # table for "read-value" trigger CREATE TABLE t2(col CHAR(20)); # table for "write-value" trigger # Tables for tesing column-level privileges: CREATE TABLE t3(col CHAR(20)); # table for "read-value" trigger CREATE TABLE t4(col CHAR(20)); # table for "write-value" trigger CREATE USER mysqltest_u1@localhost; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost; SET @mysqltest_var = NULL; --connect (bug15166_u1_con,localhost,mysqltest_u1,,mysqltest_db1) # parsing (CREATE TRIGGER) time: # - check that nor SELECT either UPDATE is required to execute triggger w/o # NEW/OLD variables. --connection default --echo --echo ---> connection: default use mysqltest_db1; GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; --connection bug15166_u1_con --echo --echo ---> connection: bug15166_u1_con use mysqltest_db1; CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1 FOR EACH ROW SET @mysqltest_var = 'Hello, world!'; # parsing (CREATE TRIGGER) time: # - check that UPDATE is not enough to read the value; # - check that UPDATE is required to modify the value; --connection default --echo --echo ---> connection: default use mysqltest_db1; GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost; GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; --connection bug15166_u1_con --echo --echo ---> connection: bug15166_u1_con use mysqltest_db1; # - table-level privileges # TODO: check privileges at CREATE TRIGGER time. # --error ER_COLUMNACCESS_DENIED_ERROR CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1 FOR EACH ROW SET @mysqltest_var = NEW.col; DROP TRIGGER t1_trg_err_1; # TODO: check privileges at CREATE TRIGGER time. # --error ER_COLUMNACCESS_DENIED_ERROR CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1 FOR EACH ROW SET @mysqltest_var = OLD.col; DROP TRIGGER t1_trg_err_2; CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2 FOR EACH ROW SET NEW.col = 't2_trg_before_insert'; # - column-level privileges # TODO: check privileges at CREATE TRIGGER time. # --error ER_COLUMNACCESS_DENIED_ERROR CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3 FOR EACH ROW SET @mysqltest_var = NEW.col; DROP TRIGGER t3_trg_err_1; # TODO: check privileges at CREATE TRIGGER time. # --error ER_COLUMNACCESS_DENIED_ERROR CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3 FOR EACH ROW SET @mysqltest_var = OLD.col; DROP TRIGGER t3_trg_err_2; CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4 FOR EACH ROW SET NEW.col = 't4_trg_before_insert'; # parsing (CREATE TRIGGER) time: # - check that SELECT is required to read the value; # - check that SELECT is not enough to modify the value; --connection default --echo --echo ---> connection: default use mysqltest_db1; REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost; REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost; GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost; --connection bug15166_u1_con --echo --echo ---> connection: bug15166_u1_con use mysqltest_db1; # - table-level privileges CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1 FOR EACH ROW SET @mysqltest_var = NEW.col; CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1 FOR EACH ROW SET @mysqltest_var = OLD.col; # TODO: check privileges at CREATE TRIGGER time. # --error ER_COLUMNACCESS_DENIED_ERROR CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2 FOR EACH ROW SET NEW.col = 't2_trg_err_1'; DROP TRIGGER t2_trg_err_1; # TODO: check privileges at CREATE TRIGGER time. # --error ER_COLUMNACCESS_DENIED_ERROR CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2 FOR EACH ROW SET NEW.col = CONCAT(OLD.col, '(updated)'); DROP TRIGGER t2_trg_err_2; # - column-level privileges CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3 FOR EACH ROW SET @mysqltest_var = NEW.col; CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3 FOR EACH ROW SET @mysqltest_var = OLD.col; # TODO: check privileges at CREATE TRIGGER time. # --error ER_COLUMNACCESS_DENIED_ERROR CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4 FOR EACH ROW SET NEW.col = 't4_trg_err_1'; DROP TRIGGER t4_trg_err_1; # TODO: check privileges at CREATE TRIGGER time. # --error ER_COLUMNACCESS_DENIED_ERROR CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4 FOR EACH ROW SET NEW.col = CONCAT(OLD.col, '(updated)'); DROP TRIGGER t4_trg_err_2; # execution time: # - check that UPDATE is not enough to read the value; # - check that UPDATE is required to modify the value; --connection default --echo --echo ---> connection: default use mysqltest_db1; REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost; REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; # - table-level privileges --error ER_COLUMNACCESS_DENIED_ERROR INSERT INTO t1 VALUES('line1'); SELECT * FROM t1; SELECT @mysqltest_var; INSERT INTO t2 VALUES('line2'); SELECT * FROM t2; # - column-level privileges --error ER_COLUMNACCESS_DENIED_ERROR INSERT INTO t3 VALUES('t3_line1'); SELECT * FROM t3; SELECT @mysqltest_var; INSERT INTO t4 VALUES('t4_line2'); SELECT * FROM t4; # execution time: # - check that SELECT is required to read the value; # - check that SELECT is not enough to modify the value; --connection default --echo --echo ---> connection: default use mysqltest_db1; REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost; REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; # - table-level privileges INSERT INTO t1 VALUES('line3'); SELECT * FROM t1; SELECT @mysqltest_var; --error ER_COLUMNACCESS_DENIED_ERROR INSERT INTO t2 VALUES('line4'); SELECT * FROM t2; # - column-level privileges INSERT INTO t3 VALUES('t3_line2'); SELECT * FROM t3; SELECT @mysqltest_var; --error ER_COLUMNACCESS_DENIED_ERROR INSERT INTO t4 VALUES('t4_line2'); SELECT * FROM t4; # execution time: # - check that nor SELECT either UPDATE is required to execute triggger w/o # NEW/OLD variables. DELETE FROM t1; SELECT @mysqltest_var; # # Cleanup. # DROP USER mysqltest_u1@localhost; DROP DATABASE mysqltest_db1;