REVOKE system_privileges_and_roles

Syntax

Purpose

To revoke system privileges and roles from users and roles. To revoke object privileges from users and roles, refer to "REVOKE schema_object_privileges". For information on granting system privileges and roles, see "GRANT system_privileges_and_roles".

Prerequisites

You must have been granted the system privilege or role with the ADMIN OPTION. Also, you can revoke any role if you have the GRANT ANY ROLE system privilege.

The REVOKE statement can revoke only privileges and roles that were previously granted directly with a GRANT statement. You cannot use this statement to revoke:

• Privileges or roles not granted to the revokee

• Roles granted through the operating system

• Privileges or roles granted to the revokee through roles

Keywords and Parameters

system_priv	is a system privilege to be revoked. For a list of the system privileges, see Table 7-5. If you revoke a privilege from a user, Oracle removes the privilege from the user's privilege domain. Effective immediately, the user cannot exercise the privilege.
	If you revoke a privilege from a role, Oracle removes the privilege from the role's privilege domain. Effective immediately, users with the role enabled cannot exercise the privilege. Also, other users who have been granted the role and subsequently enable the role cannot exercise the privilege. If you revoke a privilege from PUBLIC, Oracle removes the privilege from the privilege domain of each user who has been granted the privilege through PUBLIC. Effective immediately, such users can no longer exercise the privilege. However, the privilege is not revoked from users who have been granted the privilege directly or through roles. Restriction: A system privilege cannot appear more than once in the list of privileges to be revoked.
role	is a role to be revoked. For a list of the roles predefined by Oracle, see "GRANT system_privileges_and_roles". If you revoke a role from a user, Oracle makes the role unavailable to the user. If the role is currently enabled for the user, the user can continue to exercise the privileges in the role's privilege domain as long as it remains enabled. However, the user cannot subsequently enable the role. If you revoke a role from another role, Oracle removes the revoked role's privilege domain from the revokee role's privilege domain. Users who have been granted and have enabled the revokee role can continue to exercise the privileges in the revoked role's privilege domain as long as the revokee role remains enabled. However, other users who have been granted the revokee role and subsequently enable it cannot exercise the privileges in the privilege domain of the revoked role. If you revoke a role from PUBLIC, Oracle makes the role unavailable to all users who have been granted the role through PUBLIC. Any user who has enabled the role can continue to exercise the privileges in its privilege domain as long as it remains enabled. However, users cannot subsequently enable the role. The role is not revoked from users who have been granted the role directly or through other roles. Restriction: A system role cannot appear more than once in the list of roles to be revoked.
FROM	identifies users and roles from which the system privileges or roles are to be revoked. Restriction: A user, a role, or PUBLIC cannot appear more than once in the FROM clause.
PUBLIC	revokes the system privilege or role from all users.

Examples

The following statement revokes the DROP ANY TABLE system privilege from the users BILL and MARY:

REVOKE DROP ANY TABLE 
    FROM bill, mary; 

BILL and MARY can no longer drop tables in schemas other than their own.

The following statement revokes the role CONTROLLER from the user HANSON:

REVOKE controller 
    FROM hanson; 

HANSON can no longer enable the CONTROLLER role.

The following statement revokes the CREATE TABLESPACE system privilege from the CONTROLLER role:

REVOKE CREATE TABLESPACE 
   FROM controller; 

Enabling the CONTROLLER role no longer allows users to create tablespaces.

To revoke the role VP from the role CEO, issue the following statement:

REVOKE vp
  FROM ceo; 

VP is no longer granted to CEO.

To revoke the CREATE ANY DIRECTORY system privilege from user SCOTT, issue the following statement:

REVOKE CREATE ANY DIRECTORY FROM scott;