Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Won't Fix
-
Affects Version/s: None
-
Fix Version/s: 10.0.5
-
Component/s: None
-
Labels:None
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
>> 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'
"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.
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.