Oracle Label Security supports replication using read-only materialized views (snapshots).

Oracle Database uses materialized views for replicating data. A materialized view is a local copy of a local or remote master table that reflects a recent state of the master table.

As illustrated in Figure 14-4, a master table is a table you wish to replicate, on a node that you designate as the master node. Using a dblink account, you can create a materialized view of the table in a different database. (This can also be done in the same database, and on the same system.) You can select rows from the remote master table, and copy them into the local materialized view. Here, mvEMP represents the materialized view of table EMP, and mlog$_EMP represents the materialized view log.

In a distributed environment, a materialized view alleviates query traffic over the network and increases data availability when a node is not available.

An Oracle Label Security policy applies Row Level Security (RLS) to a table if READ_CONTROL is specified as one of the policy options.

Problems occur if both of the following conditions are true:

• The Oracle Label Security policy is applied to any table relevant to replication (such as the master table, materialized view, or materialized view log), and

• The policy returns a predicate in the WHERE clause of SELECT statements.

To avoid the additional predicate (and therefore avoid this problem), the users involved in a replication environment should be given the necessary Oracle Label Security privileges. To be specific, the designated users in the database link (such as REPADMIN and the materialized view owner) must have the READ or the FULL privilege. As a result, the queries used to perform the replication will not be modified by RLS.

Oracle Label Security performs a set steps when creating materialized views.

The following steps determine the contents of the view:

1. It reads the definition of the master table in the remote database.

2. It reads the rows in the master table that meet the conditions defined in the materialized view definition.

3. It writes these rows to the materialized view in the local database.

Because Oracle Label Security writes only those rows to which you have write access in the local database, the contents of the materialized view vary according to:

• The policy options in effect

• The privileges you have defined in the local database

• The session label

Oracle Label Security supports complete materialized views.

If you read all of the rows in the master table and have write access in the local database to each label in the materialized view, then the result is a complete materialized view of the master table. To ensure that the materialized view is complete, you should have read access to all of the data in the master table and write access in the local database to all labels at which data is stored in the master table.

A partial materialized view is created when you specify a WHERE clause in the materialized view definition.

A partial materialized view is a convenient way to pass subsets of data to a remote database.

To create a partial materialized view, a user must have write access to all the rows being replicated. You can find the currently granted privileges for a user by querying the DBA_SA_USER_PRIVS data dictionary view.

Requirements for a replication administrator, typically using a REPADMIN account, vary depending on the configuration.

In general, however, it should meet the following requirements:

• It must have the FULL Oracle Label Security privilege (mandatory for all configurations).

• It must have the SELECT privilege on the master table.

• It must be the account that establishes the database link from the remote node to the database containing the master table.

The privileges that belong to the owner of the materialized view are used during the refresh of the materialized view.

If these privileges are not sufficient, then there are two options:

• The materialized view can be created in the REPADMIN account, or

• Additional privileges must be granted to the owner of the materialized view.

Consider, for example, the following materialized view created by user SCOTT:

CREATE MATERIALIZED VIEW mvemp as
SELECT *
FROM EMP@link_to_master
WHERE label_to_char(sa_label) = 'HS';

Here, SCOTT should have permission to insert records at the HS level in the local database. If Oracle Label Security policies are applied on the materialized view, then SCOTT must have the FULL privilege to avoid the RLS restriction.

Different configurations can be set up depending on whether Oracle Label Security policies are applied on the materialized view, what privileges are granted to the owner of the materialized view, and so on. If Oracle Label Security policies are applied to the materialized view, but SCOTT should not be granted the FULL privilege, then the REPADMIN account must be used to create the materialized view. SCOTT can then be granted the SELECT privilege on that table.

If no policies are applied to the materialized view, then the view can be created in SCOTT's schema without any additional privileges. In this case, the materialized view should be created in such a way that a WHERE condition limits the records to those which SCOTT can read.

Finally, if SCOTT can be granted the FULL privilege, then the materialized view can be created in SCOTT's schema, and Oracle Label Security policies can also be applied on the materialized view.

Note that the master table can have Oracle Label Security policies containing any set of policy options. If SCOTT has the FULL or the READ privilege, he can select all rows, regardless of policy options.

A partial materialized view can include only some of the rows in a remote master table that is protected by Oracle Label Security.

If the partial materialized view is used in a table that Oracle Label Security protects, then you should ensure that you have sufficient privileges to WRITE in the local database at every label retrieved by your query. You can find your currently granted privileges by querying the ALL_SA_USER_PRIVS data dictionary view.

A complete materialized view can include every row in a remote master table that is protected by Oracle Label Security.

If the complete materialized view is used in a table that Oracle Label Security protects, then you must be able to have WRITE access in the local database at the labels of all of the rows retrieved by the defined materialized view query. You can find your currently granted privileges by querying the ALL_SA_USER_PRIVS data dictionary view.