Debian Codegraph @

mysql-test/suite/roles/flush_roles-12366.test

raw
1 --source include/not_embedded.inc
2 --echo #
3 --echo # MDEV-12366: FLUSH PRIVILEGES can break hierarchy of roles
4 --echo #
5 --echo # This testcase contains a user, who is granted a master role
6 --echo # operations_cluster. operations_cluster is granted 8 different roles
7 --echo # who in turn each have 4 different roles granted to them.
8 --echo #
9 --echo # Only the leaf roles contain privileges to access databases.
10 --echo # Make sure the user has access to all databases if the master role
11 --echo # is granted to him.
12 --echo #
13 CREATE USER u;
14 CREATE ROLE operations_cluster;
15 GRANT operations_cluster TO u;
16 CREATE DATABASE bob_live_sg;
17 CREATE TABLE bob_live_sg.a (i INT(10));
18 CREATE TABLE bob_live_sg.b (i INT(10));
19 CREATE TABLE bob_live_sg.c (i INT(10));
20 CREATE TABLE bob_live_sg.d (i INT(10));
21 CREATE DATABASE oms_live_sg;
22 CREATE TABLE oms_live_sg.a (i INT(10));
23 CREATE TABLE oms_live_sg.b (i INT(10));
24 CREATE TABLE oms_live_sg.c (i INT(10));
25 CREATE TABLE oms_live_sg.d (i INT(10));
26 CREATE DATABASE bob_live_ph;
27 CREATE TABLE bob_live_ph.a (i INT(10));
28 CREATE TABLE bob_live_ph.b (i INT(10));
29 CREATE TABLE bob_live_ph.c (i INT(10));
30 CREATE TABLE bob_live_ph.d (i INT(10));
31 CREATE DATABASE oms_live_ph;
32 CREATE TABLE oms_live_ph.a (i INT(10));
33 CREATE TABLE oms_live_ph.b (i INT(10));
34 CREATE TABLE oms_live_ph.c (i INT(10));
35 CREATE TABLE oms_live_ph.d (i INT(10));
36 CREATE DATABASE bob_live_id;
37 CREATE TABLE bob_live_id.a (i INT(10));
38 CREATE TABLE bob_live_id.b (i INT(10));
39 CREATE TABLE bob_live_id.c (i INT(10));
40 CREATE TABLE bob_live_id.d (i INT(10));
41 CREATE DATABASE oms_live_id;
42 CREATE TABLE oms_live_id.a (i INT(10));
43 CREATE TABLE oms_live_id.b (i INT(10));
44 CREATE TABLE oms_live_id.c (i INT(10));
45 CREATE TABLE oms_live_id.d (i INT(10));
46 CREATE DATABASE bob_live_hk;
47 CREATE TABLE bob_live_hk.a (i INT(10));
48 CREATE TABLE bob_live_hk.b (i INT(10));
49 CREATE TABLE bob_live_hk.c (i INT(10));
50 CREATE TABLE bob_live_hk.d (i INT(10));
51 CREATE DATABASE oms_live_hk;
52 CREATE TABLE oms_live_hk.a (i INT(10));
53 CREATE TABLE oms_live_hk.b (i INT(10));
54 CREATE TABLE oms_live_hk.c (i INT(10));
55 CREATE TABLE oms_live_hk.d (i INT(10));
56 CREATE DATABASE bob_live_vn;
57 CREATE TABLE bob_live_vn.a (i INT(10));
58 CREATE TABLE bob_live_vn.b (i INT(10));
59 CREATE TABLE bob_live_vn.c (i INT(10));
60 CREATE TABLE bob_live_vn.d (i INT(10));
61 CREATE DATABASE oms_live_vn;
62 CREATE TABLE oms_live_vn.a (i INT(10));
63 CREATE TABLE oms_live_vn.b (i INT(10));
64 CREATE TABLE oms_live_vn.c (i INT(10));
65 CREATE TABLE oms_live_vn.d (i INT(10));
66 CREATE DATABASE bob_live_tw;
67 CREATE TABLE bob_live_tw.a (i INT(10));
68 CREATE TABLE bob_live_tw.b (i INT(10));
69 CREATE TABLE bob_live_tw.c (i INT(10));
70 CREATE TABLE bob_live_tw.d (i INT(10));
71 CREATE DATABASE oms_live_tw;
72 CREATE TABLE oms_live_tw.a (i INT(10));
73 CREATE TABLE oms_live_tw.b (i INT(10));
74 CREATE TABLE oms_live_tw.c (i INT(10));
75 CREATE TABLE oms_live_tw.d (i INT(10));
76 CREATE DATABASE bob_live_my;
77 CREATE TABLE bob_live_my.a (i INT(10));
78 CREATE TABLE bob_live_my.b (i INT(10));
79 CREATE TABLE bob_live_my.c (i INT(10));
80 CREATE TABLE bob_live_my.d (i INT(10));
81 CREATE DATABASE oms_live_my;
82 CREATE TABLE oms_live_my.a (i INT(10));
83 CREATE TABLE oms_live_my.b (i INT(10));
84 CREATE TABLE oms_live_my.c (i INT(10));
85 CREATE TABLE oms_live_my.d (i INT(10));
86 CREATE DATABASE bob_live_th;
87 CREATE TABLE bob_live_th.a (i INT(10));
88 CREATE TABLE bob_live_th.b (i INT(10));
89 CREATE TABLE bob_live_th.c (i INT(10));
90 CREATE TABLE bob_live_th.d (i INT(10));
91 CREATE DATABASE oms_live_th;
92 CREATE TABLE oms_live_th.a (i INT(10));
93 CREATE TABLE oms_live_th.b (i INT(10));
94 CREATE TABLE oms_live_th.c (i INT(10));
95 CREATE TABLE oms_live_th.d (i INT(10));
96 CREATE ROLE a_sg;
97 CREATE ROLE b_sg;
98 CREATE ROLE c_sg;
99 CREATE ROLE d_sg;
100 CREATE ROLE operations_sg;
101 GRANT a_sg TO operations_sg;
102 GRANT b_sg TO operations_sg;
103 GRANT c_sg TO operations_sg;
104 GRANT d_sg TO operations_sg;
105 GRANT SELECT ON bob_live_sg.a TO a_sg;
106 GRANT SELECT ON bob_live_sg.b TO b_sg;
107 GRANT SELECT ON bob_live_sg.c TO c_sg;
108 GRANT SELECT ON bob_live_sg.d TO d_sg;
109 GRANT SELECT ON oms_live_sg.a TO a_sg;
110 GRANT SELECT ON oms_live_sg.b TO b_sg;
111 GRANT SELECT ON oms_live_sg.c TO c_sg;
112 GRANT SELECT ON oms_live_sg.d TO d_sg;
113 CREATE ROLE a_ph;
114 CREATE ROLE b_ph;
115 CREATE ROLE c_ph;
116 CREATE ROLE d_ph;
117 CREATE ROLE operations_ph;
118 GRANT a_ph TO operations_ph;
119 GRANT b_ph TO operations_ph;
120 GRANT c_ph TO operations_ph;
121 GRANT d_ph TO operations_ph;
122 GRANT SELECT ON bob_live_ph.a TO a_ph;
123 GRANT SELECT ON bob_live_ph.b TO b_ph;
124 GRANT SELECT ON bob_live_ph.c TO c_ph;
125 GRANT SELECT ON bob_live_ph.d TO d_ph;
126 GRANT SELECT ON oms_live_ph.a TO a_ph;
127 GRANT SELECT ON oms_live_ph.b TO b_ph;
128 GRANT SELECT ON oms_live_ph.c TO c_ph;
129 GRANT SELECT ON oms_live_ph.d TO d_ph;
130 CREATE ROLE a_id;
131 CREATE ROLE b_id;
132 CREATE ROLE c_id;
133 CREATE ROLE d_id;
134 CREATE ROLE operations_id;
135 GRANT a_id TO operations_id;
136 GRANT b_id TO operations_id;
137 GRANT c_id TO operations_id;
138 GRANT d_id TO operations_id;
139 GRANT SELECT ON bob_live_id.a TO a_id;
140 GRANT SELECT ON bob_live_id.b TO b_id;
141 GRANT SELECT ON bob_live_id.c TO c_id;
142 GRANT SELECT ON bob_live_id.d TO d_id;
143 GRANT SELECT ON oms_live_id.a TO a_id;
144 GRANT SELECT ON oms_live_id.b TO b_id;
145 GRANT SELECT ON oms_live_id.c TO c_id;
146 GRANT SELECT ON oms_live_id.d TO d_id;
147 CREATE ROLE a_hk;
148 CREATE ROLE b_hk;
149 CREATE ROLE c_hk;
150 CREATE ROLE d_hk;
151 CREATE ROLE operations_hk;
152 GRANT a_hk TO operations_hk;
153 GRANT b_hk TO operations_hk;
154 GRANT c_hk TO operations_hk;
155 GRANT d_hk TO operations_hk;
156 GRANT SELECT ON bob_live_hk.a TO a_hk;
157 GRANT SELECT ON bob_live_hk.b TO b_hk;
158 GRANT SELECT ON bob_live_hk.c TO c_hk;
159 GRANT SELECT ON bob_live_hk.d TO d_hk;
160 GRANT SELECT ON oms_live_hk.a TO a_hk;
161 GRANT SELECT ON oms_live_hk.b TO b_hk;
162 GRANT SELECT ON oms_live_hk.c TO c_hk;
163 GRANT SELECT ON oms_live_hk.d TO d_hk;
164 CREATE ROLE a_vn;
165 CREATE ROLE b_vn;
166 CREATE ROLE c_vn;
167 CREATE ROLE d_vn;
168 CREATE ROLE operations_vn;
169 GRANT a_vn TO operations_vn;
170 GRANT b_vn TO operations_vn;
171 GRANT c_vn TO operations_vn;
172 GRANT d_vn TO operations_vn;
173 GRANT SELECT ON bob_live_vn.a TO a_vn;
174 GRANT SELECT ON bob_live_vn.b TO b_vn;
175 GRANT SELECT ON bob_live_vn.c TO c_vn;
176 GRANT SELECT ON bob_live_vn.d TO d_vn;
177 GRANT SELECT ON oms_live_vn.a TO a_vn;
178 GRANT SELECT ON oms_live_vn.b TO b_vn;
179 GRANT SELECT ON oms_live_vn.c TO c_vn;
180 GRANT SELECT ON oms_live_vn.d TO d_vn;
181 CREATE ROLE a_tw;
182 CREATE ROLE b_tw;
183 CREATE ROLE c_tw;
184 CREATE ROLE d_tw;
185 CREATE ROLE operations_tw;
186 GRANT a_tw TO operations_tw;
187 GRANT b_tw TO operations_tw;
188 GRANT c_tw TO operations_tw;
189 GRANT d_tw TO operations_tw;
190 GRANT SELECT ON bob_live_tw.a TO a_tw;
191 GRANT SELECT ON bob_live_tw.b TO b_tw;
192 GRANT SELECT ON bob_live_tw.c TO c_tw;
193 GRANT SELECT ON bob_live_tw.d TO d_tw;
194 GRANT SELECT ON oms_live_tw.a TO a_tw;
195 GRANT SELECT ON oms_live_tw.b TO b_tw;
196 GRANT SELECT ON oms_live_tw.c TO c_tw;
197 GRANT SELECT ON oms_live_tw.d TO d_tw;
198 CREATE ROLE a_my;
199 CREATE ROLE b_my;
200 CREATE ROLE c_my;
201 CREATE ROLE d_my;
202 CREATE ROLE operations_my;
203 GRANT a_my TO operations_my;
204 GRANT b_my TO operations_my;
205 GRANT c_my TO operations_my;
206 GRANT d_my TO operations_my;
207 GRANT SELECT ON bob_live_my.a TO a_my;
208 GRANT SELECT ON bob_live_my.b TO b_my;
209 GRANT SELECT ON bob_live_my.c TO c_my;
210 GRANT SELECT ON bob_live_my.d TO d_my;
211 GRANT SELECT ON oms_live_my.a TO a_my;
212 GRANT SELECT ON oms_live_my.b TO b_my;
213 GRANT SELECT ON oms_live_my.c TO c_my;
214 GRANT SELECT ON oms_live_my.d TO d_my;
215 CREATE ROLE a_th;
216 CREATE ROLE b_th;
217 CREATE ROLE c_th;
218 CREATE ROLE d_th;
219 CREATE ROLE operations_th;
220 GRANT a_th TO operations_th;
221 GRANT b_th TO operations_th;
222 GRANT c_th TO operations_th;
223 GRANT d_th TO operations_th;
224 GRANT SELECT ON bob_live_th.a TO a_th;
225 GRANT SELECT ON bob_live_th.b TO b_th;
226 GRANT SELECT ON bob_live_th.c TO c_th;
227 GRANT SELECT ON bob_live_th.d TO d_th;
228 GRANT SELECT ON oms_live_th.a TO a_th;
229 GRANT SELECT ON oms_live_th.b TO b_th;
230 GRANT SELECT ON oms_live_th.c TO c_th;
231 GRANT SELECT ON oms_live_th.d TO d_th;
232 GRANT operations_sg TO operations_cluster;
233 GRANT operations_ph TO operations_cluster;
234 GRANT operations_id TO operations_cluster;
235 GRANT operations_hk TO operations_cluster;
236 GRANT operations_vn TO operations_cluster;
237 GRANT operations_tw TO operations_cluster;
238 GRANT operations_my TO operations_cluster;
239 GRANT operations_th TO operations_cluster;
240
241 connect(con1,localhost,u,,);
242 SHOW DATABASES;
243 SET ROLE operations_cluster;
244 SHOW DATABASES;
245 SELECT COUNT(1) FROM oms_live_sg.a;
246 SELECT COUNT(1) FROM oms_live_sg.b;
247 SELECT COUNT(1) FROM oms_live_sg.c;
248 SELECT COUNT(1) FROM oms_live_sg.d;
249 SELECT COUNT(1) FROM oms_live_ph.a;
250 SELECT COUNT(1) FROM oms_live_ph.b;
251 SELECT COUNT(1) FROM oms_live_ph.c;
252 SELECT COUNT(1) FROM oms_live_ph.d;
253 SELECT COUNT(1) FROM oms_live_id.a;
254 SELECT COUNT(1) FROM oms_live_id.b;
255 SELECT COUNT(1) FROM oms_live_id.c;
256 SELECT COUNT(1) FROM oms_live_id.d;
257 SELECT COUNT(1) FROM oms_live_hk.a;
258 SELECT COUNT(1) FROM oms_live_hk.b;
259 SELECT COUNT(1) FROM oms_live_hk.c;
260 SELECT COUNT(1) FROM oms_live_hk.d;
261 SELECT COUNT(1) FROM oms_live_vn.a;
262 SELECT COUNT(1) FROM oms_live_vn.b;
263 SELECT COUNT(1) FROM oms_live_vn.c;
264 SELECT COUNT(1) FROM oms_live_vn.d;
265 SELECT COUNT(1) FROM oms_live_tw.a;
266 SELECT COUNT(1) FROM oms_live_tw.b;
267 SELECT COUNT(1) FROM oms_live_tw.c;
268 SELECT COUNT(1) FROM oms_live_tw.d;
269 SELECT COUNT(1) FROM oms_live_my.a;
270 SELECT COUNT(1) FROM oms_live_my.b;
271 SELECT COUNT(1) FROM oms_live_my.c;
272 SELECT COUNT(1) FROM oms_live_my.d;
273 SELECT COUNT(1) FROM oms_live_th.a;
274 SELECT COUNT(1) FROM oms_live_th.b;
275 SELECT COUNT(1) FROM oms_live_th.c;
276 SELECT COUNT(1) FROM oms_live_th.d;
277
278
279 connect(con2,localhost,root,,);
280 FLUSH PRIVILEGES;
281
282 connect(con3,localhost,u,,);
283 SHOW DATABASES;
284 SET ROLE operations_cluster;
285 SHOW DATABASES;
286 SELECT COUNT(1) FROM oms_live_sg.a;
287 SELECT COUNT(1) FROM oms_live_sg.b;
288 SELECT COUNT(1) FROM oms_live_sg.c;
289 SELECT COUNT(1) FROM oms_live_sg.d;
290 SELECT COUNT(1) FROM oms_live_ph.a;
291 SELECT COUNT(1) FROM oms_live_ph.b;
292 SELECT COUNT(1) FROM oms_live_ph.c;
293 SELECT COUNT(1) FROM oms_live_ph.d;
294 SELECT COUNT(1) FROM oms_live_id.a;
295 SELECT COUNT(1) FROM oms_live_id.b;
296 SELECT COUNT(1) FROM oms_live_id.c;
297 SELECT COUNT(1) FROM oms_live_id.d;
298 SELECT COUNT(1) FROM oms_live_hk.a;
299 SELECT COUNT(1) FROM oms_live_hk.b;
300 SELECT COUNT(1) FROM oms_live_hk.c;
301 SELECT COUNT(1) FROM oms_live_hk.d;
302 SELECT COUNT(1) FROM oms_live_vn.a;
303 SELECT COUNT(1) FROM oms_live_vn.b;
304 SELECT COUNT(1) FROM oms_live_vn.c;
305 SELECT COUNT(1) FROM oms_live_vn.d;
306 SELECT COUNT(1) FROM oms_live_tw.a;
307 SELECT COUNT(1) FROM oms_live_tw.b;
308 SELECT COUNT(1) FROM oms_live_tw.c;
309 SELECT COUNT(1) FROM oms_live_tw.d;
310 SELECT COUNT(1) FROM oms_live_my.a;
311 SELECT COUNT(1) FROM oms_live_my.b;
312 SELECT COUNT(1) FROM oms_live_my.c;
313 SELECT COUNT(1) FROM oms_live_my.d;
314 SELECT COUNT(1) FROM oms_live_th.a;
315 SELECT COUNT(1) FROM oms_live_th.b;
316 SELECT COUNT(1) FROM oms_live_th.c;
317 SELECT COUNT(1) FROM oms_live_th.d;
318
319
320 connect(con4,localhost,root,,);
321
322 DROP DATABASE bob_live_sg;
323 DROP DATABASE oms_live_sg;
324 DROP DATABASE bob_live_ph;
325 DROP DATABASE oms_live_ph;
326 DROP DATABASE bob_live_id;
327 DROP DATABASE oms_live_id;
328 DROP DATABASE bob_live_hk;
329 DROP DATABASE oms_live_hk;
330 DROP DATABASE bob_live_vn;
331 DROP DATABASE oms_live_vn;
332 DROP DATABASE bob_live_tw;
333 DROP DATABASE oms_live_tw;
334 DROP DATABASE bob_live_my;
335 DROP DATABASE oms_live_my;
336 DROP DATABASE bob_live_th;
337 DROP DATABASE oms_live_th;
338 DROP ROLE operations_sg;
339 DROP ROLE a_sg;
340 DROP ROLE b_sg;
341 DROP ROLE c_sg;
342 DROP ROLE d_sg;
343 DROP ROLE operations_ph;
344 DROP ROLE a_ph;
345 DROP ROLE b_ph;
346 DROP ROLE c_ph;
347 DROP ROLE d_ph;
348 DROP ROLE operations_id;
349 DROP ROLE a_id;
350 DROP ROLE b_id;
351 DROP ROLE c_id;
352 DROP ROLE d_id;
353 DROP ROLE operations_hk;
354 DROP ROLE a_hk;
355 DROP ROLE b_hk;
356 DROP ROLE c_hk;
357 DROP ROLE d_hk;
358 DROP ROLE operations_vn;
359 DROP ROLE a_vn;
360 DROP ROLE b_vn;
361 DROP ROLE c_vn;
362 DROP ROLE d_vn;
363 DROP ROLE operations_tw;
364 DROP ROLE a_tw;
365 DROP ROLE b_tw;
366 DROP ROLE c_tw;
367 DROP ROLE d_tw;
368 DROP ROLE operations_my;
369 DROP ROLE a_my;
370 DROP ROLE b_my;
371 DROP ROLE c_my;
372 DROP ROLE d_my;
373 DROP ROLE operations_th;
374 DROP ROLE a_th;
375 DROP ROLE b_th;
376 DROP ROLE c_th;
377 DROP ROLE d_th;
378 DROP USER u;
379 DROP ROLE operations_cluster;