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 mix of user and db level of Triggers #
12
#########################################################
14
# General setup to be used in all testcases
15
let $message= #### Testcase for mix of user(global) and db level: ####;
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;
23
create database no_priv_db;
25
eval create table t1 (f1 char(20)) engine= $engine_type;
27
eval create table t1 (f1 char(20)) engine= $engine_type;
29
create User test_yesprivs@localhost;
30
set password for test_yesprivs@localhost = password('PWD');
31
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
32
grant ALL on *.* to test_yesprivs@localhost;
33
show grants for test_yesprivs@localhost;
35
create User test_noprivs@localhost;
36
set password for test_noprivs@localhost = password('PWD');
37
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
38
grant SELECT,INSERT on *.* to test_noprivs@localhost;
39
show grants for test_noprivs@localhost;
41
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
42
connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
44
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
45
connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
49
let $message= trigger privilege on user level for create:;
50
--source include/show_msg.inc
53
create trigger trg1_1 before INSERT on t1 for each row
54
set new.f1 = 'trig 1_1-yes';
55
insert into t1 (f1) values ('insert-no');
56
select f1 from t1 order by f1;
59
create trigger priv_db.trg1_5 before UPDATE on priv_db.t1
61
set new.f1 = 'trig 1_5-yes';
62
insert into priv_db.t1 (f1) values ('insert-no');
63
select f1 from priv_db.t1 order by f1;
64
drop trigger priv_db.trg1_5;
69
insert into t1 (f1) values ('insert-no');
70
select f1 from t1 order by f1;
75
insert into t1 (f1) values ('insert-no');
76
select f1 from t1 order by f1;
78
revoke TRIGGER on *.* from test_yesprivs@localhost;
79
show grants for test_yesprivs@localhost;
81
# change of privilege only active after reconnecting the session
86
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
87
connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
91
select * from information_schema.triggers;
101
################ Section 3.5.3 ############
102
# Check for the db level of Triggers #
103
###########################################
104
let $message= no trigger privilege on db level for create:;
105
--source include/show_msg.inc
107
connection yes_privs;
110
create trigger trg1_1 before INSERT on t1 for each row
111
set new.f1 = 'trig 1_1-no';
116
insert into t1 (f1) values ('insert-yes');
117
select f1 from t1 order by f1;
121
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
122
show grants for test_yesprivs@localhost;
124
let $message= trigger privilege on db level for create:;
125
--source include/show_msg.inc
126
connection yes_privs;
129
# active after 'use db'
132
create trigger trg1_2 before INSERT on t1 for each row
133
set new.f1 = 'trig 1_2-yes';
135
create trigger no_priv_db.trg1_9 before insert on no_priv_db.t1
137
set new.f1 = 'trig 1_9-yes';
140
create trigger trg1_2 before INSERT on t1 for each row
141
set new.f1 = 'trig 1_2-no';
142
create trigger priv_db.trg1_9 before UPDATE on priv_db.t1
144
set new.f1 = 'trig 1_9-yes';
149
insert into t1 (f1) values ('insert-yes');
150
select f1 from t1 order by f1;
152
insert into t1 (f1) values ('insert-yes');
153
select f1 from t1 order by f1;
155
drop trigger priv_db.trg1_9;
159
drop trigger priv_db.trg1_9;
160
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
163
insert into t1 (f1) values ('insert-yes');
164
select f1 from t1 order by f1;
165
grant TRIGGER on *.* to test_yesprivs@localhost;
166
show grants for test_yesprivs@localhost;
168
connection yes_privs;
172
create trigger trg1_2 before INSERT on t1 for each row
173
set new.f1 = 'trig 1_2-no';
178
insert into t1 (f1) values ('insert-no');
179
select f1 from t1 order by f1;
181
insert into t1 (f1) values ('insert-yes');
182
select f1 from t1 order by f1;
185
disconnect yes_privs;
187
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
188
connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
191
create trigger trg1_2 before INSERT on t1 for each row
192
set new.f1 = 'trig 1_2-yes';
194
disconnect yes_privs;
200
insert into t1 (f1) values ('insert-no');
201
select f1 from t1 order by f1;
203
insert into t1 (f1) values ('insert-no');
204
select f1 from t1 order by f1;
208
# Cleanup table level
213
drop database if exists priv_db;
214
drop database if exists no_priv_db;
215
drop database if exists h1;
216
drop user test_yesprivs@localhost;
217
drop user test_noprivs@localhost;