REVOKE All Table Privileges

The REVOKE All Table Privileges authorization statement removes the definition and access privileges from one or more users or groups. The statement applies to a specified table, view, function, procedure, and table procedure.
idmscu19
The REVOKE All Table Privileges authorization statement removes the definition and access privileges from one or more users or groups. The statement applies to a specified table, view, function, procedure, and table procedure.
This article describes the following information:
2
2
Authorization
To issue the REVOKE ALL PRIVILEGES statement, one of the following statements 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. This parameter applies to the table, view, function, procedure, and 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 that is identified by function-identifier is associated. If no schema-name parameter is 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 through the GRANT statement.
  • authorization-identifier
    Identifies a user or group.
    The privileges must have been previously given to
    authorization-identifier
    through 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, by using wildcards) or through multiple authorization identifiers (for example, through two different group identifiers). A REVOKE statement revokes the privileges that are 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 that are 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