GRANT (use privileges)

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

Syntax


>>-GRANT USE OF------------------------------------------------->

                 .-,------.                                   
                 V        |                                   
>--+-BUFFERPOOL----bpname-+-------------------------------+----->
   +-ALL BUFFERPOOLS--------------------------------------+   
   |           .-,-------------.                          |   
   |           V               |                          |   
   +-STOGROUP----stogroup-name-+--------------------------+   
   |             .-,------------------------------------. |   
   |             V                                      | |   
   '-TABLESPACE----+----------------+--table-space-name-+-'   
                   '-database-name.-'                         

       .-,----------------------.                          
       V                        |                          
>--TO----+-authorization-name-+-+--+-------------------+-------><
         +-ROLE--role-name----+    '-WITH GRANT OPTION-'   
         '-PUBLIC-------------'

Description

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 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 for a description of the TO clause.
WITH GRANT OPTION: Refer to GRANT for a description of the WITH GRANT OPTION clause.

Notes

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

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 DSN8S10D in database DSN8D10A.

   GRANT USE OF TABLESPACE
     DSN8D10A.DSN8S10D
     TO PUBLIC;

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

   GRANT USE OF STOGROUP SG1
      TO ROLE ROLE1;