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