REVOKE Table Access Privileges

Revokes from one or more users or groups the privilege of accessing a specified table-like object in a specified way.
idms19
Revokes from one or more users or groups the privilege of accessing a specified table-like object in a specified way.
Authorization
To revoke a table-like object access privilege, one of the following must be true:
  • You hold the corresponding grantable privilege on the table-like object.
  • You own the table-like object.
  • You hold DBADMIN privilege on the database that contains the table-like object.
  • You hold SYSADMIN privilege.
Syntax
►►─── REVOKE ──┬─ ACCESS ──────────┬──────────────────────────────────────────►                │  ┌───── , ──────┐ │                └──▼─┬─ DELETE ─┬─┴─┘                     ├─ INSERT ─┤                     ├─ SELECT ─┤                     └─ UPDATE ─┘    ►─── ON table 
table-name
 ────────────────────────────────────────────────────►               ┌─────────────── , ──────────────┐  ►─── FROM ─▼─┬─ PUBLIC ───────────────────┬─┴────────────────────────────────►◄               └─ 
authorization-identifier
 ─┘
Parameters
 
  • ACCESS
    Revokes the DELETE, INSERT, SELECT, and UPDATE privileges on the table-like object identified in the ON parameter from the users or groups identified in the FROM parameter.
  • DELETE
    Revokes the DELETE privilege on the table-like object identified in the ON parameter from the users or groups identified in the FROM parameter.
  • INSERT
    Revokes the INSERT privilege on the table-like object identified in the ON parameter from the users or groups identified in the FROM parameter.
  • SELECT
    Revokes the SELECT privilege on the table-like object identified in the ON parameter from the users or groups identified in the FROM parameter.
  • UPDATE
    Revokes the UPDATE privilege on the table-like object identified in the ON parameter from the users or groups identified in the FROM parameter.
  • ON table table-name
    Identifies the table-like object to which the access privileges apply.
    Expanded syntax for
    table-name
    is presented in Notes on Security Statement Syntax.
    You can wildcard any identifier in
    table-name
    when you grant access on
    table-name
    . If you specify
    schema-name
    in
    table-name
    , the wildcard character is valid after the period following
    schema-name
    .
    For more information on wildcarding, see Using a Wildcard.
    If you do not specify
    schema-name
    , it defaults to the current schema in effect for your session.
  • FROM
    Specifies the users or groups from whom you are revoking the specified table access privileges.
  • PUBLIC
    Specifies all users.
    The privileges must have been previously given to PUBLIC by means of the GRANT statement.
  • authorization-identifier
    Identifies a user or group.
    The privileges must have been previously given to
    authorization-identifier
    by means of the GRANT statement.
    Expanded syntax for
    authorization-identifier
    is presented in Notes on Security Statement Syntax.
Usage
The ACCESS Keyword
When you use the ACCESS keyword with a GRANT statement, you grant a set of access privileges on a table-like object to one or more users or groups.
When you use the ACCESS keyword with a REVOKE statement, you revoke any access privileges that have been previously granted on the table or view from the specified users or groups.
This means that if you GRANT SELECT privilege on a table, you can revoke the privilege with either a REVOKE SELECT statement or a REVOKE ACCESS statement. Using REVOKE ACCESS is an efficient technique when you intend to revoke all access privileges on a table from a user or group, whether the privileges were granted singly or as a set.
Similarly, you can GRANT ACCESS on a table to a user and then REVOKE DELETE on the table from the same user as a way to grant all but one table access privilege.
Revoking Selected Privileges on a Table
The following statement revokes the SELECT and UPDATE privileges on the EMPLOYEE table associated with the current schema from users KRP, SAE, and PGD:
revoke select, update    on employee    from krp, sae, pgd;
More Information
For more information
about granting table access privileges
, see GRANT Table Access Privileges.