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 individual tables or views. One or more of the privilege keywords defined below (separated with commas) or the optional keyword(s) ALL [PRIVILEGES] may be specified after GRANT, allowing multiple table privileges to be evoked in one GRANT statement. Does not include ALTER, INDEX, REFERENCES, or TRIGGER for a grant to PUBLIC AT ALL LOCATIONS.
GRANT
{
[ALTER[, ]]
[DELETE[, ]]
[INDEX[, ]]
[INSERT[, ]]
[SELECT[, ]]
[UPDATE [(column-name[,...])]]
[TRIGGER]
[REFERENCES [(column-name [,...])]]
| ALL [PRIVILEGES]
}
ON [TABLE] {table-name[,...] | view-name[,...]}
TO [AuthID][,...][PUBLIC [AT ALL LOCATIONS]]
[WITH GRANT OPTION]
This format is used to grant privileges on individual tables or views. One or more of the privilege keywords defined below (separated with commas) or the optional keyword(s) ALL [PRIVILEGES] may be specified after GRANT, allowing multiple table privileges to be evoked in one GRANT statement. Does not include ALTER, INDEX, REFERENCES, or TRIGGER for a grant to PUBLIC AT ALL LOCATIONS.
GRANT
{
[ALTER[, ]]
[DELETE[, ]]
[INDEX[, ]]
[INSERT[, ]]
[SELECT[, ]]
[UPDATE [(column-name[,...])]]
[TRIGGER]
[REFERENCES [(column-name [,...])]]
| ALL [PRIVILEGES]
}
ON [TABLE] {table-name[,...] | view-name[,...]}
TO [AuthID][,...][PUBLIC [AT ALL LOCATIONS]]
[WITH GRANT OPTION]
| ALTER | Privilege to use the ALTER TABLE statement ALTER cannot be granted to PUBLIC AT ALL LOCATIONS. (cannot specify an auxiliary table or view). |
| DELETE | Privilege to use the DELETE statement. |
| INDEX | Privilege to use the CREATE INDEX statement (cannot specify a view). |
| INSERT | Privilege to use the INSERT statement (cannot be granted on an auxiliary table.) |
| SELECT | Privilege to use the SELECT statement. (cannot be granted on an auxiliary table.) |
| UPDATE | Privilege to use the UPDATE statement on all columns of the tables named in the ON clause. When followed by (column-name[,...]), the update privilege applies only to the columns specified in parentheses.( must not identify a column of an auxiliary table.) |
| REFERENCES | Privilege to create/drop a foreign key. (cannot specify an auxiliary table or view). |
| column-name | Specifies an individual column in a table for which the UPDATE or the REFERENCES privilege is being granted. If you specify a list of columns after the REFERENCES keyword, each column-name must be the unqualified name of a column in a table that is identified in the ON clause. |
| table-name or view-name | Preceded by the keyword(s) ON [TABLE], identifies a table or view affected. |
| authID | Identifies a user authorization ID being granted the privilege(s) specified. The optional PUBLIC [AT ALL LOCATIONS] keyword(s) can be specified along with (or in place of) an AuthID (or a list of AuthIDs). The PUBLIC AT ALL LOCATIONS keyword option grants the specified table privileges to all users at every location. |
To grant privileges to all tables (or views) contained within a database, simply grant the privilege to the database.
If the ALL [PRIVILEGES] keywords are specified following GRANT, all table or view privileges (for which the issuer has GRANT authority) are granted for the objects identified in the ON clause.
In every case, the privileges specified are granted only on the tables or views named in the ON [TABLE] clause. Multiple table and/or view names can be listed, provided they are separated by commas. Table privileges may be granted by users possessing DBADM for the database or by the creator of the table or view the privilege is granted on. ALTER or INDEX privileges are not included in a grant to PUBLIC AT ALL LOCATIONS.
For an auxiliary table, only the INDEX privilege can be granted. DELETE, INSERT, SELECT, and UPDATE privileges on the base table that is associated with the auxiliary table extend to the auxiliary table.
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 example below grants authority for the individuals with the AuthIDs userb and userc to update the PART table.
GRANT UPDATE
ON part
TO userb, userc
To grant privileges to all users in the location to select, update, insert, and delete records from the PART and SUPPLIER tables, use the command:
GRANT SELECT, UPDATE, INSERT, DELETE
ON part, supplier
TO PUBLIC
To grant privileges on a subset of the columns in the EMPLOYEE table to usera, and at the same time give usera the ability to grant the privilege to other users, issue the command:
GRANT UPDATE(address, city, state, zip, phone)
ON employee
TO usera
WITH GRANT OPTION
In this case AuthID usera does not have the ability to update employee number, name, department, or pay rate values.
To grant all privileges on the EMPLOYEE table to AuthID usera:
GRANT ALL
ON TABLE 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.