The REVOKE command cancels database privileges held by specified authorization IDs. All relevant objects must exist in the database. Revoked privileges are deleted from the privilege tables in the SYSTEM schema for the database.
In most instances, the authority needed to revoke a specific privilege must be identical to the authority that originally granted the privilege which means that privileges individually granted by a specific authorization ID should be revoked individually by the same authorization ID. However, a user authorization with DBA privileges may revoke any privilege regardless of whether that user granted the privilege originally.
The column list argument specifies an optional list of columns in the table on which the SELECT, INSERT, UPDATE, or REFERENCES privilege is being revoked. The column list, if specified, must be enclosed in parentheses, with each column name separated from the next by a comma.
These specify the name of an existing table/view, domain, or schema, respectively. If the table, view, or domain does not belong to the current schema, preface the object name with the schema name and a period ( . ), that is, schema-name.object-name. If privileges are being revoked from a schema or domain, the object type keyword (SCHEMA or DOMAIN) must be included before the object name. If no object type is specified, the target object is assumed to be a table or view, and privileges are revoked from the table/view having the specified name.
The user name argument identifies one or more user authorization IDs (separated by commas) from which the specified privilege or privileges are being revoked. The PUBLIC keyword option can be specified along with or in place of an authorization ID (or a list of authorization IDs), revoking the specified privileges from all valid authorization IDs.
This statement format is used to revoke privileges on specified database objects. One or more of the privilege option keywords may be specified after REVOKE (separated by commas), allowing multiple privileges on a single database object to be revoked in one statement.
Individual grants (or groups of grants) are removed from the privilege tables in the SYSTEM schema for the database by executing REVOKE command statements. Each individual REVOKE action involves the removal of one privilege by one revoker from one revokee, except when ALL PRIVILEGES is revoked on a table or schema.
If ALL PRIVILEGES on a table or schema is revoked from an authorization, each of the individual privileges that constitute ALL PRIVILEGES for the object is removed (regardless of whether they were originally granted with the ALL PRIVILEGES option). Conversely, if ALL PRIVILEGES is granted on a table or schema, the individual privileges that make up ALL PRIVILEGES may be subsequently revoked one at a time.
ALL PRIVILEGES on a domain consists solely of the USAGE privilege, so this privilege may be removed by revoking either ALL PRIVILEGES or USAGE on the domain.
DBA privileges cannot be revoked partially by revoking individual object privileges; they must be revoked altogether.
Refer to the GRANT description for more information about the privileges that may be revoked.
Since the same privilege can be granted to a single grantee by several different grantors, the grantee retains the privilege as long as one (or more) of these grants remains recorded in the privilege tables in the database SYSTEM schema.
Privileges may be revoked from a specific set of users by listing their authorization ID names in the FROM clause of the REVOKE statement. To revoke privileges from all authorization IDs in the database, use the keyword PUBLIC in the FROM clause. When a privilege is revoked from PUBLIC, the SYSTEM schema tables continue to maintain the list of authorization IDs that were granted the privilege individually.
If any database objects (for example, views) have been created with or depend upon the revoked privilege, the REVOKE will fail. All objects dependent on the privilege must be dropped before the privilege can be revoked.
The following example revokes DBA authority from the user with the authorization ID usera.
REVOKE DBA FROM userA;
The following example revokes from users with the authorization IDs userb and userc the authority to update the supplier table.
FROM userB, userC;
The following example revokes from PUBLIC (all users) the authority to select, update, delete and insert records in the part table:
REVOKE SELECT, UPDATE, DELETE, INSERT