1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
#======================================================================
#
# Trigger Tests
# test cases for TRIGGER privilege on db, table and column level
# These tests ensure that at activation time (execute statement)
# the user must have trigger privilege.
#======================================================================
--disable_abort_on_error
###########################################################
################ Section 3.5.3 ############################
# Check for the trigger privilege in case of prepare/exec #
###########################################################
# General setup to be used in all testcases
let $message= #### Testcase for trigger privilege on execution time ########;
--source include/show_msg.inc
--disable_warnings
drop database if exists priv_db;
--enable_warnings
create database priv_db;
use priv_db;
eval create table t1 (f1 char(20)) engine= $engine_type;
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = password('PWD');
create User test_useprivs@localhost;
set password for test_useprivs@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
revoke ALL PRIVILEGES, GRANT OPTION FROM test_useprivs@localhost;
let $MASTER_MYSOCK= query_get_value(SHOW VARIABLES LIKE 'socket', Value, 1);
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connection default;
select current_user;
show triggers;
grant select, insert, update ,trigger
on priv_db.t1 to test_yesprivs@localhost
with grant option;
grant select
on priv_db.t1 to test_useprivs@localhost;
show grants for test_yesprivs@localhost;
connection yes_privs;
select current_user;
use priv_db;
create trigger trg1_1 before INSERT on t1 for each row
set new.f1 = 'trig 1_1-yes';
grant insert on t1 to test_useprivs@localhost;
prepare ins1 from 'insert into t1 (f1) values (''insert1-no'')';
execute ins1;
select f1 from t1 order by f1;
prepare ins1 from 'insert into t1 (f1) values (''insert2-no'')';
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
connect (use_privs,localhost,test_useprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
select current_user;
use priv_db;
prepare ins1 from 'insert into t1 (f1) values (''insert3-no'')';
execute ins1;
select f1 from t1 order by f1;
connection default;
select current_user;
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
connection yes_privs;
select current_user;
--error ER_TABLEACCESS_DENIED_ERROR
execute ins1;
select f1 from t1 order by f1;
prepare ins1 from 'insert into t1 (f1) values (''insert4-no'')';
connection use_privs;
select current_user;
prepare ins1 from 'insert into t1 (f1) values (''insert5-no'')';
--error ER_TABLEACCESS_DENIED_ERROR
execute ins1;
select f1 from t1 order by f1;
connection default;
select current_user;
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
connection yes_privs;
select current_user;
execute ins1;
select f1 from t1 order by f1;
prepare ins1 from 'insert into t1 (f1) values (''insert6-no'')';
connection use_privs;
select current_user;
execute ins1;
select f1 from t1 order by f1;
prepare ins1 from 'insert into t1 (f1) values (''insert7-no'')';
connection default;
select current_user;
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
connection yes_privs;
select current_user;
--error ER_TABLEACCESS_DENIED_ERROR
execute ins1;
select f1 from t1 order by f1;
connection use_privs;
select current_user;
--error ER_TABLEACCESS_DENIED_ERROR
execute ins1;
select f1 from t1 order by f1;
connection default;
select current_user;
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
connection yes_privs;
select current_user;
execute ins1;
select f1 from t1 order by f1;
connection use_privs;
select current_user;
execute ins1;
select f1 from t1 order by f1;
connection default;
select current_user;
revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
connection yes_privs;
select current_user;
execute ins1;
select f1 from t1 order by f1;
deallocate prepare ins1;
connection use_privs;
select current_user;
execute ins1;
select f1 from t1 order by f1;
deallocate prepare ins1;
connection default;
select current_user;
grant TRIGGER on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
connection yes_privs;
select current_user;
drop trigger trg1_1;
connection default;
select current_user;
# Cleanup prepare
--disable_warnings
disconnect yes_privs;
connection default;
select current_user;
--enable_warnings
# general Cleanup
--disable_warnings
drop database if exists priv_db;
drop user test_yesprivs@localhost;
drop user test_useprivs@localhost;
--enable_warnings
|
