Revoke the dropped roles automatically when the role is dropped

[flaneur2020]

Summary

currently when the granted role is dropped, the dropped role is still displayed in an user's SHOW GRANTS.

this behaviour has similarities with MySQL. but it might be better to revoke the dropped roles automatically when the role is dropped, like snowflake does 1:

If a role is a grantor of roles to users, dropping the role revokes these grants automatically.

[junnplus]

Can we just clear role cache from the role_cache_manager?