SQL Reference

REVOKE (Table or View Privileges)

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

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.

Authorization

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

• SYSADM or DBADM authority
• CONTROL privilege on the referenced table or view.

To revoke the CONTROL privilege, either SYSADM or DBADM authority is required.

To revoke the privileges on catalog tables and views, either SYSADM or DBADM authority is required.

Syntax

.-PRIVILEGES--. .-TABLE--. >>-REVOKE----+-ALL--+-------------+-+--ON----+--------+---------> | .-,---------------. | | V | | '----+-ALTER------+--+-' +-CONTROL----+ +-DELETE-----+ +-INDEX------+ +-INSERT-----+ +-REFERENCES-+ +-SELECT-----+ '-UPDATE-----' >-----+-table-name-+--------------------------------------------> '-view-name--' .-,------------------------------------. V | >----FROM----+-+-------+---authorization-name--+--+------------>< | +-USER--+ | | '-GROUP-' | '-PUBLIC--------------------------'

Description

ALL  or  ALL PRIVILEGES

Revokes all privileges held by an authorization-name for the specified tables or views.

If ALL is not used, one or more of the keywords listed below must be used. Each keyword revokes the privilege described, but only as it applies to the tables or views 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, create or drop a check constraint, or create a trigger.

CONTROL

Revokes the ability to drop the table or view, 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 or updatable view.

INDEX

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

INSERT

Revokes the privilege to insert rows into the table or updatable view and the privilege 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, create a view on a table, and run the EXPORT utility. Revoking SELECT privilege may cause some views to be marked inoperative. For information on inoperative views, see Notes.

UPDATE

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

ON TABLE table-name  or  view-name

Specifies the table or view on which privileges are to be revoked.

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.

authorization-name,...

Lists one or more authorization IDs.

The ID of the REVOKE statement itself cannot be used. (It is not possible to revoke the privileges from an authorization-name that is the same as the authorization ID of the REVOKE statement.)

PUBLIC

Revokes the privileges from PUBLIC.

Rules

• If neither USER nor GROUP is specified, then:

  If all rows for the grantee in the SYSCAT.TABAUTH and SYSCAT.COLAUTH catalog views have a GRANTEETYPE of U, then USER will be assumed.

  If all rows have a GRANTEETYPE of G, then GROUP will be assumed.

  If some rows have U and some rows have G, then an error (SQLSTATE 56092) is raised.

  If DCE authentication is used, then an error is raised (SQLSTATE 56092).

Notes

• If a privilege is revoked from the authorization-name used to create a view (this is called the view's DEFINER in SYSCAT.VIEWS), that privilege is also revoked from any dependent views.

• If the DEFINER 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)), then the view will be made inoperative (see the "Notes" section in CREATE VIEW for information on inoperative views).

  However, if a DBADM or SYSADM explicitly revokes all privileges on the view from the DEFINER, then the record of the DEFINER will not appear in SYSCAT.TABAUTH but nothing will happen to the view - it remains operative.

• Privileges on inoperative views cannot be revoked.

• All packages dependent upon an object for which a privilege is revoked are marked invalid. 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 for table T1 is revoked from USER1, then the package will be marked invalid. If SELECT authority is re-granted, or if the user holds DBADM authority, the package is successfully rebound when executed.

• 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. If one of these SELECT privileges is revoked, such packages are invalidated and such triggers or views are made inoperative.

• Table or view 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 may proceed with their task if other privileges are held by PUBLIC or a group, or if they have privileges such as ALTERIN on the schema of a table or a view.

• If the DEFINER of the summary table loses a SELECT privilege on a table on which the summary table definition depends, (or a table upon which the summary table definition depends is dropped), then the summary table will be made inoperative (see the Notes for information on inoperative summary tables).

  However, if a DBADM or SYSADM explicitly revokes all privileges on the summary table from the DEFINER, then the record in SYSTABAUTH for the DEFINER will be deleted, but nothing will happen to the summary table - it remains operative.

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

[ DB2 List of Books | Search the DB2 Books ]