Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_03e_table_level.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 db level of Triggers #
12 ###########################################
13
14 # General setup to be used in all testcases
15 let $message= ######### Testcase for table level: ########;
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 charset utf8mb4;
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 = 'PWD';
27 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
28
29 create User test_noprivs@localhost;
30 set password for test_noprivs@localhost = 'PWD';
31 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
32
33 connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
34
35 connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
36
37 ################ Section 3.5.3 ############
38 # Check for the table level of Triggers #
39 ###########################################
40
41 # user has no trigger privilege->create trigger fail
42
43 let $message= no trigger privilege on table level for create:;
44 --source include/show_msg.inc
45 connection default;
46 select current_user;
47 --replace_column 6 #
48 show triggers;
49 grant select, insert, update on priv_db.t1 to test_yesprivs@localhost;
50 show grants for test_yesprivs@localhost;
51 grant select, update, insert on priv_db.t1 to test_noprivs@localhost;
52 show grants for test_noprivs@localhost;
53
54 connection yes_privs;
55 select current_user;
56 use priv_db;
57 show tables;
58 --error ER_TABLEACCESS_DENIED_ERROR
59 create trigger trg1_1 before INSERT on t1 for each row
60 set new.f1 = 'trig 1_1-no';
61
62 # no trigger execution, as trigger does'nt exist
63
64 connection no_privs;
65 select current_user;
66 use priv_db;
67 insert into t1 (f1) values ('insert1-yes');
68 select f1 from t1 order by f1;
69
70 connection default;
71 select current_user;
72 --replace_column 6 #
73 show triggers;
74 show tables;
75 insert into t1 (f1) values ('insert2-yes');
76 select f1 from t1 order by f1;
77 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
78 show grants for test_yesprivs@localhost;
79
80 # user got trigger privilege->create trigger successful
81
82 let $message= trigger privilege on table level for create:;
83 --source include/show_msg.inc
84 connection yes_privs;
85 select current_user;
86 --replace_column 6 #
87 show triggers;
88 create trigger trg1_2 before INSERT on t1 for each row
89 set new.f1 = 'trig 1_2-yes';
90
91 # insert now executes the trigger
92
93 connection no_privs;
94 select current_user;
95 insert into t1 (f1) values ('insert3-no');
96 select f1 from t1 order by f1;
97
98 connection default;
99 select current_user;
100 insert into t1 (f1) values ('insert4-no');
101 select f1 from t1 order by f1;
102 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
103 show grants for test_yesprivs@localhost;
104
105 # revoke triggerprivilege->drop trigger fail
106
107 let $message= no trigger privilege on table level for drop:;
108 --source include/show_msg.inc
109 connection yes_privs;
110 select current_user;
111
112 --error ER_TABLEACCESS_DENIED_ERROR
113 drop trigger trg1_2;
114
115 # no trigger priv at activation time->insert fails
116
117 let $message= no trigger privilege at activation time:;
118 --source include/show_msg.inc
119 connection no_privs;
120 select current_user;
121 --error ER_TABLEACCESS_DENIED_ERROR
122 insert into t1 (f1) values ('insert5-no');
123 select f1 from t1 order by f1;
124
125 connection default;
126 select current_user;
127 grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
128
129 # trigger privilege at activation time->insert with trigger successful
130
131 let $message= trigger privilege at activation time:;
132 --source include/show_msg.inc
133 connection no_privs;
134 select current_user;
135 insert into t1 (f1) values ('insert6-no');
136 select f1 from t1 order by f1;
137
138 # trigger privilege->drop trigger successful
139 let $message= trigger privilege on table level for drop:;
140 --source include/show_msg.inc
141 connection yes_privs;
142 select current_user;
143 show grants for test_yesprivs@localhost;
144 drop trigger trg1_2;
145
146 # inserts without trigger
147
148 connection no_privs;
149 select current_user;
150 insert into t1 (f1) values ('insert7-yes');
151 select f1 from t1 order by f1;
152
153 connection default;
154 select current_user;
155 insert into t1 (f1) values ('insert8-yes');
156 select f1 from t1 order by f1;
157
158 # trigger privilege must be keep when mixinf tables with and without
159 # trigger privilege
160
161 let $message= switch to table without having trigger priv for it:;
162 --source include/show_msg.inc
163 eval create table t2 (f1 char(20)) engine= $engine_type;
164 # Adding the minimal priv to be able to set to the db
165 grant SELECT, INSERT, UPDATE on priv_db.t2 to test_yesprivs@localhost;
166 show grants for test_yesprivs@localhost;
167
168 grant SELECT, INSERT, UPDATE on priv_db.t2 to test_noprivs@localhost;
169 show grants for test_noprivs@localhost;
170
171 let $message= use table with trigger privilege and without...:;
172 --source include/show_msg.inc
173 connection yes_privs;
174 select current_user;
175 --error ER_TABLEACCESS_DENIED_ERROR
176 create trigger trg2_1 before INSERT on t2 for each row
177 set new.f1 = 'trig 2_1-no';
178 create trigger trg1_3 before INSERT on t1 for each row
179 set new.f1 = 'trig 1_3-yes';
180 --error ER_TABLEACCESS_DENIED_ERROR
181 create trigger trg2_2 before UPDATE on t2 for each row
182 set new.f1 = 'trig 2_2-no';
183 create trigger trg1_4 before UPDATE on t1 for each row
184 set new.f1 = 'trig 1_4-yes';
185 --replace_column 6 #
186 show triggers;
187 connection no_privs;
188 select current_user;
189 insert into t2 (f1) values ('insert9-yes');
190 select f1 from t2 order by f1;
191 insert into t1 (f1) values ('insert10-no');
192 select f1 from t1 order by f1;
193 disconnect no_privs;
194
195 connection yes_privs;
196 select current_user;
197 --error ER_TRG_DOES_NOT_EXIST
198 drop trigger trg2_1;
199 drop trigger trg1_3;
200 --error ER_TRG_DOES_NOT_EXIST
201 drop trigger trg2_2;
202 drop trigger trg1_4;
203
204
205 # Cleanup table level
206 --disable_warnings
207 disconnect yes_privs;
208
209 connection default;
210 select current_user;
211 --enable_warnings
212
213
214 # general Cleanup
215 --disable_warnings
216 drop database if exists priv_db;
217 drop user test_yesprivs@localhost;
218 drop user test_noprivs@localhost;
219 --enable_warnings
220