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