#
# MDEV-12366: FLUSH PRIVILEGES can break hierarchy of roles
#
# This testcase contains a user, who is granted a master role
# operations_cluster. operations_cluster is granted 8 different roles
# who in turn each have 4 different roles granted to them.
#
# Only the leaf roles contain privileges to access databases.
# Make sure the user has access to all databases if the master role
# is granted to him.
#
CREATE USER u;
CREATE ROLE operations_cluster;
GRANT operations_cluster TO u;
CREATE DATABASE bob_live_sg;
CREATE TABLE bob_live_sg.a (i INT(10));
CREATE TABLE bob_live_sg.b (i INT(10));
CREATE TABLE bob_live_sg.c (i INT(10));
CREATE TABLE bob_live_sg.d (i INT(10));
CREATE DATABASE oms_live_sg;
CREATE TABLE oms_live_sg.a (i INT(10));
CREATE TABLE oms_live_sg.b (i INT(10));
CREATE TABLE oms_live_sg.c (i INT(10));
CREATE TABLE oms_live_sg.d (i INT(10));
CREATE DATABASE bob_live_ph;
CREATE TABLE bob_live_ph.a (i INT(10));
CREATE TABLE bob_live_ph.b (i INT(10));
CREATE TABLE bob_live_ph.c (i INT(10));
CREATE TABLE bob_live_ph.d (i INT(10));
CREATE DATABASE oms_live_ph;
CREATE TABLE oms_live_ph.a (i INT(10));
CREATE TABLE oms_live_ph.b (i INT(10));
CREATE TABLE oms_live_ph.c (i INT(10));
CREATE TABLE oms_live_ph.d (i INT(10));
CREATE DATABASE bob_live_id;
CREATE TABLE bob_live_id.a (i INT(10));
CREATE TABLE bob_live_id.b (i INT(10));
CREATE TABLE bob_live_id.c (i INT(10));
CREATE TABLE bob_live_id.d (i INT(10));
CREATE DATABASE oms_live_id;
CREATE TABLE oms_live_id.a (i INT(10));
CREATE TABLE oms_live_id.b (i INT(10));
CREATE TABLE oms_live_id.c (i INT(10));
CREATE TABLE oms_live_id.d (i INT(10));
CREATE DATABASE bob_live_hk;
CREATE TABLE bob_live_hk.a (i INT(10));
CREATE TABLE bob_live_hk.b (i INT(10));
CREATE TABLE bob_live_hk.c (i INT(10));
CREATE TABLE bob_live_hk.d (i INT(10));
CREATE DATABASE oms_live_hk;
CREATE TABLE oms_live_hk.a (i INT(10));
CREATE TABLE oms_live_hk.b (i INT(10));
CREATE TABLE oms_live_hk.c (i INT(10));
CREATE TABLE oms_live_hk.d (i INT(10));
CREATE DATABASE bob_live_vn;
CREATE TABLE bob_live_vn.a (i INT(10));
CREATE TABLE bob_live_vn.b (i INT(10));
CREATE TABLE bob_live_vn.c (i INT(10));
CREATE TABLE bob_live_vn.d (i INT(10));
CREATE DATABASE oms_live_vn;
CREATE TABLE oms_live_vn.a (i INT(10));
CREATE TABLE oms_live_vn.b (i INT(10));
CREATE TABLE oms_live_vn.c (i INT(10));
CREATE TABLE oms_live_vn.d (i INT(10));
CREATE DATABASE bob_live_tw;
CREATE TABLE bob_live_tw.a (i INT(10));
CREATE TABLE bob_live_tw.b (i INT(10));
CREATE TABLE bob_live_tw.c (i INT(10));
CREATE TABLE bob_live_tw.d (i INT(10));
CREATE DATABASE oms_live_tw;
CREATE TABLE oms_live_tw.a (i INT(10));
CREATE TABLE oms_live_tw.b (i INT(10));
CREATE TABLE oms_live_tw.c (i INT(10));
CREATE TABLE oms_live_tw.d (i INT(10));
CREATE DATABASE bob_live_my;
CREATE TABLE bob_live_my.a (i INT(10));
CREATE TABLE bob_live_my.b (i INT(10));
CREATE TABLE bob_live_my.c (i INT(10));
CREATE TABLE bob_live_my.d (i INT(10));
CREATE DATABASE oms_live_my;
CREATE TABLE oms_live_my.a (i INT(10));
CREATE TABLE oms_live_my.b (i INT(10));
CREATE TABLE oms_live_my.c (i INT(10));
CREATE TABLE oms_live_my.d (i INT(10));
CREATE DATABASE bob_live_th;
CREATE TABLE bob_live_th.a (i INT(10));
CREATE TABLE bob_live_th.b (i INT(10));
CREATE TABLE bob_live_th.c (i INT(10));
CREATE TABLE bob_live_th.d (i INT(10));
CREATE DATABASE oms_live_th;
CREATE TABLE oms_live_th.a (i INT(10));
CREATE TABLE oms_live_th.b (i INT(10));
CREATE TABLE oms_live_th.c (i INT(10));
CREATE TABLE oms_live_th.d (i INT(10));
CREATE ROLE a_sg;
CREATE ROLE b_sg;
CREATE ROLE c_sg;
CREATE ROLE d_sg;
CREATE ROLE operations_sg;
GRANT a_sg TO operations_sg;
GRANT b_sg TO operations_sg;
GRANT c_sg TO operations_sg;
GRANT d_sg TO operations_sg;
GRANT SELECT ON bob_live_sg.a TO a_sg;
GRANT SELECT ON bob_live_sg.b TO b_sg;
GRANT SELECT ON bob_live_sg.c TO c_sg;
GRANT SELECT ON bob_live_sg.d TO d_sg;
GRANT SELECT ON oms_live_sg.a TO a_sg;
GRANT SELECT ON oms_live_sg.b TO b_sg;
GRANT SELECT ON oms_live_sg.c TO c_sg;
GRANT SELECT ON oms_live_sg.d TO d_sg;
CREATE ROLE a_ph;
CREATE ROLE b_ph;
CREATE ROLE c_ph;
CREATE ROLE d_ph;
CREATE ROLE operations_ph;
GRANT a_ph TO operations_ph;
GRANT b_ph TO operations_ph;
GRANT c_ph TO operations_ph;
GRANT d_ph TO operations_ph;
GRANT SELECT ON bob_live_ph.a TO a_ph;
GRANT SELECT ON bob_live_ph.b TO b_ph;
GRANT SELECT ON bob_live_ph.c TO c_ph;
GRANT SELECT ON bob_live_ph.d TO d_ph;
GRANT SELECT ON oms_live_ph.a TO a_ph;
GRANT SELECT ON oms_live_ph.b TO b_ph;
GRANT SELECT ON oms_live_ph.c TO c_ph;
GRANT SELECT ON oms_live_ph.d TO d_ph;
CREATE ROLE a_id;
CREATE ROLE b_id;
CREATE ROLE c_id;
CREATE ROLE d_id;
CREATE ROLE operations_id;
GRANT a_id TO operations_id;
GRANT b_id TO operations_id;
GRANT c_id TO operations_id;
GRANT d_id TO operations_id;
GRANT SELECT ON bob_live_id.a TO a_id;
GRANT SELECT ON bob_live_id.b TO b_id;
GRANT SELECT ON bob_live_id.c TO c_id;
GRANT SELECT ON bob_live_id.d TO d_id;
GRANT SELECT ON oms_live_id.a TO a_id;
GRANT SELECT ON oms_live_id.b TO b_id;
GRANT SELECT ON oms_live_id.c TO c_id;
GRANT SELECT ON oms_live_id.d TO d_id;
CREATE ROLE a_hk;
CREATE ROLE b_hk;
CREATE ROLE c_hk;
CREATE ROLE d_hk;
CREATE ROLE operations_hk;
GRANT a_hk TO operations_hk;
GRANT b_hk TO operations_hk;
GRANT c_hk TO operations_hk;
GRANT d_hk TO operations_hk;
GRANT SELECT ON bob_live_hk.a TO a_hk;
GRANT SELECT ON bob_live_hk.b TO b_hk;
GRANT SELECT ON bob_live_hk.c TO c_hk;
GRANT SELECT ON bob_live_hk.d TO d_hk;
GRANT SELECT ON oms_live_hk.a TO a_hk;
GRANT SELECT ON oms_live_hk.b TO b_hk;
GRANT SELECT ON oms_live_hk.c TO c_hk;
GRANT SELECT ON oms_live_hk.d TO d_hk;
CREATE ROLE a_vn;
CREATE ROLE b_vn;
CREATE ROLE c_vn;
CREATE ROLE d_vn;
CREATE ROLE operations_vn;
GRANT a_vn TO operations_vn;
GRANT b_vn TO operations_vn;
GRANT c_vn TO operations_vn;
GRANT d_vn TO operations_vn;
GRANT SELECT ON bob_live_vn.a TO a_vn;
GRANT SELECT ON bob_live_vn.b TO b_vn;
GRANT SELECT ON bob_live_vn.c TO c_vn;
GRANT SELECT ON bob_live_vn.d TO d_vn;
GRANT SELECT ON oms_live_vn.a TO a_vn;
GRANT SELECT ON oms_live_vn.b TO b_vn;
GRANT SELECT ON oms_live_vn.c TO c_vn;
GRANT SELECT ON oms_live_vn.d TO d_vn;
CREATE ROLE a_tw;
CREATE ROLE b_tw;
CREATE ROLE c_tw;
CREATE ROLE d_tw;
CREATE ROLE operations_tw;
GRANT a_tw TO operations_tw;
GRANT b_tw TO operations_tw;
GRANT c_tw TO operations_tw;
GRANT d_tw TO operations_tw;
GRANT SELECT ON bob_live_tw.a TO a_tw;
GRANT SELECT ON bob_live_tw.b TO b_tw;
GRANT SELECT ON bob_live_tw.c TO c_tw;
GRANT SELECT ON bob_live_tw.d TO d_tw;
GRANT SELECT ON oms_live_tw.a TO a_tw;
GRANT SELECT ON oms_live_tw.b TO b_tw;
GRANT SELECT ON oms_live_tw.c TO c_tw;
GRANT SELECT ON oms_live_tw.d TO d_tw;
CREATE ROLE a_my;
CREATE ROLE b_my;
CREATE ROLE c_my;
CREATE ROLE d_my;
CREATE ROLE operations_my;
GRANT a_my TO operations_my;
GRANT b_my TO operations_my;
GRANT c_my TO operations_my;
GRANT d_my TO operations_my;
GRANT SELECT ON bob_live_my.a TO a_my;
GRANT SELECT ON bob_live_my.b TO b_my;
GRANT SELECT ON bob_live_my.c TO c_my;
GRANT SELECT ON bob_live_my.d TO d_my;
GRANT SELECT ON oms_live_my.a TO a_my;
GRANT SELECT ON oms_live_my.b TO b_my;
GRANT SELECT ON oms_live_my.c TO c_my;
GRANT SELECT ON oms_live_my.d TO d_my;
CREATE ROLE a_th;
CREATE ROLE b_th;
CREATE ROLE c_th;
CREATE ROLE d_th;
CREATE ROLE operations_th;
GRANT a_th TO operations_th;
GRANT b_th TO operations_th;
GRANT c_th TO operations_th;
GRANT d_th TO operations_th;
GRANT SELECT ON bob_live_th.a TO a_th;
GRANT SELECT ON bob_live_th.b TO b_th;
GRANT SELECT ON bob_live_th.c TO c_th;
GRANT SELECT ON bob_live_th.d TO d_th;
GRANT SELECT ON oms_live_th.a TO a_th;
GRANT SELECT ON oms_live_th.b TO b_th;
GRANT SELECT ON oms_live_th.c TO c_th;
GRANT SELECT ON oms_live_th.d TO d_th;
GRANT operations_sg TO operations_cluster;
GRANT operations_ph TO operations_cluster;
GRANT operations_id TO operations_cluster;
GRANT operations_hk TO operations_cluster;
GRANT operations_vn TO operations_cluster;
GRANT operations_tw TO operations_cluster;
GRANT operations_my TO operations_cluster;
GRANT operations_th TO operations_cluster;
connect con1,localhost,u,,;
SHOW DATABASES;
Database
information_schema
test
SET ROLE operations_cluster;
SHOW DATABASES;
Database
bob_live_hk
bob_live_id
bob_live_my
bob_live_ph
bob_live_sg
bob_live_th
bob_live_tw
bob_live_vn
information_schema
oms_live_hk
oms_live_id
oms_live_my
oms_live_ph
oms_live_sg
oms_live_th
oms_live_tw
oms_live_vn
test
SELECT COUNT(1) FROM oms_live_sg.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_sg.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_sg.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_sg.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_ph.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_ph.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_ph.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_ph.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_id.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_id.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_id.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_id.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_hk.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_hk.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_hk.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_hk.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_vn.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_vn.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_vn.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_vn.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_tw.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_tw.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_tw.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_tw.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_my.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_my.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_my.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_my.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_th.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_th.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_th.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_th.d;
COUNT(1)
0
connect con2,localhost,root,,;
FLUSH PRIVILEGES;
connect con3,localhost,u,,;
SHOW DATABASES;
Database
information_schema
test
SET ROLE operations_cluster;
SHOW DATABASES;
Database
bob_live_hk
bob_live_id
bob_live_my
bob_live_ph
bob_live_sg
bob_live_th
bob_live_tw
bob_live_vn
information_schema
oms_live_hk
oms_live_id
oms_live_my
oms_live_ph
oms_live_sg
oms_live_th
oms_live_tw
oms_live_vn
test
SELECT COUNT(1) FROM oms_live_sg.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_sg.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_sg.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_sg.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_ph.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_ph.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_ph.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_ph.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_id.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_id.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_id.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_id.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_hk.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_hk.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_hk.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_hk.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_vn.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_vn.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_vn.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_vn.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_tw.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_tw.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_tw.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_tw.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_my.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_my.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_my.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_my.d;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_th.a;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_th.b;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_th.c;
COUNT(1)
0
SELECT COUNT(1) FROM oms_live_th.d;
COUNT(1)
0
connect con4,localhost,root,,;
DROP DATABASE bob_live_sg;
DROP DATABASE oms_live_sg;
DROP DATABASE bob_live_ph;
DROP DATABASE oms_live_ph;
DROP DATABASE bob_live_id;
DROP DATABASE oms_live_id;
DROP DATABASE bob_live_hk;
DROP DATABASE oms_live_hk;
DROP DATABASE bob_live_vn;
DROP DATABASE oms_live_vn;
DROP DATABASE bob_live_tw;
DROP DATABASE oms_live_tw;
DROP DATABASE bob_live_my;
DROP DATABASE oms_live_my;
DROP DATABASE bob_live_th;
DROP DATABASE oms_live_th;
DROP ROLE operations_sg;
DROP ROLE a_sg;
DROP ROLE b_sg;
DROP ROLE c_sg;
DROP ROLE d_sg;
DROP ROLE operations_ph;
DROP ROLE a_ph;
DROP ROLE b_ph;
DROP ROLE c_ph;
DROP ROLE d_ph;
DROP ROLE operations_id;
DROP ROLE a_id;
DROP ROLE b_id;
DROP ROLE c_id;
DROP ROLE d_id;
DROP ROLE operations_hk;
DROP ROLE a_hk;
DROP ROLE b_hk;
DROP ROLE c_hk;
DROP ROLE d_hk;
DROP ROLE operations_vn;
DROP ROLE a_vn;
DROP ROLE b_vn;
DROP ROLE c_vn;
DROP ROLE d_vn;
DROP ROLE operations_tw;
DROP ROLE a_tw;
DROP ROLE b_tw;
DROP ROLE c_tw;
DROP ROLE d_tw;
DROP ROLE operations_my;
DROP ROLE a_my;
DROP ROLE b_my;
DROP ROLE c_my;
DROP ROLE d_my;
DROP ROLE operations_th;
DROP ROLE a_th;
DROP ROLE b_th;
DROP ROLE c_th;
DROP ROLE d_th;
DROP USER u;
DROP ROLE operations_cluster;