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