[MDEV-5175] Revoking a role does not revoke corresponding grants from open sessions Created: 2013-10-23  Updated: 2013-10-23  Resolved: 2013-10-23

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 10.0.5

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-4397 Roles Closed
relates to MDEV-5164 Testing Roles Closed

 Description   

If a session has set a role X, revoking this role X from the user does not revoke the grants it provided.
I am not 100% sure whether it's design or oversight, but I suspect the latter because a) revoking the grants from the role does revoke them from an open session; b) dropping the role also revokes the grants from an open session. However, it's obviously not practical to do modify a role this way if you only want to deny it for a particular user, and do it immediately.

Test case:

--enable_connect_log
 
 
 
create database db1;
 
 
 
create user user1@localhost;
 
create role role1;
 
grant role1 to user1@localhost;
 
grant all on db1.* to role1;
 
 
 
--connect (con1,localhost,user1,,)
 
set role role1;
 
show grants;
 
create table db1.t1 (i int);
 
 
 
--connection default
 
revoke role1 from user1@localhost;
 
 
 
--connection con1
 
show grants;
 
drop table db1.t1;

Output:

create database db1;
 
create user user1@localhost;
 
create role role1;
 
grant role1 to user1@localhost;
 
grant all on db1.* to role1;
 
connect  con1,localhost,user1,,;
 
set role role1;
 
show grants;
 
Grants for user1@localhost
 
GRANT role1 TO 'user1'@'localhost'
 
GRANT USAGE ON *.* TO 'user1'@'localhost'
 
GRANT USAGE ON *.* TO 'role1'
 
GRANT ALL PRIVILEGES ON `db1`.* TO 'role1'
 
create table db1.t1 (i int);
 
connection default;
 
revoke role1 from user1@localhost;
 
connection con1;
 
show grants;
 
Grants for user1@localhost
 
GRANT USAGE ON *.* TO 'user1'@'localhost'
 
GRANT USAGE ON *.* TO 'role1'
 
GRANT ALL PRIVILEGES ON `db1`.* TO 'role1'
 
drop table db1.t1;
 

revision-id: sergii@pisem.net-20131020232702-db6ekuk4lz0itg1b
 
revno: 3892
 
branch-nick: 10.0-serg
 
BUILD/compile-pentium-debug-max-no-ndb



 Comments   
Comment by Sergei Golubchik [ 2013-10-23 ]

by design. also, revoking or dropping a role which is enabled (directly or indirectly) does not affect user's global privileges or privileges on the current database, but does affect privileges on the non-current database, table, column, and routine privileges. this is identical to how revoking privileges directly from a user work ­— which also doesn't affect privileges, cached in the THD.

Comment by Elena Stepanova [ 2013-10-23 ]

>> revoking or dropping a role which is enabled (directly or indirectly) does not affect user's global privileges or privileges on the current database, but does affect privileges on the non-current database, table, column, and routine privileges

But the example above was about a non-current database, was it not?

>> this is identical to how revoking privileges directly from a user work

Strange, I don't see the symmetry here. Please consider the following example. user1 is given two symmetrical grants, one for db1 through role1, another one for db2 directly. Then the grants are revoked at the same time; but access to db1 remains, while access to db2 doesn't.

--enable_connect_log
create database db1;
create database db2;

create user user1@localhost;
create role role1;
grant role1 to user1@localhost;
grant all on db1.* to role1;
grant all on db2.* to user1@localhost;

--connect (con1,localhost,user1,,)
set role role1;
show grants;
create table db1.t1 (i int);
create table db2.t2 (i int);

--connection default
revoke role1 from user1@localhost;
revoke all on db2.* from user1@localhost;

--connection con1
show grants;
drop table db1.t1;
drop table db2.t2;

The end of output:

Grants for user1@localhost
GRANT USAGE ON . TO 'user1'@'localhost'
GRANT USAGE ON . TO 'role1'
GRANT ALL PRIVILEGES ON `db1`.* TO 'role1'
drop table db1.t1;
drop table db2.t2;
mysqltest: At line 24: query 'drop table db2.t2' failed: 1142: DROP command denied to user 'user1'@'localhost' for table 't2'

Comment by Sergei Golubchik [ 2013-10-23 ]

"access to db1 remains" was a bug that I've fixed recently, but hasn't pushed yet.

Access to the current database will remain, access to the non-current database is affected by grant/revoke — independently whether it was a grant to a role or to a user.

Generated at Thu Feb 08 07:02:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.