REVOKE All Table Privileges

Statements › REVOKE All Table Privileges

REVOKE All Table Privileges

The REVOKE All Table Privileges authorization statement removes from one or more users or groups all definition and access privileges on a specified table, view, function, procedure or table procedure.

Authorization

To issue the REVOKE ALL PRIVILEGES statement, one of the following must be true:

You own the table, view, function, procedure or table procedure
You hold all privileges on the table, view, function, procedure or table procedure as grantable
You hold the DBADMIN privilege for the database that contains the table, view, function, procedure or table procedure

Syntax

►►─── REVOKE ALL PRIVILEGES ──────────────────────────────────────────────────►

 ►─── ON table─┬─ table-name───────────────────────────────────────────────┬──►
               └──────────┬────────────────┬────────── function-identifier ┘
                          └─ schema-name. ─┘

            ┌─────────────────────────────────┐
 ►─── FROM ─▼─┬─ PUBLIC ────────────────────┬─┴───────────────────────────────►◄
              └─ authorization-identifier ──┘

Parameters

ALL PRIVILEGES

Removes the DELETE, INSERT, SELECT, UPDATE, ALTER, CREATE, DROP, and REFERENCES privileges, as applicable, on the table, view, function, procedure, or table procedure identified in the ON parameter from the users or groups identified in the FROM parameter.

ON table table-name

Identifies the table, view, function, procedure or table procedure to which the 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

Specifies the users from whom you are removing the 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

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 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 data from the table because PKB is a member of SALES_ADMIN.

Example

Revoking All Privileges From All Users

The following statement removes all privileges on all tables, views, functions, procedures and table procedures in the TEST schema from the group PUBLIC:

revoke all privileges
   on test.*
   from public;

More Information

For more information about table access privileges, see GRANT Table Access Privileges and REVOKE Table Access Privileges.
For more information about table definition privileges, see GRANT Definition Privileges and REVOKE SQL Definition Privileges.
For more information about granting privileges, see your security administrator.