GRANT (database privileges)

This form of the GRANT statement grants privileges on databases.

Syntax


          .-,-------------.               .-,-------------.   
          V               |               V               |   
>>-GRANT----+-DBADM-----+-+--ON DATABASE----database-name-+----->
            +-DBCTRL----+                                     
            +-DBMAINT---+                                     
            +-CREATETAB-+                                     
            +-CREATETS--+                                     
            +-DISPLAYDB-+                                     
            +-DROP------+                                     
            +-IMAGCOPY--+                                     
            +-LOAD------+                                     
            +-RECOVERDB-+                                     
            +-REORG-----+                                     
            +-REPAIR----+                                     
            +-STARTDB---+                                     
            +-STATS-----+                                     
            '-STOPDB----'                                     

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

Description

Each keyword listed grants the privilege described, but only as it applies to or within the databases named in the statement.

DBADM: Grants the database administrator authority.
DBCTRL: Grants the database control authority.
DBMAINT: Grants the database maintenance authority.
CREATETAB: Grants the privilege to create new tables. To create tables in an implicitly created database, CREATETAB privileges are needed on the DSNDB04 database. For a work file database, PUBLIC implicitly has the CREATETAB privilege (without GRANT authority) to define declared temporary tables; this privilege is not recorded in the DB2® catalog, and it cannot be revoked.
CREATETS: Grants the privilege to create new table spaces.
DISPLAYDB: Grants the privilege to issue the DISPLAY DATABASE command.
DROP: Grants the privilege to issue the DROP or ALTER DATABASE statements for the designated databases.
IMAGCOPY: Grants the privilege to run the COPY, MERGECOPY, and QUIESCE utilities against table spaces of the specified databases, and to run the MODIFY RECOVERY utility.
LOAD: Grants the privilege to use the LOAD utility to load tables.
RECOVERDB: Grants the privilege to use the RECOVER and REPORT utilities to recover table spaces and indexes.
REORG: Grants the privilege to use the REORG utility to reorganize table spaces and indexes.
REPAIR: Grants the privilege to use the REPAIR and DIAGNOSE utilities.
STARTDB: Grants the privilege to issue the START DATABASE command.
STATS: Grants the privilege to use the RUNSTATS utility to update statistics, the CHECK utility to test whether indexes are consistent with the data they index, and the MODIFY STATISTICS utility to delete unwanted statistics history records from the corresponding catalog tables.
STOPDB: Grants the privilege to issue the STOP DATABASE command.
ON DATABASE database-name,...: Identifies databases on which privileges are to be granted. For each named database, the grantor must have all the specified privileges with the GRANT option. Each name must identify a database that exists at the current server. DSNDB01 must not be identified; however, a grant of a privilege on DSNDB06 implies the granting of the same privilege on DSNDB01 for utility operations only.
Database privileges granted on DSNDB04 are applicable to all implicitly created databases. This means that a user with the STOPDB privilege on DSNDB04 can also stop database objects in any implicitly created database. Similarly, having DBADM on DSNDB04 allows access to all tables in all implicitly created databases. However, having a database privilege on DSNDB04 does not allow granting of this privilege on an implicitly created database to others.
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.

Examples

Example 1: Grant drop privileges on database DSN8D10A to user PEREZ.

   GRANT DROP
     ON DATABASE DSN8D10A
     TO PEREZ;

Example 2: Grant repair privileges on database DSN8D10A to all local users.

   GRANT REPAIR
     ON DATABASE DSN8D10A
     TO PUBLIC;

Example 3: Grant authority to create new tables and load tables in database DSN8D10A to users WALKER, PIANKA, and FUJIMOTO, and give them grant privileges.

   GRANT CREATETAB,LOAD
     ON DATABASE DSN8D10A
     TO WALKER,PIANKA,FUJIMOTO
     WITH GRANT OPTION;

Example 4: Grant load privileges to database DSN9D91A to role ROLE1:

   GRANT LOAD 
     ON DATABASE DSN9D91A 
     TO ROLE ROLE1;