REVOKE schema_object_privileges

Syntax

Purpose

To revoke object privileges for a particular object from users and roles.

For information on granting schema object privileges, see "GRANT object_privileges". To revoke system privileges or roles, refer to "REVOKE system_privileges_and_roles".

Each object privilege authorizes some operation on an object. By revoking an object privilege, you prevent the revokee from performing that operation. However, multiple users may grant the same object privilege to the same user, role, or PUBLIC. To remove the privilege from the grantee's privilege domain, all grantors must revoke the privilege. If even one grantor does not revoke the privilege, the grantee can still exercise the privilege by virtue of that grant.

For a summary of the object privileges for each type of object, see Table 7-7.

Prerequisites

You must have previously granted the object privileges to each user and role.

You can use the REVOKE statement only to revoke object privileges that you previously granted directly to the revokee. You cannot use this statement to revoke:

• Object privileges that you did not grant to the revokee

• Privileges granted through the operating system

• Privileges granted to roles granted to the revokee

Keywords and Parameters

object_priv	is an object privilege to be revoked. You can substitute any of the following values: ALTER, DELETE, EXECUTE, INDEX, INSERT, READ, REFERENCES, SELECT, UPDATE.
	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 that user has granted that privilege to other users or roles, Oracle also revokes the privilege from those other users or roles. - If that user's schema contains a procedure, function, or package that contains SQL statements that exercise the privilege, the procedure, function, or package can no longer be executed. - If that user's schema contains a view on that object, Oracle invalidates the view. - If you revoke the REFERENCES privilege from a user who has exercised the privilege to define referential integrity constraints, you must specify the CASCADE CONSTRAINTS clause. 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. Other users who have been granted the role cannot exercise the privilege after enabling the role. 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, all such users are restricted from exercising the privilege. However, the privilege is not revoked from users who have been granted the privilege directly or through roles. Restriction: A privilege cannot appear more than once in the list of privileges to be revoked. A user, a role, or PUBLIC cannot appear more than once in the FROM clause.
ALL PRIVILEGES	revokes all object privileges that you have granted to the revokee.
	Note: If no privileges have been granted on the object, Oracle takes no action and does not return an error.
ON DIRECTORY directory_object	identifies a directory object on which privileges are revoked. You cannot qualify directory_object with schema when using the ON DIRECTORY clause. The object must be a directory. See "CREATE DIRECTORY".
ON object	identifies the object on which the object privileges are revoked. This object can be A table, view, sequence, procedure, stored function, or package, materialized view/snapshot A synonym for a table, view, sequence, procedure, stored function, package, or materialized view/snapshot A library, indextype, or user-defined operator If you do not qualify object with schema, Oracle assumes the object is in your own schema.
	If you revoke the SELECT object privilege (with or without the GRANT OPTION) on the containing table or snapshot of a materialized view, the materialized view will be invalidated. If you revoke the SELECT object privilege (with or without the GRANT OPTION) on any of the master tables of a materialized view, both the view and its containing table or materialized view will be invalidated.
FROM	identifies users and roles from which the object privileges are revoked.
	PUBLIC	revokes object privileges from all users.
CASCADE CONSTRAINTS	This clause is relevant only if you revoke the REFERENCES privilege or ALL [PRIVILEGES]. It drops any referential integrity constraints that the revokee has defined using the REFERENCES privilege (which might have been granted either explicitly or implicitly through a grant of ALL [PRIVILEGES]).
FORCE	revokes EXECUTE object privilege on user-defined type objects with table or type dependencies. You must use the FORCE clause to revoke the EXECUTE object privilege on user-defined type objects with table dependencies. If you specify FORCE, all privileges will be revoked, but all dependent objects are marked INVALID, data in dependent tables becomes inaccessible, and all dependent function-based indexes are marked UNUSABLE. (Regranting the necessary type privilege will revalidate the table.) For detailed information about type dependencies and user-defined object privileges, see Oracle8i Concepts.

Examples

Basic Example

You can grant DELETE, INSERT, SELECT, and UPDATE privileges on the table BONUS to the user PEDRO with the following statement:

GRANT ALL 
    ON bonus TO pedro; 

To revoke the DELETE privilege on BONUS from PEDRO, issue the following statement:

REVOKE DELETE 
    ON bonus FROM pedro; 

To revoke the remaining privileges on BONUS that you granted to PEDRO, issue the following statement:

REVOKE ALL 
    ON bonus FROM pedro; 

PUBLIC Example

You can grant SELECT and UPDATE privileges on the view REPORTS to all users by granting the privileges to the role PUBLIC:

GRANT SELECT, UPDATE 
    ON reports TO public; 

The following statement revokes UPDATE privilege on REPORTS from all users:

REVOKE UPDATE 
    ON reports FROM public;

Users can no longer update the REPORTS view, although users can still query it. However, if you have also granted the UPDATE privilege on REPORTS to any users, either directly or through roles, these users retain the privilege.

Schema Example

You can grant the user BLAKE the SELECT privilege on the ESEQ sequence in the schema ELLY with the following statement:

GRANT SELECT 
    ON elly.eseq TO blake; 

To revoke the SELECT privilege on ESEQ from BLAKE, issue the following statement:

REVOKE SELECT 
    ON elly.eseq FROM blake; 

However, if the user ELLY has also granted SELECT privilege on ESEQ to BLAKE, BLAKE can still use ESEQ by virtue of ELLY's grant.

CASCADE CONSTRAINTS Example

You can grant BLAKE the privileges REFERENCES and UPDATE on the EMP table in the schema SCOTT with the following statement:

GRANT REFERENCES, UPDATE 
    ON scott.emp TO blake; 

BLAKE can exercise the REFERENCES privilege to define a constraint in his own DEPENDENT table that refers to the EMP table in the schema SCOTT:

CREATE TABLE dependent 
(dependno   NUMBER, 
 dependname VARCHAR2(10), 
 employee   NUMBER                   
    CONSTRAINT in_emp REFERENCES scott.emp(ename) ); 

You can revoke the REFERENCES privilege on SCOTT.EMP from BLAKE, by issuing the following statement that contains the CASCADE CONSTRAINTS clause:

REVOKE REFERENCES 
    ON scott.emp 
    FROM blake 
    CASCADE CONSTRAINTS; 

Revoking BLAKE's REFERENCES privilege on SCOTT.EMP causes Oracle to drop the IN_EMP constraint, because BLAKE required the privilege to define the constraint.

However, if BLAKE has also been granted the REFERENCES privilege on SCOTT.EMP by a user other than you, Oracle does not drop the constraint. BLAKE still has the privilege necessary for the constraint by virtue of the other user's grant.

Directory Example

You can revoke READ privilege on directory BFILE_DIR1 from SUE, by issuing the following statement:

REVOKE READ ON DIRECTORY bfile_dir1 FROM sue;