Revokes various privileges from the specified users of the database. There are two forms of the REVOKE statement:

  • Revoke database-wide privileges, either system administration (DBA), general creation (RESOURCE), audit administration (AUDIT_ADMIN), audit archive (AUDIT_ARCHIVE), or audit insert (AUDIT_INSERT)
  • Revoke various privileges on specific tables and views, sequences, stored procedures and user defined functions.

Syntax

REVOKE  { RESOURCE , DBA, AUDIT_ADMIN, AUDIT_ARCHIVE, AUDIT_INSERT}  
   FROM {user_identifier[ , user_identifier] , ...}  
   [ RESTRICT | CASCADE ]  
   [ GRANTED BY ANY_USER ];

Parameters

RESOURCE

Revokes the privilege to issue CREATE statement for specified users.

DBA

Revokes the privilege to create, access, modify, or delete any database object, and revokes the privilege to grant other users any privileges for specified users.

AUDIT_ADMIN

Revokes the privilege to administer and maintain a database auditing system for specified users.

AUDIT_ARCHIVE

Revokes the privilege to read and delete audit records for specific users.

AUDIT_INSERT

Revokes the privilege to insert application audit records for specified users.

FROM user_identifier[ , user_identifier] , ...

Revokes the specified privileges on the table or view from the specified list of users.

EXECUTE

Revokes the EXECUTE privileges for the specified list of users from using user defined functions and stored procedures.

RESTRICT | CASCADE

Prompts SQL to check if the privilege being revoked was passed onto other users. If the original privilege was included the WITH GRANT OPTION clause, then the REVOKE statement fails and generates an error. If the privilege was not passed on, the REVOKE statement succeeds.

If the REVOKE statement specifies CASCADE, revoking the access privileges from a user also revokes the privileges from all users who received the privilege from that user.

If the REVOKE statement does not specify either RESTRICT or CASCADE, then the behavior follows only CASCADE.

user_identifier

Identifies a username. For a tenant user, you must mention the fully qualified user name, username@domain_name, to revoke access from a user.

Uses the following syntax:

username | username@domain_name
Note: CASCADE is not supported for AUDIT_ADMIN, AUDIT_ARCHIVE, and AUDIT_INSERT privileges. The only user who can revoke an audit privilege is the user who granted it.
GRANTED BY ANY_USER

A DBA can use this phrase to revoke all access privileges to a table for a specified user, even if the user was the creator of the table. This phrase is only applicable to users with DBA privileges.

Syntax to revoke privileges on stored procedures

Use the following syntax to revoke privileges for user(s) on specified procedures:

REVOKE EXECUTE ON StoredJavaProcedureName
 FROM  {username[, username] , ...|  PUBLIC }
  [ RESTRICT | CASCADE ];

Syntax to revoke sequence privileges

Use the following syntax to revoke sequence privileges:

REVOKE [SELECT | UPDATE]
ON SEQUENCE schema.sequence
FROM user_name[,user_name]...

Parameters

SELECT

For the specified users, removes the privilege to read data from the sequence.

UPDATE

For the specified users, removes the privilege to modify data for the sequence.

Examples

In this example, the audit administration privilege is revoked from bsmith:

REVOKE AUDIT_ADMIN FROM bsmith RESTRICT;

Revoking privileges to modify a sequence

In this example, the UPDATE permission of the sequence pub.customer_sequence is revoked from the user slsadmin:


REVOKE UPDATE
     ON SEQUENCE pub.customer_sequence     FROM slsadmin;