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 the db level of Triggers #
12
###########################################
14
# General setup to be used in all testcases
15
let $message= ######### Testcase for table level: ########;
16
--source include/show_msg.inc
19
drop database if exists priv_db;
21
create database priv_db;
23
eval create table t1 (f1 char(20)) engine= $engine_type;
25
create User test_yesprivs@localhost;
26
set password for test_yesprivs@localhost = password('PWD');
27
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
29
create User test_noprivs@localhost;
30
set password for test_noprivs@localhost = password('PWD');
31
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
34
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
35
connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
37
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
38
connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
40
################ Section 3.5.3 ############
41
# Check for the table level of Triggers #
42
###########################################
44
# user has no trigger privilege->create trigger fail
46
let $message= no trigger privilege on table level for create:;
47
--source include/show_msg.inc
51
grant select, insert, update on priv_db.t1 to test_yesprivs@localhost;
52
show grants for test_yesprivs@localhost;
53
grant select, update, insert on priv_db.t1 to test_noprivs@localhost;
54
show grants for test_noprivs@localhost;
61
create trigger trg1_1 before INSERT on t1 for each row
62
set new.f1 = 'trig 1_1-no';
64
# no trigger execution, as trigger does'nt exist
69
insert into t1 (f1) values ('insert1-yes');
70
select f1 from t1 order by f1;
76
insert into t1 (f1) values ('insert2-yes');
77
select f1 from t1 order by f1;
78
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
79
show grants for test_yesprivs@localhost;
81
# user got trigger privilege->create trigger successful
83
let $message= trigger privilege on table level for create:;
84
--source include/show_msg.inc
88
create trigger trg1_2 before INSERT on t1 for each row
89
set new.f1 = 'trig 1_2-yes';
91
# insert now executes the trigger
95
insert into t1 (f1) values ('insert3-no');
96
select f1 from t1 order by f1;
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;
105
# revoke triggerprivilege->drop trigger fail
107
let $message= no trigger privilege on table level for drop:;
108
--source include/show_msg.inc
109
connection yes_privs;
115
# no trigger priv at activation time->insert fails
117
let $message= no trigger privilege at activation time:;
118
--source include/show_msg.inc
122
insert into t1 (f1) values ('insert5-no');
123
select f1 from t1 order by f1;
127
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
129
# trigger privilege at activation time->insert with trigger successful
131
let $message= trigger privilege at activation time:;
132
--source include/show_msg.inc
135
insert into t1 (f1) values ('insert6-no');
136
select f1 from t1 order by f1;
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;
143
show grants for test_yesprivs@localhost;
146
# inserts without trigger
150
insert into t1 (f1) values ('insert7-yes');
151
select f1 from t1 order by f1;
155
insert into t1 (f1) values ('insert8-yes');
156
select f1 from t1 order by f1;
158
# trigger privilege must be keep when mixinf tables with and without
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;
168
grant SELECT, INSERT, UPDATE on priv_db.t2 to test_noprivs@localhost;
169
show grants for test_noprivs@localhost;
171
let $message= use table with trigger privilege and without...:;
172
--source include/show_msg.inc
173
connection yes_privs;
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';
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';
188
insert into t2 (f1) values ('insert9-yes');
189
select f1 from t2 order by f1;
190
insert into t1 (f1) values ('insert10-no');
191
select f1 from t1 order by f1;
194
connection yes_privs;
204
# Cleanup table level
206
disconnect yes_privs;
215
drop database if exists priv_db;
216
drop user test_yesprivs@localhost;
217
drop user test_noprivs@localhost;