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 db and table level of Triggers #
12
#########################################################
14
# General setup to be used in all testcases
15
let $message= ####### Testcase for mix of db and table level: #######;
16
--source include/show_msg.inc
19
drop database if exists priv1_db;
20
drop database if exists priv2_db;
22
create database priv1_db;
23
create database priv2_db;
25
eval create table t1 (f1 char(20)) engine= $engine_type;
26
eval create table t2 (f1 char(20)) engine= $engine_type;
28
eval create table t1 (f1 char(20)) engine= $engine_type;
30
create User test_yesprivs@localhost;
31
set password for test_yesprivs@localhost = password('PWD');
32
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
33
grant ALL on priv1_db.* to test_yesprivs@localhost;
34
grant SELECT,UPDATE on priv2_db.* to test_yesprivs@localhost;
35
show grants for test_yesprivs@localhost;
37
create User test_noprivs@localhost;
38
set password for test_noprivs@localhost = password('PWD');
39
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
40
grant SELECT,INSERT,UPDATE on priv1_db.* to test_noprivs@localhost;
41
grant SELECT,INSERT on priv2_db.* to test_noprivs@localhost;
42
show grants for test_noprivs@localhost;
44
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
45
connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
47
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
48
connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
51
# trigger priv on db level->create trigger for all tables successful
53
let $message= trigger privilege on one db1 db level, not on db2;
54
--source include/show_msg.inc
58
create trigger trg1_1 before INSERT on t1 for each row
59
set new.f1 = 'trig 1_1-yes';
60
create trigger trg2_1 before INSERT on t2 for each row
61
set new.f1 = 'trig 2_1-yes';
63
--error ER_TABLEACCESS_DENIED_ERROR
64
create trigger trg1_1 before INSERT on t1 for each row
65
set new.f1 = 'trig1_1-yes';
69
insert into t1 (f1) values ('insert1_no');
70
select f1 from t1 order by f1;
71
insert into t2 (f1) values ('insert1_no');
72
select f1 from t2 order by f1;
73
insert into priv2_db.t1 (f1) values ('insert21-yes');
74
select f1 from priv2_db.t1 order by f1;
77
insert into t1 (f1) values ('insert1_yes');
78
select f1 from t1 order by f1;
79
insert into priv1_db.t1 (f1) values ('insert11-no');
80
select f1 from priv1_db.t1 order by f1;
81
insert into priv1_db.t2 (f1) values ('insert22-no');
82
select f1 from priv1_db.t2 order by f1;
84
# revoke trigger priv on table level, that doesn't exists->fail
86
let $message= revoke trigger privilege on table level (not existing);
87
--source include/show_msg.inc
91
--error ER_NONEXISTING_TABLE_GRANT
92
revoke TRIGGER on priv1_db.t1 from test_yesprivs@localhost;
93
show grants for test_yesprivs@localhost;
103
# revoke the db level->create/drop/use trigger fail
108
revoke TRIGGER on priv1_db.* from test_yesprivs@localhost;
110
################ Section 3.5.3 ############
111
# Check for the table level of Triggers #
112
###########################################
113
let $message= no trigger privilege on table level for create:;
114
--source include/show_msg.inc
116
connection yes_privs;
119
--error ER_TABLEACCESS_DENIED_ERROR
120
create trigger trg1_1 before INSERT on t1 for each row
121
set new.f1 = 'trig 1_1-no';
126
grant TRIGGER on priv1_db.t1 to test_yesprivs@localhost;
127
show grants for test_yesprivs@localhost;
129
let $message= trigger privilege on table level for create:;
130
--source include/show_msg.inc
131
connection yes_privs;
134
create trigger trg1_2 before INSERT on t1 for each row
135
set new.f1 = 'trig 1_2-yes';
136
--error ER_TABLEACCESS_DENIED_ERROR
137
create trigger trg2_1 before INSERT on t2 for each row
138
set new.f1 = 'trig 2_1-no';
142
# need 'use db' to get the newest privileges
144
insert into t1 (f1) values ('insert2-no');
145
select f1 from t1 order by f1;
146
insert into t2 (f1) values ('insert2-yes');
147
select f1 from t2 order by f1;
148
insert into priv2_db.t1 (f1) values ('insert22-yes');
149
select f1 from priv2_db.t1 order by f1;
153
grant TRIGGER on priv1_db.* to test_yesprivs@localhost;
154
show grants for test_yesprivs@localhost;
156
# though granted on db level->create trigger fails (no use db)
158
connection yes_privs;
160
--error ER_TABLEACCESS_DENIED_ERROR
161
create trigger trg2_1 before INSERT on t2 for each row
162
set new.f1 = 'trig 2_1-yes';
164
# grant trigger takes effect
167
create trigger trg2_1 before INSERT on t2 for each row
168
set new.f1 = 'trig 2_1-yes';
173
insert into t1 (f1) values ('insert3-no');
174
select f1 from t1 order by f1;
175
insert into t2 (f1) values ('insert3-no');
176
select f1 from t2 order by f1;
178
insert into priv1_db.t1 (f1) values ('insert12-no');
179
select f1 from priv1_db.t1 order by f1;
180
insert into priv1_db.t2 (f1) values ('insert23-no');
181
select f1 from priv1_db.t2 order by f1;
185
connection yes_privs;
191
# Cleanup table level
193
disconnect yes_privs;
200
drop database if exists priv1_db;
201
drop database if exists priv2_db;
202
drop user test_yesprivs@localhost;
203
drop user test_noprivs@localhost;