Revoking a role (SQL)

Revoke a role from a user, a user-extended role, or a user-defined role.

Prerequisites

You must have administrative rights for the role to revoke user-defined roles. You must have MANAGE ROLES system privilege to revoke system roles.

You cannot revoke roles from the SYS role. You can revoke roles from other system roles, provided that these roles are not the default roles for the system role.

You can only revoke 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

Connect to the database.

Execute a REVOKE ROLE statement. For example:

Option

Statement

Revoke a role

REVOKE ROLE role-name FROM userid;

Revoke administrative rights for a role, but user can still exercise the role

REVOKE ADMIN OPTION FOR ROLE role-name FROM userid;

Results

The role is revoked from the specified user or role.

Example

Revoke the SYS_AUTH_RESOURCE_ROLE compatibility role from user Jim.

REVOKE ROLE SYS_AUTH_RESOURCE_ROLE FROM Jim;

Revoke only the administrative rights on the myRole role from user AnnW.

REVOKE ADMIN OPTION FOR ROLE myRole FROM AnnW;

Revoking a role (SQL)

Task

See also