SQL Statements: DROP SEQUENCE to UPDATE, 18 of 27

REVOKE

Purpose

Use the REVOKE statement to:

Revoke system privileges from users and roles
Revoke roles from users and roles
Revoke object privileges for a particular object from users and roles
See Also:

GRANT for information on granting system privileges and roles

Table 11-3 for a summary of the object privileges for each type of object

Prerequisites

To revoke a system privilege or role, you must have been granted the privilege with the ADMIN OPTION.

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

To revoke an object privilege, you must have previously granted the object privileges to each user and role.

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 or object privileges granted through the operating system
Privileges or roles granted to the revokee through roles

Syntax

revoke_system_privileges_and_roles_clause::=

revoke_object_privileges_clause::=

grantee_clause::=

object_clause::=

Keywords and Parameters

`revoke_system_privileges_and_roles_clause`

system_privilege

Specify the system privilege to be revoked.

See Also: Table 11-1 for a list of the system privileges

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.

Oracle provides a shortcut for specifying all system privileges at once:

ALL PRIVILEGES: Specify ALL PRIVILEGES to revoke all the system privileges listed in Table 11-1 .

role

Specify the role to be revoked.

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.

See Also: Table 11-2 for a list of the roles predefined by Oracle

grantee_clause

FROM grantee_clause identifies users or roles from which the system privilege, role, or object privilege is to be revoked.

PUBLIC

Specify PUBLIC to revoke the privileges or roles from all users.

`revoke_object_privileges_clause`

object_privilege

Specify the object privilege to be revoked. You can substitute any of the following values: ALTER, DELETE, EXECUTE, INDEX, INSERT, READ, REFERENCES, SELECT, UPDATE.

Note: Each privilege authorizes some operation. By revoking a privilege, you prevent the revokee from performing that operation. However, multiple users may grant the same 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.

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]

Specify ALL to revoke all object privileges that you have granted to the revokee. (The keyword PRIVILEGES is optional.)

Note: If no privileges have been granted on the object, Oracle takes no action and does not return an error.

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

Specify FORCE to revoke the EXECUTE object privilege on user-defined type objects with table or type dependencies. You must use FORCE 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.)

See Also: Oracle8i Concepts for detailed information about type dependencies and user-defined object privileges

object_clause

ON object_clause identifies the objects on which privileges are to be revoked.

object

Specify the object on which the object privileges are to be 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.

DIRECTORY directory_name

Specify the directory object on which privileges are to be revoked. You cannot qualify directory_name with schema. The object must be a directory.

See Also: CREATE DIRECTORY

JAVA SOURCE | RESOURCE

The JAVA clause lets you specify a Java source or resource schema object on which privileges are to be revoked.

Examples

Revoke a System Privilege from Users Example

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.

Revoke a Role from a User Example

The following statement revokes the role controller from the user hanson:

REVOKE controller 
    FROM hanson;

hanson can no longer enable the controller role.

Revoke a System Privilege from a Role Example

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.

Revoke a Role from a Role Example

To revoke the role vp from the role ceo, issue the following statement:

REVOKE vp
  FROM ceo;

VP is no longer granted to ceo.

Revoke an Object Privilege from a User 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;

Revoke All Object Privileges from a User Example

To revoke the remaining privileges on bonus that you granted to pedro, issue the following statement:

REVOKE ALL 
    ON bonus FROM pedro;

Revoke Object Privileges from 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.

Revoke an Object Privilege on a Sequence from a User 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 eseqfrom 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.

Revoke an Object Privilege with `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.

Revoke an Object Privilege on a Directory from a User 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;

`system_privilege`	Specify the system privilege to be revoked. See Also: Table 11-1 for a list of the system privileges
	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.
	Oracle provides a shortcut for specifying all system privileges at once: `ALL` `PRIVILEGES`: Specify `ALL` `PRIVILEGES` to revoke all the system privileges listed in Table 11-1 .
`role`	Specify the role to be revoked.
	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. See Also: Table 11-2 for a list of the roles predefined by Oracle
`grantee_clause`	`FROM` `grantee_clause` identifies users or roles from which the system privilege, role, or object privilege is to be revoked.
	`PUBLIC`	Specify `PUBLIC` to revoke the privileges or roles from all users.

`object_privilege`	Specify the object privilege to be revoked. You can substitute any of the following values: `ALTER`, `DELETE`, `EXECUTE`, `INDEX`, `INSERT`, `READ`, `REFERENCES`, `SELECT`, `UPDATE`.
	Note: Each privilege authorizes some operation. By revoking a privilege, you prevent the revokee from performing that operation. However, multiple users may grant the same 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.
	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`]	Specify ALL to revoke all object privileges that you have granted to the revokee. (The keyword `PRIVILEGES` is optional.) Note: If no privileges have been granted on the object, Oracle takes no action and does not return an error.
`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`	Specify `FORCE` to revoke the `EXECUTE` object privilege on user-defined type objects with table or type dependencies. You must use `FORCE` 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.) See Also: Oracle8i Concepts for detailed information about type dependencies and user-defined object privileges
`object_clause`	`ON` `object_clause` identifies the objects on which privileges are to be revoked.
	`object`	Specify the object on which the object privileges are to be 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.
	`DIRECTORY` `directory_name`	Specify the directory object on which privileges are to be revoked. You cannot qualify `directory_name` with schema. The object must be a directory. See Also: CREATE DIRECTORY
	`JAVA` `SOURCE` \| `RESOURCE`	The `JAVA` clause lets you specify a Java source or resource schema object on which privileges are to be revoked.