REVOKE (table, view, or nickname privileges) statement

This form of the REVOKE statement revokes privileges on a table, view, or nickname.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

CONTROL privilege on the referenced table, view, or nickname
ACCESSCTRL authority on the schema containing the identified table, view, or nickname
ACCESSCTRL or SECADM authority

Schema ACCESSCTRL, ACCESSCTRL or SECADM authority is required to revoke the CONTROL privilege. ACCESSCTRL or SECADM authority is required to revoke privileges on catalog tables and views.

Syntax

Description

ALL or ALL PRIVILEGES

Revokes all privileges (except CONTROL) held by an authorization-name for the specified tables, views, or nicknames.

If ALL is not used, one or more of the keywords listed in the option stack (ALTER through UPDATE) must be used. Each keyword revokes the privilege described, but only as it applies to the tables, views, or nicknames named in the ON clause. The same keyword must not be specified more than once.

ALTER

Revokes the privilege to add columns to the base table definition; create or drop a primary key or unique constraint on the table; create or drop a foreign key on the table; add/change a comment on the table, view, or nickname; create or drop a check constraint; create a trigger; add, reset, or drop a column option for a nickname; or, change nickname column names or data types.

CONTROL

Revokes the ability to drop the table, view, or nickname, and the ability to execute the RUNSTATS utility on the table and indexes.

Revoking CONTROL privilege from an authorization-name does not revoke other privileges granted to the user on that object.

DELETE

Revokes the privilege to delete rows from the table, updatable view, or nickname.

INDEX

Revokes the privilege to create an index on the table or an index specification on the nickname. The creator of an index or index specification automatically has the CONTROL privilege over the index or index specification (authorizing the creator to drop the index or index specification). In addition, the creator retains this privilege even if the INDEX privilege is revoked.

INSERT

Revokes the privileges to insert rows into the table, updatable view, or nickname, and to run the IMPORT utility.

REFERENCES

Revokes the privilege to create or drop a foreign key referencing the table as the parent. Any column level REFERENCES privileges are also revoked.

SELECT

Revokes the privilege to retrieve rows from the table or view, to create a view on a table, and to run the EXPORT utility against the table or view.

Revoking SELECT privilege may cause some views to be marked inoperative. (For information about inoperative views, see CREATE VIEW.)

UPDATE

Revokes the privilege to update rows in the table, updatable view, or nickname. Any column level UPDATE privileges are also revoked.

ON TABLE table-name or view-name or nickname

Specifies the table, view, or nickname on which privileges are to be revoked. The table-name cannot be a declared temporary table (SQLSTATE 42995).

FROM

Indicates from whom the privileges are revoked.

USER: Specifies that the authorization-name identifies a user.
GROUP: Specifies that the authorization-name identifies a group name.
ROLE: Specifies that the authorization-name identifies a role name.; Lists the authorization IDs of one or more users, groups, or roles.
The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).
PUBLIC: Revokes the privileges from PUBLIC.

BY ALL

Revokes each named privilege from all named users who were explicitly granted those privileges, regardless of who granted them. This is the default behavior.

Rules

For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
- For all rows for the specified object in the SYSCAT.TABAUTH and SYSCAT.COLAUTH catalog views where the grantee is authorization-name:
  - If all rows have a GRANTEETYPE of 'U', USER is assumed.
  - If all rows have a GRANTEETYPE of 'G', GROUP is assumed.
  - If all rows have a GRANTEETYPE of 'R', ROLE is assumed.
  - If all rows do not have the same value for GRANTEETYPE, an error is returned (SQLSTATE 56092).

Notes

If a privilege is revoked from the authorization-name that is the owner of the view (as recorded in the OWNER column in SYSCAT.VIEWS), that privilege is also revoked from any dependent views.
If the owner of the view loses a SELECT privilege on some object on which the view definition depends (or an object upon which the view definition depends is dropped, or made inoperative in the case of another view), the view will be made inoperative.
However, if a user who holds ACCESSCTRL or SECADM authority explicitly revokes all privileges on the view from the owner, then the record of the OWNER will not appear in SYSCAT.TABAUTH but nothing will happen to the view - it remains operative.
Privileges on inoperative views cannot be revoked.
A package might become invalid when the authorization ID under which the package was bound loses a privilege on an object on which the package depends. The privilege can be lost in one of the following ways:
- The privilege is revoked from the authorization ID
- The privilege is revoked from a role of which the authorization ID is a member
- The privilege is revoked from PUBLIC
A package remains invalid until a bind or rebind operation on the application is successfully executed, or the application is executed and the database manager successfully rebinds the application (using information stored in the catalogs). Packages marked invalid due to a revoke may be successfully rebound without any additional grants.
For example, if a package owned by USER1 contains a SELECT from table T1, and the SELECT privilege on table T1 is revoked from USER1, the package will be marked invalid. If SELECT authority is granted again, or if the user holds DBADM authority, the package is successfully rebound when executed.

Another example is a package owned by USER1, who is a member of role R1. The package contains a SELECT from table T1, and the SELECT privilege on table T1 is revoked from role R1. The package will be marked invalid, assuming USER1 does not hold the SELECT privilege on table T1 by other means.
Packages, triggers or views that include the use of OUTER(Z) in the FROM clause, are dependent on having SELECT privilege on every subtable or subview of Z. Similarly, packages, triggers, or views that include the use of DEREF(Y) where Y is a reference type with a target table or view Z, are dependent on having SELECT privilege on every subtable or subview of Z. Such packages might become invalid, and such triggers or views made inoperative when the authorization ID under which the packages were bound, or the owner of the triggers or views loses the SELECT privilege. The SELECT privilege can be lost in one of the following ways:
- SELECT privilege is revoked from the authorization ID
- SELECT privilege is revoked from a role of which the authorization ID is a member
- SELECT privilege is revoked from PUBLIC
Table, view, or nickname privileges cannot be revoked from an authorization-name with CONTROL on the object without also revoking the CONTROL privilege (SQLSTATE 42504).
Revoking a specific privilege does not necessarily revoke the ability to perform the action. A user can proceed with a task if other privileges are held by PUBLIC, a group, or a role, or if the user holds privileges such as ALTERIN on the schema of a table or a view.
If the owner of the materialized query table loses a SELECT privilege on a table on which the materialized query table definition depends (or a table upon which the materialized query table definition depends is dropped), the materialized query table will be dropped.
However, if a user who holds SECADM or ACCESSCTRL authority explicitly revokes all privileges on the materialized query table from the owner, then the record in SYSTABAUTH for the OWNER will be deleted, but nothing will happen to the materialized query table - it remains operative.
Revoking nickname privileges has no affect on data source object (table or view) privileges.
Revoking the SELECT privilege for a table or view that is directly or indirectly referenced in an SQL function or method body may fail if the SQL function or method body cannot be dropped because some other object is dependent on it (SQLSTATE 42893).
Revoking the SELECT privilege causes an SQL function or method body to be dropped when:
- The owner of the SQL function or method body loses the SELECT privilege on some object on which the SQL function or method body definition depends; note that the privilege can be lost because of a revoke from PUBLIC or from a role of which the owner is a member
- An object on which the SQL function or method body definition depends is dropped
However, the revoke fails if another object depends on the function or method (SQLSTATE 42893).
Revoking WITH GRANT OPTION: The only way to revoke the WITH GRANT OPTION is to revoke the privilege itself and then grant it again without specifying WITH GRANT OPTION.
Revoking column privileges: The only way to revoke column privileges is to revoke the privilege from the entire table itself and then grant it again for each column.

Examples

Example 1: Revoke SELECT privilege on table EMPLOYEE from user ENGLES. There is one row in the SYSCAT.TABAUTH catalog view for this table and grantee and the GRANTEETYPE value is U.
```
   REVOKE SELECT
     ON TABLE EMPLOYEE
     FROM ENGLES
```
Example 2: Revoke update privileges on table EMPLOYEE previously granted to all local users. Note that grants to specific users are not affected.
```
   
   REVOKE UPDATE
     ON EMPLOYEE
     FROM PUBLIC
```
Example 3: Revoke all privileges on table EMPLOYEE from users PELLOW and MLI and from group PLANNERS.
```
   REVOKE ALL
     ON EMPLOYEE
     FROM USER PELLOW, USER MLI, GROUP PLANNERS
```
Example 4: Revoke SELECT privilege on table CORPDATA.EMPLOYEE from a user named JOHN. There is one row in the SYSCAT.TABAUTH catalog view for this table and grantee and the GRANTEETYPE value is U.
```
   REVOKE SELECT
     ON CORPDATA.EMPLOYEE FROM JOHN
```
or
```
   REVOKE SELECT
     ON CORPDATA.EMPLOYEE FROM USER JOHN
```
Note that an attempt to revoke the privilege from GROUP JOHN would result in an error, since the privilege was not previously granted to GROUP JOHN.
Example 5: Revoke SELECT privilege on table CORPDATA.EMPLOYEE from a group named JOHN. There is one row in the SYSCAT.TABAUTH catalog view for this table and grantee and the GRANTEETYPE value is G.
```
   REVOKE SELECT
     ON CORPDATA.EMPLOYEE FROM JOHN
```
or
```
   REVOKE SELECT
     ON CORPDATA.EMPLOYEE FROM GROUP JOHN
```
Example 6: Revoke user SHAWN's privilege to create an index specification on nickname ORAREM1.
```
   REVOKE INDEX 
     ON ORAREM1 FROM USER SHAWN
```