diff options
Diffstat (limited to 'mysql-test/main/trigger_notembedded.test')
| -rw-r--r-- | mysql-test/main/trigger_notembedded.test | 994 |
1 files changed, 994 insertions, 0 deletions
diff --git a/mysql-test/main/trigger_notembedded.test b/mysql-test/main/trigger_notembedded.test new file mode 100644 index 00000000000..a31594826e7 --- /dev/null +++ b/mysql-test/main/trigger_notembedded.test @@ -0,0 +1,994 @@ +# 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 + +CREATE TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); + +--disconnect wl2818_definer_con + +--connection default + +GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; +GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; + +# +# Check that the user must have TRIGGER privilege to create a trigger. +# + +--connection default + +GRANT SUPER ON *.* TO mysqltest_dfn@localhost; + +--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) +--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 + +GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; + +--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) +--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 + +REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; + +--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) +--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 + +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 VALUES(0); + +--disconnect wl2818_definer_con + +--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 + +INSERT INTO t1 VALUES(0); + +# Cleanup for further tests. +DROP TRIGGER trg1; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; + +--disconnect wl2818_definer_con + +--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 + +CREATE TRIGGER trg1 AFTER INSERT ON t1 + FOR EACH ROW + INSERT INTO t2 VALUES(CURRENT_USER()); + +--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 + +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 + +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 + +use mysqltest_db1; + +REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; + +--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 + +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 + +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 + +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_NO_SUCH_USER +INSERT INTO t1 VALUES(6); + +# +# Check that SHOW TRIGGERS statement provides "Definer" column. +# + +--replace_column 6 # +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; + +# Replace definers with the "weird" definers +let MYSQLD_DATADIR= `select @@datadir`; +perl; +use strict; +use warnings; +my $fname= "$ENV{'MYSQLD_DATADIR'}/mysqltest_db1/t1.TRG"; +open(FILE, "<", $fname) or die; +my @content= grep($_ !~ /^definers=/, <FILE>); +close FILE; +open(FILE, ">", $fname) or die; +# Use binary file mode to avoid CR/LF's being added on windows +binmode FILE; +print FILE @content; +print FILE "definers='' '\@' '\@abc\@def\@\@' '\@hostname' '\@abcdef\@\@\@hostname'\n"; +close FILE; +EOF + +--echo + +SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; + +--echo + +--replace_column 17 # +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; + +# +# Cleanup +# + +--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 + +use mysqltest_db1; + +GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; +SHOW GRANTS FOR mysqltest_u1@localhost; + +--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 + +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 + +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 + +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 + +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 + +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 + +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; + + +# +# Test for bug #14635 Accept NEW.x as INOUT parameters to stored +# procedures from within triggers +# +# We require UPDATE privilege when NEW.x passed as OUT parameter, and +# SELECT and UPDATE when NEW.x passed as INOUT parameter. +# +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; + +CREATE TABLE t1 (i1 INT); +CREATE TABLE t2 (i1 INT); + +CREATE USER mysqltest_dfn@localhost; +CREATE USER mysqltest_inv@localhost; + +GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost; +GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost; + +connect (definer,localhost,mysqltest_dfn,,mysqltest_db1); +connect (invoker,localhost,mysqltest_inv,,mysqltest_db1); + +connection definer; +CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3; +CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5; + +# Check that having no privilege won't work. +connection definer; +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW + CALL p1(NEW.i1); +CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW + CALL p2(NEW.i1); + +connection invoker; +--error ER_COLUMNACCESS_DENIED_ERROR +INSERT INTO t1 VALUES (7); +--error ER_COLUMNACCESS_DENIED_ERROR +INSERT INTO t2 VALUES (11); + +connection definer; +DROP TRIGGER t2_bi; +DROP TRIGGER t1_bi; + +# Check that having only SELECT privilege is not enough. +connection default; +GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +connection definer; +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW + CALL p1(NEW.i1); +CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW + CALL p2(NEW.i1); + +connection invoker; +--error ER_COLUMNACCESS_DENIED_ERROR +INSERT INTO t1 VALUES (13); +--error ER_COLUMNACCESS_DENIED_ERROR +INSERT INTO t2 VALUES (17); + +connection default; +REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost; + +connection definer; +DROP TRIGGER t2_bi; +DROP TRIGGER t1_bi; + +# Check that having only UPDATE privilege is enough for OUT parameter, +# but not for INOUT parameter. +connection default; +GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +connection definer; +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW + CALL p1(NEW.i1); +CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW + CALL p2(NEW.i1); + +connection invoker; +INSERT INTO t1 VALUES (19); +--error ER_COLUMNACCESS_DENIED_ERROR +INSERT INTO t2 VALUES (23); + +connection default; +REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; + +connection definer; +DROP TRIGGER t2_bi; +DROP TRIGGER t1_bi; + +# Check that having SELECT and UPDATE privileges is enough. +connection default; +GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +connection definer; +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW + CALL p1(NEW.i1); +CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW + CALL p2(NEW.i1); + +connection invoker; +INSERT INTO t1 VALUES (29); +INSERT INTO t2 VALUES (31); + +connection default; +REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; + +connection definer; +DROP TRIGGER t2_bi; +DROP TRIGGER t1_bi; + +connection default; +DROP PROCEDURE p2; +DROP PROCEDURE p1; + +# Check that late procedure redefining won't open a security hole. +connection default; +GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; + +connection definer; +CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37; +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW + CALL p1(NEW.i1); + +connection invoker; +INSERT INTO t1 VALUES (41); + +connection definer; +DROP PROCEDURE p1; +CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43; + +connection invoker; +--error ER_COLUMNACCESS_DENIED_ERROR +INSERT INTO t1 VALUES (47); + +connection definer; +DROP PROCEDURE p1; +CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51; + +connection invoker; +--error ER_COLUMNACCESS_DENIED_ERROR +INSERT INTO t1 VALUES (53); + +connection default; +DROP PROCEDURE p1; +REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; + +connection definer; +DROP TRIGGER t1_bi; + +# Cleanup. +disconnect definer; +disconnect invoker; +connection default; +DROP USER mysqltest_inv@localhost; +DROP USER mysqltest_dfn@localhost; +DROP TABLE t2; +DROP TABLE t1; +DROP DATABASE mysqltest_db1; +USE test; + +# +# Bug #26162: Trigger DML ignores low_priority_updates setting +# +CREATE TABLE t1 (id INTEGER); +CREATE TABLE t2 (id INTEGER); + +INSERT INTO t2 VALUES (1),(2); + +# trigger that produces the high priority insert, but should be low, adding +# LOW_PRIORITY fixes this +CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW + INSERT INTO t2 VALUES (new.id); + +CONNECT (rl_holder, localhost, root,,); +CONNECT (rl_acquirer, localhost, root,,); +CONNECT (wl_acquirer, localhost, root,,); +CONNECT (rl_contender, localhost, root,,); + +CONNECTION rl_holder; +SELECT GET_LOCK('B26162',120); + +CONNECTION rl_acquirer; +let $rl_acquirer_thread_id = `SELECT @@pseudo_thread_id`; +--send +SELECT 'rl_acquirer', GET_LOCK('B26162',120), id FROM t2 WHERE id = 1; + +CONNECTION wl_acquirer; +let $wl_acquirer_thread_id = `SELECT @@pseudo_thread_id`; +SET SESSION LOW_PRIORITY_UPDATES=1; +SET GLOBAL LOW_PRIORITY_UPDATES=1; +#need to wait for rl_acquirer to lock on the B26162 lock +let $wait_condition= + SELECT STATE = 'User lock' FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE ID = $rl_acquirer_thread_id; +--source include/wait_condition.inc +--send +INSERT INTO t1 VALUES (5); + +CONNECTION rl_contender; +# Wait until wl_acquirer is waiting for the read lock on t2 to be released. +let $wait_condition= + SELECT STATE = 'Waiting for table level lock' FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE ID = $wl_acquirer_thread_id; +--source include/wait_condition.inc +# must not "see" the row inserted by the INSERT (as it must run before the +# INSERT) +--send +SELECT 'rl_contender', id FROM t2 WHERE id > 1; + +CONNECTION rl_holder; +#need to wait for wl_acquirer and rl_contender to lock on t2 +sleep 2; +SELECT RELEASE_LOCK('B26162'); + +CONNECTION rl_acquirer; +--reap +SELECT RELEASE_LOCK('B26162'); +CONNECTION wl_acquirer; +--reap +CONNECTION rl_contender; +--reap + +CONNECTION default; +DISCONNECT rl_acquirer; +DISCONNECT wl_acquirer; +DISCONNECT rl_contender; +DISCONNECT rl_holder; + +DROP TRIGGER t1_test; +DROP TABLE t1,t2; +SET SESSION LOW_PRIORITY_UPDATES=DEFAULT; +SET GLOBAL LOW_PRIORITY_UPDATES=DEFAULT; + +--echo End of 5.0 tests. + +# +# Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock +# + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (i int); +connect (flush,localhost,root,,test,,); +connection default; +lock tables t1 write; +connection flush; +--send flush tables with read lock; +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for global read lock"; +--source include/wait_condition.inc +create trigger t1_bi before insert on t1 for each row begin end; +unlock tables; +connection flush; +--reap +unlock tables; +connection default; +select * from t1; +drop table t1; +disconnect flush; + +# +# Bug#45412 SHOW CREATE TRIGGER does not require privileges to disclose trigger data +# +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a char(30)) ENGINE=MEMORY; +CREATE TRIGGER db1.trg AFTER INSERT ON db1.t1 FOR EACH ROW + INSERT INTO db1.t1 VALUES('Some very sensitive data goes here'); + +CREATE USER 'no_rights'@'localhost'; +REVOKE ALL ON *.* FROM 'no_rights'@'localhost'; +FLUSH PRIVILEGES; + +connect (con1,localhost,no_rights,,); +SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS + WHERE trigger_schema = 'db1'; +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +SHOW CREATE TRIGGER db1.trg; + +connection default; +disconnect con1; +DROP USER 'no_rights'@'localhost'; +DROP DATABASE db1; + +# +# Bug#55421 Protocol::end_statement(): Assertion `0' on multi-table UPDATE IGNORE +# To reproduce a crash we need to provoke a trigger execution with +# the following conditions: +# - active SELECT statement during trigger execution +# (i.e. LEX::current_select != NULL); +# - IGNORE option (i.e. LEX::current_select->no_error == TRUE); +--disable_warnings +DROP DATABASE IF EXISTS mysqltest_db1; +--enable_warnings + +CREATE DATABASE mysqltest_db1; +USE mysqltest_db1; + +CREATE USER mysqltest_u1@localhost; +GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost; + +--connect(con1,localhost,mysqltest_u1,,mysqltest_db1) + +CREATE TABLE t1 ( + a1 int, + a2 int +); +INSERT INTO t1 VALUES (1, 20); + +CREATE TRIGGER mysqltest_db1.upd_t1 +BEFORE UPDATE ON t1 FOR EACH ROW SET new.a2 = 200; + +CREATE TABLE t2 ( + a1 int +); + +INSERT INTO t2 VALUES (2); + +--connection default + +REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; + +--error ER_TABLEACCESS_DENIED_ERROR +UPDATE IGNORE t1, t2 SET t1.a1 = 2, t2.a1 = 3 WHERE t1.a1 = 1 AND t2.a1 = 2; +# Cleanup + +DROP DATABASE mysqltest_db1; +DROP USER mysqltest_u1@localhost; + +--disconnect con1 +--connection default +USE test; + +--echo End of 5.1 tests. |