Oracle7 Server SQL Reference

REVOKE (System Privileges and Roles)

Purpose

To revoke system privileges and roles from users and roles. To revoke object privileges from users and roles, use the REVOKE command (Object Privileges) described in the next section of this chapter.

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.

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

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

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

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

It is recommended that you perform media recovery using the Server Manager RECOVER command rather than the ALTER DATABASE command with the RECOVER clause.

Syntax

Keywords and Parameters

system_priv

is a system privilege to be revoked. For a list of the system privileges, see Table 4 - 11.

role

is a role to be revoked. For a list of the roles predefined by Oracle7, see Table 4 - 12.

FROM

identifies users and roles from which the system privileges or roles are revoked.

PUBLIC revokes the system privilege or role from all users.

Usage Notes

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

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

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

The REVOKE command can only revoke privileges and roles that have been granted directly with a GRANT statement. The REVOKE command cannot perform the following operations:

revoke privileges or roles not granted to the revokee

revoke roles granted through the operating system

revoke privileges or roles granted to the revokee through roles

A system privilege or role cannot appear more than once in the list of privileges and roles to be revoked. A user, a role, or PUBLIC cannot appear more than once in the FROM clause.

Example I

The following statement revokes 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.

Example II

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

REVOKE controller

	FROM hanson

HANSON can no longer enable the CONTROLLER role.

Example III

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.

Example IV

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

REVOKE vp

 	FROM ceo

VP is no longer granted to CEO.