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# 12CREATE USER u; 13CREATE ROLE operations_cluster; 14GRANT operations_cluster TO u; 15CREATE DATABASE bob_live_sg; 16CREATE TABLE bob_live_sg.a (i INT(10)); 17CREATE TABLE bob_live_sg.b (i INT(10)); 18CREATE TABLE bob_live_sg.c (i INT(10)); 19CREATE TABLE bob_live_sg.d (i INT(10)); 20CREATE DATABASE oms_live_sg; 21CREATE TABLE oms_live_sg.a (i INT(10)); 22CREATE TABLE oms_live_sg.b (i INT(10)); 23CREATE TABLE oms_live_sg.c (i INT(10)); 24CREATE TABLE oms_live_sg.d (i INT(10)); 25CREATE DATABASE bob_live_ph; 26CREATE TABLE bob_live_ph.a (i INT(10)); 27CREATE TABLE bob_live_ph.b (i INT(10)); 28CREATE TABLE bob_live_ph.c (i INT(10)); 29CREATE TABLE bob_live_ph.d (i INT(10)); 30CREATE DATABASE oms_live_ph; 31CREATE TABLE oms_live_ph.a (i INT(10)); 32CREATE TABLE oms_live_ph.b (i INT(10)); 33CREATE TABLE oms_live_ph.c (i INT(10)); 34CREATE TABLE oms_live_ph.d (i INT(10)); 35CREATE DATABASE bob_live_id; 36CREATE TABLE bob_live_id.a (i INT(10)); 37CREATE TABLE bob_live_id.b (i INT(10)); 38CREATE TABLE bob_live_id.c (i INT(10)); 39CREATE TABLE bob_live_id.d (i INT(10)); 40CREATE DATABASE oms_live_id; 41CREATE TABLE oms_live_id.a (i INT(10)); 42CREATE TABLE oms_live_id.b (i INT(10)); 43CREATE TABLE oms_live_id.c (i INT(10)); 44CREATE TABLE oms_live_id.d (i INT(10)); 45CREATE DATABASE bob_live_hk; 46CREATE TABLE bob_live_hk.a (i INT(10)); 47CREATE TABLE bob_live_hk.b (i INT(10)); 48CREATE TABLE bob_live_hk.c (i INT(10)); 49CREATE TABLE bob_live_hk.d (i INT(10)); 50CREATE DATABASE oms_live_hk; 51CREATE TABLE oms_live_hk.a (i INT(10)); 52CREATE TABLE oms_live_hk.b (i INT(10)); 53CREATE TABLE oms_live_hk.c (i INT(10)); 54CREATE TABLE oms_live_hk.d (i INT(10)); 55CREATE DATABASE bob_live_vn; 56CREATE TABLE bob_live_vn.a (i INT(10)); 57CREATE TABLE bob_live_vn.b (i INT(10)); 58CREATE TABLE bob_live_vn.c (i INT(10)); 59CREATE TABLE bob_live_vn.d (i INT(10)); 60CREATE DATABASE oms_live_vn; 61CREATE TABLE oms_live_vn.a (i INT(10)); 62CREATE TABLE oms_live_vn.b (i INT(10)); 63CREATE TABLE oms_live_vn.c (i INT(10)); 64CREATE TABLE oms_live_vn.d (i INT(10)); 65CREATE DATABASE bob_live_tw; 66CREATE TABLE bob_live_tw.a (i INT(10)); 67CREATE TABLE bob_live_tw.b (i INT(10)); 68CREATE TABLE bob_live_tw.c (i INT(10)); 69CREATE TABLE bob_live_tw.d (i INT(10)); 70CREATE DATABASE oms_live_tw; 71CREATE TABLE oms_live_tw.a (i INT(10)); 72CREATE TABLE oms_live_tw.b (i INT(10)); 73CREATE TABLE oms_live_tw.c (i INT(10)); 74CREATE TABLE oms_live_tw.d (i INT(10)); 75CREATE DATABASE bob_live_my; 76CREATE TABLE bob_live_my.a (i INT(10)); 77CREATE TABLE bob_live_my.b (i INT(10)); 78CREATE TABLE bob_live_my.c (i INT(10)); 79CREATE TABLE bob_live_my.d (i INT(10)); 80CREATE DATABASE oms_live_my; 81CREATE TABLE oms_live_my.a (i INT(10)); 82CREATE TABLE oms_live_my.b (i INT(10)); 83CREATE TABLE oms_live_my.c (i INT(10)); 84CREATE TABLE oms_live_my.d (i INT(10)); 85CREATE DATABASE bob_live_th; 86CREATE TABLE bob_live_th.a (i INT(10)); 87CREATE TABLE bob_live_th.b (i INT(10)); 88CREATE TABLE bob_live_th.c (i INT(10)); 89CREATE TABLE bob_live_th.d (i INT(10)); 90CREATE DATABASE oms_live_th; 91CREATE TABLE oms_live_th.a (i INT(10)); 92CREATE TABLE oms_live_th.b (i INT(10)); 93CREATE TABLE oms_live_th.c (i INT(10)); 94CREATE TABLE oms_live_th.d (i INT(10)); 95CREATE ROLE a_sg; 96CREATE ROLE b_sg; 97CREATE ROLE c_sg; 98CREATE ROLE d_sg; 99CREATE ROLE operations_sg; 100GRANT a_sg TO operations_sg; 101GRANT b_sg TO operations_sg; 102GRANT c_sg TO operations_sg; 103GRANT d_sg TO operations_sg; 104GRANT SELECT ON bob_live_sg.a TO a_sg; 105GRANT SELECT ON bob_live_sg.b TO b_sg; 106GRANT SELECT ON bob_live_sg.c TO c_sg; 107GRANT SELECT ON bob_live_sg.d TO d_sg; 108GRANT SELECT ON oms_live_sg.a TO a_sg; 109GRANT SELECT ON oms_live_sg.b TO b_sg; 110GRANT SELECT ON oms_live_sg.c TO c_sg; 111GRANT SELECT ON oms_live_sg.d TO d_sg; 112CREATE ROLE a_ph; 113CREATE ROLE b_ph; 114CREATE ROLE c_ph; 115CREATE ROLE d_ph; 116CREATE ROLE operations_ph; 117GRANT a_ph TO operations_ph; 118GRANT b_ph TO operations_ph; 119GRANT c_ph TO operations_ph; 120GRANT d_ph TO operations_ph; 121GRANT SELECT ON bob_live_ph.a TO a_ph; 122GRANT SELECT ON bob_live_ph.b TO b_ph; 123GRANT SELECT ON bob_live_ph.c TO c_ph; 124GRANT SELECT ON bob_live_ph.d TO d_ph; 125GRANT SELECT ON oms_live_ph.a TO a_ph; 126GRANT SELECT ON oms_live_ph.b TO b_ph; 127GRANT SELECT ON oms_live_ph.c TO c_ph; 128GRANT SELECT ON oms_live_ph.d TO d_ph; 129CREATE ROLE a_id; 130CREATE ROLE b_id; 131CREATE ROLE c_id; 132CREATE ROLE d_id; 133CREATE ROLE operations_id; 134GRANT a_id TO operations_id; 135GRANT b_id TO operations_id; 136GRANT c_id TO operations_id; 137GRANT d_id TO operations_id; 138GRANT SELECT ON bob_live_id.a TO a_id; 139GRANT SELECT ON bob_live_id.b TO b_id; 140GRANT SELECT ON bob_live_id.c TO c_id; 141GRANT SELECT ON bob_live_id.d TO d_id; 142GRANT SELECT ON oms_live_id.a TO a_id; 143GRANT SELECT ON oms_live_id.b TO b_id; 144GRANT SELECT ON oms_live_id.c TO c_id; 145GRANT SELECT ON oms_live_id.d TO d_id; 146CREATE ROLE a_hk; 147CREATE ROLE b_hk; 148CREATE ROLE c_hk; 149CREATE ROLE d_hk; 150CREATE ROLE operations_hk; 151GRANT a_hk TO operations_hk; 152GRANT b_hk TO operations_hk; 153GRANT c_hk TO operations_hk; 154GRANT d_hk TO operations_hk; 155GRANT SELECT ON bob_live_hk.a TO a_hk; 156GRANT SELECT ON bob_live_hk.b TO b_hk; 157GRANT SELECT ON bob_live_hk.c TO c_hk; 158GRANT SELECT ON bob_live_hk.d TO d_hk; 159GRANT SELECT ON oms_live_hk.a TO a_hk; 160GRANT SELECT ON oms_live_hk.b TO b_hk; 161GRANT SELECT ON oms_live_hk.c TO c_hk; 162GRANT SELECT ON oms_live_hk.d TO d_hk; 163CREATE ROLE a_vn; 164CREATE ROLE b_vn; 165CREATE ROLE c_vn; 166CREATE ROLE d_vn; 167CREATE ROLE operations_vn; 168GRANT a_vn TO operations_vn; 169GRANT b_vn TO operations_vn; 170GRANT c_vn TO operations_vn; 171GRANT d_vn TO operations_vn; 172GRANT SELECT ON bob_live_vn.a TO a_vn; 173GRANT SELECT ON bob_live_vn.b TO b_vn; 174GRANT SELECT ON bob_live_vn.c TO c_vn; 175GRANT SELECT ON bob_live_vn.d TO d_vn; 176GRANT SELECT ON oms_live_vn.a TO a_vn; 177GRANT SELECT ON oms_live_vn.b TO b_vn; 178GRANT SELECT ON oms_live_vn.c TO c_vn; 179GRANT SELECT ON oms_live_vn.d TO d_vn; 180CREATE ROLE a_tw; 181CREATE ROLE b_tw; 182CREATE ROLE c_tw; 183CREATE ROLE d_tw; 184CREATE ROLE operations_tw; 185GRANT a_tw TO operations_tw; 186GRANT b_tw TO operations_tw; 187GRANT c_tw TO operations_tw; 188GRANT d_tw TO operations_tw; 189GRANT SELECT ON bob_live_tw.a TO a_tw; 190GRANT SELECT ON bob_live_tw.b TO b_tw; 191GRANT SELECT ON bob_live_tw.c TO c_tw; 192GRANT SELECT ON bob_live_tw.d TO d_tw; 193GRANT SELECT ON oms_live_tw.a TO a_tw; 194GRANT SELECT ON oms_live_tw.b TO b_tw; 195GRANT SELECT ON oms_live_tw.c TO c_tw; 196GRANT SELECT ON oms_live_tw.d TO d_tw; 197CREATE ROLE a_my; 198CREATE ROLE b_my; 199CREATE ROLE c_my; 200CREATE ROLE d_my; 201CREATE ROLE operations_my; 202GRANT a_my TO operations_my; 203GRANT b_my TO operations_my; 204GRANT c_my TO operations_my; 205GRANT d_my TO operations_my; 206GRANT SELECT ON bob_live_my.a TO a_my; 207GRANT SELECT ON bob_live_my.b TO b_my; 208GRANT SELECT ON bob_live_my.c TO c_my; 209GRANT SELECT ON bob_live_my.d TO d_my; 210GRANT SELECT ON oms_live_my.a TO a_my; 211GRANT SELECT ON oms_live_my.b TO b_my; 212GRANT SELECT ON oms_live_my.c TO c_my; 213GRANT SELECT ON oms_live_my.d TO d_my; 214CREATE ROLE a_th; 215CREATE ROLE b_th; 216CREATE ROLE c_th; 217CREATE ROLE d_th; 218CREATE ROLE operations_th; 219GRANT a_th TO operations_th; 220GRANT b_th TO operations_th; 221GRANT c_th TO operations_th; 222GRANT d_th TO operations_th; 223GRANT SELECT ON bob_live_th.a TO a_th; 224GRANT SELECT ON bob_live_th.b TO b_th; 225GRANT SELECT ON bob_live_th.c TO c_th; 226GRANT SELECT ON bob_live_th.d TO d_th; 227GRANT SELECT ON oms_live_th.a TO a_th; 228GRANT SELECT ON oms_live_th.b TO b_th; 229GRANT SELECT ON oms_live_th.c TO c_th; 230GRANT SELECT ON oms_live_th.d TO d_th; 231GRANT operations_sg TO operations_cluster; 232GRANT operations_ph TO operations_cluster; 233GRANT operations_id TO operations_cluster; 234GRANT operations_hk TO operations_cluster; 235GRANT operations_vn TO operations_cluster; 236GRANT operations_tw TO operations_cluster; 237GRANT operations_my TO operations_cluster; 238GRANT operations_th TO operations_cluster; 239connect con1,localhost,u,,; 240SHOW DATABASES; 241Database 242information_schema 243test 244SET ROLE operations_cluster; 245SHOW DATABASES; 246Database 247bob_live_hk 248bob_live_id 249bob_live_my 250bob_live_ph 251bob_live_sg 252bob_live_th 253bob_live_tw 254bob_live_vn 255information_schema 256oms_live_hk 257oms_live_id 258oms_live_my 259oms_live_ph 260oms_live_sg 261oms_live_th 262oms_live_tw 263oms_live_vn 264test 265SELECT COUNT(1) FROM oms_live_sg.a; 266COUNT(1) 2670 268SELECT COUNT(1) FROM oms_live_sg.b; 269COUNT(1) 2700 271SELECT COUNT(1) FROM oms_live_sg.c; 272COUNT(1) 2730 274SELECT COUNT(1) FROM oms_live_sg.d; 275COUNT(1) 2760 277SELECT COUNT(1) FROM oms_live_ph.a; 278COUNT(1) 2790 280SELECT COUNT(1) FROM oms_live_ph.b; 281COUNT(1) 2820 283SELECT COUNT(1) FROM oms_live_ph.c; 284COUNT(1) 2850 286SELECT COUNT(1) FROM oms_live_ph.d; 287COUNT(1) 2880 289SELECT COUNT(1) FROM oms_live_id.a; 290COUNT(1) 2910 292SELECT COUNT(1) FROM oms_live_id.b; 293COUNT(1) 2940 295SELECT COUNT(1) FROM oms_live_id.c; 296COUNT(1) 2970 298SELECT COUNT(1) FROM oms_live_id.d; 299COUNT(1) 3000 301SELECT COUNT(1) FROM oms_live_hk.a; 302COUNT(1) 3030 304SELECT COUNT(1) FROM oms_live_hk.b; 305COUNT(1) 3060 307SELECT COUNT(1) FROM oms_live_hk.c; 308COUNT(1) 3090 310SELECT COUNT(1) FROM oms_live_hk.d; 311COUNT(1) 3120 313SELECT COUNT(1) FROM oms_live_vn.a; 314COUNT(1) 3150 316SELECT COUNT(1) FROM oms_live_vn.b; 317COUNT(1) 3180 319SELECT COUNT(1) FROM oms_live_vn.c; 320COUNT(1) 3210 322SELECT COUNT(1) FROM oms_live_vn.d; 323COUNT(1) 3240 325SELECT COUNT(1) FROM oms_live_tw.a; 326COUNT(1) 3270 328SELECT COUNT(1) FROM oms_live_tw.b; 329COUNT(1) 3300 331SELECT COUNT(1) FROM oms_live_tw.c; 332COUNT(1) 3330 334SELECT COUNT(1) FROM oms_live_tw.d; 335COUNT(1) 3360 337SELECT COUNT(1) FROM oms_live_my.a; 338COUNT(1) 3390 340SELECT COUNT(1) FROM oms_live_my.b; 341COUNT(1) 3420 343SELECT COUNT(1) FROM oms_live_my.c; 344COUNT(1) 3450 346SELECT COUNT(1) FROM oms_live_my.d; 347COUNT(1) 3480 349SELECT COUNT(1) FROM oms_live_th.a; 350COUNT(1) 3510 352SELECT COUNT(1) FROM oms_live_th.b; 353COUNT(1) 3540 355SELECT COUNT(1) FROM oms_live_th.c; 356COUNT(1) 3570 358SELECT COUNT(1) FROM oms_live_th.d; 359COUNT(1) 3600 361connect con2,localhost,root,,; 362FLUSH PRIVILEGES; 363connect con3,localhost,u,,; 364SHOW DATABASES; 365Database 366information_schema 367test 368SET ROLE operations_cluster; 369SHOW DATABASES; 370Database 371bob_live_hk 372bob_live_id 373bob_live_my 374bob_live_ph 375bob_live_sg 376bob_live_th 377bob_live_tw 378bob_live_vn 379information_schema 380oms_live_hk 381oms_live_id 382oms_live_my 383oms_live_ph 384oms_live_sg 385oms_live_th 386oms_live_tw 387oms_live_vn 388test 389SELECT COUNT(1) FROM oms_live_sg.a; 390COUNT(1) 3910 392SELECT COUNT(1) FROM oms_live_sg.b; 393COUNT(1) 3940 395SELECT COUNT(1) FROM oms_live_sg.c; 396COUNT(1) 3970 398SELECT COUNT(1) FROM oms_live_sg.d; 399COUNT(1) 4000 401SELECT COUNT(1) FROM oms_live_ph.a; 402COUNT(1) 4030 404SELECT COUNT(1) FROM oms_live_ph.b; 405COUNT(1) 4060 407SELECT COUNT(1) FROM oms_live_ph.c; 408COUNT(1) 4090 410SELECT COUNT(1) FROM oms_live_ph.d; 411COUNT(1) 4120 413SELECT COUNT(1) FROM oms_live_id.a; 414COUNT(1) 4150 416SELECT COUNT(1) FROM oms_live_id.b; 417COUNT(1) 4180 419SELECT COUNT(1) FROM oms_live_id.c; 420COUNT(1) 4210 422SELECT COUNT(1) FROM oms_live_id.d; 423COUNT(1) 4240 425SELECT COUNT(1) FROM oms_live_hk.a; 426COUNT(1) 4270 428SELECT COUNT(1) FROM oms_live_hk.b; 429COUNT(1) 4300 431SELECT COUNT(1) FROM oms_live_hk.c; 432COUNT(1) 4330 434SELECT COUNT(1) FROM oms_live_hk.d; 435COUNT(1) 4360 437SELECT COUNT(1) FROM oms_live_vn.a; 438COUNT(1) 4390 440SELECT COUNT(1) FROM oms_live_vn.b; 441COUNT(1) 4420 443SELECT COUNT(1) FROM oms_live_vn.c; 444COUNT(1) 4450 446SELECT COUNT(1) FROM oms_live_vn.d; 447COUNT(1) 4480 449SELECT COUNT(1) FROM oms_live_tw.a; 450COUNT(1) 4510 452SELECT COUNT(1) FROM oms_live_tw.b; 453COUNT(1) 4540 455SELECT COUNT(1) FROM oms_live_tw.c; 456COUNT(1) 4570 458SELECT COUNT(1) FROM oms_live_tw.d; 459COUNT(1) 4600 461SELECT COUNT(1) FROM oms_live_my.a; 462COUNT(1) 4630 464SELECT COUNT(1) FROM oms_live_my.b; 465COUNT(1) 4660 467SELECT COUNT(1) FROM oms_live_my.c; 468COUNT(1) 4690 470SELECT COUNT(1) FROM oms_live_my.d; 471COUNT(1) 4720 473SELECT COUNT(1) FROM oms_live_th.a; 474COUNT(1) 4750 476SELECT COUNT(1) FROM oms_live_th.b; 477COUNT(1) 4780 479SELECT COUNT(1) FROM oms_live_th.c; 480COUNT(1) 4810 482SELECT COUNT(1) FROM oms_live_th.d; 483COUNT(1) 4840 485connect con4,localhost,root,,; 486DROP DATABASE bob_live_sg; 487DROP DATABASE oms_live_sg; 488DROP DATABASE bob_live_ph; 489DROP DATABASE oms_live_ph; 490DROP DATABASE bob_live_id; 491DROP DATABASE oms_live_id; 492DROP DATABASE bob_live_hk; 493DROP DATABASE oms_live_hk; 494DROP DATABASE bob_live_vn; 495DROP DATABASE oms_live_vn; 496DROP DATABASE bob_live_tw; 497DROP DATABASE oms_live_tw; 498DROP DATABASE bob_live_my; 499DROP DATABASE oms_live_my; 500DROP DATABASE bob_live_th; 501DROP DATABASE oms_live_th; 502DROP ROLE operations_sg; 503DROP ROLE a_sg; 504DROP ROLE b_sg; 505DROP ROLE c_sg; 506DROP ROLE d_sg; 507DROP ROLE operations_ph; 508DROP ROLE a_ph; 509DROP ROLE b_ph; 510DROP ROLE c_ph; 511DROP ROLE d_ph; 512DROP ROLE operations_id; 513DROP ROLE a_id; 514DROP ROLE b_id; 515DROP ROLE c_id; 516DROP ROLE d_id; 517DROP ROLE operations_hk; 518DROP ROLE a_hk; 519DROP ROLE b_hk; 520DROP ROLE c_hk; 521DROP ROLE d_hk; 522DROP ROLE operations_vn; 523DROP ROLE a_vn; 524DROP ROLE b_vn; 525DROP ROLE c_vn; 526DROP ROLE d_vn; 527DROP ROLE operations_tw; 528DROP ROLE a_tw; 529DROP ROLE b_tw; 530DROP ROLE c_tw; 531DROP ROLE d_tw; 532DROP ROLE operations_my; 533DROP ROLE a_my; 534DROP ROLE b_my; 535DROP ROLE c_my; 536DROP ROLE d_my; 537DROP ROLE operations_th; 538DROP ROLE a_th; 539DROP ROLE b_th; 540DROP ROLE c_th; 541DROP ROLE d_th; 542DROP USER u; 543DROP ROLE operations_cluster; 544