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 system privileges. In every case, the system privileges specified are granted only to the AuthID(s) specified in the TO clause. One or more of the privilege option keywords defined below (separated with commas) may be specified after GRANT, allowing multiple system privileges to be granted in one GRANT statement.
GRANT
[CREATEALIAS[, ]]
[CREATEDBA[, ]]
[CREATEDBC[, ]]
[CREATETMTAB[, ]]
[CREATESG[, ]]
[SYSADM[, ]]
[SYSCTRL[, ]]
TO [AuthID][,...][PUBLIC]
[WITH GRANT OPTION]
This format is used to grant system privileges. In every case, the system privileges specified are granted only to the AuthID(s) specified in the TO clause. One or more of the privilege option keywords defined below (separated with commas) may be specified after GRANT, allowing multiple system privileges to be granted in one GRANT statement.
GRANT
[CREATEALIAS[, ]]
[CREATEDBA[, ]]
[CREATEDBC[, ]]
[CREATETMTAB[, ]]
[CREATESG[, ]]
[SYSADM[, ]]
[SYSCTRL[, ]]
TO [AuthID][,...][PUBLIC]
[WITH GRANT OPTION]
| CREATEALIAS | Privilege to use the CREATE ALIAS statement. |
| CREATEDBA | Privilege to issue the CREATE DATABASE statement and acquire DBADM authority over those databases. |
| CREATEDBC | Privilege to issue the CREATE DATABASE statement and acquire DBCTRL authority over those databases. |
| CREATETMTAB | Privilege to issue the CREATE GLOBAL TEMPORARY TABLE statement. |
| CREATESG | Privilege to create new storage groups. |
| SYSADM | Grants system administrator authority for the location, which includes all privileges except for a few reserved for INSTALL authority. These privileges are all grantable, including the SYSADM authority itself. |
| SYSCTRL | Grants the system control authority for the location, allowing the holder most of the privileges of a system administrator, but excluding privileges to read or change user data. |
The ARCHIVE, BINDADD, BINDAGENT, BSDS, DISPLAY, MONITOR1, MONITOR2, RECOVER, STOPALL, STOSPACE, SYSOPR, and TRACE system 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.
| AuthID | Identifies a user authorization ID being granted the specified privilege(s). The PUBLIC keyword option can be specified along with (or in place of) an AuthID (or a list of AuthIDs), granting privileges to each valid AuthID 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.
The following example grants SYSADM authority for the TUTORIAL location to the individual with the AuthID usera. With SYSADM authority AuthID usera acquires authority to perform all functions on the TUTORIAL location, including authority to grant privileges.
GRANT SYSADM 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.