MySQL Bugs: #85562: dropping a role does not remove privileges from active users

Bug #85562	dropping a role does not remove privileges from active users
Submitted:	21 Mar 2017 9:48	Modified:	22 Dec 2017 8:42
Reporter:	Giuseppe Maxia (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Security: Roles	Severity:	S2 (Serious)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
According to the manual (https://dev.mysql.com/doc/refman/8.0/en/drop-role.html) 
"A dropped role is automatically revoked from any user account (or role) to which the role was granted. Within any current session for such an account, its privileges are adjusted for the next statement executed."

Using the scenario described in Bug#85559, we can connect with user u_test_rw, and insert a row in a table.

# SESSION n. 1 - user u_test_rw

mysql [localhost] {u_test_rw} (test) > show grants;
+---------------------------------------------------------------------+
| Grants for u_test_rw@%                                              |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u_test_rw`@`%`                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO `u_test_rw`@`%` |
| GRANT `r_test_ro`@`%`,`r_test_rw`@`%` TO `u_test_rw`@`%`            |
+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql [localhost] {u_test_rw} (test) > insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

# SESSION n. 2 - as root
mysql [localhost] {root} ((none)) > drop role r_test_rw;
Query OK, 0 rows affected (0.00 sec)

# SESSION n. 1 - user u_test_rw

mysql [localhost] {u_test_rw} (test) > show grants;
ERROR 3530 (HY000): `r_test_rw`@`%` is not granted to `u_test_rw`@`%`

mysql [localhost] {u_test_rw} (test) > show grants for u_test_rw;
+------------------------------------------+
| Grants for u_test_rw@%                   |
+------------------------------------------+
| GRANT USAGE ON *.* TO `u_test_rw`@`%`    |
| GRANT `r_test_ro`@`%` TO `u_test_rw`@`%` |
+------------------------------------------+
2 rows in set (0.00 sec)

mysql [localhost] {u_test_rw} (test) > insert into t1 values (2);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {u_test_rw} (test) > select * from t1;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

The grants were adjusted, but the user still retains its original privileges until either *the session ends* or *it changes default database*.

mysql [localhost] {u_test_rw} (test) > use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql [localhost] {u_test_rw} (information_schema) > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql [localhost] {u_test_rw} (test) > insert into t1 values (3);
ERROR 1142 (42000): INSERT command denied to user 'u_test_rw'@'localhost' for table 't1'

How to repeat:
1. Connect to the database as user that uses a R/W role.
2. Insert a row
3. From a separate session, as root, drop the R/W role
4. in the first session, insert another row.
5. Check the user grants

Hello Giuseppe,

Thank you for the report and feedback!
Verified as described.

Thanks,
Umesh

Changed category to "Roles"

Posted by developer:
 
A session checks out a a privilege map which contain all current privileges. This happens naturally before query execution. If the underlying roles are dropped in another session the session will update its privileges on the next available moment but not sooner. This means that when testing the first statement will succeed because it's executing under privileges already granted. The reason privileges aren't updated at once is because we prioritize performance over absolute consistency here.

The following test case pass as expected:

CREATE DATABASE db1;
USE db1;
CREATE ROLE r_ro;
CREATE ROLE r_rw;
GRANT SELECT ON db1.* TO r_ro;
GRANT INSERT, UPDATE, DELETE ON db1.* TO r_rw;
CREATE USER u_ro IDENTIFIED BY 'optimism' DEFAULT ROLE r_ro;
CREATE USER u_rw IDENTIFIED BY 'positive_thinking' DEFAULT ROLE r_ro,r_rw;
GRANT r_ro TO u_ro;
GRANT r_ro, r_rw TO u_rw;
CREATE TABLE t1(id int);

connect(con1, localhost, u_rw, positive_thinking, db1);
SHOW GRANTS;
--echo # u_rw has INSERT grants. The following statement will succeed:
INSERT INTO db1.t1 VALUES (1);

connection default;
--echo # Root now drops the role which grants INSERT statements.
DROP ROLE r_rw;

connection con1;
--echo # The existing connection for u_rw should have updated.
USE db1;
--echo # SHOW GRANTS will fail with an error because the active roles
--echo # are now invalid
--error ER_ROLE_NOT_GRANTED
SHOW GRANTS;
--echo # Ignoring active roles will not fail:
SHOW GRANTS FOR u_rw USING r_ro;
--echo # The session checked out a new authorization map and
--echo # inserts are not possible despite previous active roles.
--error ER_TABLEACCESS_DENIED_ERROR
INSERT INTO db1.t1 VALUES (1);

connect(con2, localhost, u_rw, positive_thinking, db1);
--echo # A new connection is established for user 'u_rw' and it should
--echo # not demonstrate any INSERT grants nor any grants for r_rw.
SHOW GRANTS;
SHOW GRANTS FOR u_rw;
--echo # Any INSERT statement should fail too:
--error ER_TABLEACCESS_DENIED_ERROR
INSERT INTO t1 VALUES (2);
--echo # SELECT will still work:
SELECT * FROM t1;

connection default;
disconnect con1;
disconnect con2;
DROP ROLE r_ro;
DROP USER u_rw,u_ro;