REVOKE statement

Use the REVOKE statement to cancel access privileges or roles that are held by users, by roles, or by PUBLIC, or to cancel user security labels or exemptions from the rules of security policies.

Syntax

(1)
FROM options

1  FROM 
2.2.1   %User List3
2.2.2.1! CASCADE
2.2.2.1?  RESTRICT
2.2.1 1
2.2.2.1+ ,
2.2.2.2.1 %Role Name4
2.2.2.2.1  ' user '
1?  AS
2.1 revoker
2.1  ' revoker '
Notes:
Element Description Restrictions Syntax
revoker Authorization identifier of the grantor of the privileges to be revoked Must be grantor of the specified privileges Owner name
role Role from which you revoke another role Must exist Owner name
user User whose role (or default role) you cancel Must exist Owner name

Usage

To cancel privileges on one or more fragments of a table that has been fragmented by expression, see REVOKE FRAGMENT statement.

You can revoke privileges if any of the following conditions is true for the privileges that you are attempting to revoke on some database object:
  • You granted them and did not designate another user as grantor.
  • The GRANT statement specified you as grantor.
  • You are revoking privileges from PUBLIC on an object that you own, and those privileges were granted by default when you created the object.
  • You have database-level DBA privileges and you specify in the AS clause the name of a user who was grantor of the privilege.
The REVOKE statement can cancel any of the following access privileges or roles that a user, or PUBLIC, or a role currently holds:
  • Privileges on the database (but a role cannot hold database-level privileges)
  • Privileges on a table, synonym, view, or sequence object
  • Privileges on a user-defined data type (UDT), a user-defined routine (UDR), or on the SPL language
  • A non-default role, or the default role of PUBLIC or of a user.

You cannot revoke privileges from yourself. You cannot revoke grantor status from another user. To revoke a privilege that was granted to another user by the AS grantor clause of the GRANT statement, you must have the DBA privilege, and you must use the AS clause to specify that user as revoker.

Delimiting revoker, role, and user identifiers

The REVOKE statement syntax diagram is simplified, because
  • the quotation-mark delimiters that can enclose the revoker, role, or user names are optional,
  • and you can also substitute double ( " ) quotation marks for single ( ' ) quotation marks to delimit those identifiers. Both delimiters must be the same character.
If you enclose revoker, role, or user in quotation marks, the name is case sensitive, and the database server stores it in the system catalog exactly as you enter it in the REVOKE statement. For example, the following statement revokes the DBA privilege from user sam.
REVOKE DBA FROM "sam";
If another user who holds the DBA privilege has the authorization identifier Sam, she would not be affected by the REVOKE example, because her case sensitive user name does not match the delimited name. In a database that was not created as MODE ANSI, however, the following example revokes the DBA privilege of both users sam and Sam:
REVOKE DBA FROM sam;

In an ANSI-compliant database, if you do not use quotation marks as delimiters, that identifier is stored in uppercase letters.

In locales that support letter case, authorization identifiers for distinct users or roles that differ only in letter case might produce unexpected results, if GRANT and REVOKE statements use both delimited and undelimited user and role specifications to manage access privileges.