[MDEV-29851] Cached role privileges are not invalidated when needed Created: 2022-10-21  Updated: 2022-10-22  Resolved: 2022-10-22

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.11, 10.7.7, 10.8.6, 10.9.4

Type: Bug Priority: Major
Reporter: Angelique Sklavounos (Inactive) Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-5771 Privileges acquired via roles depend ... Closed

 Description   

There appears to be an inconsistency of when privileges of a role are applied indirectly via another role and when the user connects to the server.

For instance - role1->user, role2->role1; user connects and has role2 privileges:

create role admin;
 
create role student;
 
create database crm;
 
grant create on crm.* to admin;
 
grant select on crm.* to student;
 
create user intern@localhost;
 
grant student to intern@localhost;
 
set default role student for intern@localhost;
 
grant admin to student;
 
 
 
connect (con1, localhost, intern,,);
 
use crm;
 
create table t1 (a int);
 
disconnect con1;
 
 
 
# cleanup
 
connection default;
 
drop user intern@localhost;
 
drop role student;
 
drop role admin;
 
drop database crm;
 
flush privileges;

However - role1->user; user connects; role2->role1; user connects but does not have role2 privileges. FLUSH PRIVILEGES is needed before user has role2 capabilities:

create role admin;
 
create role student;
 
create database crm;
 
grant create on crm.* to admin;
 
grant select on crm.* to student;
 
create user intern@localhost;
 
grant student to intern@localhost;
 
set default role student for intern@localhost;
 
 
 
connect (con1, localhost, intern,,);
 
use crm;
 
disconnect con1;
 
 
 
connection default;
 
grant admin to student;
 
 
 
connect (con1, localhost, intern,,);
 
use crm;
 
--error ER_TABLEACCESS_DENIED_ERROR
 
create table t1 (a int);
 
disconnect con1;
 
 
 
connection default;
 
flush privileges;
 
 
 
connect (con1, localhost, intern,,);
 
use crm;
 
create table t1 (a int);
 
disconnect con1;
 
 
 
# cleanup
 
connection default;
 
drop user intern@localhost;
 
drop role student;
 
drop role admin;
 
drop database crm;
 
flush privileges;

Why this is the case is not clear to me.

 Comments   
Comment by Sergei Golubchik [ 2022-10-22 ]

The reason is intermediate

connect (con1, localhost, intern,,);
 
use crm;
 
disconnect con1;

this computes privileges that user intern has on the schema crm, the result is cached. And, apparently, the cache is not invalidated when you grant admin to student.

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