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 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.
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.
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 |
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 |
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.