SQL Reference

REVOKE (Index Privileges)

This form of the REVOKE statement revokes the CONTROL privilege on an index.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The authorization ID of the statement must hold either SYSADM or DBADM authority (SQLSTATE 42501).

Refer to REVOKE (Database Authorities), REVOKE (Package Privileges), REVOKE (Schema Privileges), REVOKE (Server Privileges) and REVOKE (Table, View, or Nickname Privileges) for other GRANT statements.

Syntax

>>-REVOKE--CONTROL--ON INDEX--index-name------------------------>
 
           .-,------------------------------------.
           V                                      |
>----FROM----+-+-------+---authorization-name--+--+------------><
             | +-USER--+                       |
             | '-GROUP-'                       |
             '-PUBLIC--------------------------'
 

Description

CONTROL

Revokes the privilege to drop the index. This is the CONTROL privilege for indexes, which is automatically granted to creators of indexes.

ON INDEX index-name

Specifies the name of the index on which the CONTROL privilege is 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 authorization ID of the REVOKE statement itself cannot be used (SQLSTATE 42502). 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.INDEXAUTH catalog view 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

• 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 authorities such as ALTERIN on the schema of an index.

Examples

Example 1:  Given that USER4 is only a user and not a group, revoke the privilege to drop an index DEPTIDX from the user USER4.

  REVOKE CONTROL ON INDEX DEPTIDX FROM USER4

Example 2:  Revoke the privilege to drop an index LUNCHITEMS from the user CHEF and the group WAITERS.

  REVOKE CONTROL ON INDEX LUNCHITEMS
     FROM USER CHEF, GROUP WAITERS