# SETUP SCHEMA
#
# SETUP SCHEMA
#
use test;
#
#  CREATE ROLES
#
CREATE ROLE reader, updater;
GRANT reader TO updater WITH ADMIN OPTION;
CREATE ROLE task1, task1_lead, task2, task2_lead;
GRANT updater TO task1_lead;
GRANT updater TO task2_lead;
GRANT task1 TO task1_lead;
GRANT task2 TO task2_lead;
CREATE ROLE project_lead;
GRANT task1_lead TO project_lead;
GRANT task2_lead TO project_lead;
CREATE ROLE product_lead;
GRANT project_lead TO product_lead;
CREATE ROLE hr_rules, hr_access;
GRANT hr_rules TO hr_access WITH ADMIN OPTION;
#
# USER ACCOUNT AND ASSIGN ROLE's TO THEM.
#
CREATE USER user0 IDENTIFIED BY 'foo';
CREATE USER user1 IDENTIFIED BY 'foo';
CREATE USER user2 IDENTIFIED BY 'foo';
GRANT reader, hr_rules TO user1;
GRANT updater, hr_rules TO user2;
CREATE USER lead_user1 IDENTIFIED BY 'foo';
CREATE USER lead_user2 IDENTIFIED BY 'foo';
GRANT task1_lead, hr_access TO lead_user1;
GRANT task2_lead, hr_access TO lead_user2;
CREATE USER project_lead_user IDENTIFIED BY 'foo';
GRANT project_lead, hr_access TO project_lead_user;
CREATE USER product_lead_user IDENTIFIED BY 'foo';
GRANT product_lead, hr_access TO product_lead_user;
#
# CREATE DATABASE OBJECTS
#
CREATE TABLE task1_ledger1 (f1 INT, f2 INT);
CREATE TABLE task1_ledger2 (f1 INT, f2 INT);
CREATE TABLE task2_ledger1 (f1 INT, f2 INT);
CREATE TABLE task2_ledger2 (f1 INT, f2 INT);
CREATE PROCEDURE daily_report (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t1;
END//
CREATE PROCEDURE weekly_report (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t1;
END//
CREATE PROCEDURE proc1 (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t1;
END//
CREATE TABLE hr_ledger1 (f1 INT, f2 INT);
CREATE TABLE hr_ledger2 (f1 INT, f2 INT);
CREATE TABLE t1 (f1 INT);
#
# GRANTS TO ROLE
#
GRANT SELECT ON task1_ledger1 TO reader;
GRANT UPDATE ON task1_ledger1 TO updater;
GRANT UPDATE ON task1_ledger2 TO updater;
GRANT UPDATE ON task2_ledger1 TO updater;
GRANT UPDATE ON task2_ledger2 TO updater;
GRANT INSERT ON task1_ledger1 TO task1_lead;
GRANT INSERT ON task1_ledger2 TO task1_lead;
GRANT INSERT ON task2_ledger1 TO task2_lead;
GRANT INSERT ON task2_ledger2 TO task2_lead;
GRANT DELETE ON task2_ledger1 TO project_lead;
GRANT DELETE ON task2_ledger2 TO project_lead;
GRANT SELECT ON hr_ledger1 TO hr_rules;
GRANT SELECT ON hr_ledger2 to hr_access;
GRANT SELECT (f2) ON task1_ledger1 to reader;
GRANT SELECT (f2) ON task1_ledger2 to reader WITH GRANT OPTION;
GRANT SELECT (f2) ON task2_ledger2 to product_lead;
GRANT SELECT (f2) ON task2_ledger2 to product_lead WITH GRANT OPTION;
GRANT SELECT (f2) ON hr_ledger1 TO hr_rules;
GRANT SELECT (f2) ON hr_ledger2 to hr_access WITH GRANT OPTION;
GRANT EXECUTE ON PROCEDURE daily_report TO task1_lead;
GRANT EXECUTE ON PROCEDURE weekly_report TO task2_lead;
GRANT ALTER ROUTINE ON PROCEDURE daily_report TO product_lead;
GRANT ALTER ROUTINE ON PROCEDURE weekly_report TO product_lead;
GRANT EXECUTE ON PROCEDURE weekly_report TO hr_rules;
GRANT EXECUTE ON PROCEDURE weekly_report TO hr_access;
GRANT SELECT ON t1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
GRANT SELECT (f1) ON t1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
GRANT EXECUTE ON PROCEDURE proc1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
# USER WITH ROLES.
#
# Case 1: Login as user1
#
# Connect as user1
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for user1@%
GRANT USAGE ON *.* TO `user1`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user1`@`%`
GRANT `hr_rules`@`%`,`reader`@`%` TO `user1`@`%`
# Enable roles;
SET ROLE hr_rules;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
hr_rules	%	NO	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_rules`@`%`
SHOW GRANTS;
Grants for user1@%
GRANT USAGE ON *.* TO `user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `user1`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `user1`@`%`
GRANT `hr_rules`@`%`,`reader`@`%` TO `user1`@`%`
Disconnect user1
#
# Case 2: Login as user2
#
# Connect as user2
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for user2@%
GRANT USAGE ON *.* TO `user2`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user2`@`%`
GRANT `hr_rules`@`%`,`updater`@`%` TO `user2`@`%`
# Enable roles;
SET ROLE hr_rules;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
hr_rules	%	NO	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_rules`@`%`
SHOW GRANTS;
Grants for user2@%
GRANT USAGE ON *.* TO `user2`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `user2`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `user2`@`%`
GRANT `hr_rules`@`%`,`updater`@`%` TO `user2`@`%`
Disconnect user2
#
# Case 3: Login as lead_user1 with mandatory roles.
#
SET GLOBAL mandatory_roles = 'hr_access';
# Connect as lead_user1
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
# Enable roles;
SET ROLE hr_access;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
hr_access	%	NO	YES
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
Disconnect lead_user1
SET GLOBAL activate_all_roles_on_login=on;
# Connect as lead_user1
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
task1_lead	%	NO	NO
hr_access	%	NO	YES
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`,`task1_lead`@`%`
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger1` TO `lead_user1`@`%`
GRANT SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT UPDATE ON `test`.`task2_ledger1` TO `lead_user1`@`%`
GRANT UPDATE ON `test`.`task2_ledger2` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`daily_report` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
GRANT `hr_rules`@`%`,`reader`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
# Enable roles;
SET ROLE hr_access;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
hr_access	%	NO	YES
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
Disconnect lead_user1
SET GLOBAL activate_all_roles_on_login=off;
# Set 'task2' as mandatory which is not directly granted to lead_user1
# and then use SET ROLE ALL
SET GLOBAL mandatory_roles = 'task2';
# Connect as lead_user1
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
# Enable roles;
SET ROLE ALL;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
task1_lead	%	NO	NO
hr_access	%	NO	NO
task2	%	NO	YES
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`,`task1_lead`@`%`,`task2`@`%`
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger1` TO `lead_user1`@`%`
GRANT SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT UPDATE ON `test`.`task2_ledger1` TO `lead_user1`@`%`
GRANT UPDATE ON `test`.`task2_ledger2` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`daily_report` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
GRANT `hr_rules`@`%`,`reader`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
Disconnect lead_user1
# Set 'task2' as mandatory which is not directly granted to lead_user1
# and then use SET ROLE ALL EXCEPT 'task2'
SET GLOBAL mandatory_roles = 'task2';
# Connect as lead_user1
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
# Enable roles;
SET ROLE ALL EXCEPT task2;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
task1_lead	%	NO	NO
hr_access	%	NO	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`,`task1_lead`@`%`
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger1` TO `lead_user1`@`%`
GRANT SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT UPDATE ON `test`.`task2_ledger1` TO `lead_user1`@`%`
GRANT UPDATE ON `test`.`task2_ledger2` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`daily_report` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
GRANT `hr_rules`@`%`,`reader`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
Disconnect lead_user1
SET GLOBAL mandatory_roles = default;
#
# Case 4: Login as lead_user2 with default roles.
#
SET DEFAULT ROLE hr_access TO lead_user2;
# Connect as lead_user2
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
hr_access	%	YES	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`
SHOW GRANTS;
Grants for lead_user2@%
GRANT USAGE ON *.* TO `lead_user2`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user2`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
GRANT `hr_rules`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
# Enable roles;
SET ROLE task2_lead;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
task2_lead	%	NO	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`task2_lead`@`%`
SHOW GRANTS;
Grants for lead_user2@%
GRANT USAGE ON *.* TO `lead_user2`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
GRANT SELECT, SELECT (`f2`), UPDATE ON `test`.`task1_ledger1` TO `lead_user2`@`%`
GRANT SELECT (`f2`), UPDATE ON `test`.`task1_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
GRANT INSERT, UPDATE ON `test`.`task2_ledger1` TO `lead_user2`@`%`
GRANT INSERT, UPDATE ON `test`.`task2_ledger2` TO `lead_user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
GRANT `reader`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
Disconnect lead_user2
SET DEFAULT ROLE NONE TO lead_user1;
# Set 'task2' as mandatory while CREATEing and ALTERing the user.
CREATE USER lead_user3 IDENTIFIED BY 'foo' DEFAULT ROLE task2;
GRANT SELECT ON test.* TO lead_user3;
GRANT task2 TO lead_user3;
# Connect as lead_user3
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
task2	%	YES	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`task2`@`%`
SHOW GRANTS;
Grants for lead_user3@%
GRANT USAGE ON *.* TO `lead_user3`@`%`
GRANT SELECT ON `test`.* TO `lead_user3`@`%`
GRANT `task2`@`%` TO `lead_user3`@`%`
# Enable roles;
SET ROLE NONE;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for lead_user3@%
GRANT USAGE ON *.* TO `lead_user3`@`%`
GRANT SELECT ON `test`.* TO `lead_user3`@`%`
GRANT `task2`@`%` TO `lead_user3`@`%`
Disconnect lead_user3
ALTER USER lead_user3 DEFAULT ROLE NONE;
# Connect as lead_user3
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for lead_user3@%
GRANT USAGE ON *.* TO `lead_user3`@`%`
GRANT SELECT ON `test`.* TO `lead_user3`@`%`
GRANT `task2`@`%` TO `lead_user3`@`%`
# Enable roles;
SET ROLE NONE;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for lead_user3@%
GRANT USAGE ON *.* TO `lead_user3`@`%`
GRANT SELECT ON `test`.* TO `lead_user3`@`%`
GRANT `task2`@`%` TO `lead_user3`@`%`
Disconnect lead_user3
ALTER USER lead_user3 DEFAULT ROLE task2;
# Connect as lead_user3
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
task2	%	YES	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`task2`@`%`
SHOW GRANTS;
Grants for lead_user3@%
GRANT USAGE ON *.* TO `lead_user3`@`%`
GRANT SELECT ON `test`.* TO `lead_user3`@`%`
GRANT `task2`@`%` TO `lead_user3`@`%`
# Enable roles;
SET ROLE NONE;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for lead_user3@%
GRANT USAGE ON *.* TO `lead_user3`@`%`
GRANT SELECT ON `test`.* TO `lead_user3`@`%`
GRANT `task2`@`%` TO `lead_user3`@`%`
Disconnect lead_user3
DROP USER lead_user3;
#
# Case 5: Login as project_lead_user with role graph.
#
# Connect as project_lead_user
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for project_lead_user@%
GRANT USAGE ON *.* TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
# Enable roles;
SET ROLE hr_access;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
hr_access	%	NO	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`
SHOW GRANTS;
Grants for project_lead_user@%
GRANT USAGE ON *.* TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
Disconnect project_lead_user
#
# Case 6: Login as project_lead_user with cyclic role graph.
#
# Form a role graph loop.
#
#              reader
#                |
#                V
#             updater<------------\
#                |                |
#        |``````````````|         |
#        V              V         |
#   task1_lead     task2_lead     |
#        |              |         |
#        ````````|```````         |
#                V                |
#          project_lead-----------/
#
INSERT INTO mysql.role_edges (FROM_USER,FROM_HOST,TO_USER,TO_HOST)
VALUES('project_lead','%','updater','%');
FLUSH PRIVILEGES;
# Connect as project_lead_user
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for project_lead_user@%
GRANT USAGE ON *.* TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
# Enable roles;
SET ROLE hr_access;
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
ROLE_NAME	ROLE_HOST	IS_DEFAULT	IS_MANDATORY
hr_access	%	NO	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`
SHOW GRANTS;
Grants for project_lead_user@%
GRANT USAGE ON *.* TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
Disconnect project_lead_user
REVOKE project_lead FROM updater;
# Cleanup
DROP TABLE task1_ledger1;
DROP TABLE task1_ledger2;
DROP TABLE task2_ledger1;
DROP TABLE task2_ledger2;
DROP PROCEDURE daily_report;
DROP PROCEDURE weekly_report;
DROP PROCEDURE proc1;
DROP TABLE hr_ledger1;
DROP TABLE hr_ledger2;
DROP TABLE t1;
DROP USER user0;
DROP USER user1, user2;
DROP USER lead_user1, lead_user2;
DROP USER project_lead_user;
DROP USER product_lead_user;
DROP ROLE reader, updater;
DROP ROLE task1, task1_lead, task2, task2_lead;
DROP ROLE project_lead;
DROP ROLE product_lead;
DROP ROLE hr_access;
DROP ROLE hr_rules;