Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_03e_columns.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 column privileges of Triggers #
12 #########################################################
13
14 # General setup to be used in all testcases
15 let $message= ####### Testcase for column privileges of triggers: #######;
16 --source include/show_msg.inc
17
18 --disable_warnings
19 drop database if exists priv_db;
20 drop database if exists no_priv_db;
21 --enable_warnings
22 create database priv_db;
23 use priv_db;
24 eval create table t1 (f1 char(20)) engine= $engine_type;
25 eval create table t2 (f1 char(20)) engine= $engine_type;
26
27 create User test_yesprivs@localhost;
28 set password for test_yesprivs@localhost = password('PWD');
29 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
30 grant TRIGGER on priv_db.* to test_yesprivs@localhost;
31 show grants for test_yesprivs@localhost;
32
33 create User test_noprivs@localhost;
34 set password for test_noprivs@localhost = password('PWD');
35 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
36 grant SELECT,UPDATE on priv_db.* to test_noprivs@localhost;
37 show grants for test_noprivs@localhost;
38
39 connect (yes_privs,localhost,test_yesprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK);
40 # next is to check that we connected above
41 connection yes_privs;
42
43 connect (no_privs,localhost,test_noprivs,PWD,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK);
44 # next is to check that we connected above
45 connection no_privs;
46
47 # grant TRIGGER and UPDATE on column -> succeed
48
49 let $message= update only on column:;
50 --source include/show_msg.inc
51
52 connection default;
53 select current_user;
54 grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t1
55 to test_yesprivs@localhost;
56 grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t2
57 to test_yesprivs@localhost;
58
59 connection yes_privs;
60 select current_user;
61 use priv_db;
62 insert into t1 (f1) values ('insert1-yes');
63 insert into t2 (f1) values ('insert1-yes');
64 create trigger trg1_1 before UPDATE on t1 for each row
65 set new.f1 = 'trig 1_1-yes';
66 create trigger trg2_1 before UPDATE on t2 for each row
67 set new.f1 = 'trig 2_1-yes';
68
69 connection no_privs;
70 select current_user;
71 use priv_db;
72 select f1 from t1 order by f1;
73 update t1 set f1 = 'update1_no'
74 where f1 like '%insert%';
75 select f1 from t1 order by f1;
76 select f1 from t2 order by f1;
77 update t2 set f1 = 'update1_no'
78 where f1 like '%insert%';
79 select f1 from t2 order by f1;
80
81 connection default;
82 select current_user;
83 revoke UPDATE on priv_db.*
84 from test_yesprivs@localhost;
85 revoke UPDATE(f1) on priv_db.t2
86 from test_yesprivs@localhost;
87 show grants for test_yesprivs@localhost;
88
89 connection yes_privs;
90 select current_user;
91 use priv_db;
92 insert into t1 (f1) values ('insert2-yes');
93 insert into t2 (f1) values ('insert2-yes');
94
95 connection no_privs;
96 select current_user;
97 use priv_db;
98 update t1 set f1 = 'update2_no'
99 where f1 like '%insert%';
100 --error ER_COLUMNACCESS_DENIED_ERROR
101 update t2 set f1 = 'update2_no'
102 where f1 like '%insert%';
103 update t1 set f1 = 'update3_no'
104 where f1 like '%insert%';
105 --error ER_COLUMNACCESS_DENIED_ERROR
106 update t2 set f1 = 'update3_no'
107 where f1 like '%insert%';
108 select f1 from t1 order by f1;
109 select f1 from t2 order by f1;
110
111 # check with three columns
112 let $message= check if access only on one of three columns;
113 --source include/show_msg.inc
114
115 connection default;
116 select current_user;
117 alter table priv_db.t1 add f2 char(20), add f3 int;
118 revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
119 grant TRIGGER,SELECT on priv_db.t1 to test_yesprivs@localhost;
120 grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
121
122 connection yes_privs;
123 select current_user;
124 use priv_db;
125 insert into t1 values ('insert2-yes','insert2-yes',1);
126 insert into t1 values ('insert3-yes','insert3-yes',2);
127 select * from t1 order by f1;
128
129 connection no_privs;
130 select current_user;
131 use priv_db;
132 update t1 set f1 = 'update4-no',
133 f2 = 'update4-yes',
134 f3 = f3*10
135 where f2 like '%yes';
136 select * from t1 order by f1,f2,f3;
137
138 connection yes_privs;
139 select current_user;
140 create trigger trg1_2 after UPDATE on t1 for each row
141 set @f2 = 'trig 1_2-yes';
142
143 connection no_privs;
144 select current_user;
145 update t1 set f1 = 'update5-yes',
146 f2 = 'update5-yes'
147 where f2 like '%yes';
148 select * from t1 order by f1,f2,f3;
149 select @f2;
150
151 update t1 set f1 = 'update6_no'
152 where f1 like '%insert%';
153 --error ER_TABLEACCESS_DENIED_ERROR
154 update t2 set f1 = 'update6_no'
155 where f1 like '%insert%';
156 update t1 set f1 = 'update7_no'
157 where f1 like '%insert%';
158 --error ER_TABLEACCESS_DENIED_ERROR
159 update t2 set f1 = 'update7_no'
160 where f1 like '%insert%';
161 select f1 from t1 order by f1;
162 select f1 from t2 order by f1;
163
164 # check with three columns
165
166
167 # check if update is rejected without trigger privilege
168
169 let $message= check if rejected without trigger privilege:;
170 --source include/show_msg.inc
171
172 connection default;
173 select current_user;
174 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
175
176 connection no_privs;
177 select current_user;
178 --error ER_TABLEACCESS_DENIED_ERROR
179 update t1 set f1 = 'update8-no',
180 f2 = 'update8-no'
181 where f2 like '%yes';
182 select * from t1 order by f1,f2,f3;
183 select @f2;
184
185 # check trigger, but not update privilege on column
186
187 let $message= check trigger, but not update privilege on column:;
188 --source include/show_msg.inc
189
190 connection default;
191 select current_user;
192 revoke UPDATE(f1) on priv_db.t1 from test_yesprivs@localhost;
193 grant TRIGGER,UPDATE(f2),UPDATE(f3) on priv_db.t1
194 to test_yesprivs@localhost;
195 show grants for test_yesprivs@localhost;
196
197 connection yes_privs;
198 select current_user;
199 use priv_db;
200 drop trigger trg1_1;
201 create trigger trg1_3 before UPDATE on t1 for each row
202 set new.f1 = 'trig 1_3-yes';
203
204 connection no_privs;
205 select current_user;
206 use priv_db;
207 --error ER_COLUMNACCESS_DENIED_ERROR
208 update t1 set f1 = 'update9-no',
209 f2 = 'update9-no'
210 where f2 like '%yes';
211 select * from t1 order by f1,f2,f3;
212
213 # trigger is involved (table privilege) ->fail
214 --error ER_COLUMNACCESS_DENIED_ERROR
215 update t1 set f3= f3+1;
216 select f3 from t1 order by f3;
217
218 connection default;
219 select current_user;
220 revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
221 grant UPDATE(f1),UPDATE(f2),UPDATE(f3) on priv_db.t1
222 to test_yesprivs@localhost;
223 show grants for test_yesprivs@localhost;
224
225 # trigger is involved (table privilege) ->fail
226 connection no_privs;
227 select current_user;
228 use priv_db;
229 --error ER_TABLEACCESS_DENIED_ERROR
230 update t1 set f3= f3+1;
231 select f3 from t1 order by f3;
232
233 let $message= ##### trigger privilege on column level? #######;
234 --source include/show_msg.inc
235 --error ER_PARSE_ERROR
236 grant TRIGGER(f1) on priv_db.t1 to test_yesprivs@localhost;
237
238 # Cleanup table level
239 --disable_warnings
240 disconnect yes_privs;
241 disconnect no_privs;
242
243 connection default;
244 select current_user;
245
246
247 # general Cleanup
248 drop database if exists priv_db;
249 drop user test_yesprivs@localhost;
250 drop user test_noprivs@localhost;
251 --enable_warnings
252