This form of the GRANT statement grants privileges on a table, view, or nickname.
Invocation
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).
Authorization
The privileges held by the authorization ID of the statement must include at least one of the following:
To grant the CONTROL privilege, SYSADM or DBADM authority is required.
To grant privileges on catalog tables and views, either SYSADM or DBADM authority is required.
Refer to GRANT (Database Authorities), GRANT (Index Privileges), GRANT (Package Privileges), GRANT (Schema Privileges) and GRANT (Server Privileges) for other GRANT statements.
Syntax
>>-GRANT-------------------------------------------------------->
.-PRIVILEGES--.
>-----+-ALL--+-------------+-----------------------------------+>
| .-,-------------------------------------------------. |
| V | |
'----+-ALTER----------------------------------------+--+-'
+-CONTROL--------------------------------------+
+-DELETE---------------------------------------+
+-INDEX----------------------------------------+
+-INSERT---------------------------------------+
+-REFERENCES----+----------------------------+-+
| | .-,--------------. | |
| | V | | |
| '-(-----column-name---+---)--' |
+-SELECT---------------------------------------+
'-UPDATE--+----------------------------+-------'
| .-,--------------. |
| V | |
'-(-----column-name---+---)--'
.-TABLE--.
>----ON--+--------+---+-table-name-----+------------------------>
| (1) |
+-view-name------+
| (2) |
'-nickname-------'
.-,------------------------------------.
V |
>----TO----+-+-------+---authorization-name--+--+--------------->
| +-USER--+ |
| '-GROUP-' |
'-PUBLIC--------------------------'
>----+-------------------+-------------------------------------><
'-WITH GRANT OPTION-'
Notes:
Description
If the authorization ID of the statement has CONTROL privilege on the table, view, or nickname, or DBADM or SYSADM authority, then all the privileges applicable to the object (except CONTROL) are granted. Otherwise, the privileges granted are all those grantable privileges that the authorization ID of the statement has on the identified table, view, or nickname.
If ALL is not specified, one or more of the keywords in the list of privileges must be specified.
The REFERENCES privilege on each column of the parent table is also required.
This ability cannot be extended to others on the basis of holding CONTROL privilege. The only way that it can be extended is by granting the CONTROL privilege itself and that can only be done by someone with SYSADM or DBADM authority.
The definer of a base table, summary table, or nickname automatically receives the CONTROL privilege.
The definer of a view automatically receives the CONTROL privilege if the definer holds the CONTROL privilege on all tables, views, and nicknames identified in the fullselect.
If the authorization ID of the statement has one of:
then the grantee(s) can create referential constraints using all columns of the table as parent key, even those added later using the ALTER TABLE statement. Otherwise, the privileges granted are all those grantable column REFERENCES privileges that the authorization ID of the statement has on the identified table. For more information on the authorization required to create or drop a foreign key, see ALTER TABLE.
The privilege can be granted on a nickname although foreign keys cannot be defined to reference nicknames.
If the authorization ID of the statement has one of:
then the grantee(s) can update all updatable columns of the table or view on which the grantor has with grant privilege as well as those columns added later using the ALTER TABLE statement. Otherwise, the privileges granted are all those grantable column UPDATE privileges that the authorization ID of the statement has on the identified table or view.
No privileges may be granted on an inoperative view or an inoperative summary table (SQLSTATE 51024). No privileges may be granted on a declared temporary table (SQLSTATE 42995).
A privilege granted to a group is not used for authorization checking on static DML statements in a package. Nor is it used when checking authorization on a base table while processing a CREATE VIEW statement.
In DB2 Universal Database, table privileges granted to groups only apply to statements that are dynamically prepared. For example, if the INSERT privilege on the PROJECT table has been granted to group D204 but not UBIQUITY (a member of D204) UBIQUITY could issue the statement:
EXEC SQL EXECUTE IMMEDIATE :INSERT_STRING;
where the content of the string is:
INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP)
VALUES ('AD3114', 'TOOL PROGRAMMING', 'D21', '000260');
but could not precompile or bind a program with the statement:
EXEC SQL INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP)
VALUES ('AD3114', 'TOOL PROGRAMMING', 'D21', '000260');
If the specified privileges include CONTROL, the WITH GRANT OPTION applies to all the applicable privileges except for CONTROL (SQLSTATE 01516).
Rules
Notes
Examples
Example 1: Grant all privileges on the table WESTERN_CR to PUBLIC.
GRANT ALL ON WESTERN_CR TO PUBLIC
Example 2: Grant the appropriate privileges on the CALENDAR table so that users PHIL and CLAIRE can read it and insert new entries into it. Do not allow them to change or remove any existing entries.
GRANT SELECT, INSERT ON CALENDAR TO USER PHIL, USER CLAIRE
Example 3: Grant all privileges on the COUNCIL table to user FRANK and the ability to extend all privileges to others.
GRANT ALL ON COUNCIL TO USER FRANK WITH GRANT OPTION
Example 4: GRANT SELECT privilege on table CORPDATA.EMPLOYEE to a user named JOHN. There is a user called JOHN and no group called JOHN.
GRANT SELECT ON CORPDATA.EMPLOYEE TO JOHN
or
GRANT SELECT
ON CORPDATA.EMPLOYEE TO USER JOHN
Example 5: GRANT SELECT privilege on table CORPDATA.EMPLOYEE to a group named JOHN. There is a group called JOHN and no user called JOHN.
GRANT SELECT ON CORPDATA.EMPLOYEE TO JOHN
or
GRANT SELECT ON CORPDATA.EMPLOYEE TO GROUP JOHN
Example 6: GRANT INSERT and SELECT on table T1 to both a group named D024 and a user named D024.
GRANT INSERT, SELECT ON TABLE T1
TO GROUP D024, USER D024
In this case, both the members of the D024 group and the user D024 would be allowed to INSERT into and SELECT from the table T1. Also, there would be two rows added to the SYSCAT.TABAUTH catalog view.
Example 7: GRANT INSERT, SELECT, and CONTROL on the CALENDAR table to user FRANK. FRANK must be able to pass the privileges on to others.
GRANT CONTROL ON TABLE CALENDAR
TO FRANK WITH GRANT OPTION
The result of this statement is a warning (SQLSTATE 01516) that CONTROL was not given the WITH GRANT OPTION. Frank now has the ability to grant any privilege on CALENDAR including INSERT and SELECT as required. FRANK cannot grant CONTROL on CALENDAR to other users unless he has SYSADM or DBADM authority.
Example 8: User JON created a nickname for an Oracle table that had no index. The nickname is ORAREM1. Later, the Oracle DBA defined an index for this table. User SHAWN now wants DB2 to know that this index exists, so that the optimizer can devise strategies to access the table more efficiently. SHAWN can inform DB2 of the index by creating an index specification for ORAREM1. Give SHAWN the index privilege on this nickname, so that he can create the index specification.
GRANT INDEX ON NICKNAME ORAREM1
TO USER SHAWN