Revokes a role from users or other roles.
Required Privileges
- User DBC.
- A user who was granted the specified role WITH ADMIN OPTION.
A role is automatically granted to the creator of the role WITH ADMIN OPTION.
- A user who has an active role to which the specified role was granted WITH ADMIN OPTION. An active role can be either a current role or a nested role of a current role.
Syntax
REVOKE [ ADMIN OPTION FOR ] role_name [,...] { TO | FROM } { user_name | role_name } [,...] [;]
Syntax Elements
- ADMIN OPTION FOR
- Indicates that the roles and users who were granted this role lose the privilege to use GRANT, REVOKE, and DROP ROLE statements to administer the specified role.
- role_name
- One or more comma-separated names of roles that are being revoked.
- TO
- FROM
- user_name
- role_name
- The names of roles or users or both from which the role or the ability to administer the role is being revoked.
Example of Revoking One Role and Granting Another
If user marks gets a promotion from sales to management, the DBA can use the following statements to revoke the sales role and grant the management role:
REVOKE sales FROM marks; GRANT management TO marks;
The Effects of Revoking A Role
Roles define privileges on database objects. A user who activates a role inherits all the privileges for the role and its nested roles. A user can only activate a role that has been granted to that user.
Users can undergo role changes within their organization. An administrator can revoke a role when users no longer require access to the objects that the role has privileges to. An administrator can also revoke the WITH ADMIN OPTION privilege on a role when users no longer require the privilege to grant the role to users or other roles.
Authorized users can revoke the WITH ADMIN OPTION privilege on a role from the creator of the role.
The effect of revoking a role is immediate. Users who are logged on with the revoked role as the current role or a nested role of the current role lose the privileges that the role defines.
Users who have a default role set to the revoked role do not receive errors or warnings the next time they log on. However, the system does not use the obsolete default role for privilege validation. If the role is again granted to users, the default role again becomes the current role the next time users log on.