GRANT (table, view, or nickname privileges) statement

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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

CONTROL privilege on the referenced table, view, or nickname
The WITH GRANT OPTION for each identified privilege. If ALL is specified, the authorization ID must have some grantable privilege on the identified table, view, or nickname
Schema ACCESSCTRL authority on the schema containing the identified table, view, or nickname
ACCESSCTRL or SECADM authority

Schema ACCESSCTRL, database ACCESSCTRL or SECADM authority is required to grant the CONTROL privilege. Database ACCESSCTRL or SECADM authority is required to grant privileges on catalog tables and views.

Syntax

Notes:

¹ ALTER, INDEX, and REFERENCE privileges are not applicable to views.

Description

ALL or ALL PRIVILEGES

Grants all the appropriate privileges, except CONTROL, on the base table, view, or nickname named in the ON clause.

If the authorization ID of the statement has CONTROL privilege on the table, view, or nickname, or ACCESSCTRL or SECADM 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.

ALTER

Grants the privilege to:

Add columns to a base table definition.
Create or drop a primary key or unique constraint on a base table.
Create or drop a foreign key on a base table.
The REFERENCE privilege on each column of the parent table is also required.
Create or drop a check constraint on a base table.
Create a trigger on a base table.
Add, reset, or drop a column option for a nickname.
Change a nickname column name or data type.
Add or change a comment on a base table or a nickname.

CONTROL

Grants:

All of the appropriate privileges in the list, that is:
- ALTER, CONTROL, DELETE, INSERT, INDEX, REFERENCE, SELECT, and UPDATE to base tables
- CONTROL, DELETE, INSERT, SELECT, and UPDATE to views
- ALTER, CONTROL, INDEX, and REFERENCE to nicknames
The ability to grant the previously mentioned privileges (except for CONTROL) to others.
The ability to drop the base table, view, or nickname.
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 an authorization ID with ACCESSCTRL or SECADM authority.
The ability to execute the RUNSTATS utility on the table and indexes.
The ability to execute the REORG utility on the table.
The ability to issue the SET INTEGRITY statement against a base table, materialized query table, or staging table.

The definer of a base table, materialized query table, staging 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.

DELETE

Grants the privilege to delete rows from the table or updatable view.

INDEX

Grants the privilege to create an index on a table, or an index specification on a nickname. This privilege cannot be granted on a view. The creator of an index or index specification automatically has the CONTROL privilege on the index or index specification (authorizing the creator to drop the index or index specification). In addition, the creator retains the CONTROL privilege even if the INDEX privilege is revoked.

INSERT

Grants the privilege to insert rows into the table or updatable view and to run the IMPORT utility.

REFERENCES

Grants the privilege to create and drop a foreign key referencing the table as the parent.

If the authorization ID of the statement has one of:

ACCESSCTRL or SECADM authority
CONTROL privilege on the table
REFERENCES WITH GRANT OPTION on the table

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 REFERENCE privileges that the authorization ID of the statement has on the identified table.

The privilege can be granted on a nickname, although foreign keys cannot be defined to reference nicknames.

REFERENCES (column-name,...)

Grants the privilege to create and drop a foreign key using only those columns specified in the column list as a parent key. Each column-name must be an unqualified name that identifies a column of the table identified in the ON clause. Column level REFERENCE privilege cannot be granted on typed tables, typed views, or nicknames (SQLSTATE 42997).

SELECT

Grants the privilege to:

Retrieve rows from the table or view.
Create views on the table.
Run the EXPORT utility against the table or view.

UPDATE

Grants the privilege to use the UPDATE statement on the table or updatable view identified in the ON clause.

If the authorization ID of the statement has one of:

ACCESSCTRL or SECADM authority
CONTROL privilege on the table or view
UPDATE WITH GRANT OPTION on the table or view

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.

UPDATE (column-name,...)

Grants the privilege to use the UPDATE statement to update only those columns specified in the column list. Each column-name must be an unqualified name that identifies a column of the table or view identified in the ON clause. Column level UPDATE privilege cannot be granted on typed tables, typed views, or nicknames (SQLSTATE 42997).

ON TABLE table-name or view-name or nickname

Specifies the table, view, or nickname on which privileges are to be granted.

No privileges may be granted on an inoperative view or an inoperative materialized query table (SQLSTATE 51024). No privileges may be granted on a declared temporary table (SQLSTATE 42995).

TO

Specifies to whom the privileges are granted.

USER

Specifies that the authorization-name identifies a user.

GROUP

Specifies that the authorization-name identifies a group name.

ROLE

Specifies that the authorization-name identifies a role name. The role name must exist at the current server (SQLSTATE 42704).

authorization-name,...

Lists the authorization IDs of one or more users, groups, or roles.

A privilege that is granted to a group is not used for authorization checking:

On static DML statements in a package
On a base table while processing a CREATE VIEW statement
On a base table while processing a CREATE TABLE statement for a materialized query table

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');

PUBLIC

Grants the privileges to a set of users (authorization IDs). For more information, see Authorization, privileges and object ownership. (Previous restrictions on the use of privileges granted to PUBLIC for static SQL statements and the CREATE VIEW statement have been removed.)

WITH GRANT OPTION

Allows the specified authorization-names to GRANT the privileges to others.

If the specified privileges include CONTROL, the WITH GRANT OPTION applies to all the applicable privileges except for CONTROL (SQLSTATE 01516).

Rules

For each authorization-name specified, if neither USER, GROUP, nor ROLE is specified, then:
- If the security plug-in in effect for the instance cannot determine the status of the authorization-name, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined as ROLE in the database, and as either GROUP or USER according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined according to the security plug-in in effect as both USER and GROUP, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined according to the security plug-in in effect as USER only, or if it is undefined, USER is assumed.
- If the authorization-name is defined according to the security plug-in in effect as GROUP only, GROUP is assumed.
- If the authorization-name is defined in the database as ROLE only, ROLE is assumed.
In general, the GRANT statement will process the granting of privileges that the authorization ID of the statement is allowed to grant, returning a warning (SQLSTATE 01007) if one or more privileges was not granted. If no privileges were granted, an error is returned (SQLSTATE 42501). (If the package used for processing the statement was precompiled with LANGLEVEL set to SQL92E or MIA, a warning is returned (SQLSTATE 01007), unless the grantor has no privileges on the object of the grant operation.) If CONTROL privilege is specified, privileges will only be granted if the authorization ID of the statement has ACCESSCTRL or SECADM authority (SQLSTATE 42501).

Notes

Privileges may be granted independently at every level of a table hierarchy. A user with a privilege on a supertable may affect the subtables. For example, an update specifying the supertable T may show up as a change to a row in the subtable S of T done by a user with UPDATE privilege on T but without UPDATE privilege on S. A user can only operate directly on the subtable if the necessary privilege is held on the subtable.
Granting nickname privileges has no effect on data source object (table or view) privileges. Typically, data source privileges are required for the table or view that a nickname references when attempting to retrieve data.
Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of Db2® and with other database products. The following syntax is tolerated and ignored:
- PUBLIC AT ALL LOCATIONS
WITH GRANT OPTION is ignored when granting table or view (CONTROL) privilege

Examples

Grant all privileges on the table WESTERN_CR to PUBLIC.
```
   GRANT ALL ON WESTERN_CR
      TO PUBLIC
```
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
```
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
```
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
```
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
```
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.
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 ACCESSCTRL or SECADM authority.
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 
```