SQL Reference

GRANT (Table Space Privileges)

This form of the GRANT statement grants privileges 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 privileges held by the authorization ID of the statement must include at least one of the following:

• WITH GRANT OPTION for use of the table space
• SYSADM, SYSCTRL, or DBADM authority

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

Syntax

>>-GRANT--USE--OF TABLESPACE--tablespace-name----TO------------->
 
      .-,------------------------------------.
      V                                      |
>-------+-+-------+---authorization-name--+--+------------------>
        | +-USER--+                       |
        | '-GROUP-'                       |
        '-PUBLIC--------------------------'
 
>-----+--------------------+-----------------------------------><
      '-WITH GRANT OPTION--'
 

Description

USE

Grants the privilege to specify or default to the table space when creating a table. The creator of a table space automatically receives USE privilege with grant option.

OF TABLESPACE tablespace-name

Identifies the table space on which the USE privilege is to be granted. The table space cannot be SYSCATSPACE (SQLSTATE 42838) or a system temporary table space (SQLSTATE 42809).

TO

Specifies to whom the USE privilege is granted.

USER

Specifies that the authorization-name identifies a user.

GROUP

Specifies that the authorization-name identifies a group name.

authorization-name

Lists the authorization IDs of one or more users or groups.

The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).

PUBLIC

Grants the USE privilege to all users.

WITH GRANT OPTION

Allows the specified authorization-name to GRANT the USE privilege to others.

If the WITH GRANT OPTION is omitted, the specified authorization-name can only GRANT the USE privilege to others if they:

• have SYSADM or DBADM authority or
• received the ability to GRANT the USE privilege from some other source.

Notes

If neither USER nor GROUP is specified, then

• If the authorization-name is defined in the operating system only as GROUP, then GROUP is assumed.
• If the authorization-name is defined in the operating system only as USER, or if it is undefined, then USER is assumed.
• If the authorization-name is defined in the operating system as both, or DCE authentication is used, an error is returned (SQLSTATE 56092).

Examples

Example 1:  Grant user BOBBY the ability to create tables in table space PLANS and to grant this privilege to others.

   GRANT USE OF TABLESPACE PLANS TO BOBBY WITH GRANT OPTION