GRANT statement (database privileges)

This form of the GRANT statement grants privileges on databases.

Syntax for GRANT (database privileges)

Read syntax diagramSkip visual syntax diagram GRANT ,DBADMDBCTRLDBMAINTCREATETABCREATETSDISPLAYDBDROPIMAGCOPYLOADRECOVERDBREORGREPAIRSTARTDBSTATSSTOPDB ON DATABASE ,database-name TO ,authorization-nameROLErole-namePUBLIC WITH GRANT OPTION

Description for GRANT (database privileges)

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

Examples for GRANT (database privileges)

Example 1: Grant drop privileges on database DSN8D13A to user PEREZ. 
   GRANT DROP
     ON DATABASE DSN8D13A
     TO PEREZ;
Example 2: Grant repair privileges on database DSN8D13A to all local users. 
   GRANT REPAIR
     ON DATABASE DSN8D13A
     TO PUBLIC;
Example 3: Grant authority to create new tables and load tables in database DSN8D13A to users WALKER, PIANKA, and FUJIMOTO, and give them grant privileges. 
   GRANT CREATETAB,LOAD
     ON DATABASE DSN8D13A
     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;