REVOKE ROLE statement

Revokes roles and privileges from users and roles.

Syntax

Revoke system roles

REVOKE ROLE system-role 
FROM grantee, ...

grantee : 
{ system-role | userid }

system-role : 
dbo
| DIAGNOSTICS
| PUBLIC
| rs_systabgroup
| SA_DEBUG
| SYS
| SYS_REPLICATION_ADMIN_ROLE
| SYS_RUN_REPLICATION_ROLE
| SYS_SAMONITOR_ADMIN_ROLE
| SYS_SPATIAL_ADMIN_ROLE

Revoke user-defined roles

REVOKE [ { EXERCISE | ADMIN } OPTION FOR ] ROLE user-defined-role 
FROM grantee, ...

grantee : 
{ system-role | userid }

Revoke compatibility roles

REVOKE [ { EXERCISE | ADMIN } OPTION FOR ] ROLE compatibility-role-name 
FROM grantee, ...

compatibility-role-name : 
SYS_AUTH_BACKUP_ROLE
| SYS_AUTH_DBA_ROLE
| SYS_AUTH_PROFILE_ROLE
| SYS_AUTH_READCLIENTFILE_ROLE
| SYS_AUTH_READFILE_ROLE
| SYS_AUTH_RESOURCE_ROLE
| SYS_AUTH_SA_ROLE
| SYS_AUTH_SSO_ROLE
| SYS_AUTH_VALIDATE_ROLE
| SYS_AUTH_WRITECLIENTFILE_ROLE
| SYS_AUTH_WRITEFILE_ROLE

grantee : 
{ system-role | userid }

Parameters

{ EXERCISE | ADMIN } OPTION FOR clause
Specify the ADMIN OPTION FOR clause to revoke administration rights for the role, but leave exercise rights. Specify the EXERCISE OPTION FOR clause to revoke exercise rights for the role, but leave administration rights. If the clause is not specified, both rights are revoked.

Remarks

If a role that is being revoked was not granted to grantee, then the statement does nothing, and does not return an error.

REVOKE ROLE fails with an error if, as a consequence of executing the statement, the number of administrators for the role being revoked would fall below the required minimum as set by the min_role_admins database option.

When revoking a role from the MANAGE ROLES system privilege, you must use the special internal representation SYS_MANAGE_ROLES_ROLE. For example, REVOKE ROLE role-name FROM SYS_MANAGE_ROLES_ROLE;.

The REVOKE syntax related to authorities, permissions, and groups used in pre-16.0 versions of the software is still supported but deprecated.

Privileges

You must have administration rights for the role that you are revoking.

To revoke the SYS_RUN_REPLICATION_ROLE system role, you must have the SYS_REPLICATION_ADMIN_ROLE.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

This example revokes the SYS_AUTH_VALIDATE_ROLE compatibility role from fictitious user Jim.

REVOKE ROLE SYS_AUTH_VALIDATE_ROLE FROM Jim;

This example revokes the DIAGNOSTICS system role from a fictitious user named Administrator.

REVOKE ROLE DIAGNOSTICS FROM Administrator;

The following statement revokes SYS_REPLICATION_ADMIN_ROLE from user Sam_Singer.

REVOKE ROLE SYS_REPLICATION_ADMIN_ROLE FROM Sam_Singer;

The following statement revokes SYS_RUN_REPLICATION_ROLE from user Sam_Singer.

REVOKE ROLE SYS_RUN_REPLICATION_ROLE FROM Sam_Singer;