Statements › REVOKE Table Access Privileges

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.

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

• For more information about granting table access privileges, see GRANT Table Access Privileges.
• For more information about revoking privileges, see your security administrator.