REVOKE (table space privileges) statement

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, SECADM, SYSCTRL, or SYSADM authority.

Syntax

>>-REVOKE USE OF TABLESPACE--tablespace-name--FROM-------------->

   .-,---------------------------------.               
   V                                   |  .-BY ALL-.   
>----+-+-------+--authorization-name-+-+--+--------+-----------><
     | +-USER--+                     |                 
     | +-GROUP-+                     |                 
     | '-ROLE--'                     |                 
     '-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.

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 USE privilege 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.TBSPACEAUTH 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 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.

Example

   
   REVOKE USE OF TABLESPACE PLANS FROM USER BOBBY