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 70. 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 72
*OBJMGT (Revoke only)
*OBJOPR
*OBJREF
*ADD
*DLT
*READ
*UPD
ALTER *OBJALTER 73
DELETE
*OBJOPR
*DLT
INDEX *OBJALTER 73
INSERT
*OBJOPR
*ADD
REFERENCES *OBJREF 73
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. 74

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 71. 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 73 None
DELETE
*OBJOPR
*DLT
*DLT
INDEX Not Applicable Not Applicable
INSERT
*OBJOPR
*ADD
*ADD
REFERENCES *OBJREF 73 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 72. 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 73. 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
DELETE75
*OBJOPR and *DLT
*DLT
INDEX Not Applicable Not Applicable
INSERT76
*OBJOPR and *ADD
*ADD
REFERENCES *OBJREF or *OBJMGT None
SELECT
*OBJOPR and *READ
*READ
UPDATE77
*OBJOPR and *UPD
*UPD

72.
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.
73.
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.
74.
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.
75.
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.
76.
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.
77.
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.