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.
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) | |
ALTER | *OBJALTER 2 |
DELETE | *OBJOPR 3
*DLT |
INDEX | *OBJALTER 2 |
INSERT | *OBJOPR3
*ADD |
REFERENCES | *OBJREF 2 |
SELECT | *OBJOPR3
*READ |
UPDATE | *OBJOPR3
*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. 4
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.
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 2 | None |
DELETE | *OBJOPR3
*DLT |
*DLT |
INDEX | Not Applicable | Not Applicable |
INSERT | *OBJOPR3
*ADD |
*ADD |
REFERENCES | *OBJREF 2 | None |
SELECT | *OBJOPR3
*READ |
*READ |
UPDATE | *OBJOPR3
*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.
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.
SQL Privilege | Corresponding System Authorities to the View | Corresponding System Authorities to the Referenced Tables and Views |
---|---|---|
ALTER | *OBJALTER and *OBJMGT | None |
DELETE5 | *OBJOPR and *DLT
|
*DLT |
INDEX | Not Applicable | Not Applicable |
INSERT6 | *OBJOPR and *ADD
|
*ADD |
REFERENCES | *OBJREF or *OBJMGT | None |
SELECT | *OBJOPR and *READ
|
*READ |
UPDATE7 | *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