1DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; 2DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; 3DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; 4DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; 5FLUSH PRIVILEGES; 6DROP DATABASE IF EXISTS mysqltest_db1; 7CREATE DATABASE mysqltest_db1; 8CREATE USER mysqltest_dfn@localhost; 9CREATE USER mysqltest_inv@localhost; 10GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; 11connect wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1; 12connection wl2818_definer_con; 13CREATE TABLE t1(num_value INT); 14CREATE TABLE t2(user_str TEXT); 15disconnect wl2818_definer_con; 16connection default; 17GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; 18GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; 19connection default; 20GRANT SUPER ON *.* TO mysqltest_dfn@localhost; 21connect wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1; 22connection wl2818_definer_con; 23CREATE TRIGGER trg1 AFTER INSERT ON t1 24FOR EACH ROW 25INSERT INTO t2 VALUES(CURRENT_USER()); 26ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1' 27disconnect wl2818_definer_con; 28connection default; 29GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; 30connect wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1; 31connection wl2818_definer_con; 32CREATE TRIGGER trg1 AFTER INSERT ON t1 33FOR EACH ROW 34INSERT INTO t2 VALUES(CURRENT_USER()); 35disconnect wl2818_definer_con; 36connection default; 37REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; 38connect wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1; 39connection wl2818_definer_con; 40DROP TRIGGER trg1; 41ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1' 42disconnect wl2818_definer_con; 43connect wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1; 44connection wl2818_definer_con; 45INSERT INTO t1 VALUES(0); 46ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1' 47disconnect wl2818_definer_con; 48connection default; 49GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; 50connect wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1; 51connection wl2818_definer_con; 52INSERT INTO t1 VALUES(0); 53DROP TRIGGER trg1; 54TRUNCATE TABLE t1; 55TRUNCATE TABLE t2; 56disconnect wl2818_definer_con; 57connection default; 58REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost; 59connect wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1; 60connection wl2818_definer_con; 61CREATE TRIGGER trg1 AFTER INSERT ON t1 62FOR EACH ROW 63INSERT INTO t2 VALUES(CURRENT_USER()); 64connection default; 65GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; 66GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; 67GRANT ALL PRIVILEGES ON mysqltest_db1.t1 68TO 'mysqltest_inv'@localhost; 69GRANT SELECT ON mysqltest_db1.t2 70TO 'mysqltest_inv'@localhost; 71connection wl2818_definer_con; 72use mysqltest_db1; 73INSERT INTO t1 VALUES(1); 74SELECT * FROM t1; 75num_value 761 77SELECT * FROM t2; 78user_str 79mysqltest_dfn@localhost 80connect wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1; 81connection wl2818_invoker_con; 82use mysqltest_db1; 83INSERT INTO t1 VALUES(2); 84SELECT * FROM t1; 85num_value 861 872 88SELECT * FROM t2; 89user_str 90mysqltest_dfn@localhost 91mysqltest_dfn@localhost 92connection default; 93use mysqltest_db1; 94REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; 95connection wl2818_invoker_con; 96use mysqltest_db1; 97INSERT INTO t1 VALUES(3); 98ERROR 42000: INSERT command denied to user 'mysqltest_dfn'@'localhost' for table 't2' 99SELECT * FROM t1; 100num_value 1011 1022 1033 104SELECT * FROM t2; 105user_str 106mysqltest_dfn@localhost 107mysqltest_dfn@localhost 108connection wl2818_definer_con; 109use mysqltest_db1; 110DROP TRIGGER trg1; 111CREATE DEFINER='mysqltest_inv'@'localhost' 112 TRIGGER trg1 BEFORE INSERT ON t1 113FOR EACH ROW 114SET @new_sum = 0; 115ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 116connection default; 117use mysqltest_db1; 118GRANT SUPER ON *.* TO mysqltest_dfn@localhost; 119disconnect wl2818_definer_con; 120connect wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1; 121connection wl2818_definer_con; 122CREATE DEFINER='mysqltest_inv'@'localhost' 123 TRIGGER trg1 BEFORE INSERT ON t1 124FOR EACH ROW 125SET @new_sum = 0; 126CREATE DEFINER='mysqltest_nonexs'@'localhost' 127 TRIGGER trg2 AFTER INSERT ON t1 128FOR EACH ROW 129SET @new_sum = 0; 130Warnings: 131Note 1449 The user specified as a definer ('mysqltest_nonexs'@'localhost') does not exist 132INSERT INTO t1 VALUES(6); 133ERROR HY000: The user specified as a definer ('mysqltest_nonexs'@'localhost') does not exist 134SHOW TRIGGERS; 135Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 136trg1 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 137trg2 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 138DROP TRIGGER trg1; 139DROP TRIGGER trg2; 140CREATE TRIGGER trg1 BEFORE INSERT ON t1 141FOR EACH ROW 142SET @a = 1; 143CREATE TRIGGER trg2 AFTER INSERT ON t1 144FOR EACH ROW 145SET @a = 2; 146CREATE TRIGGER trg3 BEFORE UPDATE ON t1 147FOR EACH ROW 148SET @a = 3; 149CREATE TRIGGER trg4 AFTER UPDATE ON t1 150FOR EACH ROW 151SET @a = 4; 152CREATE TRIGGER trg5 BEFORE DELETE ON t1 153FOR EACH ROW 154SET @a = 5; 155 156SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; 157trigger_name definer 158trg1 159trg2 @ 160trg3 @abc@def@@% 161trg4 @hostname 162trg5 @abcdef@@@hostname 163 164SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; 165TRIGGER_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 166def 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 167def 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 168def 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 169def 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 170def 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 171connection default; 172DROP USER mysqltest_dfn@localhost; 173DROP USER mysqltest_inv@localhost; 174DROP DATABASE mysqltest_db1; 175DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; 176DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; 177DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; 178DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; 179FLUSH PRIVILEGES; 180DROP DATABASE IF EXISTS mysqltest_db1; 181CREATE DATABASE mysqltest_db1; 182use mysqltest_db1; 183CREATE TABLE t1(col CHAR(20)); 184CREATE TABLE t2(col CHAR(20)); 185CREATE TABLE t3(col CHAR(20)); 186CREATE TABLE t4(col CHAR(20)); 187CREATE USER mysqltest_u1@localhost; 188REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; 189GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost; 190SET @mysqltest_var = NULL; 191connect bug15166_u1_con,localhost,mysqltest_u1,,mysqltest_db1; 192connection default; 193use mysqltest_db1; 194GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; 195SHOW GRANTS FOR mysqltest_u1@localhost; 196Grants for mysqltest_u1@localhost 197GRANT USAGE ON *.* TO `mysqltest_u1`@`localhost` 198GRANT DELETE, TRIGGER ON `mysqltest_db1`.* TO `mysqltest_u1`@`localhost` 199connection bug15166_u1_con; 200use mysqltest_db1; 201CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1 202FOR EACH ROW 203SET @mysqltest_var = 'Hello, world!'; 204connection default; 205use mysqltest_db1; 206GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; 207GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost; 208GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; 209GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; 210connection bug15166_u1_con; 211use mysqltest_db1; 212CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1 213FOR EACH ROW 214SET @mysqltest_var = NEW.col; 215DROP TRIGGER t1_trg_err_1; 216CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1 217FOR EACH ROW 218SET @mysqltest_var = OLD.col; 219DROP TRIGGER t1_trg_err_2; 220CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2 221FOR EACH ROW 222SET NEW.col = 't2_trg_before_insert'; 223CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3 224FOR EACH ROW 225SET @mysqltest_var = NEW.col; 226DROP TRIGGER t3_trg_err_1; 227CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3 228FOR EACH ROW 229SET @mysqltest_var = OLD.col; 230DROP TRIGGER t3_trg_err_2; 231CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4 232FOR EACH ROW 233SET NEW.col = 't4_trg_before_insert'; 234connection default; 235use mysqltest_db1; 236REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; 237REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; 238GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; 239GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost; 240REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; 241REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; 242GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost; 243GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost; 244connection bug15166_u1_con; 245use mysqltest_db1; 246CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1 247FOR EACH ROW 248SET @mysqltest_var = NEW.col; 249CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1 250FOR EACH ROW 251SET @mysqltest_var = OLD.col; 252CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2 253FOR EACH ROW 254SET NEW.col = 't2_trg_err_1'; 255DROP TRIGGER t2_trg_err_1; 256CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2 257FOR EACH ROW 258SET NEW.col = CONCAT(OLD.col, '(updated)'); 259DROP TRIGGER t2_trg_err_2; 260CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3 261FOR EACH ROW 262SET @mysqltest_var = NEW.col; 263CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3 264FOR EACH ROW 265SET @mysqltest_var = OLD.col; 266CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4 267FOR EACH ROW 268SET NEW.col = 't4_trg_err_1'; 269DROP TRIGGER t4_trg_err_1; 270CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4 271FOR EACH ROW 272SET NEW.col = CONCAT(OLD.col, '(updated)'); 273DROP TRIGGER t4_trg_err_2; 274connection default; 275use mysqltest_db1; 276REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; 277REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; 278GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; 279GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost; 280REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; 281REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; 282GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; 283GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; 284INSERT INTO t1 VALUES('line1'); 285ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't1' 286SELECT * FROM t1; 287col 288line1 289SELECT @mysqltest_var; 290@mysqltest_var 291NULL 292INSERT INTO t2 VALUES('line2'); 293SELECT * FROM t2; 294col 295t2_trg_before_insert 296INSERT INTO t3 VALUES('t3_line1'); 297ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't3' 298SELECT * FROM t3; 299col 300t3_line1 301SELECT @mysqltest_var; 302@mysqltest_var 303NULL 304INSERT INTO t4 VALUES('t4_line2'); 305SELECT * FROM t4; 306col 307t4_trg_before_insert 308connection default; 309use mysqltest_db1; 310REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; 311REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; 312GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; 313GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost; 314REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; 315REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; 316GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; 317GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; 318INSERT INTO t1 VALUES('line3'); 319SELECT * FROM t1; 320col 321line1 322line3 323SELECT @mysqltest_var; 324@mysqltest_var 325line3 326INSERT INTO t2 VALUES('line4'); 327ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't2' 328SELECT * FROM t2; 329col 330t2_trg_before_insert 331INSERT INTO t3 VALUES('t3_line2'); 332SELECT * FROM t3; 333col 334t3_line1 335t3_line2 336SELECT @mysqltest_var; 337@mysqltest_var 338t3_line2 339INSERT INTO t4 VALUES('t4_line2'); 340ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't4' 341SELECT * FROM t4; 342col 343t4_trg_before_insert 344DELETE FROM t1; 345SELECT @mysqltest_var; 346@mysqltest_var 347Hello, world! 348DROP USER mysqltest_u1@localhost; 349DROP DATABASE mysqltest_db1; 350DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; 351DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; 352DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; 353DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; 354FLUSH PRIVILEGES; 355DROP DATABASE IF EXISTS mysqltest_db1; 356CREATE DATABASE mysqltest_db1; 357USE mysqltest_db1; 358CREATE TABLE t1 (i1 INT); 359CREATE TABLE t2 (i1 INT); 360CREATE USER mysqltest_dfn@localhost; 361CREATE USER mysqltest_inv@localhost; 362GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost; 363GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost; 364connect definer,localhost,mysqltest_dfn,,mysqltest_db1; 365connect invoker,localhost,mysqltest_inv,,mysqltest_db1; 366connection definer; 367CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3; 368CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5; 369connection definer; 370CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 371CALL p1(NEW.i1); 372CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW 373CALL p2(NEW.i1); 374connection invoker; 375INSERT INTO t1 VALUES (7); 376ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1' 377INSERT INTO t2 VALUES (11); 378ERROR 42000: SELECT, UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2' 379connection definer; 380DROP TRIGGER t2_bi; 381DROP TRIGGER t1_bi; 382connection default; 383GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost; 384connection definer; 385CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 386CALL p1(NEW.i1); 387CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW 388CALL p2(NEW.i1); 389connection invoker; 390INSERT INTO t1 VALUES (13); 391ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1' 392INSERT INTO t2 VALUES (17); 393ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2' 394connection default; 395REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost; 396connection definer; 397DROP TRIGGER t2_bi; 398DROP TRIGGER t1_bi; 399connection default; 400GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; 401connection definer; 402CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 403CALL p1(NEW.i1); 404CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW 405CALL p2(NEW.i1); 406connection invoker; 407INSERT INTO t1 VALUES (19); 408INSERT INTO t2 VALUES (23); 409ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2' 410connection default; 411REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; 412connection definer; 413DROP TRIGGER t2_bi; 414DROP TRIGGER t1_bi; 415connection default; 416GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; 417connection definer; 418CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 419CALL p1(NEW.i1); 420CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW 421CALL p2(NEW.i1); 422connection invoker; 423INSERT INTO t1 VALUES (29); 424INSERT INTO t2 VALUES (31); 425connection default; 426REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; 427connection definer; 428DROP TRIGGER t2_bi; 429DROP TRIGGER t1_bi; 430connection default; 431DROP PROCEDURE p2; 432DROP PROCEDURE p1; 433connection default; 434GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; 435connection definer; 436CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37; 437CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 438CALL p1(NEW.i1); 439connection invoker; 440INSERT INTO t1 VALUES (41); 441connection definer; 442DROP PROCEDURE p1; 443CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43; 444connection invoker; 445INSERT INTO t1 VALUES (47); 446ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1' 447connection definer; 448DROP PROCEDURE p1; 449CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51; 450connection invoker; 451INSERT INTO t1 VALUES (53); 452ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1' 453connection default; 454DROP PROCEDURE p1; 455REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; 456connection definer; 457DROP TRIGGER t1_bi; 458disconnect definer; 459disconnect invoker; 460connection default; 461DROP USER mysqltest_inv@localhost; 462DROP USER mysqltest_dfn@localhost; 463DROP TABLE t2; 464DROP TABLE t1; 465DROP DATABASE mysqltest_db1; 466USE test; 467CREATE TABLE t1 (id INTEGER); 468CREATE TABLE t2 (id INTEGER); 469INSERT INTO t2 VALUES (1),(2); 470CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW 471INSERT INTO t2 VALUES (new.id); 472CONNECT rl_holder, localhost, root,,; 473CONNECT rl_acquirer, localhost, root,,; 474CONNECT wl_acquirer, localhost, root,,; 475CONNECT rl_contender, localhost, root,,; 476connection rl_holder; 477SELECT GET_LOCK('B26162',120); 478GET_LOCK('B26162',120) 4791 480connection rl_acquirer; 481SELECT 'rl_acquirer', GET_LOCK('B26162',120), id FROM t2 WHERE id = 1; 482connection wl_acquirer; 483SET SESSION LOW_PRIORITY_UPDATES=1; 484SET GLOBAL LOW_PRIORITY_UPDATES=1; 485INSERT INTO t1 VALUES (5); 486connection rl_contender; 487SELECT 'rl_contender', id FROM t2 WHERE id > 1; 488connection rl_holder; 489SELECT RELEASE_LOCK('B26162'); 490RELEASE_LOCK('B26162') 4911 492connection rl_acquirer; 493rl_acquirer GET_LOCK('B26162',120) id 494rl_acquirer 1 1 495SELECT RELEASE_LOCK('B26162'); 496RELEASE_LOCK('B26162') 4971 498connection wl_acquirer; 499connection rl_contender; 500rl_contender id 501rl_contender 2 502connection default; 503disconnect rl_acquirer; 504disconnect wl_acquirer; 505disconnect rl_contender; 506disconnect rl_holder; 507DROP TRIGGER t1_test; 508DROP TABLE t1,t2; 509SET SESSION LOW_PRIORITY_UPDATES=DEFAULT; 510SET GLOBAL LOW_PRIORITY_UPDATES=DEFAULT; 511End of 5.0 tests. 512drop table if exists t1; 513create table t1 (i int); 514connect flush,localhost,root,,test,,; 515connection default; 516lock tables t1 write; 517connection flush; 518flush tables with read lock;; 519connection default; 520create trigger t1_bi before insert on t1 for each row begin end; 521unlock tables; 522connection flush; 523unlock tables; 524connection default; 525select * from t1; 526i 527drop table t1; 528disconnect flush; 529CREATE DATABASE db1; 530CREATE TABLE db1.t1 (a char(30)) ENGINE=MEMORY; 531CREATE TRIGGER db1.trg AFTER INSERT ON db1.t1 FOR EACH ROW 532INSERT INTO db1.t1 VALUES('Some very sensitive data goes here'); 533CREATE USER 'no_rights'@'localhost'; 534REVOKE ALL ON *.* FROM 'no_rights'@'localhost'; 535FLUSH PRIVILEGES; 536connect con1,localhost,no_rights,,; 537SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS 538WHERE trigger_schema = 'db1'; 539trigger_name 540SHOW CREATE TRIGGER db1.trg; 541ERROR 42000: Access denied; you need (at least one of) the TRIGGER privilege(s) for this operation 542connection default; 543disconnect con1; 544DROP USER 'no_rights'@'localhost'; 545DROP DATABASE db1; 546DROP DATABASE IF EXISTS mysqltest_db1; 547CREATE DATABASE mysqltest_db1; 548USE mysqltest_db1; 549CREATE USER mysqltest_u1@localhost; 550GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost; 551connect con1,localhost,mysqltest_u1,,mysqltest_db1; 552CREATE TABLE t1 ( 553a1 int, 554a2 int 555); 556INSERT INTO t1 VALUES (1, 20); 557CREATE TRIGGER mysqltest_db1.upd_t1 558BEFORE UPDATE ON t1 FOR EACH ROW SET new.a2 = 200; 559CREATE TABLE t2 ( 560a1 int 561); 562INSERT INTO t2 VALUES (2); 563connection default; 564REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; 565UPDATE IGNORE t1, t2 SET t1.a1 = 2, t2.a1 = 3 WHERE t1.a1 = 1 AND t2.a1 = 2; 566ERROR 42000: TRIGGER command denied to user 'mysqltest_u1'@'localhost' for table 't1' 567DROP DATABASE mysqltest_db1; 568DROP USER mysqltest_u1@localhost; 569disconnect con1; 570connection default; 571USE test; 572End of 5.1 tests. 573