[MDEV-28743] Roles without grants are handled wrong Created: 2022-06-03  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.6.8, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Walter van der Geest Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: None
Environment:

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                 |
 
+--------------------+



 Comments   
Comment by Sergei Golubchik [ 2022-06-21 ]

Thanks! Confirmed.
A complete mtr test case, for reference:

create database mysqltest1;
 
create table mysqltest1.t2 (a int);
 
create role app_role_1;
 
create role app_role_2;
 
grant select on mysqltest1.t2 to app_role_2;
 
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 role user_role_1;
 
grant org_role_1 to user_role_1;
 
grant org_role_2 to user_role_1;
 
create user user_1 identified by 'user_1';
 
grant user_role_1 to user_1;
 
flush privileges; # without it the test passes
 
 
 
connect con1,localhost,user_1,'user_1';
 
set role user_role_1;
 
use mysqltest1;
 
show tables;
 
disconnect con1;
 
connection default;
 
 
 
drop user user_1;
 
drop role user_role_1, org_role_1, org_role_2, app_role_1, app_role_2;
 
drop database mysqltest1;

Note that if flush privileges is commented out, the error disappears

Generated at Thu Feb 08 10:03:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.