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 after the drop. If the minimum value is not maintained, the command fails.

Prerequisites

Requires 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

A user-defined role can be deleted as long as all dependent roles retain the minimum required number of administrator users with active passwords after the drop. If the minimum value is not maintained, the delete fails.

If a user-extended role is converted back to a user, the objects owned are not deleted. They remain owned by the converted user.

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 the 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.