Revoking privileges for materialized query tables
If the SELECT privilege on a source table is revoked from the owner of a materialized query table, the corresponding privilege on the materialized query table is revoked. The same privilege is also revoked from other IDs if it was granted by the table owner.
About this task
If the SELECT privilege on the source table is revoked
from the owner of a materialized query table, the materialized query
table is dropped. However, if another grantor granted the SELECT privilege
to the materialized query table owner before the materialized query
table was created, the materialized query table is not dropped.
Example: Suppose that OPER7 has the SELECT privilege on table T1 and creates a materialized query table T2 by selecting from T1. If the SELECT privilege on T1 is revoked from OPER7, and if OPER7 did not have the SELECT privilege from another grantor before T2 was created, T2 is dropped.
If
a materialized query table uses a user-defined function, the owner
of the materialized query table must have the EXECUTE privilege on
the function. If the EXECUTE privilege is revoked, the revoke fails
because the materialized query table is using the privilege and the
RESTRICT clause prevents the revoke. 