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; DROP DATABASE IF EXISTS mysqltest_db1; 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; connection default; GRANT SUPER ON *.* 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()); ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1' 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; 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; DROP TRIGGER trg1; ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1' disconnect wl2818_definer_con; connect wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1; connection wl2818_definer_con; INSERT INTO t1 VALUES(0); ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1' 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); DROP TRIGGER trg1; TRUNCATE TABLE t1; TRUNCATE TABLE t2; disconnect wl2818_definer_con; connection default; REVOKE SUPER ON *.* FROM 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()); connection default; GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; 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; num_value 1 SELECT * FROM t2; user_str mysqltest_dfn@localhost connect wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1; connection wl2818_invoker_con; use mysqltest_db1; INSERT INTO t1 VALUES(2); SELECT * FROM t1; num_value 1 2 SELECT * FROM t2; user_str mysqltest_dfn@localhost mysqltest_dfn@localhost connection default; use mysqltest_db1; REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; connection wl2818_invoker_con; use mysqltest_db1; INSERT INTO t1 VALUES(3); ERROR 42000: INSERT command denied to user 'mysqltest_dfn'@'localhost' for table 't2' SELECT * FROM t1; num_value 1 2 3 SELECT * FROM t2; user_str mysqltest_dfn@localhost mysqltest_dfn@localhost connection wl2818_definer_con; use mysqltest_db1; DROP TRIGGER trg1; CREATE DEFINER='mysqltest_inv'@'localhost' TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @new_sum = 0; ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 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 DEFINER='mysqltest_nonexs'@'localhost' TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW SET @new_sum = 0; Warnings: Note 1449 The user specified as a definer ('mysqltest_nonexs'@'localhost') does not exist INSERT INTO t1 VALUES(6); ERROR HY000: The user specified as a definer ('mysqltest_nonexs'@'localhost') does not exist SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation trg1 INSERT t1 SET @new_sum = 0 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mysqltest_inv@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg2 INSERT t1 SET @new_sum = 0 AFTER # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mysqltest_nonexs@localhost latin1 latin1_swedish_ci latin1_swedish_ci 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; SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; trigger_name definer trg1 trg2 @ trg3 @abc@def@@% trg4 @hostname trg5 @abcdef@@@hostname SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION def mysqltest_db1 trg1 INSERT def mysqltest_db1 t1 1 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION latin1 latin1_swedish_ci latin1_swedish_ci def mysqltest_db1 trg2 INSERT def mysqltest_db1 t1 1 NULL SET @a = 2 ROW AFTER NULL NULL OLD NEW # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @ latin1 latin1_swedish_ci latin1_swedish_ci def mysqltest_db1 trg3 UPDATE def mysqltest_db1 t1 1 NULL SET @a = 3 ROW BEFORE NULL NULL OLD NEW # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @abc@def@@% latin1 latin1_swedish_ci latin1_swedish_ci def mysqltest_db1 trg4 UPDATE def mysqltest_db1 t1 1 NULL SET @a = 4 ROW AFTER NULL NULL OLD NEW # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @hostname latin1 latin1_swedish_ci latin1_swedish_ci def mysqltest_db1 trg5 DELETE def mysqltest_db1 t1 1 NULL SET @a = 5 ROW BEFORE NULL NULL OLD NEW # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @abcdef@@@hostname latin1 latin1_swedish_ci latin1_swedish_ci connection default; DROP USER mysqltest_dfn@localhost; DROP USER mysqltest_inv@localhost; DROP DATABASE mysqltest_db1; 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; DROP DATABASE IF EXISTS mysqltest_db1; CREATE DATABASE mysqltest_db1; use mysqltest_db1; CREATE TABLE t1(col CHAR(20)); CREATE TABLE t2(col CHAR(20)); CREATE TABLE t3(col CHAR(20)); CREATE TABLE t4(col CHAR(20)); 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; connection default; use mysqltest_db1; GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; SHOW GRANTS FOR mysqltest_u1@localhost; Grants for mysqltest_u1@localhost GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` GRANT DELETE, TRIGGER ON `mysqltest_db1`.* TO `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!'; 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; CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1 FOR EACH ROW SET @mysqltest_var = NEW.col; DROP TRIGGER t1_trg_err_1; 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'; CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3 FOR EACH ROW SET @mysqltest_var = NEW.col; DROP TRIGGER t3_trg_err_1; 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'; 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; 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; 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; 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; 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; 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; 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; 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; INSERT INTO t1 VALUES('line1'); ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't1' SELECT * FROM t1; col line1 SELECT @mysqltest_var; @mysqltest_var NULL INSERT INTO t2 VALUES('line2'); SELECT * FROM t2; col t2_trg_before_insert INSERT INTO t3 VALUES('t3_line1'); ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't3' SELECT * FROM t3; col t3_line1 SELECT @mysqltest_var; @mysqltest_var NULL INSERT INTO t4 VALUES('t4_line2'); SELECT * FROM t4; col t4_trg_before_insert 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; INSERT INTO t1 VALUES('line3'); SELECT * FROM t1; col line1 line3 SELECT @mysqltest_var; @mysqltest_var line3 INSERT INTO t2 VALUES('line4'); ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't2' SELECT * FROM t2; col t2_trg_before_insert INSERT INTO t3 VALUES('t3_line2'); SELECT * FROM t3; col t3_line1 t3_line2 SELECT @mysqltest_var; @mysqltest_var t3_line2 INSERT INTO t4 VALUES('t4_line2'); ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't4' SELECT * FROM t4; col t4_trg_before_insert DELETE FROM t1; SELECT @mysqltest_var; @mysqltest_var Hello, world! DROP USER mysqltest_u1@localhost; DROP DATABASE mysqltest_db1; 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; DROP DATABASE IF EXISTS mysqltest_db1; 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; 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 (7); ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1' INSERT INTO t2 VALUES (11); ERROR 42000: SELECT, UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2' connection definer; DROP TRIGGER t2_bi; DROP TRIGGER t1_bi; 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; INSERT INTO t1 VALUES (13); ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1' INSERT INTO t2 VALUES (17); ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2' connection default; REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost; connection definer; DROP TRIGGER t2_bi; DROP TRIGGER t1_bi; 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); INSERT INTO t2 VALUES (23); ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2' connection default; REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; connection definer; DROP TRIGGER t2_bi; DROP TRIGGER t1_bi; 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; 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; INSERT INTO t1 VALUES (47); ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1' connection definer; DROP PROCEDURE p1; CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51; connection invoker; INSERT INTO t1 VALUES (53); ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1' connection default; DROP PROCEDURE p1; REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; connection definer; DROP TRIGGER t1_bi; 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; CREATE TABLE t1 (id INTEGER); CREATE TABLE t2 (id INTEGER); INSERT INTO t2 VALUES (1),(2); 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); GET_LOCK('B26162',120) 1 connection rl_acquirer; SELECT 'rl_acquirer', GET_LOCK('B26162',120), id FROM t2 WHERE id = 1; connection wl_acquirer; SET SESSION LOW_PRIORITY_UPDATES=1; SET GLOBAL LOW_PRIORITY_UPDATES=1; INSERT INTO t1 VALUES (5); connection rl_contender; SELECT 'rl_contender', id FROM t2 WHERE id > 1; connection rl_holder; SELECT RELEASE_LOCK('B26162'); RELEASE_LOCK('B26162') 1 connection rl_acquirer; rl_acquirer GET_LOCK('B26162',120) id rl_acquirer 1 1 SELECT RELEASE_LOCK('B26162'); RELEASE_LOCK('B26162') 1 connection wl_acquirer; connection rl_contender; rl_contender id rl_contender 2 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; End of 5.0 tests. drop table if exists t1; create table t1 (i int); connect flush,localhost,root,,test,,; connection default; lock tables t1 write; connection flush; flush tables with read lock;; connection default; create trigger t1_bi before insert on t1 for each row begin end; unlock tables; connection flush; unlock tables; connection default; select * from t1; i drop table t1; disconnect flush; 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'; trigger_name SHOW CREATE TRIGGER db1.trg; ERROR 42000: Access denied; you need (at least one of) the TRIGGER privilege(s) for this operation connection default; disconnect con1; DROP USER 'no_rights'@'localhost'; DROP DATABASE db1; DROP DATABASE IF EXISTS mysqltest_db1; 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; UPDATE IGNORE t1, t2 SET t1.a1 = 2, t2.a1 = 3 WHERE t1.a1 = 1 AND t2.a1 = 2; ERROR 42000: TRIGGER command denied to user 'mysqltest_u1'@'localhost' for table 't1' DROP DATABASE mysqltest_db1; DROP USER mysqltest_u1@localhost; disconnect con1; connection default; USE test; End of 5.1 tests.