GRANT (Database Privileges)

The GRANT command confers database, table or view, and system privileges to AuthIDs. The XDB Server supports three separate GRANT statement formats for granting database, table and system privileges. All relevant objects must be located in the current location's catalog tables. Granted privileges are recorded in the system catalog for the current location. Specific privileges may later be revoked (see REVOKE statement).

The XDB Server syntactically supports the collection, package, and plan classes of GRANT/REVOKE privilege commands. Also syntactically supported are the use class of GRANT/REVOKE privileges, including the USE OF ALL BUFFERPOOLS clause. Since the XDB Server does not functionally use these types of privileges, they are not detailed in this reference. Users are encouraged to consult their DB2 documentation for information on these four classes of privilege commands.

Invocation

GRANT is an executable command that can be issued interactively, embedded in a host language, or dynamically prepared.

Authorization

To grant privileges on an object, users must either possess a specific privilege WITH GRANT OPTION for the object, be the owner of the object, or possess overall SYSADM authority for the location. Users with DBADM authority over a database also possess GRANT option privileges on tables (except for views) within that database, provided DBADM authority was acquired with the GRANT option. When embedded in a host language or dynamically prepared, the GRANT command has access to the same privileges held by the CURRENT SQLID of the application process.

Syntax

This format is used to grant privileges on a specified database. Please note that to grant the CREATETAB privilege, you must also grant CREATETS privileges for that database to the same AuthID. One or more privilege option keywords (separated with commas) may be specified after GRANT, allowing multiple privileges to be granted in one statement.

For a TEMP 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.

In every case, the privileges chosen are granted only to the logical database(s) named in the GRANT statement.

GRANT 
        [DBADM[, ]] 
        [DBCTRL[, ]] 
        [DBMAINT[, ]] 
        [CREATETAB[, ]] 
        [CREATETS[, ]] 
        [DROP[, ]] 
        [STARTDB[, ]] 
        [STATS[, ]] 
        [STOPDB[, ]]    
    ON DATABASE database-name[,...]
    TO [AuthID][,...][PUBLIC]
    [WITH GRANT OPTION]

Parameters:

DBADM Database administrator authority.
DBCTRL Database control authority.
DBMAINT Database maintenance authority.
CREATETAB Privilege to create new tables.
CREATETS Privilege to create new table spaces.
DROP Privilege to issue the DROP or ALTER DATABASE statements for the designated databases.
STARTDB Privilege to issue the START DATABASE command.
STOPDB Privilege to issue the STOP DATABASE command.
Note: The DISPLAYDB, IMAGCOPY, LOAD, RECOVERDB, REORG and REPAIR, and STATS database grant/revoke privileges are specific to the DB2 mainframe environment, and control specific authorities for running mainframe utilities (for example, CHECK, RUNSTATS and QUIESCE). The XDB Server does not functionally support these privileges. However, to maintain maximum compatibility with mainframe DB2 operations, the XDB Server syntactically checks these privileges (if encountered) and updates the appropriate catalog tables.
database-name Identifies a database on which privileges are to be granted. The grantor must already possess all the privileges specified with the GRANT option, for each database named. Each database named must exist at the current location.
AuthID Identifies a user authorization ID being granted the privilege(s) specified. The PUBLIC keyword option can be specified along with (or in place of) an AuthID (or a list of AuthIDs), granting privileges to all valid authorization IDs at the current location.

Description

The GRANT command is used to confer system, database, table and view privileges to specified AuthID(s). The XDB Server supports three separate formats for the GRANT command, depending on the type of privilege granted. Database privileges include CREATETAB, DBADM and DROP privileges. Table privileges include ALTER, DELETE, INDEX, INSERT, SELECT, and UPDATE privileges. System privileges that imply other privileges are also characterized as authorities.

The privileges resulting from the execution of a GRANT command statement are recorded as one or more individual grants in the current location system catalog. Each individual grant involves the granting of one privilege by a grantor to one grantee. A grantee can be any AuthID (as recorded in the system catalog for that location), plus the PUBLIC or PUBLIC AT ALL LOCATIONS designations. The status of each privilege granted is recorded in the system catalog for each AuthID.

The same privilege can be granted to a single grantee by several different grantor AuthIDs. The grantee retains the privilege as long as one (or more) of these grants remains recorded in the system catalog. Individual grants (or groups of grants) can be removed from the system catalog by executing REVOKE commands.

WITH GRANT OPTION

If the WITH GRANT OPTION clause is used, the grantee can grant the privileges specified in the GRANT command to other valid AuthIDs. If users attempt to assign GRANT authority to either PUBLIC or PUBLIC AT ALL LOCATIONS, the privileges named in the GRANT command are granted, but without GRANT authority. Specifying WITH GRANT OPTION is valid but unnecessary when granting SYSADM privileges, since whoever is granted this high-level system privilege automatically receives the ability to assign privileges to other AuthIDs.

PUBLIC

Privileges may be granted to a specific set of users by listing their AuthIDs in the TO clause of the GRANT statement. To grant privileges to all system users in the location, use the keyword PUBLIC. To GRANT to all users system-wide, use PUBLIC AT ALL LOCATIONS in the TO clause of the GRANT statement. Users who have not been specifically granted any privileges have only those privileges granted to PUBLIC. When a privilege is granted to PUBLIC, the system continues to maintain the list of user names that have been specifically granted the privilege.

Example:

To grant database administrator (DBADM) authority on the EMPLOYEE database to AuthID usera:

GRANT DBADM 
    ON DATABASE employee 
    TO usera;