GRANT (table space privileges) statement

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 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 at least one of the following authorities:
  • The WITH GRANT OPTION for use of the table space
  • ACCESSCTRL, SECADM, SYSADM, or SYSCTRL authority

Syntax

Read syntax diagramSkip visual syntax diagramGRANTUSEOF TABLESPACEtablespace-name TO,USERGROUPROLEauthorization-namePUBLICWITH 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.
ROLE
Specifies that the authorization-name identifies a role name. The role name must exist at the current server (SQLSTATE 42704).
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
Grants the USE privilege to a set of users (authorization IDs).
WITH GRANT OPTION
Allows the specified authorization-name to GRANT the USE privilege to others.

Rules

  • For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
    • If the security plug-in in effect for the instance cannot determine the status of the authorization-name, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined as ROLE in the database, and as either GROUP or USER according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as both USER and GROUP, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as USER only, or if it is undefined, USER is assumed.
    • If the authorization-name is defined according to the security plug-in in effect as GROUP only, GROUP is assumed.
    • If the authorization-name is defined in the database as ROLE only, ROLE is assumed.

Example

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