SQL Reference

REVOKE (Table Space Privileges)

This form of the REVOKE statement revokes the USE privilege on a table space.

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, SYSCTRL or DBADM authority (SQLSTATE 42501).

Syntax

>>-REVOKE--USE--OF TABLESPACE--tablespace-name---FROM----------->
 
      .-,------------------------------------.
      V                                      |
>-------+-+-------+---authorization-name--+--+-----------------><
        | +-USER--+                       |
        | '-GROUP-'                       |
        '-PUBLIC--------------------------'
 

Description

USE

Revokes the privilege to specify or default to the table space when creating a table.

OF TABLESPACE tablespace-name

Specifies the table space on which the USE privilege is to be revoked. The table space cannot be SYSCATSPACE (SQLSTATE 42838) or a SYSTEM TEMPORARY table space (SQLSTATE 42809).

FROM

Indicates from whom the USE privilege is 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 USE privilege from PUBLIC.

Rules

• If neither USER nor GROUP is specified, then:
  • If all rows for the grantee in the SYSCAT.TBSPACEAUTH 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 results (SQLSTATE 56092).
  • If DCE authentication is used, then an error results (SQLSTATE 56092).

Notes

• Revoking the USE privilege does not necessarily revoke the ability to create tables in that table space. A user may still be able to create tables in that table space if the USE privilege is held by PUBLIC or a group, or if the user has a higher level authority, such as DBADM.

Examples

Example 1: Revoke the privilege to create tables in table space PLANS from the user BOBBY.

   REVOKE USE OF TABLESPACE PLANS FROM USER BOBBY