1
#======================================================================
4
# test cases for TRIGGER privilege on db, table and column level
5
#======================================================================
7
--disable_abort_on_error
9
#########################################################
10
################ Section 3.5.3 ##########################
11
# Check for column privileges of Triggers #
12
#########################################################
14
# General setup to be used in all testcases
15
let $message= ####### Testcase for column privileges of triggers: #######;
16
--source include/show_msg.inc
19
drop database if exists priv_db;
20
drop database if exists no_priv_db;
22
create database priv_db;
24
eval create table t1 (f1 char(20)) engine= $engine_type;
25
eval create table t2 (f1 char(20)) engine= $engine_type;
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;
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;
39
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
40
connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
42
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
43
connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
45
# grant TRIGGER and UPDATE on column -> succeed
47
let $message= update only on column:;
48
--source include/show_msg.inc
52
grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t1
53
to test_yesprivs@localhost;
54
grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t2
55
to test_yesprivs@localhost;
60
insert into t1 (f1) values ('insert1-yes');
61
insert into t2 (f1) values ('insert1-yes');
62
create trigger trg1_1 before UPDATE on t1 for each row
63
set new.f1 = 'trig 1_1-yes';
64
create trigger trg2_1 before UPDATE on t2 for each row
65
set new.f1 = 'trig 2_1-yes';
70
select f1 from t1 order by f1;
71
update t1 set f1 = 'update1_no'
72
where f1 like '%insert%';
73
select f1 from t1 order by f1;
74
select f1 from t2 order by f1;
75
update t2 set f1 = 'update1_no'
76
where f1 like '%insert%';
77
select f1 from t2 order by f1;
81
revoke UPDATE on priv_db.*
82
from test_yesprivs@localhost;
83
revoke UPDATE(f1) on priv_db.t2
84
from test_yesprivs@localhost;
85
show grants for test_yesprivs@localhost;
90
insert into t1 (f1) values ('insert2-yes');
91
insert into t2 (f1) values ('insert2-yes');
96
update t1 set f1 = 'update2_no'
97
where f1 like '%insert%';
99
update t2 set f1 = 'update2_no'
100
where f1 like '%insert%';
101
update t1 set f1 = 'update3_no'
102
where f1 like '%insert%';
104
update t2 set f1 = 'update3_no'
105
where f1 like '%insert%';
106
select f1 from t1 order by f1;
107
select f1 from t2 order by f1;
109
# check with three columns
110
let $message= check if access only on one of three columns;
111
--source include/show_msg.inc
115
alter table priv_db.t1 add f2 char(20), add f3 int;
116
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
117
grant TRIGGER,SELECT on priv_db.t1 to test_yesprivs@localhost;
118
grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
120
connection yes_privs;
123
insert into t1 values ('insert2-yes','insert2-yes',1);
124
insert into t1 values ('insert3-yes','insert3-yes',2);
125
select * from t1 order by f1;
130
update t1 set f1 = 'update4-no',
133
where f2 like '%yes';
134
select * from t1 order by f1,f2,f3;
136
connection yes_privs;
138
create trigger trg1_2 after UPDATE on t1 for each row
139
set @f2 = 'trig 1_2-yes';
143
update t1 set f1 = 'update5-yes',
145
where f2 like '%yes';
146
select * from t1 order by f1,f2,f3;
149
update t1 set f1 = 'update6_no'
150
where f1 like '%insert%';
152
update t2 set f1 = 'update6_no'
153
where f1 like '%insert%';
154
update t1 set f1 = 'update7_no'
155
where f1 like '%insert%';
157
update t2 set f1 = 'update7_no'
158
where f1 like '%insert%';
159
select f1 from t1 order by f1;
160
select f1 from t2 order by f1;
162
# check with three columns
165
# check if update is rejected without trigger privilege
167
let $message= check if rejected without trigger privilege:;
168
--source include/show_msg.inc
172
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
177
update t1 set f1 = 'update8-no',
179
where f2 like '%yes';
180
select * from t1 order by f1,f2,f3;
183
# check trigger, but not update privilege on column
185
let $message= check trigger, but not update privilege on column:;
186
--source include/show_msg.inc
190
revoke UPDATE(f1) on priv_db.t1 from test_yesprivs@localhost;
191
grant TRIGGER,UPDATE(f2),UPDATE(f3) on priv_db.t1
192
to test_yesprivs@localhost;
193
show grants for test_yesprivs@localhost;
195
connection yes_privs;
199
create trigger trg1_3 before UPDATE on t1 for each row
200
set new.f1 = 'trig 1_3-yes';
206
update t1 set f1 = 'update9-no',
208
where f2 like '%yes';
209
select * from t1 order by f1,f2,f3;
211
# trigger is involved (table privilege) ->fail
213
update t1 set f3= f3+1;
214
select f3 from t1 order by f3;
218
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
219
grant UPDATE(f1),UPDATE(f2),UPDATE(f3) on priv_db.t1
220
to test_yesprivs@localhost;
221
show grants for test_yesprivs@localhost;
223
# trigger is involved (table privilege) ->fail
228
update t1 set f3= f3+1;
229
select f3 from t1 order by f3;
231
let $message= ##### trigger privilege on column level? #######;
232
--source include/show_msg.inc
234
grant TRIGGER(f1) on priv_db.t1 to test_yesprivs@localhost;
236
# Cleanup table level
238
disconnect yes_privs;
246
drop database if exists priv_db;
247
drop user test_yesprivs@localhost;
248
drop user test_noprivs@localhost;