Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_03e_global_db_mix.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 # Check for mix of user and db level of Triggers #
12 #########################################################
13
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
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 use no_priv_db;
27 eval create table t1 (f1 char(20)) engine= $engine_type;
28
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;
34
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;
40
41 connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
42
43 connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
44
45 connection yes_privs;
46 select current_user;
47 let $message= trigger privilege on user level for create:;
48 --source include/show_msg.inc
49 use priv_db;
50
51 create trigger trg1_1 before INSERT on t1 for each row
52 set new.f1 = 'trig 1_1-yes';
53 insert into t1 (f1) values ('insert-no');
54 select f1 from t1 order by f1;
55
56 use no_priv_db;
57 create trigger priv_db.trg1_5 before UPDATE on priv_db.t1
58 for each row
59 set new.f1 = 'trig 1_5-yes';
60 insert into priv_db.t1 (f1) values ('insert-no');
61 select f1 from priv_db.t1 order by f1;
62 drop trigger priv_db.trg1_5;
63
64 connection no_privs;
65 select current_user;
66 use priv_db;
67 insert into t1 (f1) values ('insert-no');
68 select f1 from t1 order by f1;
69
70 connection default;
71 select current_user;
72 use priv_db;
73 insert into t1 (f1) values ('insert-no');
74 select f1 from t1 order by f1;
75
76 revoke TRIGGER on *.* from test_yesprivs@localhost;
77 show grants for test_yesprivs@localhost;
78
79 # change of privilege only active after reconnecting the session
80
81 --disable_warnings
82 disconnect yes_privs;
83 --enable_warnings
84 connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
85 select current_user;
86 use priv_db;
87 --replace_column 6 #
88 show triggers;
89 --replace_column 17 #
90 --sorted_result
91 select * from information_schema.triggers;
92 --error ER_TABLEACCESS_DENIED_ERROR
93 drop trigger trg1_1;
94
95 connection default;
96 select current_user;
97 show grants;
98 drop trigger trg1_1;
99 use priv_db;
100
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
106
107 connection yes_privs;
108 select current_user;
109 --error ER_TABLEACCESS_DENIED_ERROR
110 create trigger trg1_1 before INSERT on t1 for each row
111 set new.f1 = 'trig 1_1-no';
112
113 connection no_privs;
114 select current_user;
115 use priv_db;
116 insert into t1 (f1) values ('insert-yes');
117 select f1 from t1 order by f1;
118
119 connection default;
120 select current_user;
121 grant TRIGGER on priv_db.* to test_yesprivs@localhost;
122 show grants for test_yesprivs@localhost;
123
124 let $message= trigger privilege on db level for create:;
125 --source include/show_msg.inc
126 connection yes_privs;
127 select current_user;
128
129 # active after 'use db'
130
131 use priv_db;
132 create trigger trg1_2 before INSERT on t1 for each row
133 set new.f1 = 'trig 1_2-yes';
134 --error ER_TABLEACCESS_DENIED_ERROR
135 create trigger no_priv_db.trg1_9 before insert on no_priv_db.t1
136 for each row
137 set new.f1 = 'trig 1_9-yes';
138 use no_priv_db;
139 --error ER_TABLEACCESS_DENIED_ERROR
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
143 for each row
144 set new.f1 = 'trig 1_9-yes';
145
146 connection no_privs;
147 select current_user;
148 use priv_db;
149 insert into t1 (f1) values ('insert-yes');
150 select f1 from t1 order by f1;
151 use no_priv_db;
152 insert into t1 (f1) values ('insert-yes');
153 select f1 from t1 order by f1;
154 --error ER_TABLEACCESS_DENIED_ERROR
155 drop trigger priv_db.trg1_9;
156
157 connection default;
158 select current_user;
159 drop trigger priv_db.trg1_9;
160 revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
161 use priv_db;
162 --error ER_TABLEACCESS_DENIED_ERROR
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;
167
168 connection yes_privs;
169 select current_user;
170 use no_priv_db;
171 --error ER_TABLEACCESS_DENIED_ERROR
172 create trigger trg1_2 before INSERT on t1 for each row
173 set new.f1 = 'trig 1_2-no';
174
175 connection no_privs;
176 select current_user;
177 use priv_db;
178 insert into t1 (f1) values ('insert-no');
179 select f1 from t1 order by f1;
180 use no_priv_db;
181 insert into t1 (f1) values ('insert-yes');
182 select f1 from t1 order by f1;
183
184 --disable_warnings
185 disconnect yes_privs;
186 --enable_warnings
187 connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
188 select current_user;
189 use no_priv_db;
190 create trigger trg1_2 before INSERT on t1 for each row
191 set new.f1 = 'trig 1_2-yes';
192 --disable_warnings
193 disconnect yes_privs;
194 --enable_warnings
195
196 connection no_privs;
197 select current_user;
198 use priv_db;
199 insert into t1 (f1) values ('insert-no');
200 select f1 from t1 order by f1;
201 use no_priv_db;
202 insert into t1 (f1) values ('insert-no');
203 select f1 from t1 order by f1;
204 --disable_warnings
205 disconnect no_privs;
206
207 # Cleanup table level
208 connection default;
209 select current_user;
210
211 # general Cleanup
212 drop database if exists priv_db;
213 drop database if exists no_priv_db;
214 drop database if exists h1;
215 drop user test_yesprivs@localhost;
216 drop user test_noprivs@localhost;
217 --enable_warnings