Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_03e_definer.inc

raw
1 #======================================================================
2 #
3 # Trigger Tests
4 # test cases for TRIGGER privilege on db, table and column level
5 #======================================================================
6
7 --disable_abort_on_error
8
9 ###########################################
10 ################ Section 3.5.3 ############
11 # Check for the definer of Triggers #
12 ###########################################
13
14 # General setup to be used in all testcases
15 let $message= ######### Testcase for definer: ########;
16 --source include/show_msg.inc
17
18 --disable_warnings
19 drop database if exists priv_db;
20 --enable_warnings
21 create database priv_db;
22 use priv_db;
23 eval create table t1 (f1 char(20)) engine= $engine_type;
24
25 create User test_yesprivs@localhost;
26 set password for test_yesprivs@localhost = password('PWD');
27
28 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
29
30 connect (yes_privs,localhost,test_yesprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK);
31 # next is to check that we connected above
32 connection yes_privs;
33
34 # create trigger with not existing definer shall deliver a warning:
35 connection default;
36 select current_user;
37 # --warning 1449
38 create definer=not_ex_user@localhost trigger trg1_0
39 before INSERT on t1 for each row
40 set new.f1 = 'trig 1_0-yes';
41 drop trigger trg1_0;
42 # create trigger with definer test_yesprivs@localhost->succeed:
43 create definer=test_yesprivs@localhost trigger trg1_0
44 before INSERT on t1 for each row
45 set new.f1 = 'trig 1_0-yes';
46 grant select, insert, update
47 on priv_db.t1 to test_yesprivs@localhost;
48
49 connection yes_privs;
50 select current_user;
51 use priv_db;
52 # user hasn't trigger privilege->fail:
53 --error ER_TABLEACCESS_DENIED_ERROR
54 insert into t1 (f1) values ('insert-no');
55 select f1 from t1 order by f1;
56 --error ER_TABLEACCESS_DENIED_ERROR
57 drop trigger trg1_0;
58
59 connection default;
60 select current_user;
61 grant select, insert, update ,trigger
62 on priv_db.t1 to test_yesprivs@localhost;
63 show grants for test_yesprivs@localhost;
64
65 connection yes_privs;
66 select current_user;
67 # user now has trigger privilege->succeed:
68 insert into t1 (f1) values ('insert-no');
69 select f1 from t1 order by f1;
70 drop trigger trg1_0;
71 # user has not super privilege->fail:
72 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
73 create definer=not_ex_user@localhost trigger trg1_0
74 before INSERT on t1 for each row
75 set new.f1 = 'trig 1_0-yes';
76 # shall always succeed:
77 create definer=current_user trigger trg1_1
78 before INSERT on t1 for each row
79 set new.f1 = 'trig 1_1-yes';
80 insert into t1 (f1) values ('insert-no');
81 select f1 from t1 order by f1;
82 # shall always succeed:
83 create definer=test_yesprivs@localhost trigger trg1_2
84 before UPDATE on t1 for each row
85 set new.f1 = 'trig 1_2-yes';
86 update t1 set f1 = 'update-yes' where f1 like '%trig%';
87 select f1 from t1 order by f1;
88
89 connection default;
90 select current_user;
91 grant trigger on priv_db.* to test_yesprivs@localhost
92 with grant option;
93
94 # user has not super privilege->fail:
95 connection yes_privs;
96 select current_user;
97 show grants;
98 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
99 create definer=not_ex_user@localhost trigger trg1_3
100 after UPDATE on t1 for each row
101 set @var1 = 'trig 1_3-yes';
102
103 connection default;
104 select current_user;
105
106 # Cleanup prepare
107 --disable_warnings
108 disconnect yes_privs;
109
110 connection default;
111 select current_user;
112 --enable_warnings
113
114
115 # general Cleanup
116 --disable_warnings
117 drop database if exists priv_db;
118 drop user test_yesprivs@localhost;
119 --enable_warnings
120