DB2 Version 9.7 for Linux, UNIX, and Windows

REVOKE (index privileges) statement

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 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 ACCESSCTRL or SECADM authority.

Syntax


>>-REVOKE CONTROL ON INDEX--index-name-------------------------->

         .-,---------------------------------.               
         V                                   |  .-BY ALL-.   
>--FROM----+-+-------+--authorization-name-+-+--+--------+-----><
           | +-USER--+                     |                 
           | +-GROUP-+                     |                 
           | '-ROLE--'                     |                 
           '-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.
ROLE: Specifies that the authorization-name identifies a role name.
authorization-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 the privilege from all named users who were explicitly granted that privilege, regardless of who granted it. 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.INDEXAUTH catalog view 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

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 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 KIESLER

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