Debian Codegraph @

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

raw
1 #
2 # MDEV-12366: FLUSH PRIVILEGES can break hierarchy of roles
3 #
4 # This testcase contains a user, who is granted a master role
5 # operations_cluster. operations_cluster is granted 8 different roles
6 # who in turn each have 4 different roles granted to them.
7 #
8 # Only the leaf roles contain privileges to access databases.
9 # Make sure the user has access to all databases if the master role
10 # is granted to him.
11 #
12 CREATE USER u;
13 CREATE ROLE operations_cluster;
14 GRANT operations_cluster TO u;
15 CREATE DATABASE bob_live_sg;
16 CREATE TABLE bob_live_sg.a (i INT(10));
17 CREATE TABLE bob_live_sg.b (i INT(10));
18 CREATE TABLE bob_live_sg.c (i INT(10));
19 CREATE TABLE bob_live_sg.d (i INT(10));
20 CREATE DATABASE oms_live_sg;
21 CREATE TABLE oms_live_sg.a (i INT(10));
22 CREATE TABLE oms_live_sg.b (i INT(10));
23 CREATE TABLE oms_live_sg.c (i INT(10));
24 CREATE TABLE oms_live_sg.d (i INT(10));
25 CREATE DATABASE bob_live_ph;
26 CREATE TABLE bob_live_ph.a (i INT(10));
27 CREATE TABLE bob_live_ph.b (i INT(10));
28 CREATE TABLE bob_live_ph.c (i INT(10));
29 CREATE TABLE bob_live_ph.d (i INT(10));
30 CREATE DATABASE oms_live_ph;
31 CREATE TABLE oms_live_ph.a (i INT(10));
32 CREATE TABLE oms_live_ph.b (i INT(10));
33 CREATE TABLE oms_live_ph.c (i INT(10));
34 CREATE TABLE oms_live_ph.d (i INT(10));
35 CREATE DATABASE bob_live_id;
36 CREATE TABLE bob_live_id.a (i INT(10));
37 CREATE TABLE bob_live_id.b (i INT(10));
38 CREATE TABLE bob_live_id.c (i INT(10));
39 CREATE TABLE bob_live_id.d (i INT(10));
40 CREATE DATABASE oms_live_id;
41 CREATE TABLE oms_live_id.a (i INT(10));
42 CREATE TABLE oms_live_id.b (i INT(10));
43 CREATE TABLE oms_live_id.c (i INT(10));
44 CREATE TABLE oms_live_id.d (i INT(10));
45 CREATE DATABASE bob_live_hk;
46 CREATE TABLE bob_live_hk.a (i INT(10));
47 CREATE TABLE bob_live_hk.b (i INT(10));
48 CREATE TABLE bob_live_hk.c (i INT(10));
49 CREATE TABLE bob_live_hk.d (i INT(10));
50 CREATE DATABASE oms_live_hk;
51 CREATE TABLE oms_live_hk.a (i INT(10));
52 CREATE TABLE oms_live_hk.b (i INT(10));
53 CREATE TABLE oms_live_hk.c (i INT(10));
54 CREATE TABLE oms_live_hk.d (i INT(10));
55 CREATE DATABASE bob_live_vn;
56 CREATE TABLE bob_live_vn.a (i INT(10));
57 CREATE TABLE bob_live_vn.b (i INT(10));
58 CREATE TABLE bob_live_vn.c (i INT(10));
59 CREATE TABLE bob_live_vn.d (i INT(10));
60 CREATE DATABASE oms_live_vn;
61 CREATE TABLE oms_live_vn.a (i INT(10));
62 CREATE TABLE oms_live_vn.b (i INT(10));
63 CREATE TABLE oms_live_vn.c (i INT(10));
64 CREATE TABLE oms_live_vn.d (i INT(10));
65 CREATE DATABASE bob_live_tw;
66 CREATE TABLE bob_live_tw.a (i INT(10));
67 CREATE TABLE bob_live_tw.b (i INT(10));
68 CREATE TABLE bob_live_tw.c (i INT(10));
69 CREATE TABLE bob_live_tw.d (i INT(10));
70 CREATE DATABASE oms_live_tw;
71 CREATE TABLE oms_live_tw.a (i INT(10));
72 CREATE TABLE oms_live_tw.b (i INT(10));
73 CREATE TABLE oms_live_tw.c (i INT(10));
74 CREATE TABLE oms_live_tw.d (i INT(10));
75 CREATE DATABASE bob_live_my;
76 CREATE TABLE bob_live_my.a (i INT(10));
77 CREATE TABLE bob_live_my.b (i INT(10));
78 CREATE TABLE bob_live_my.c (i INT(10));
79 CREATE TABLE bob_live_my.d (i INT(10));
80 CREATE DATABASE oms_live_my;
81 CREATE TABLE oms_live_my.a (i INT(10));
82 CREATE TABLE oms_live_my.b (i INT(10));
83 CREATE TABLE oms_live_my.c (i INT(10));
84 CREATE TABLE oms_live_my.d (i INT(10));
85 CREATE DATABASE bob_live_th;
86 CREATE TABLE bob_live_th.a (i INT(10));
87 CREATE TABLE bob_live_th.b (i INT(10));
88 CREATE TABLE bob_live_th.c (i INT(10));
89 CREATE TABLE bob_live_th.d (i INT(10));
90 CREATE DATABASE oms_live_th;
91 CREATE TABLE oms_live_th.a (i INT(10));
92 CREATE TABLE oms_live_th.b (i INT(10));
93 CREATE TABLE oms_live_th.c (i INT(10));
94 CREATE TABLE oms_live_th.d (i INT(10));
95 CREATE ROLE a_sg;
96 CREATE ROLE b_sg;
97 CREATE ROLE c_sg;
98 CREATE ROLE d_sg;
99 CREATE ROLE operations_sg;
100 GRANT a_sg TO operations_sg;
101 GRANT b_sg TO operations_sg;
102 GRANT c_sg TO operations_sg;
103 GRANT d_sg TO operations_sg;
104 GRANT SELECT ON bob_live_sg.a TO a_sg;
105 GRANT SELECT ON bob_live_sg.b TO b_sg;
106 GRANT SELECT ON bob_live_sg.c TO c_sg;
107 GRANT SELECT ON bob_live_sg.d TO d_sg;
108 GRANT SELECT ON oms_live_sg.a TO a_sg;
109 GRANT SELECT ON oms_live_sg.b TO b_sg;
110 GRANT SELECT ON oms_live_sg.c TO c_sg;
111 GRANT SELECT ON oms_live_sg.d TO d_sg;
112 CREATE ROLE a_ph;
113 CREATE ROLE b_ph;
114 CREATE ROLE c_ph;
115 CREATE ROLE d_ph;
116 CREATE ROLE operations_ph;
117 GRANT a_ph TO operations_ph;
118 GRANT b_ph TO operations_ph;
119 GRANT c_ph TO operations_ph;
120 GRANT d_ph TO operations_ph;
121 GRANT SELECT ON bob_live_ph.a TO a_ph;
122 GRANT SELECT ON bob_live_ph.b TO b_ph;
123 GRANT SELECT ON bob_live_ph.c TO c_ph;
124 GRANT SELECT ON bob_live_ph.d TO d_ph;
125 GRANT SELECT ON oms_live_ph.a TO a_ph;
126 GRANT SELECT ON oms_live_ph.b TO b_ph;
127 GRANT SELECT ON oms_live_ph.c TO c_ph;
128 GRANT SELECT ON oms_live_ph.d TO d_ph;
129 CREATE ROLE a_id;
130 CREATE ROLE b_id;
131 CREATE ROLE c_id;
132 CREATE ROLE d_id;
133 CREATE ROLE operations_id;
134 GRANT a_id TO operations_id;
135 GRANT b_id TO operations_id;
136 GRANT c_id TO operations_id;
137 GRANT d_id TO operations_id;
138 GRANT SELECT ON bob_live_id.a TO a_id;
139 GRANT SELECT ON bob_live_id.b TO b_id;
140 GRANT SELECT ON bob_live_id.c TO c_id;
141 GRANT SELECT ON bob_live_id.d TO d_id;
142 GRANT SELECT ON oms_live_id.a TO a_id;
143 GRANT SELECT ON oms_live_id.b TO b_id;
144 GRANT SELECT ON oms_live_id.c TO c_id;
145 GRANT SELECT ON oms_live_id.d TO d_id;
146 CREATE ROLE a_hk;
147 CREATE ROLE b_hk;
148 CREATE ROLE c_hk;
149 CREATE ROLE d_hk;
150 CREATE ROLE operations_hk;
151 GRANT a_hk TO operations_hk;
152 GRANT b_hk TO operations_hk;
153 GRANT c_hk TO operations_hk;
154 GRANT d_hk TO operations_hk;
155 GRANT SELECT ON bob_live_hk.a TO a_hk;
156 GRANT SELECT ON bob_live_hk.b TO b_hk;
157 GRANT SELECT ON bob_live_hk.c TO c_hk;
158 GRANT SELECT ON bob_live_hk.d TO d_hk;
159 GRANT SELECT ON oms_live_hk.a TO a_hk;
160 GRANT SELECT ON oms_live_hk.b TO b_hk;
161 GRANT SELECT ON oms_live_hk.c TO c_hk;
162 GRANT SELECT ON oms_live_hk.d TO d_hk;
163 CREATE ROLE a_vn;
164 CREATE ROLE b_vn;
165 CREATE ROLE c_vn;
166 CREATE ROLE d_vn;
167 CREATE ROLE operations_vn;
168 GRANT a_vn TO operations_vn;
169 GRANT b_vn TO operations_vn;
170 GRANT c_vn TO operations_vn;
171 GRANT d_vn TO operations_vn;
172 GRANT SELECT ON bob_live_vn.a TO a_vn;
173 GRANT SELECT ON bob_live_vn.b TO b_vn;
174 GRANT SELECT ON bob_live_vn.c TO c_vn;
175 GRANT SELECT ON bob_live_vn.d TO d_vn;
176 GRANT SELECT ON oms_live_vn.a TO a_vn;
177 GRANT SELECT ON oms_live_vn.b TO b_vn;
178 GRANT SELECT ON oms_live_vn.c TO c_vn;
179 GRANT SELECT ON oms_live_vn.d TO d_vn;
180 CREATE ROLE a_tw;
181 CREATE ROLE b_tw;
182 CREATE ROLE c_tw;
183 CREATE ROLE d_tw;
184 CREATE ROLE operations_tw;
185 GRANT a_tw TO operations_tw;
186 GRANT b_tw TO operations_tw;
187 GRANT c_tw TO operations_tw;
188 GRANT d_tw TO operations_tw;
189 GRANT SELECT ON bob_live_tw.a TO a_tw;
190 GRANT SELECT ON bob_live_tw.b TO b_tw;
191 GRANT SELECT ON bob_live_tw.c TO c_tw;
192 GRANT SELECT ON bob_live_tw.d TO d_tw;
193 GRANT SELECT ON oms_live_tw.a TO a_tw;
194 GRANT SELECT ON oms_live_tw.b TO b_tw;
195 GRANT SELECT ON oms_live_tw.c TO c_tw;
196 GRANT SELECT ON oms_live_tw.d TO d_tw;
197 CREATE ROLE a_my;
198 CREATE ROLE b_my;
199 CREATE ROLE c_my;
200 CREATE ROLE d_my;
201 CREATE ROLE operations_my;
202 GRANT a_my TO operations_my;
203 GRANT b_my TO operations_my;
204 GRANT c_my TO operations_my;
205 GRANT d_my TO operations_my;
206 GRANT SELECT ON bob_live_my.a TO a_my;
207 GRANT SELECT ON bob_live_my.b TO b_my;
208 GRANT SELECT ON bob_live_my.c TO c_my;
209 GRANT SELECT ON bob_live_my.d TO d_my;
210 GRANT SELECT ON oms_live_my.a TO a_my;
211 GRANT SELECT ON oms_live_my.b TO b_my;
212 GRANT SELECT ON oms_live_my.c TO c_my;
213 GRANT SELECT ON oms_live_my.d TO d_my;
214 CREATE ROLE a_th;
215 CREATE ROLE b_th;
216 CREATE ROLE c_th;
217 CREATE ROLE d_th;
218 CREATE ROLE operations_th;
219 GRANT a_th TO operations_th;
220 GRANT b_th TO operations_th;
221 GRANT c_th TO operations_th;
222 GRANT d_th TO operations_th;
223 GRANT SELECT ON bob_live_th.a TO a_th;
224 GRANT SELECT ON bob_live_th.b TO b_th;
225 GRANT SELECT ON bob_live_th.c TO c_th;
226 GRANT SELECT ON bob_live_th.d TO d_th;
227 GRANT SELECT ON oms_live_th.a TO a_th;
228 GRANT SELECT ON oms_live_th.b TO b_th;
229 GRANT SELECT ON oms_live_th.c TO c_th;
230 GRANT SELECT ON oms_live_th.d TO d_th;
231 GRANT operations_sg TO operations_cluster;
232 GRANT operations_ph TO operations_cluster;
233 GRANT operations_id TO operations_cluster;
234 GRANT operations_hk TO operations_cluster;
235 GRANT operations_vn TO operations_cluster;
236 GRANT operations_tw TO operations_cluster;
237 GRANT operations_my TO operations_cluster;
238 GRANT operations_th TO operations_cluster;
239 connect con1,localhost,u,,;
240 SHOW DATABASES;
241 Database
242 information_schema
243 SET ROLE operations_cluster;
244 SHOW DATABASES;
245 Database
246 bob_live_hk
247 bob_live_id
248 bob_live_my
249 bob_live_ph
250 bob_live_sg
251 bob_live_th
252 bob_live_tw
253 bob_live_vn
254 information_schema
255 oms_live_hk
256 oms_live_id
257 oms_live_my
258 oms_live_ph
259 oms_live_sg
260 oms_live_th
261 oms_live_tw
262 oms_live_vn
263 SELECT COUNT(1) FROM oms_live_sg.a;
264 COUNT(1)
265 0
266 SELECT COUNT(1) FROM oms_live_sg.b;
267 COUNT(1)
268 0
269 SELECT COUNT(1) FROM oms_live_sg.c;
270 COUNT(1)
271 0
272 SELECT COUNT(1) FROM oms_live_sg.d;
273 COUNT(1)
274 0
275 SELECT COUNT(1) FROM oms_live_ph.a;
276 COUNT(1)
277 0
278 SELECT COUNT(1) FROM oms_live_ph.b;
279 COUNT(1)
280 0
281 SELECT COUNT(1) FROM oms_live_ph.c;
282 COUNT(1)
283 0
284 SELECT COUNT(1) FROM oms_live_ph.d;
285 COUNT(1)
286 0
287 SELECT COUNT(1) FROM oms_live_id.a;
288 COUNT(1)
289 0
290 SELECT COUNT(1) FROM oms_live_id.b;
291 COUNT(1)
292 0
293 SELECT COUNT(1) FROM oms_live_id.c;
294 COUNT(1)
295 0
296 SELECT COUNT(1) FROM oms_live_id.d;
297 COUNT(1)
298 0
299 SELECT COUNT(1) FROM oms_live_hk.a;
300 COUNT(1)
301 0
302 SELECT COUNT(1) FROM oms_live_hk.b;
303 COUNT(1)
304 0
305 SELECT COUNT(1) FROM oms_live_hk.c;
306 COUNT(1)
307 0
308 SELECT COUNT(1) FROM oms_live_hk.d;
309 COUNT(1)
310 0
311 SELECT COUNT(1) FROM oms_live_vn.a;
312 COUNT(1)
313 0
314 SELECT COUNT(1) FROM oms_live_vn.b;
315 COUNT(1)
316 0
317 SELECT COUNT(1) FROM oms_live_vn.c;
318 COUNT(1)
319 0
320 SELECT COUNT(1) FROM oms_live_vn.d;
321 COUNT(1)
322 0
323 SELECT COUNT(1) FROM oms_live_tw.a;
324 COUNT(1)
325 0
326 SELECT COUNT(1) FROM oms_live_tw.b;
327 COUNT(1)
328 0
329 SELECT COUNT(1) FROM oms_live_tw.c;
330 COUNT(1)
331 0
332 SELECT COUNT(1) FROM oms_live_tw.d;
333 COUNT(1)
334 0
335 SELECT COUNT(1) FROM oms_live_my.a;
336 COUNT(1)
337 0
338 SELECT COUNT(1) FROM oms_live_my.b;
339 COUNT(1)
340 0
341 SELECT COUNT(1) FROM oms_live_my.c;
342 COUNT(1)
343 0
344 SELECT COUNT(1) FROM oms_live_my.d;
345 COUNT(1)
346 0
347 SELECT COUNT(1) FROM oms_live_th.a;
348 COUNT(1)
349 0
350 SELECT COUNT(1) FROM oms_live_th.b;
351 COUNT(1)
352 0
353 SELECT COUNT(1) FROM oms_live_th.c;
354 COUNT(1)
355 0
356 SELECT COUNT(1) FROM oms_live_th.d;
357 COUNT(1)
358 0
359 connect con2,localhost,root,,;
360 FLUSH PRIVILEGES;
361 connect con3,localhost,u,,;
362 SHOW DATABASES;
363 Database
364 information_schema
365 SET ROLE operations_cluster;
366 SHOW DATABASES;
367 Database
368 bob_live_hk
369 bob_live_id
370 bob_live_my
371 bob_live_ph
372 bob_live_sg
373 bob_live_th
374 bob_live_tw
375 bob_live_vn
376 information_schema
377 oms_live_hk
378 oms_live_id
379 oms_live_my
380 oms_live_ph
381 oms_live_sg
382 oms_live_th
383 oms_live_tw
384 oms_live_vn
385 SELECT COUNT(1) FROM oms_live_sg.a;
386 COUNT(1)
387 0
388 SELECT COUNT(1) FROM oms_live_sg.b;
389 COUNT(1)
390 0
391 SELECT COUNT(1) FROM oms_live_sg.c;
392 COUNT(1)
393 0
394 SELECT COUNT(1) FROM oms_live_sg.d;
395 COUNT(1)
396 0
397 SELECT COUNT(1) FROM oms_live_ph.a;
398 COUNT(1)
399 0
400 SELECT COUNT(1) FROM oms_live_ph.b;
401 COUNT(1)
402 0
403 SELECT COUNT(1) FROM oms_live_ph.c;
404 COUNT(1)
405 0
406 SELECT COUNT(1) FROM oms_live_ph.d;
407 COUNT(1)
408 0
409 SELECT COUNT(1) FROM oms_live_id.a;
410 COUNT(1)
411 0
412 SELECT COUNT(1) FROM oms_live_id.b;
413 COUNT(1)
414 0
415 SELECT COUNT(1) FROM oms_live_id.c;
416 COUNT(1)
417 0
418 SELECT COUNT(1) FROM oms_live_id.d;
419 COUNT(1)
420 0
421 SELECT COUNT(1) FROM oms_live_hk.a;
422 COUNT(1)
423 0
424 SELECT COUNT(1) FROM oms_live_hk.b;
425 COUNT(1)
426 0
427 SELECT COUNT(1) FROM oms_live_hk.c;
428 COUNT(1)
429 0
430 SELECT COUNT(1) FROM oms_live_hk.d;
431 COUNT(1)
432 0
433 SELECT COUNT(1) FROM oms_live_vn.a;
434 COUNT(1)
435 0
436 SELECT COUNT(1) FROM oms_live_vn.b;
437 COUNT(1)
438 0
439 SELECT COUNT(1) FROM oms_live_vn.c;
440 COUNT(1)
441 0
442 SELECT COUNT(1) FROM oms_live_vn.d;
443 COUNT(1)
444 0
445 SELECT COUNT(1) FROM oms_live_tw.a;
446 COUNT(1)
447 0
448 SELECT COUNT(1) FROM oms_live_tw.b;
449 COUNT(1)
450 0
451 SELECT COUNT(1) FROM oms_live_tw.c;
452 COUNT(1)
453 0
454 SELECT COUNT(1) FROM oms_live_tw.d;
455 COUNT(1)
456 0
457 SELECT COUNT(1) FROM oms_live_my.a;
458 COUNT(1)
459 0
460 SELECT COUNT(1) FROM oms_live_my.b;
461 COUNT(1)
462 0
463 SELECT COUNT(1) FROM oms_live_my.c;
464 COUNT(1)
465 0
466 SELECT COUNT(1) FROM oms_live_my.d;
467 COUNT(1)
468 0
469 SELECT COUNT(1) FROM oms_live_th.a;
470 COUNT(1)
471 0
472 SELECT COUNT(1) FROM oms_live_th.b;
473 COUNT(1)
474 0
475 SELECT COUNT(1) FROM oms_live_th.c;
476 COUNT(1)
477 0
478 SELECT COUNT(1) FROM oms_live_th.d;
479 COUNT(1)
480 0
481 connect con4,localhost,root,,;
482 DROP DATABASE bob_live_sg;
483 DROP DATABASE oms_live_sg;
484 DROP DATABASE bob_live_ph;
485 DROP DATABASE oms_live_ph;
486 DROP DATABASE bob_live_id;
487 DROP DATABASE oms_live_id;
488 DROP DATABASE bob_live_hk;
489 DROP DATABASE oms_live_hk;
490 DROP DATABASE bob_live_vn;
491 DROP DATABASE oms_live_vn;
492 DROP DATABASE bob_live_tw;
493 DROP DATABASE oms_live_tw;
494 DROP DATABASE bob_live_my;
495 DROP DATABASE oms_live_my;
496 DROP DATABASE bob_live_th;
497 DROP DATABASE oms_live_th;
498 DROP ROLE operations_sg;
499 DROP ROLE a_sg;
500 DROP ROLE b_sg;
501 DROP ROLE c_sg;
502 DROP ROLE d_sg;
503 DROP ROLE operations_ph;
504 DROP ROLE a_ph;
505 DROP ROLE b_ph;
506 DROP ROLE c_ph;
507 DROP ROLE d_ph;
508 DROP ROLE operations_id;
509 DROP ROLE a_id;
510 DROP ROLE b_id;
511 DROP ROLE c_id;
512 DROP ROLE d_id;
513 DROP ROLE operations_hk;
514 DROP ROLE a_hk;
515 DROP ROLE b_hk;
516 DROP ROLE c_hk;
517 DROP ROLE d_hk;
518 DROP ROLE operations_vn;
519 DROP ROLE a_vn;
520 DROP ROLE b_vn;
521 DROP ROLE c_vn;
522 DROP ROLE d_vn;
523 DROP ROLE operations_tw;
524 DROP ROLE a_tw;
525 DROP ROLE b_tw;
526 DROP ROLE c_tw;
527 DROP ROLE d_tw;
528 DROP ROLE operations_my;
529 DROP ROLE a_my;
530 DROP ROLE b_my;
531 DROP ROLE c_my;
532 DROP ROLE d_my;
533 DROP ROLE operations_th;
534 DROP ROLE a_th;
535 DROP ROLE b_th;
536 DROP ROLE c_th;
537 DROP ROLE d_th;
538 DROP USER u;
539 DROP ROLE operations_cluster;