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
# basic tests for the db level of Triggers #
12
############################################
14
# General setup to be used in all testcases
15
let $message= Testcase for 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
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 select 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,insert on priv_db.* to test_noprivs@localhost;
37
show grants for test_noprivs@localhost;
39
# no trigger privilege->create trigger must fail:
40
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
41
connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
42
let $message= no trigger privilege on db level for create:;
43
--source include/show_msg.inc
46
create trigger trg1_1 before INSERT on t1 for each row
47
set new.f1 = 'trig 1_1-no';
49
# user with minimum privs on t1->no trigger executed;
50
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
51
connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
53
insert into t1 (f1) values ('insert-yes');
54
select f1 from t1 order by f1;
58
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
59
show grants for test_yesprivs@localhost;
61
# user got trigger privilege->create successful:
62
let $message= trigger privilege on db level for create:;
63
--source include/show_msg.inc
67
create trigger trg1_2 before INSERT on t1 for each row
68
set new.f1 = 'trig 1_2-yes';
70
# user with minimum privs on t1->fail,as trigger definer no update priv:;
74
insert into t1 (f1) values ('insert-yes');
75
select f1 from t1 order by f1;
79
grant UPDATE on priv_db.* to test_yesprivs@localhost;
80
# succeed,as trigger definer has update privilege:
81
# new privilege take effect after 'use db':
83
insert into t1 (f1) values ('insert-no');
84
select f1 from t1 order by f1;
90
insert into t1 (f1) values ('insert-yes');
91
select f1 from t1 order by f1;
95
revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
96
show grants for test_yesprivs@localhost;
98
# drop must fail, as no trigger privilege:
99
let $message= no trigger privilege on db level for drop:;
100
--source include/show_msg.inc
101
connection yes_privs;
110
# no trigger privilege at activation time:
111
let $message= no trigger privilege at activation time:;
112
--source include/show_msg.inc
114
insert into t1 (f1) values ('insert-yes');
115
select f1 from t1 order by f1;
117
let $message= trigger privilege at activation time:;
118
--source include/show_msg.inc
121
grant TRIGGER on priv_db.* to test_yesprivs@localhost;
123
# succeed, as trigger privilege at activation time:
127
insert into t1 (f1) values ('insert-no');
128
select f1 from t1 order by f1;
129
# drop must fail, as no 'use db' executed:
130
let $message= trigger privilege on db level for drop:;
131
--source include/show_msg.inc
132
connection yes_privs;
134
show grants for test_yesprivs@localhost;
139
let $message= takes effect after use priv_db:;
140
--source include/show_msg.inc
147
insert into t1 (f1) values ('insert-yes');
148
select f1 from t1 order by f1;
150
let $message= switch to db without having trigger priv for it:;
151
--source include/show_msg.inc
153
eval create table t1 (f1 char(20)) engine= $engine_type;
154
# Adding the minimal priv to be able to set to the db
155
grant SELECT,UPDATE on no_priv_db.* to test_yesprivs@localhost;
156
show grants for test_yesprivs@localhost;
158
# trigger privilege is hold over changes between priv and no priv db:
159
let $message= use db with trigger privilege on db level and without...:;
160
--source include/show_msg.inc
161
connection yes_privs;
165
create trigger trg1_3 before INSERT on t1 for each row
166
set new.f1 = 'trig 1_3-no';
168
create trigger trg1_3 before INSERT on t1 for each row
169
set new.f1 = 'trig 1_3-yes';
172
create trigger trg1_4 before UPDATE on t1 for each row
173
set new.f1 = 'trig 1_4-no';
175
create trigger trg1_4 before UPDATE on t1 for each row
176
set new.f1 = 'trig 1_4-yes';
181
insert into t1 (f1) values ('insert-yes');
182
select f1 from t1 order by f1;
184
insert into t1 (f1) values ('insert-no');
185
select f1 from t1 order by f1;
190
connection yes_privs;
206
disconnect yes_privs;
210
drop table priv_db.t1;
211
drop table no_priv_db.t1;
216
drop database if exists priv_db;
217
drop database if exists no_priv_db;
218
drop user test_yesprivs@localhost;
219
drop user test_noprivs@localhost;