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
