GRANT (table or view privileges)

This form of the GRANT statement grants privileges on tables or views.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

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

For each table or view identified in the statement:
- Every privilege specified in the statement
- The system authority of *OBJMGT on the table or view
- The system authority *EXECUTE on the library containing the table or view
Database administrator authority
Security administrator authority

If WITH GRANT OPTION is specified, the privileges held by the authorization ID of the statement must include at least one of the following:

Ownership of the table
Database administrator authority
Security administrator authority

Syntax

Description

ALL or ALL PRIVILEGES

Grants one or more privileges. The privileges granted are all those grantable privileges that the authorization ID of the statement has on the specified tables or views.

Note that granting ALL PRIVILEGES on a table or view is not the same as granting the system authority of *ALL.

ALTER

Grants the privilege to alter the specified table or create or drop a trigger on the specified table. Grants the privilege to use the COMMENT and LABEL statements on tables and views.

DELETE

Grants the privilege to delete rows from the specified table or view. If a view is specified, it must be a deletable view.

INDEX

Grants the privilege to create an index on the specified table. This privilege cannot be granted on a view.

INSERT

Grants the privilege to insert rows into the specified table or view. If a view is specified, it must be an insertable view.

REFERENCES

Grants the privilege to add a referential constraint in which each specified table is a parent. If a list of columns is not specified or if REFERENCES is granted to all columns of the table or view via the specification of ALL PRIVILEGES, the grantee(s) can add referential constraints using all columns of each table specified in the ON clause as a parent key, even those added later via the ALTER TABLE statement. This privilege can be granted on a view, but the privilege is not used for a view.

REFERENCES (column-name,…)

Grants the privilege to add a referential constraint in which each specified table is a parent 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 each table specified in the ON clause. This privilege can be granted on the columns of a view, but the privilege is not used for a view.

SELECT

Grants the privilege to create a view or read data from the specified table or view. For example, the SELECT privilege is required if a table or view is specified in a query.

UPDATE

Grants the privilege to update rows in the specified table or view. If a list of columns is not specified or if UPDATE is granted to all columns of the table or view via the specification of ALL PRIVILEGES, the grantee(s) can update all updatable columns on each table specified in the ON clause, even those added later via the ALTER TABLE statement. If a view is specified, it must be an updatable view.

UPDATE (column-name,…)

Grants the privilege to use the UPDATE statement to update only those columns that are identified in the column list. Each column-name must be an unqualified name that identifies a column of each table and view specified in the ON clause. If a view is specified, it must be an updatable view and the specified columns must be updatable columns.

ON table-name or view-name,…

Identifies the tables or views on which the privileges are granted. The table-name or view-name must identify a table or view that exists at the current server, but must not identify a declared temporary table.

TO

Indicates to whom the privileges are granted.

USER: Specifies that the authorization-name identifies a user profile. If USER is specified, authorization-name must be a user profile.
GROUP: Specifies that the authorization-name identifies a group profile. If GROUP is specified, authorization-name must be a group profile.
authorization-name,…: Lists one or more authorization IDs.
PUBLIC: Grants the privileges to a set of users (authorization IDs). For more information, see Authorization, privileges and object ownership.

WITH GRANT OPTION

Allows the specified authorization-names to grant privileges on the tables and views specified in the ON clause to other users.

If WITH GRANT OPTION is omitted, the specified authorization-names cannot grant privileges on the tables and views specified in the ON clause unless they have received that authority from some other source (for example, from a grant of the system authority *OBJMGT).

Notes

Corresponding system authorities: The GRANT and REVOKE statements assign and remove system authorities for SQL objects. The following table describes the system authorities that correspond to the SQL privileges when granting to a table. The left column lists the SQL privilege. The right column lists the equivalent system authorities that are granted or revoked.

Table 1. Privileges Granted to or Revoked from Tables
SQL Privilege	Corresponding System Authorities when Granting to or Revoking from a Table
ALL (GRANT or revoke of ALL only grants or revokes those privileges the authorization ID of the statement has)	OBJALTER ^¹ OBJMGT (Revoke only) OBJOPR OBJREF ADD DLT READ UPD
ALTER	*OBJALTER ^²
DELETE	OBJOPR ^³ DLT
INDEX	*OBJALTER ^²
INSERT	OBJOPR^³ ADD
REFERENCES	*OBJREF ^²
SELECT	OBJOPR^³ READ
UPDATE	OBJOPR^³ UPD
WITH GRANT OPTION	*OBJMGT

The following table describes the system authorities that correspond to the SQL privileges when granting to a view. The left column lists the SQL privilege. The middle column lists the equivalent system authorities that are granted to or revoked from the view itself. The right column lists the system authorities that are granted to all tables and views referenced in the view's definition, and if a view is referenced, all tables and views referenced in its definition, and so on. ⁴

If a view references more than one table or view, the *DLT, *ADD, and *UPD system authorities are only granted to the first table or view in the fullselect of the view definition. The *READ system authority is granted to all tables and views referenced in the view definition.

If more than one system authority will be granted with an SQL privilege, and any one of the authorities cannot be granted, then a warning occurs and no authorities will be granted for that privilege. Unlike GRANT, REVOKE only revokes system authorities to the view. No system authorities are revoked from the referenced tables and views.

Table 2. Privileges Granted to or Revoked from Views
SQL Privilege	Corresponding System Authorities Granted to or Revoked from View	Corresponding System Authorities Granted to or Revoked from Referenced Tables and Views
ALL (GRANT or REVOKE of ALL only grants or revokes those privileges the authorization ID of the statement has)	OBJALTER OBJMGT (Revoke only) OBJOPR OBJREF ADD DLT READ UPD	ADD DLT READ UPD
ALTER	*OBJALTER ^²	None
DELETE	OBJOPR^³ DLT	*DLT
INDEX	Not Applicable	Not Applicable
INSERT	OBJOPR^³ ADD	*ADD
REFERENCES	*OBJREF ^²	None
SELECT	OBJOPR^³ READ	*READ
UPDATE	OBJOPR^³ UPD	*UPD
WITH GRANT OPTION	*OBJMGT	None

Corresponding system authorities when checking privileges to a table or view: The following table describes the system authorities that correspond to the SQL privileges when checking privileges to a table. The left column lists the SQL privilege. The right column lists the equivalent system authorities.

Table 3. Corresponding System Authorities when Checking Privileges to a Table
SQL Privilege	Corresponding System Authorities when Checking Privileges to a Table
ALTER	OBJALTER or OBJMGT
DELETE	OBJOPR and DLT
INDEX	OBJALTER or OBJMGT
INSERT	OBJOPR and ADD
REFERENCES	OBJREF or OBJMGT
SELECT	OBJOPR and READ
UPDATE	OBJOPR and UPD

The following table describes the system authorities that correspond to the SQL privileges when checking privileges to a view. The left column lists the SQL privilege. The middle column lists the equivalent system authorities that are checked on the view itself. The right column lists the system authorities that are checked on all tables and views referenced in the view's definition, and if a view is referenced, all tables and views referenced in its definition, and so on.

Table 4. Corresponding System Authorities when Checking Privileges to a View
SQL Privilege	Corresponding System Authorities to the View	Corresponding System Authorities to the Referenced Tables and Views
ALTER	OBJALTER and OBJMGT	None
DELETE^⁵	OBJOPR and DLT	*DLT
INDEX	Not Applicable	Not Applicable
INSERT^⁶	OBJOPR and ADD	*ADD
REFERENCES	OBJREF or OBJMGT	None
SELECT	OBJOPR and READ	*READ
UPDATE^⁷	OBJOPR and UPD	*UPD

GRANT rules: The GRANT statement will grant only those privileges that the authorization ID of the statement is allowed to grant. If no privileges were granted, an error is returned.

Examples

Example 1: Grant all privileges on the table WESTERN_CR to PUBLIC.

  GRANT ALL PRIVILEGES ON WESTERN_CR
    TO PUBLIC

Example 2: Grant the appropriate privileges on the CALENDAR table so that 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 PHIL, CLAIRE

Example 3: Grant column privileges on TABLE1 and VIEW1 to FRED. Note that both columns specified in this GRANT statement must be found in both TABLE1 and VIEW1.

   GRANT UPDATE(column_1, column_2)
     ON  TABLE1, VIEW1
     TO FRED WITH GRANT OPTION

¹ The SQL INDEX and ALTER privilege correspond to the same system authority of *OBJALTER. Granting both INDEX and ALTER will not provide the user with any additional authorities.

² If the WITH GRANT OPTION is given to a user, the user will also be able to perform the functions given by ALTER and REFERENCES authority.

³ *OBJOPR is only revoked when the last system privilege other than *OBJOPR is also revoked for the specified authorization ID or PUBLIC.

⁴ The specified rights are only granted to the tables and views referenced in the view definition if the user to whom the rights are being granted doesn't already have the rights from another authority source, for example public authority.

⁵ When a view is created, the owner does not necessarily acquire the DELETE privilege on the view. The owner only acquires the DELETE privilege if the view allows deletes and the owner also has the DELETE privilege on the first table referenced in the subselect.

⁶ When a view is created, the owner does not necessarily acquire the INSERT privilege on the view. The owner only acquires the INSERT privilege if the view allows inserts and the owner also has the INSERT privilege on the first table referenced in the subselect.

⁷ When a view is created, the owner does not necessarily acquire the UPDATE privilege on the view. The owner only acquires the UPDATE privilege if the view allows updates and the owner also has the UPDATE privilege on the first table referenced in the subselect.