Dropping a Compatibility Role

All compatibility roles, with the exception of SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE can be dropped. SYS_AUTH_SA_ROLE and SYS_AUTH_SSO_ROLE are dropped automatically when SYS_AUTH_DBA_ROLE is dropped.

Prerequisites

Administrative privilege over the role being dropped.

Task

Unlike user-defined roles, compatibility roles cannot be user-extended roles, nor can they own objects. Therefore, only the WITH REVOKE clause is valid when you are dropping a compatibility role. As with user-defined roles, the WITH REVOKE clause is required when dropping a compatibility role to which users have been granted the underlying system privileges of the role.

To drop a compatibility role, execute one of the following statements:

Drop Condition	Statement
Compatibility role that does not have its underlying system privileges granted to any user The role is successfully dropped if: No users are currently granted the underlying system privileges of the role. The role being dropped is not SYS_AUTH_SA_ROLE, SYS_AUTH_SSO_ROLE or SYS_AUTH_DBA_ROLE.	DROP ROLE `role_name`
Compatibility role that does have underlying system privileges granted to users The role is successfully dropped if: The role being dropped is not SYS_AUTH_SA_ROLE, SYS_AUTH_SSO_ROLE or SYS_AUTH_DBA_ROLE.	DROP ROLE `role_name` WITH REVOKE