You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
208 lines
6.9 KiB
208 lines
6.9 KiB
CREATE ROLE r1;
|
|
|
|
CREATE ROLE `admin-db1`;
|
|
CREATE ROLE `admin-db2`;
|
|
CREATE ROLE `admin-db1t1`;
|
|
CREATE ROLE `admin-db2t1`;
|
|
CREATE ROLE `app-updater`;
|
|
|
|
CREATE USER `app-middleware-db1`@`localhost` IDENTIFIED BY 'foo';
|
|
CREATE USER `app-middleware-db2`@`localhost` IDENTIFIED BY 'foo';
|
|
CREATE USER `app`@`localhost` IDENTIFIED BY 'foo';
|
|
|
|
GRANT `admin-db1` TO `app-middleware-db1`@`localhost`;
|
|
GRANT `admin-db2` TO `app-middleware-db2`@`localhost`;
|
|
GRANT `app-updater` TO `app-middleware-db1`@`localhost`;
|
|
|
|
CREATE DATABASE db1;
|
|
CREATE DATABASE db2;
|
|
|
|
CREATE TABLE db1.t1 (c1 INT, c2 INT, c3 INT);
|
|
CREATE TABLE db1.t2 (c1 INT, c2 INT, c3 INT);
|
|
CREATE TABLE db2.t1 (c1 INT, c2 INT, c3 INT);
|
|
CREATE TABLE db2.t2 (c1 INT, c2 INT, c3 INT);
|
|
|
|
--echo ++ admin-db1 can manage db2.t1 and admin-db2 can manage db1.t1
|
|
GRANT `admin-db2t1` TO `admin-db1`;
|
|
GRANT `admin-db1t1` TO `admin-db2`;
|
|
--echo ++ admin-db1 can propote anyone with the admin-db1t1 rights.
|
|
GRANT `admin-db1t1` TO `admin-db1` WITH ADMIN OPTION;
|
|
|
|
GRANT SELECT, UPDATE, CREATE, DROP, INSERT, DELETE ON db1.* TO `admin-db1`;
|
|
GRANT SELECT, UPDATE, CREATE, DROP, INSERT, DELETE ON db2.* TO `admin-db2`;
|
|
GRANT SELECT, UPDATE, CREATE, DROP, INSERT, DELETE ON db1.t1 TO `admin-db1t1`;
|
|
GRANT SELECT, UPDATE, CREATE, DROP, INSERT, DELETE ON db2.t1 TO `admin-db2t1`;
|
|
|
|
connect(con1, localhost, app-middleware-db1, foo, test);
|
|
SET ROLE `admin-db1`;
|
|
|
|
--echo ++ Positive test
|
|
INSERT INTO db1.t1 VALUES (1,2,3);
|
|
INSERT INTO db1.t2 VALUES (1,2,3);
|
|
INSERT INTO db2.t1 VALUES (1,2,3);
|
|
|
|
SELECT * FROM db1.t1;
|
|
SELECT * FROM db1.t2;
|
|
SELECT * FROM db2.t1;
|
|
|
|
GRANT `admin-db1t1` TO `app`@`localhost`;
|
|
|
|
connection default;
|
|
GRANT r1 TO `app-middleware-db1`@`localhost` WITH ADMIN OPTION;
|
|
|
|
connection con1;
|
|
--echo ++ Connected as app-middleware-db1
|
|
SET ROLE `admin-db1`;
|
|
GRANT `admin-db1t1` TO `app`@`localhost`;
|
|
--echo ++ r1 and inherited role admin-db1t1 should be WITH ADMIN OPTION
|
|
SHOW GRANTS FOR CURRENT_USER() USING `admin-db1`;
|
|
|
|
--echo ++ Negative test
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
INSERT INTO db2.t2 VALUES (1,2,3);
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
SELECT * FROM db2.t2;
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
GRANT `admin-db2t1` TO `app`@`localhost`;
|
|
|
|
connection default;
|
|
--echo ++ Connected as root
|
|
--echo ++ Granting WITH ADMIN OPTION role WITH ADMIN OPTION privileges
|
|
--echo ++ app@localhost has admin-db1t1 granted.
|
|
connect(con2, localhost, app, foo, test);
|
|
--echo ++ Connected as app@localhost
|
|
SHOW GRANTS FOR CURRENT_USER();
|
|
--echo ++ Positive test ; setting a granted role.
|
|
SET ROLE `admin-db1t1`;
|
|
SELECT CURRENT_USER(), CURRENT_ROLE();
|
|
|
|
--echo ++ Negative tests ; Attempt to grant the granted role to 3rd party
|
|
--echo ++ app@localhost did not inherit the ability to grant WITH ADMIN OPTION
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
GRANT `admin-db1t1` TO `app-middleware-db2`@`localhost`;
|
|
connection default;
|
|
--echo # only count nodes and edges as the sorting order is depending on platform
|
|
SELECT ExtractValue(ROLES_GRAPHML(),'count(//node)') as num_nodes;
|
|
SELECT ExtractValue(ROLES_GRAPHML(),'count(//edge)') as num_edges;
|
|
|
|
--echo ++ Now grant admin-db1t1 to app@localhost WITH ADMIN OPTION
|
|
--echo ++ Positive test
|
|
connection con1;
|
|
--echo ++ Connected as app-middleware-db1@localhost
|
|
GRANT `admin-db1t1` TO `app`@`localhost` WITH ADMIN OPTION;
|
|
connection con2;
|
|
--echo ++ Connected as app@localhost
|
|
--echo ++ app@localhost should now be able to grant admin-db1t1 to app-middleware
|
|
SET ROLE ALL;
|
|
SELECT CURRENT_USER(), CURRENT_ROLE();
|
|
GRANT `admin-db1t1` TO `app-middleware-db2`@`localhost`;
|
|
|
|
--echo ++ Revoking roles require WITH ADMIN too
|
|
--echo ++ Positive tests
|
|
REVOKE `admin-db1t1` FROM `app-middleware-db2`@`localhost`;
|
|
--echo ++ Restorning grant for negative test
|
|
GRANT `admin-db1t1` TO `app-middleware-db2`@`localhost`;
|
|
connection con1;
|
|
--echo ++ Connected as app-middleware-db1@localhost
|
|
--echo ++ Remove WITH ADMIN grants by removing and re-granting role
|
|
REVOKE `admin-db1t1` FROM `app`@`localhost`;
|
|
GRANT `admin-db1t1` TO `app`@`localhost`;
|
|
connection con2;
|
|
--echo ++ Connected as app@localhost
|
|
--echo ++ Negative tests
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
REVOKE `admin-db1t1` FROM `app-middleware-db2`@`localhost`;
|
|
connection con1;
|
|
--echo ++ Connected as app-middleware-db1@localhost
|
|
--echo ++ Positive test
|
|
SELECT CURRENT_USER(), CURRENT_ROLE();
|
|
SHOW GRANTS;
|
|
--echo ++ User stil has WITH ADMIN and can revoke from `app-middleware-db2`@`localhost`
|
|
REVOKE `admin-db1t1` FROM `app-middleware-db2`@`localhost`;
|
|
|
|
connection default;
|
|
DROP DATABASE db1;
|
|
DROP DATABASE db2;
|
|
DROP ROLE r1;
|
|
DROP ROLE `admin-db1`;
|
|
DROP ROLE `admin-db2`;
|
|
DROP ROLE `admin-db1t1`;
|
|
DROP ROLE `admin-db2t1`;
|
|
DROP ROLE `app-updater`;
|
|
DROP USER `app-middleware-db1`@`localhost`;
|
|
DROP USER `app-middleware-db2`@`localhost`;
|
|
DROP USER `app`@`localhost`;
|
|
disconnect con1;
|
|
disconnect con2;
|
|
|
|
--echo +++++++++++++++++++++++++++++
|
|
--echo ++ WITH GRANT OPTION tests ++
|
|
--echo +++++++++++++++++++++++++++++
|
|
CREATE USER u1@localhost IDENTIFIED BY 'foo';
|
|
CREATE USER u2@localhost IDENTIFIED BY 'foo';
|
|
CREATE ROLE r1;
|
|
CREATE DATABASE db1;
|
|
GRANT CREATE ON db1.* TO r1 WITH GRANT OPTION;
|
|
GRANT r1 TO u1@localhost;
|
|
connect(con1, localhost, u1, foo, test);
|
|
--echo ++ Connected as u1@localhost
|
|
SET ROLE ALL;
|
|
GRANT CREATE ON db1.* TO u2@localhost;
|
|
SET ROLE NONE;
|
|
--error ER_DBACCESS_DENIED_ERROR
|
|
GRANT CREATE ON db1.* TO u2@localhost;
|
|
connection default;
|
|
--echo ++ Connected as root
|
|
disconnect con1;
|
|
|
|
DROP USER u1@localhost, u2@localhost;
|
|
DROP ROLE r1;
|
|
DROP DATABASE db1;
|
|
SELECT user,host FROM mysql.user;
|
|
|
|
--echo #############################################
|
|
CREATE USER u1@localhost IDENTIFIED BY 'foo';
|
|
CREATE USER u2@localhost IDENTIFIED BY 'foo';
|
|
CREATE ROLE r1, r2;
|
|
use test;
|
|
GRANT CREATE ON test.* TO r1;
|
|
GRANT DROP ON test.* TO r2;
|
|
GRANT r1 TO u1@localhost WITH ADMIN OPTION;
|
|
GRANT r2 TO u1@localhost;
|
|
connect(con1, localhost, u1, foo, test);
|
|
|
|
connection con1;
|
|
--echo ++ Connected as u1@localhost
|
|
SET ROLE ALL;
|
|
SHOW GRANTS;
|
|
GRANT r1 TO u2@localhost WITH ADMIN OPTION;
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
GRANT r2 TO u2@localhost WITH ADMIN OPTION;
|
|
connection default;
|
|
--echo ++ Connected as root
|
|
--echo #############################################################
|
|
--echo ++ Dynamic privilege ROLE_ADMIN grants the ability
|
|
--echo ++ to grant any role to anyone (but not grant any privileges)
|
|
--echo +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|
|
CREATE USER u3@localhost IDENTIFIED BY 'foo';
|
|
CREATE ROLE role_admin, arbitrary_role;
|
|
GRANT ROLE_ADMIN ON *.* TO role_admin;
|
|
GRANT role_admin TO u3@localhost;
|
|
connect(con2, localhost, u3, foo, test);
|
|
--echo ++ Connected as u3@localhost
|
|
SET ROLE role_admin;
|
|
GRANT arbitrary_role TO u1@localhost;
|
|
--echo Granting a role not granted will also work.
|
|
GRANT r1 TO u1@localhost;
|
|
GRANT r1 TO u3@localhost;
|
|
--echo But you can't grant any privileges
|
|
--error ER_ACCESS_DENIED_ERROR
|
|
GRANT SELECT ON *.* TO r1;
|
|
--error ER_ACCESS_DENIED_ERROR
|
|
GRANT SELECT ON *.* TO u3@localhost;
|
|
connection default;
|
|
--echo ++ Connected as root
|
|
DROP USER u1@localhost, u2@localhost, u3@localhost;
|
|
DROP ROLE r1,r2,role_admin,arbitrary_role;
|
|
disconnect con1;
|
|
|
|
|