Oracle7 Server SQL Reference

REVOKE (Object Privileges)

Purpose

To revoke object privileges for a particular object from users and roles. To revoke system privileges or roles, use the REVOKE command (System Privileges and Roles) described in the previous section of this chapter.

Prerequisites

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

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the label at which you granted the object privilege or you must satisfy one of the following criteria:

If the label at which you granted the object privilege is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

If the label at which you granted the object privilege is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the label at which you granted the object privilege is not comparable to your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

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

REFERENCES

SELECT

UPDATE

ALL PRIVILEGES

revokes all object privileges that you have granted to the revokee.

identifies the object on which the object privileges are revoked. This object can be one of the following types:

table

view

sequence

procedure, stored function , or package

snapshot

synonym for a table, view, sequence, procedure, stored function, package, or snapshot

If you do not qualify object with schema, Oracle7 assumes the object is in your own schema.

FROM

identifies users and roles from which the object privileges are revoked.

PUBLIC revokes object privileges from all users.

CASCADE CONSTRAINTS

drops any referential integrity constraints that the revokee has defined using REFERENCES privilege that you are now revoking. You must specify this option along with the REFERENCES privilege or the ALL PRIVILEGES option if the revokee has exercised the REFERENCES privilege to define a referential integrity constraint.

Usage Notes

You can use this form of the REVOKE command to revoke object privileges from both users and roles:

If you revoke a privilege from a user: Oracle7 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: Oracle7 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: Oracle7 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. Note that the privilege is not revoked from users who have been granted the privilege directly or through roles.

You can only use the REVOKE command to revoke object privileges that you previously granted directly to the revokee. You cannot use the REVOKE command to perform the following operations:

revoke object privileges that you did not grant to the revokee

revoke privileges granted through the operating system

revoke privileges granted to roles granted to the revokee

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.

Object Privileges

Each object privilege authorizes some operation on an object. By revoking an object privilege, you prevent the revokee from performing that operation. For a summary of the object privileges for each type of object, see Table 4 - 13.

Revoking Multiple Identical Grants

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.

Cascading Revokes

Revoking an object privilege that a user has either granted or exercised to define an object or a referential integrity constraint has the following cascading effects:

If you revoke an object privilege from a user who has granted the privilege to other users or roles, Oracle7 also revokes the privilege from the grantees.

If you revoke an object privilege from a user whose 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 you revoke an object privilege on an object from a user whose schema contains a view on that object, Oracle7 invalidates the view.

If you revoke REFERENCES privilege from a user who has exercised the privilege to define referential integrity constraints, you must specify the CASCADE CONSTRAINTS option. Oracle7 then revokes the privilege and drops the constraints.

Example I

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 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

Example II

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 UPDATE privilege on REPORTS to any users (either directly or through roles), these users retain the privilege.

Example III

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

GRANT SELECT

	ON elly.eseq

 	TO blake

To revoke 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.

Example IV

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 REFERENCES privilege on SCOTT.EMP from BLAKE, by issuing the following statement that contains the CASCADE CONSTRAINTS option:

REVOKE REFERENCES

	ON scott.emp

	FROM blake

	CASCADE CONSTRAINTS

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

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