Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_03e_db_level.inc

raw
1 #======================================================================
2 #
3 # Trigger Tests
4 # test cases for TRIGGER privilege on db, table and column level
5 #======================================================================
6
7 --disable_abort_on_error
8
9 ############################################
10 ################ Section 3.5.3 #############
11 # basic tests for the db level of Triggers #
12 ############################################
13
14 # General setup to be used in all testcases
15 let $message= Testcase for db level:;
16 --source include/show_msg.inc
17
18 --disable_warnings
19 drop database if exists priv_db;
20 drop database if exists no_priv_db;
21 --enable_warnings
22 create database priv_db;
23 create database no_priv_db;
24 use priv_db;
25 eval create table t1 (f1 char(20)) engine= $engine_type;
26
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;
32
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;
38
39 # no trigger privilege->create trigger must fail:
40 connect (yes_privs,localhost,test_yesprivs,PWD,priv_db,$MASTER_MYPORT,$MASTER_MYSOCK);
41 # next is to check that we connected above
42 connection yes_privs;
43 let $message= no trigger privilege on db level for create:;
44 --source include/show_msg.inc
45 use priv_db;
46 --error ER_TABLEACCESS_DENIED_ERROR
47 create trigger trg1_1 before INSERT on t1 for each row
48 set new.f1 = 'trig 1_1-no';
49
50 # user with minimum privs on t1->no trigger executed;
51 connect (no_privs,localhost,test_noprivs,PWD,priv_db,$MASTER_MYPORT,$MASTER_MYSOCK);
52 # next is to check that we connected above
53 connection no_privs;
54 insert into t1 (f1) values ('insert-yes');
55 select f1 from t1 order by f1;
56
57 connection default;
58 select current_user;
59 grant TRIGGER on priv_db.* to test_yesprivs@localhost;
60 show grants for test_yesprivs@localhost;
61
62 # user got trigger privilege->create successful:
63 let $message= trigger privilege on db level for create:;
64 --source include/show_msg.inc
65 connection yes_privs;
66 select current_user;
67 use priv_db;
68 create trigger trg1_2 before INSERT on t1 for each row
69 set new.f1 = 'trig 1_2-yes';
70
71 # user with minimum privs on t1->fail,as trigger definer no update priv:;
72 connection no_privs;
73 select current_user;
74 use priv_db;
75 insert into t1 (f1) values ('insert-yes');
76 select f1 from t1 order by f1;
77
78 connection default;
79 select current_user;
80 grant UPDATE on priv_db.* to test_yesprivs@localhost;
81 # succeed,as trigger definer has update privilege:
82 # new privilege take effect after 'use db':
83 use priv_db;
84 insert into t1 (f1) values ('insert-no');
85 select f1 from t1 order by f1;
86
87 # succeed:
88 connection no_privs;
89 select current_user;
90 use priv_db;
91 insert into t1 (f1) values ('insert-yes');
92 select f1 from t1 order by f1;
93
94 connection default;
95 select current_user;
96 revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
97 show grants for test_yesprivs@localhost;
98
99 # drop must fail, as no trigger privilege:
100 let $message= no trigger privilege on db level for drop:;
101 --source include/show_msg.inc
102 connection yes_privs;
103 select current_user;
104 use priv_db;
105 --error ER_TABLEACCESS_DENIED_ERROR
106 drop trigger trg1_2;
107
108 connection no_privs;
109 select current_user;
110 use priv_db;
111 # no trigger privilege at activation time:
112 let $message= no trigger privilege at activation time:;
113 --source include/show_msg.inc
114 --error ER_TABLEACCESS_DENIED_ERROR
115 insert into t1 (f1) values ('insert-yes');
116 select f1 from t1 order by f1;
117
118 let $message= trigger privilege at activation time:;
119 --source include/show_msg.inc
120 connection default;
121 select current_user;
122 grant TRIGGER on priv_db.* to test_yesprivs@localhost;
123
124 # succeed, as trigger privilege at activation time:
125 connection no_privs;
126 select current_user;
127 use priv_db;
128 insert into t1 (f1) values ('insert-no');
129 select f1 from t1 order by f1;
130 # drop must fail, as no 'use db' executed:
131 let $message= trigger privilege on db level for drop:;
132 --source include/show_msg.inc
133 connection yes_privs;
134 select current_user;
135 show grants for test_yesprivs@localhost;
136 --error ER_TABLEACCESS_DENIED_ERROR
137 drop trigger trg1_2;
138
139 # succeed
140 let $message= takes effect after use priv_db:;
141 --source include/show_msg.inc
142 use priv_db;
143 drop trigger trg1_2;
144
145 connection default;
146 select current_user;
147 use priv_db;
148 insert into t1 (f1) values ('insert-yes');
149 select f1 from t1 order by f1;
150
151 let $message= switch to db without having trigger priv for it:;
152 --source include/show_msg.inc
153 use no_priv_db;
154 eval create table t1 (f1 char(20)) engine= $engine_type;
155 # Adding the minimal priv to be able to set to the db
156 grant SELECT,UPDATE on no_priv_db.* to test_yesprivs@localhost;
157 show grants for test_yesprivs@localhost;
158
159 # trigger privilege is hold over changes between priv and no priv db:
160 let $message= use db with trigger privilege on db level and without...:;
161 --source include/show_msg.inc
162 connection yes_privs;
163 select current_user;
164 use no_priv_db;
165 --error ER_TABLEACCESS_DENIED_ERROR
166 create trigger trg1_3 before INSERT on t1 for each row
167 set new.f1 = 'trig 1_3-no';
168 use priv_db;
169 create trigger trg1_3 before INSERT on t1 for each row
170 set new.f1 = 'trig 1_3-yes';
171 use no_priv_db;
172 --error ER_TABLEACCESS_DENIED_ERROR
173 create trigger trg1_4 before UPDATE on t1 for each row
174 set new.f1 = 'trig 1_4-no';
175 use priv_db;
176 create trigger trg1_4 before UPDATE on t1 for each row
177 set new.f1 = 'trig 1_4-yes';
178
179 connection no_privs;
180 select current_user;
181 use no_priv_db;
182 insert into t1 (f1) values ('insert-yes');
183 select f1 from t1 order by f1;
184 use priv_db;
185 insert into t1 (f1) values ('insert-no');
186 select f1 from t1 order by f1;
187 --disable_warnings
188 disconnect no_privs;
189 --enable_warnings
190
191 connection yes_privs;
192 select current_user;
193 use no_priv_db;
194 --error ER_TRG_DOES_NOT_EXIST
195 drop trigger trg1_3;
196 use priv_db;
197 drop trigger trg1_3;
198 use no_priv_db;
199 --error ER_TRG_DOES_NOT_EXIST
200 drop trigger trg1_4;
201 use priv_db;
202 drop trigger trg1_4;
203
204
205 # Cleanup db level
206 --disable_warnings
207 disconnect yes_privs;
208
209 connection default;
210 select current_user;
211 drop table priv_db.t1;
212 drop table no_priv_db.t1;
213 --enable_warnings
214
215 # general Cleanup
216 --disable_warnings
217 drop database if exists priv_db;
218 drop database if exists no_priv_db;
219 drop user test_yesprivs@localhost;
220 drop user test_noprivs@localhost;
221 --enable_warnings
222