Revoking privileges for views
If a table privilege is revoked from the owner of a view on the table, the corresponding privilege on the view is revoked. The same privilege is also revoked from other IDs if it was granted by the view owner.
About this task
If
the SELECT privilege on the base table is revoked from the owner of
the view, the view is dropped. However, if another grantor granted
the SELECT privilege to the view owner before the view was created,
the view is not dropped.
If a view uses a user-defined function, the view owner must have the EXECUTE privilege on the function. If the EXECUTE privilege is revoked, the revoke fails because the view is using the privilege and the RESTRICT clause prevents the revoke.
An authorization ID with the SYSADM authority can create a view for another authorization ID. In this case, the view could have both a creator and an owner. The owner is automatically given the SELECT privilege on the view. However, the privilege on the base table determines whether the view is dropped.
If one ID creates a view for another ID, the catalog table SYSIBM.SYSTABAUTH needs either one or two rows to record the associated privileges. The number of rows that Db2 uses to record the privilege is determined by the following criteria:
- If IDADM creates a view for OPER when OPER has enough privileges to create the view by itself, only one row is inserted in SYSTABAUTH. The row shows only that OPER granted the required privileges.
- If IDADM creates a view for OPER when OPER does not have enough
privileges to create the view by itself, two rows are inserted in
SYSTABAUTH. One row shows IDADM as GRANTOR and OPER as GRANTEE of
the SELECT privilege. The other row shows any other privileges that
OPER might have on the view because of privileges that are held on
the base table.