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 = '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 = '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 select * from information_schema.triggers;
91 --error ER_TABLEACCESS_DENIED_ERROR
92 drop trigger trg1_1;
93
94 connection default;
95 select current_user;
96 show grants;
97 drop trigger trg1_1;
98 use priv_db;
99
100 ################ Section 3.5.3 ############
101 # Check for the db level of Triggers #
102 ###########################################
103 let $message= no trigger privilege on db level for create:;
104 --source include/show_msg.inc
105
106 connection yes_privs;
107 select current_user;
108 --error ER_TABLEACCESS_DENIED_ERROR
109 create trigger trg1_1 before INSERT on t1 for each row
110 set new.f1 = 'trig 1_1-no';
111
112 connection no_privs;
113 select current_user;
114 use priv_db;
115 insert into t1 (f1) values ('insert-yes');
116 select f1 from t1 order by f1;
117
118 connection default;
119 select current_user;
120 grant TRIGGER on priv_db.* to test_yesprivs@localhost;
121 show grants for test_yesprivs@localhost;
122
123 let $message= trigger privilege on db level for create:;
124 --source include/show_msg.inc
125 connection yes_privs;
126 select current_user;
127
128 # active after 'use db'
129
130 use priv_db;
131 create trigger trg1_2 before INSERT on t1 for each row
132 set new.f1 = 'trig 1_2-yes';
133 --error ER_TABLEACCESS_DENIED_ERROR
134 create trigger no_priv_db.trg1_9 before insert on no_priv_db.t1
135 for each row
136 set new.f1 = 'trig 1_9-yes';
137 use no_priv_db;
138 --error ER_TABLEACCESS_DENIED_ERROR
139 create trigger trg1_2 before INSERT on t1 for each row
140 set new.f1 = 'trig 1_2-no';
141 create trigger priv_db.trg1_9 before UPDATE on priv_db.t1
142 for each row
143 set new.f1 = 'trig 1_9-yes';
144
145 connection no_privs;
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 use no_priv_db;
151 insert into t1 (f1) values ('insert-yes');
152 select f1 from t1 order by f1;
153 --error ER_TABLEACCESS_DENIED_ERROR
154 drop trigger priv_db.trg1_9;
155
156 connection default;
157 select current_user;
158 drop trigger priv_db.trg1_9;
159 revoke TRIGGER on priv_db.* from test_yesprivs@localhost;
160 use priv_db;
161 --error ER_TABLEACCESS_DENIED_ERROR
162 insert into t1 (f1) values ('insert-yes');
163 select f1 from t1 order by f1;
164 grant TRIGGER on *.* to test_yesprivs@localhost;
165 show grants for test_yesprivs@localhost;
166
167 connection yes_privs;
168 select current_user;
169 use no_priv_db;
170 --error ER_TABLEACCESS_DENIED_ERROR
171 create trigger trg1_2 before INSERT on t1 for each row
172 set new.f1 = 'trig 1_2-no';
173
174 connection no_privs;
175 select current_user;
176 use priv_db;
177 insert into t1 (f1) values ('insert-no');
178 select f1 from t1 order by f1;
179 use no_priv_db;
180 insert into t1 (f1) values ('insert-yes');
181 select f1 from t1 order by f1;
182
183 --disable_warnings
184 disconnect yes_privs;
185 --enable_warnings
186 connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
187 select current_user;
188 use no_priv_db;
189 create trigger trg1_2 before INSERT on t1 for each row
190 set new.f1 = 'trig 1_2-yes';
191 --disable_warnings
192 disconnect yes_privs;
193 --enable_warnings
194
195 connection no_privs;
196 select current_user;
197 use priv_db;
198 insert into t1 (f1) values ('insert-no');
199 select f1 from t1 order by f1;
200 use no_priv_db;
201 insert into t1 (f1) values ('insert-no');
202 select f1 from t1 order by f1;
203 --disable_warnings
204 disconnect no_privs;
205
206 # Cleanup table level
207 connection default;
208 select current_user;
209
210 # general Cleanup
211 drop database if exists priv_db;
212 drop database if exists no_priv_db;
213 drop database if exists h1;
214 drop user test_yesprivs@localhost;
215 drop user test_noprivs@localhost;
216 --enable_warnings
217