Rules for using materialized query tables in a multilevel security environment
If source tables have multilevel security with row-level granularity enabled, some additional rules apply to working with the materialized query table and the source tables.
Tables with multilevel security
enabled contain a security label column, which is defined with the
AS SECURITY LABEL clause. The values in the security label column
indicate which users can access the data in each row.Creating a materialized query tablet
If one or more source tables in the materialized query table definition contain a security label column, certain rules apply to creating a materialized query table.
- Only one source table contains a security label column
- The following conditions apply:
- You must define the security label column in the materialized query table definition with the AS SECURITY LABEL clause.
- The materialized query table inherits the security label column from the source table.
- The MAINTAINED BY USER option is allowed.
- Only one source table contains a security label column, and a DEFINITION ONLY clause was used
- The materialized query table inherits the values in the security label column from the source table. However, the inherited column is not a security label column.
- More than one source table contains a security label column
- Db2 returns an error code, and the materialized query table is not created.
Altering a source table
An ALTER TABLE statement to add a security label column to a table fails if the table is a source table for a materialized query table.
Refreshing a materialized query table
The
REFRESH TABLE statement deletes the data in a materialized query table
and then repopulates the materialized query table according to its
table definition. During this refresh process, Db2 does
not check for multilevel security with row-level granularity. 