GRANT statement (use privileges)

This form of the GRANT statement grants authority to use particular buffer pools, storage groups, or table spaces.

Syntax for GRANT (use privileges)

Description for GRANT (use privileges)

BUFFERPOOL bpname,...: Grants the privilege to refer to any of the identified buffer pools in a CREATE INDEX, CREATE TABLESPACE, ALTER INDEX, or ALTER TABLESPACE statement. See Naming conventions in SQL for more details about bpname.
ALL BUFFERPOOLS: Grants the privilege to refer to any buffer pool in a CREATE INDEX, CREATE TABLESPACE, ALTER INDEX, or ALTER TABLESPACE statement.
STOGROUP stogroup-name,...: Grants the privilege to refer to any of the identified storage groups in a CREATE INDEX, CREATE TABLESPACE, ALTER INDEX, or ALTER TABLESPACE statement.
TABLESPACE database-name.table-space-name,...: Grants the privilege to refer to any of the identified table spaces in a CREATE TABLE statement. The default for database-name is DSNDB04.
You cannot grant the privilege for table spaces that are for declared temporary tables (table spaces in a work file database). For these table spaces, PUBLIC implicitly has the TABLESPACE privilege (without GRANT authority); this privilege is not recorded in the Db2 catalog, and it cannot be revoked.
TO: Refer to GRANT statement for a description of the TO clause.
WITH GRANT OPTION: Refer to GRANT statement for a description of the WITH GRANT OPTION clause.

Notes for GRANT (use privileges)

You can grant privileges for only one type of object with each statement. Thus, you can grant the use of several table spaces with one statement, but not the use of a table space and a storage group. For each object you identify, you must have the USE privilege with GRANT authority.

Examples for GRANT (use privileges)

Example 1: Grant authority to use buffer pools BP1 and BP2 to user MARINO.

   GRANT USE OF BUFFERPOOL BP1,BP2
     TO MARINO;

Example 2: Grant to all local users the authority to use table space DSN8S13D in database DSN8D13A.

   GRANT USE OF TABLESPACE
     DSN8D13A.DSN8S13D
     TO PUBLIC;

Example 3: Grant authority to use storage group SG1 to role ROLE1:

   GRANT USE OF STOGROUP SG1
      TO ROLE ROLE1;