diff options
Diffstat (limited to 'mysql-test/t/trigger-grant.test')
| -rw-r--r-- | mysql-test/t/trigger-grant.test | 739 |
1 files changed, 739 insertions, 0 deletions
diff --git a/mysql-test/t/trigger-grant.test b/mysql-test/t/trigger-grant.test new file mode 100644 index 00000000000..12b929898a8 --- /dev/null +++ b/mysql-test/t/trigger-grant.test @@ -0,0 +1,739 @@ +# 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 SUPER ON *.* TO mysqltest_dfn@localhost; +GRANT CREATE ON mysqltest_db1.* TO 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 TABLE t1(num_value INT); +CREATE TABLE t2(user_str TEXT); + +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. +# +# NOTE: there is no dedicated TRIGGER privilege for CREATE TRIGGER statement. +# SUPER privilege is used instead. I.e., if one invokes CREATE TRIGGER, it should +# have SUPER privilege, so this test is meaningless right now. +# +# - 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. +# NOTE: meaningless at the moment + +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 +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; + +--system grep -v '^definers=' $MYSQLTEST_VARDIR/master-data/mysqltest_db1/t1.TRG > $MYSQLTEST_VARDIR/tmp/t1.TRG +--system echo "definers='' '@' '@abc@def@@' '@hostname' '@abcdef@@@hostname'" >> $MYSQLTEST_VARDIR/tmp/t1.TRG +--system mv $MYSQLTEST_VARDIR/tmp/t1.TRG $MYSQLTEST_VARDIR/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 SUPER ON *.* TO mysqltest_u1@localhost; +GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; # to allow connect + +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; + +REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; +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; + + +# +# 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, SUPER 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; + +--echo End of 5.0 tests. |