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.
GRANT is an executable command that can be issued interactively, embedded in a host language, or dynamically prepared.
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.
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]
| 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. |
| 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. |
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.
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.
To grant database administrator (DBADM) authority on the EMPLOYEE database to AuthID usera:
GRANT DBADM
ON DATABASE employee
TO usera;
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.