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"