Bug #85559 Dropping a role does not remove the associated default roles

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

(Changed category to Server: Security: Privileges)

Hello Giuseppe,

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

Thanks,
Umesh

Changed category to "Roles"

Related to Bug#85561

Posted by developer:

[29 Aug 2017 1:54] Kristofer Pettersson

A default role is dropped when the corresponding user is dropped and not the
when the role it is suppose to represent is dropped because the default role
is an attribute (a policy even) which belows to the authorization ID (user)
and not to the role. That the policy is invalid isn't an issue and can be a
feature as it relax the required order of operations. The default role is in
this sense not itself an object.