Dropping a role (SQL)

Drop user-defined, user-extended, and compatibility roles. You cannot drop system roles.

Prérequis

You must have administrative rights for the role being dropped.

You can only drop a role if all dependent roles meet the minimum required number of administrative users with active passwords, as set by the min_role_admins database option.

Task

Execute a DROP ROLE statement. For example:
DROP ROLE role-name;
If the role is assigned to users, you must specify the WITH REVOKE clause to drop the role:
DROP ROLE role-name WITH REVOKE;
If the role owns objects, you must specify the WITH DROP OBJECTS clause to drop the role:
DROP ROLE role-name WITH DROP OBJECTS;

Résultat

The role is dropped.

Exemple

Suppose no-one in your organization has been granted the SYS_AUTH_VALIDATE_ROLE compatibility role. You can drop it by executing the following statement:

DROP ROLE SYS_AUTH_VALIDATE_ROLE;

If there are users who have been granted the SYS_AUTH_VALIDATE_ROLE compatibility role, you can still drop it by executing the following statement:

DROP ROLE SYS_AUTH_VALIDATE_ROLE WITH REVOKE;

The following statement drops the SYS_AUTH_DBA_ROLE system role. This operation is possible if all of the system privileges it comes with are accounted for in other roles, with the appropriate number of administrators for those roles, as specified by the min_role_admins database option. The WITH REVOKE clause is added because the role has likely been assigned to other users.

DROP ROLE SYS_AUTH_DBA_ROLE WITH REVOKE;

Dropping a role (SQL)

Task

See also