REVOKE Table Access Privileges

The REVOKE Table Access Privileges authorization statement removes from one or more users or groups the privilege of performing selected actions on a specified table, view, function, procedure or table procedure.
idmscu19
The REVOKE Table Access Privileges authorization statement removes from one or more users or groups the privilege of performing selected actions on a specified table, view, function, procedure or table procedure.
This article describes the following information:
2
2
Authorization
To issue a REVOKE statement for a table, view, function, procedure or table procedure privilege, you must own, hold the corresponding grantable privilege on the table, view, function, procedure or table procedure, or hold the DBADMIN privilege on the database.
Syntax
  ►►─── REVOKE  ─┬─ ACCESS ──────────┬──────────────────────────────────────────►                │  ┌───── , ──────┐ │                └──▼─┬─ DELETE ─┬─┴─┘                     ├─ INSERT ─┤                     ├─ SELECT ─┤                     └─ UPDATE ─┘  ►─── ON table─┬─ 
table-name
 ───────────────────────────────────────────────┬─►                └┬────────────────┬──────────── 
function-identifier
 ─────────┘                 └─ 
schema-name.
 ─┘             ┌─────────────── , ──────────────┐  ►─── FROM ─▼─┬─ PUBLIC ───────────────────┬─┴────────────────────────────────►◄               └─ 
authorization-identifier
 ─┘  
Parameters
  • ACCESS
    Removes the DELETE, INSERT, SELECT, and UPDATE privileges on the named table, view, function, procedure or table procedure from the users or groups identified in the FROM parameter.
  • DELETE
    Removes the DELETE privilege on the table, view, or table procedure identified in the ON parameter from the users or groups identified in the FROM parameter.
  • INSERT
    Removes the INSERT privilege on the table, view, or table procedure identified in the ON parameter from the users or groups identified in the FROM parameter.
  • SELECT
    Removes the SELECT privilege on the table, view, function, procedure or table procedure identified in the ON parameter from the users or groups identified in the FROM parameter.
  • UPDATE
    Removes the UPDATE privilege on the table, view, or table procedure identified in the ON parameter from the users or groups identified in the FROM parameter.
  • ON table table-name
    Specifies the table, view, procedure or table procedure which the access privileges apply.
    If
    table-name
    does not include a schema name qualifier, the schema name qualifier defaults to the current schema in effect for your SQL session. For expanded
    table-name
    syntax, see Expansion of Table-name.
  • schema-name
    Specifies the schema with which the function identified by function-identifier is associated. If schema-name is not specified, the schema qualifier defaults to the current schema in effect for your SQL session.
  • function-identifier
    Identifies the function to which the privileges apply.
  • FROM
    Identifies the users from whom you are removing 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. For expanded
    authorization-identifier
    syntax, see Expansion of Authorization-identifier.
Usage
The ACCESS Keyword
When you use the ACCESS keyword with a GRANT statement, you grant a set of access privileges on a table, view, function, procedure or table procedure 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, view, function, procedure or table procedure from the specified users or groups.
Therefore, if you GRANT SELECT privilege on a table, you can revoke the privilege with 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 Privileges
A user can hold a privilege on a resource through multiple resource names (for example, through the use of wildcards) or through multiple authorization identifiers (for example, through two different group identifiers). A REVOKE statement revokes the privileges specified in the statement only on the specified resource name and only from the specified authorization identifier.
For example, suppose:
  • User PKB is in the group SALES_ADMIN
  • PKB has been granted the SELECT privilege on the table name SALES_SCH.SALES_FORECAST
  • SALES_ADMIN has been granted the SELECT privilege on all tables named SALES_SCH.SALES* where * is a wildcard character
You can revoke the SELECT privilege on SALES_FORECAST from the user identifier PKB. However, PKB can still select from the SALES_FORECAST table because PKB is a member of SALES_ADMIN.
Example
Revoking Selected Privileges on a Table
The following REVOKE statement removes the SELECT and UPDATE privileges on the EMPLOYEE table from users KRP, SAE, and PGD:
revoke select, update    on employee    from krp, sae, pgd;
More Information