Deleting a User-Defined Role

Delete a user-defined role from the database as long as all dependent roles retain the minimum required number of administrator users with active passwords. If the minimum value is not maintained, the command fails.

Prerequisites

Administrative privilege over the role being dropped.
If the role being dropped is a user-defined role, the role does not own any objects.

Task

If a user-extended role is converted back to a user, the objects owned are not deleted; the converted user continues to own them.

The type of role being deleted and whether it was granted to users determines the clauses required by the DROP statement.

FROM USER – required when deleting a user-extended role.
WITH REVOKE – required to delete a role that has been granted to multiple users and roles.

To delete a user-defined role, execute one of these statements:

Delete Condition	Statement
User-defined role has not been granted any members.	DROP ROLE `role_name`
User-extended role has been granted members.	DROP ROLE `role_name` WITH REVOKE
User-extended role has not been granted any members^*.	DROP ROLE FROM USER `role_name`
User-extended role has been granted members^*.	DROP ROLE FROM USER `role_name` WITH REVOKE

^*User-extended role becomes a regular user.