Description:
When a role that has been assigned as default to a user is dropped, the user still reports it as default role, although the role does not exist anymore.
Before dropping the role, the user reports the following:
mysql [localhost] {u_test_rw} ((none)) > 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} ((none)) > select current_role();
+---------------------------------+
| current_role() |
+---------------------------------+
| `r_test_ro`@`%`,`r_test_rw`@`%` |
+---------------------------------+
# DROPPING THE ROLE (as root)
mysql [localhost] {root} ((none)) > drop role r_test_rw;
Query OK, 0 rows affected (0.03 sec)
mysql [localhost] {root} ((none)) > use mysql
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] {root} (mysql) > select * from role_edges;
+-----------+-----------+---------+------------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+------------+-------------------+
| % | r_db1 | % | u_db1 | N |
| % | r_test_ro | % | u_test_ro | N |
| % | r_test_ro | % | u_test_rw | N |
| localhost | root | % | other_root | N |
+-----------+-----------+---------+------------+-------------------+
4 rows in set (0.00 sec)
mysql [localhost] {root} (mysql) > select * from default_roles;
+------+-----------+-------------------+-------------------+
| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+-----------+-------------------+-------------------+
| % | u_test_ro | % | r_test_ro |
| % | u_test_rw | % | r_test_ro |
| % | u_test_rw | % | r_test_rw |
+------+-----------+-------------------+-------------------+
3 rows in set (0.00 sec)
As you can see, the dropped role is still listed in the default roles table.
How to repeat:
# (1)
# Create two roles and two users
DROP ROLE IF EXISTS r_test_rw;
DROP ROLE IF EXISTS r_test_ro;
CREATE ROLE r_test_ro;
CREATE ROLE r_test_rw;
GRANT SELECT on test.* TO r_test_ro;
GRANT INSERT, UPDATE, DELETE on test.* TO r_test_rw;
DROP USER IF EXISTS u_test_ro;
DROP USER IF EXISTS u_test_rw;
CREATE USER u_test_ro IDENTIFIED BY 'msandbox';
CREATE USER u_test_rw IDENTIFIED BY 'msandbox';
GRANT r_test_ro TO u_test_ro;
ALTER USER u_test_ro DEFAULT ROLE r_test_ro;
GRANT r_test_ro, r_test_rw TO u_test_rw;
ALTER USER u_test_rw DEFAULT ROLE r_test_ro, r_test_rw;
# (2)
# Drop one role
DROP ROLE r_test_rw;
# (3)
# Check the contents of mysql.default_roles