Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.8, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
-
None
-
Windows 10
Description
If a role is created and no grants have been granted to this role (so it only has USAGE grant), and this role is granted to another role, which is granted to a third role, the whole privilege system of the user enabling this third role is empty.
Example
In this role, effective access rights shall be given to the app_roles. app_roles are granted to org_roles and org_roles are granted to a specific user's user_role.
-- create the application roles
|
CREATE ROLE app_role_1; |
-- if this line below is enabled, everything works fine!
|
-- GRANT SELECT ON t1 TO app_role_1;
|
CREATE ROLE app_role_2; |
GRANT SELECT ON t2 TO app_role_2; |
-- create the organisation roles
|
CREATE ROLE org_role_1; |
CREATE ROLE org_role_2; |
GRANT app_role_1 TO org_role_1; |
GRANT app_role_2 TO org_role_2; |
-- create the user role
|
CREATE ROLE user_role_1; |
GRANT org_role_1 TO user_role_1; |
GRANT org_role_2 TO user_role_1; |
-- create the user
|
CREATE USER user_1 IDENTIFIED BY 'user_1'; |
GRANT user_role_1 TO user_1; |
SET DEFAULT ROLE user_role_1 FOR user_1; |
FLUSH PRIVILEGES; |
When trying to access anything in the database, this is the result:
mysql -uuser_1 -puser_1 -D test -e "show tables;" |
ERROR 1044 (42000): Access denied for user 'user_1'@'%' to database 'test' |
As soon as the app_role_1 contains at least 1 grant, everything works fine:
mysql -uuser_1 -puser_1 -D test -e "show tables;" |
+--------------------+ |
| Tables_in_test |
|
+--------------------+ |
| t1 |
|
| t2 |
|
+--------------------+ |