1
# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not
2
# supported in embedded server. So, this test should not be run on embedded
5
-- source include/not_embedded.inc
7
###########################################################################
10
# - Check that triggers are executed under the authorization of the definer.
11
# - Check DEFINER clause of CREATE TRIGGER statement;
12
# - Check that SUPER privilege required to create a trigger with different
14
# - Check that if the user specified as DEFINER does not exist, a warning
16
# - Check that the definer of a trigger does not exist, the trigger will
18
# - Check that SHOW TRIGGERS statement provides "Definer" column.
19
# - Check that if trigger contains NEW/OLD variables, the definer must have
20
# SELECT privilege on the subject table (aka BUG#15166/BUG#15196).
22
# Let's also check that user name part of definer can contain '@' symbol (to
23
# check that triggers are not affected by BUG#13310 "incorrect user parsing
26
###########################################################################
29
# Prepare environment.
32
DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
33
DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
34
DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
35
DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
39
DROP DATABASE IF EXISTS mysqltest_db1;
42
CREATE DATABASE mysqltest_db1;
44
CREATE USER mysqltest_dfn@localhost;
45
CREATE USER mysqltest_inv@localhost;
47
GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
49
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
50
--connection wl2818_definer_con
52
--echo ---> connection: wl2818_definer_con
54
CREATE TABLE t1(num_value INT);
55
CREATE TABLE t2(user_str TEXT);
57
--disconnect wl2818_definer_con
61
--echo ---> connection: default
63
GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
64
GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
67
# Check that the user must have TRIGGER privilege to create a trigger.
72
--echo ---> connection: default
74
GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
76
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
77
--connection wl2818_definer_con
79
--echo ---> connection: wl2818_definer_con
81
--error ER_TABLEACCESS_DENIED_ERROR
82
CREATE TRIGGER trg1 AFTER INSERT ON t1
84
INSERT INTO t2 VALUES(CURRENT_USER());
86
--disconnect wl2818_definer_con
89
# Check that the user must have TRIGGER privilege to drop a trigger.
94
--echo ---> connection: default
96
GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
98
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
99
--connection wl2818_definer_con
101
--echo ---> connection: wl2818_definer_con
103
CREATE TRIGGER trg1 AFTER INSERT ON t1
105
INSERT INTO t2 VALUES(CURRENT_USER());
107
--disconnect wl2818_definer_con
111
--echo ---> connection: default
113
REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
115
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
116
--connection wl2818_definer_con
118
--echo ---> connection: wl2818_definer_con
120
--error ER_TABLEACCESS_DENIED_ERROR
123
--disconnect wl2818_definer_con
126
# Check that the definer must have TRIGGER privilege to activate a trigger.
129
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
130
--connection wl2818_definer_con
132
--echo ---> connection: wl2818_definer_con
134
--error ER_TABLEACCESS_DENIED_ERROR
135
INSERT INTO t1 VALUES(0);
137
--disconnect wl2818_definer_con
141
--echo ---> connection: default
143
GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
145
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
146
--connection wl2818_definer_con
148
--echo ---> connection: wl2818_definer_con
150
INSERT INTO t1 VALUES(0);
152
# Cleanup for further tests.
157
--disconnect wl2818_definer_con
161
--echo ---> connection: default
163
REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost;
166
# Check that triggers are executed under the authorization of the definer:
167
# - create two tables under "definer";
168
# - grant all privileges on the test db to "definer";
169
# - grant all privileges on the first table to "invoker";
170
# - grant only select privilege on the second table to "invoker";
171
# - create a trigger, which inserts a row into the second table after
172
# inserting into the first table.
173
# - insert a row into the first table under "invoker". A row also should be
174
# inserted into the second table.
177
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
178
--connection wl2818_definer_con
180
--echo ---> connection: wl2818_definer_con
182
CREATE TRIGGER trg1 AFTER INSERT ON t1
184
INSERT INTO t2 VALUES(CURRENT_USER());
188
--echo ---> connection: default
190
# Setup definer's privileges.
192
GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
193
GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
195
# Setup invoker's privileges.
197
GRANT ALL PRIVILEGES ON mysqltest_db1.t1
198
TO 'mysqltest_inv'@localhost;
200
GRANT SELECT ON mysqltest_db1.t2
201
TO 'mysqltest_inv'@localhost;
203
--connection wl2818_definer_con
205
--echo ---> connection: wl2818_definer_con
209
INSERT INTO t1 VALUES(1);
214
--connect (wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1)
215
--connection wl2818_invoker_con
217
--echo ---> connection: wl2818_invoker_con
221
INSERT INTO t1 VALUES(2);
227
# Check that if definer lost some privilege required to execute (activate) a
228
# trigger, the trigger will not be activated:
229
# - create a trigger on insert into the first table, which will insert a row
230
# into the second table;
231
# - revoke INSERT privilege on the second table from the definer;
232
# - insert a row into the first table;
233
# - check that an error has been risen;
234
# - check that no row has been inserted into the second table;
239
--echo ---> connection: default
243
REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost;
245
--connection wl2818_invoker_con
247
--echo ---> connection: wl2818_invoker_con
251
--error ER_TABLEACCESS_DENIED_ERROR
252
INSERT INTO t1 VALUES(3);
258
# Check DEFINER clause of CREATE TRIGGER statement.
260
# - Check that SUPER privilege required to create a trigger with different
262
# - try to create a trigger with DEFINER="definer@localhost" under
264
# - analyze error code;
265
# - Check that if the user specified as DEFINER does not exist, a warning is
267
# - create a trigger with DEFINER="non_existent_user@localhost" from
269
# - check that a warning emitted;
270
# - Check that the definer of a trigger does not exist, the trigger will not
272
# - activate just created trigger;
273
# - check error code;
276
--connection wl2818_definer_con
278
--echo ---> connection: wl2818_definer_con
284
# Check that SUPER is required to specify different DEFINER.
286
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
287
CREATE DEFINER='mysqltest_inv'@'localhost'
288
TRIGGER trg1 BEFORE INSERT ON t1
294
--echo ---> connection: default
298
GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
300
--disconnect wl2818_definer_con
301
--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
302
--connection wl2818_definer_con
304
--echo ---> connection: wl2818_definer_con
306
CREATE DEFINER='mysqltest_inv'@'localhost'
307
TRIGGER trg1 BEFORE INSERT ON t1
311
# Create with non-existent user.
313
CREATE DEFINER='mysqltest_nonexs'@'localhost'
314
TRIGGER trg2 AFTER INSERT ON t1
318
# Check that trg2 will not be activated.
320
--error ER_NO_SUCH_USER
321
INSERT INTO t1 VALUES(6);
324
# Check that SHOW TRIGGERS statement provides "Definer" column.
330
# Check that weird definer values do not break functionality. I.e. check the
331
# following definer values:
336
# - '@abc@def@@@hostname';
342
CREATE TRIGGER trg1 BEFORE INSERT ON t1
346
CREATE TRIGGER trg2 AFTER INSERT ON t1
350
CREATE TRIGGER trg3 BEFORE UPDATE ON t1
354
CREATE TRIGGER trg4 AFTER UPDATE ON t1
358
CREATE TRIGGER trg5 BEFORE DELETE ON t1
362
# Replace definers with the "weird" definers
363
let MYSQLD_DATADIR= `select @@datadir`;
367
my $fname= "$ENV{'MYSQLD_DATADIR'}/mysqltest_db1/t1.TRG";
368
open(FILE, "<", $fname) or die;
369
my @content= grep($_ !~ /^definers=/, <FILE>);
371
open(FILE, ">", $fname) or die;
372
# Use binary file mode to avoid CR/LF's being added on windows
375
print FILE "definers='' '\@' '\@abc\@def\@\@' '\@hostname' '\@abcdef\@\@\@hostname'\n";
381
SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
385
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
393
--echo ---> connection: default
395
DROP USER mysqltest_dfn@localhost;
396
DROP USER mysqltest_inv@localhost;
398
DROP DATABASE mysqltest_db1;
401
###########################################################################
403
# BUG#15166: Wrong update [was: select/update] permissions required to execute
406
# BUG#15196: Wrong select permission required to execute triggers.
408
###########################################################################
411
# Prepare environment.
414
DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
415
DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
416
DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
417
DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
421
DROP DATABASE IF EXISTS mysqltest_db1;
424
CREATE DATABASE mysqltest_db1;
428
# Tables for tesing table-level privileges:
429
CREATE TABLE t1(col CHAR(20)); # table for "read-value" trigger
430
CREATE TABLE t2(col CHAR(20)); # table for "write-value" trigger
432
# Tables for tesing column-level privileges:
433
CREATE TABLE t3(col CHAR(20)); # table for "read-value" trigger
434
CREATE TABLE t4(col CHAR(20)); # table for "write-value" trigger
436
CREATE USER mysqltest_u1@localhost;
437
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
438
GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost;
440
SET @mysqltest_var = NULL;
442
--connect (bug15166_u1_con,localhost,mysqltest_u1,,mysqltest_db1)
444
# parsing (CREATE TRIGGER) time:
445
# - check that nor SELECT either UPDATE is required to execute triggger w/o
450
--echo ---> connection: default
454
GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
455
SHOW GRANTS FOR mysqltest_u1@localhost;
457
--connection bug15166_u1_con
459
--echo ---> connection: bug15166_u1_con
463
CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1
465
SET @mysqltest_var = 'Hello, world!';
467
# parsing (CREATE TRIGGER) time:
468
# - check that UPDATE is not enough to read the value;
469
# - check that UPDATE is required to modify the value;
473
--echo ---> connection: default
477
GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
478
GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
480
GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
481
GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
483
--connection bug15166_u1_con
485
--echo ---> connection: bug15166_u1_con
489
# - table-level privileges
491
# TODO: check privileges at CREATE TRIGGER time.
492
# --error ER_COLUMNACCESS_DENIED_ERROR
493
CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1
495
SET @mysqltest_var = NEW.col;
496
DROP TRIGGER t1_trg_err_1;
498
# TODO: check privileges at CREATE TRIGGER time.
499
# --error ER_COLUMNACCESS_DENIED_ERROR
500
CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1
502
SET @mysqltest_var = OLD.col;
503
DROP TRIGGER t1_trg_err_2;
505
CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2
507
SET NEW.col = 't2_trg_before_insert';
509
# - column-level privileges
511
# TODO: check privileges at CREATE TRIGGER time.
512
# --error ER_COLUMNACCESS_DENIED_ERROR
513
CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3
515
SET @mysqltest_var = NEW.col;
516
DROP TRIGGER t3_trg_err_1;
518
# TODO: check privileges at CREATE TRIGGER time.
519
# --error ER_COLUMNACCESS_DENIED_ERROR
520
CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3
522
SET @mysqltest_var = OLD.col;
523
DROP TRIGGER t3_trg_err_2;
525
CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4
527
SET NEW.col = 't4_trg_before_insert';
529
# parsing (CREATE TRIGGER) time:
530
# - check that SELECT is required to read the value;
531
# - check that SELECT is not enough to modify the value;
535
--echo ---> connection: default
539
REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
540
REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
541
GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
542
GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
544
REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
545
REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
546
GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost;
547
GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost;
549
--connection bug15166_u1_con
551
--echo ---> connection: bug15166_u1_con
555
# - table-level privileges
557
CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1
559
SET @mysqltest_var = NEW.col;
561
CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1
563
SET @mysqltest_var = OLD.col;
565
# TODO: check privileges at CREATE TRIGGER time.
566
# --error ER_COLUMNACCESS_DENIED_ERROR
567
CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2
569
SET NEW.col = 't2_trg_err_1';
570
DROP TRIGGER t2_trg_err_1;
572
# TODO: check privileges at CREATE TRIGGER time.
573
# --error ER_COLUMNACCESS_DENIED_ERROR
574
CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2
576
SET NEW.col = CONCAT(OLD.col, '(updated)');
577
DROP TRIGGER t2_trg_err_2;
579
# - column-level privileges
581
CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3
583
SET @mysqltest_var = NEW.col;
585
CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3
587
SET @mysqltest_var = OLD.col;
589
# TODO: check privileges at CREATE TRIGGER time.
590
# --error ER_COLUMNACCESS_DENIED_ERROR
591
CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4
593
SET NEW.col = 't4_trg_err_1';
594
DROP TRIGGER t4_trg_err_1;
596
# TODO: check privileges at CREATE TRIGGER time.
597
# --error ER_COLUMNACCESS_DENIED_ERROR
598
CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4
600
SET NEW.col = CONCAT(OLD.col, '(updated)');
601
DROP TRIGGER t4_trg_err_2;
604
# - check that UPDATE is not enough to read the value;
605
# - check that UPDATE is required to modify the value;
609
--echo ---> connection: default
613
REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
614
REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
615
GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
616
GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
618
REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
619
REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
620
GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
621
GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
623
# - table-level privileges
625
--error ER_COLUMNACCESS_DENIED_ERROR
626
INSERT INTO t1 VALUES('line1');
629
SELECT @mysqltest_var;
631
INSERT INTO t2 VALUES('line2');
635
# - column-level privileges
637
--error ER_COLUMNACCESS_DENIED_ERROR
638
INSERT INTO t3 VALUES('t3_line1');
641
SELECT @mysqltest_var;
643
INSERT INTO t4 VALUES('t4_line2');
648
# - check that SELECT is required to read the value;
649
# - check that SELECT is not enough to modify the value;
653
--echo ---> connection: default
657
REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
658
REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
659
GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
660
GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
662
REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
663
REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
664
GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
665
GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
667
# - table-level privileges
669
INSERT INTO t1 VALUES('line3');
672
SELECT @mysqltest_var;
674
--error ER_COLUMNACCESS_DENIED_ERROR
675
INSERT INTO t2 VALUES('line4');
679
# - column-level privileges
681
INSERT INTO t3 VALUES('t3_line2');
684
SELECT @mysqltest_var;
686
--error ER_COLUMNACCESS_DENIED_ERROR
687
INSERT INTO t4 VALUES('t4_line2');
692
# - check that nor SELECT either UPDATE is required to execute triggger w/o
697
SELECT @mysqltest_var;
703
DROP USER mysqltest_u1@localhost;
705
DROP DATABASE mysqltest_db1;
709
# Test for bug #14635 Accept NEW.x as INOUT parameters to stored
710
# procedures from within triggers
712
# We require UPDATE privilege when NEW.x passed as OUT parameter, and
713
# SELECT and UPDATE when NEW.x passed as INOUT parameter.
715
DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
716
DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
717
DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
718
DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
722
DROP DATABASE IF EXISTS mysqltest_db1;
725
CREATE DATABASE mysqltest_db1;
728
CREATE TABLE t1 (i1 INT);
729
CREATE TABLE t2 (i1 INT);
731
CREATE USER mysqltest_dfn@localhost;
732
CREATE USER mysqltest_inv@localhost;
734
GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost;
735
GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
737
connect (definer,localhost,mysqltest_dfn,,mysqltest_db1);
738
connect (invoker,localhost,mysqltest_inv,,mysqltest_db1);
741
CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
742
CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
744
# Check that having no privilege won't work.
746
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
748
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
752
--error ER_COLUMNACCESS_DENIED_ERROR
753
INSERT INTO t1 VALUES (7);
754
--error ER_COLUMNACCESS_DENIED_ERROR
755
INSERT INTO t2 VALUES (11);
761
# Check that having only SELECT privilege is not enough.
763
GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
766
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
768
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
772
--error ER_COLUMNACCESS_DENIED_ERROR
773
INSERT INTO t1 VALUES (13);
774
--error ER_COLUMNACCESS_DENIED_ERROR
775
INSERT INTO t2 VALUES (17);
778
REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
784
# Check that having only UPDATE privilege is enough for OUT parameter,
785
# but not for INOUT parameter.
787
GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
790
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
792
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
796
INSERT INTO t1 VALUES (19);
797
--error ER_COLUMNACCESS_DENIED_ERROR
798
INSERT INTO t2 VALUES (23);
801
REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
807
# Check that having SELECT and UPDATE privileges is enough.
809
GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
812
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
814
CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
818
INSERT INTO t1 VALUES (29);
819
INSERT INTO t2 VALUES (31);
822
REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
832
# Check that late procedure redefining won't open a security hole.
834
GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
837
CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
838
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
842
INSERT INTO t1 VALUES (41);
846
CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
849
--error ER_COLUMNACCESS_DENIED_ERROR
850
INSERT INTO t1 VALUES (47);
854
CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
857
--error ER_COLUMNACCESS_DENIED_ERROR
858
INSERT INTO t1 VALUES (53);
862
REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
871
DROP USER mysqltest_inv@localhost;
872
DROP USER mysqltest_dfn@localhost;
875
DROP DATABASE mysqltest_db1;
878
--echo End of 5.0 tests.
881
# Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
885
drop table if exists t1;
887
create table t1 (i int);
888
connect (flush,localhost,root,,test,,);
890
--echo connection: default
891
lock tables t1 write;
893
--echo connection: flush
894
--send flush tables with read lock;
896
--echo connection: default
898
select count(*) = 1 from information_schema.processlist
899
where state = "Flushing tables";
900
--source include/wait_condition.inc
901
create trigger t1_bi before insert on t1 for each row begin end;
904
--echo connection: flush
913
# Bug#45412 SHOW CREATE TRIGGER does not require privileges to disclose trigger data
916
CREATE TABLE db1.t1 (a char(30)) ENGINE=MEMORY;
917
CREATE TRIGGER db1.trg AFTER INSERT ON db1.t1 FOR EACH ROW
918
INSERT INTO db1.t1 VALUES('Some very sensitive data goes here');
920
CREATE USER 'no_rights'@'localhost';
921
REVOKE ALL ON *.* FROM 'no_rights'@'localhost';
924
connect (con1,localhost,no_rights,,);
925
SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS
926
WHERE trigger_schema = 'db1';
927
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
928
SHOW CREATE TRIGGER db1.trg;
932
DROP USER 'no_rights'@'localhost';
935
--echo End of 5.1 tests.
added
removed
mysql-test/t/trigger_notembedded.test
