12
Using Oracle Label Security with a Distributed Database

This chapter describes special considerations for using Oracle Label Security in a distributed conf iguration. It contains the following sections:

• An Oracle Label Security Distributed Configuration
• Connecting to a Remote Database Under Oracle Label Security
• Establishing Session Label and Row Label for a Remote Session
• Setting Up Labels in a Distributed Environment
• Using Oracle Label Security Policies in a Distributed Environment
• Using Replication with Oracle Label Security

An Oracle Label Security Distributed Configuration

A network configu ration that supports distributed databases can include multiple Oracle9i servers, or other database servers, running on the same or different operating systems. Each cooperative server in a distributed system communicates with other clients a nd servers over a network.

Figure 12-1 illustrates a distributed database that includes clients and servers with and without Oracle Label Security. As described in this chapter, if you establish database links from the WESTERN_REGION database to the EASTERN_REGION database, you can access data if your userid on EAST ERN_REGION is authorized to see it, even if locally (on WESTERN_REGION) you do not have this access.

Figure 12-1 Using Oracle Label Security with a Distributed Database

Text description of the illustration olsag019.gif

Connecting to a Remote Database Under Oracle Label Security

Distributed databases behave in the standard way with Oracle Label Security: the local us er ends up connected as a particular remote user. Oracle Label Security protects the labeled data, whether you connect locally or rem otely. If the remote user has the appropriate labels, you can access the data. If not, you cannot access the data.

The database link sets up the connection to the remote database and identifies the user that will be associat ed with the remote session. Your Oracle Label Security authorizations on the remote database are based upon those of the remote user identified in the database link.

For example, local user JANE might connect as remote user AUSTEN, in the database referenced by the connect string sales, as follows:

CR
EATE DATABASE LINK sales
  CONNECT TO austen IDENTIFIED BY pride
  USING 'sales'

When JANE connects, her authorizations are based on the labels and privil eges of remote user AUSTEN, since AUSTEN is the user identified in the database link. When JANE issues the first reference to the rem ote database, the remote session is actually established. For example, the remote session would be created if JANE enters:

SELECT * FROM emp@sales

You need not be an Oracle Label Security policy user in the local database. If you connect as a policy user on the remote database, you can ac cess protected data.

Establishing Session Label and Row Label for a Remote Session

When connecting remotely, you can directly control the session label and row label in effect when you establish the connection. When you connect, Oracle Label Security passes these values (for all policies) over to the remote database. Notice that:

• The local session label and row label are used as the default for the rem ote session, if they are valid for the remote user.
• The remote session is cons trained by the minimum and maximum authorizations of the remote user.
• Although the local user's session labels are passed to the remote database, the local user's privileges are not passed. The privileges for th e remote session are those associated with the remote user.

Consider a local user, Di ana, with a maximum level of HS, and a session level of S. On the remote database, the remote user identified in the database link ha s a maximum level of S.

• If Diana's session label is S when the database link is established, the S label is passed over. This is a valid label; Diana can connect and read SENSITIVE data.
• If Diana's session label is HS when the database link is established, the HS level is passed across, but it is not valid for the remote user. Diana will pick up the remote user's default label (S).

Be aware of the label at which you are running the first time you connect to the remote database. The firs t time you reference a database link, your local session labels are sent across to the remote system when a connection is made. After ward you can change the label, but to do so you must execute the SA_SESSION.SET_LABEL procedure on the remote database.

Diana can connect at level HS, set the label to S, and then perform a remote access. Connection is impli citly made when the database link is established. Her default label is S on the remote database.

On the local database, Diana can set her session label to her maximum level of HS, but if the label of the remote user is set t o S, then she can only retrieve S data from the remote database. If she performs a distributed query, she will get HS data from the l ocal database, and S data from the remote database.

Setting Up Labels in a Distributed Environment

It is advisable to use the same label component definitions and label tags on any database that is to be protec ted by the policy.

• Setting Labe l Tags in a Distributed Environment
• Setting Numeric Form of Label Components in a Distributed Environment

Setting Label Tags in a Distributed Environment

In a distributed environment you may choose to use the same label tags across multiple da tabases. However, if you choose not to use the same tags across multiple databases, you should retrieve the c haracter form of the label when performing remote operations. This will ensure that the labels are consistent.

In the following example the character string representation of the label string is the same; the label tag, howe ver, does not match. If the retrieved label tag has a value of 11 on the WESTERN_REGION database, but a tag of 2001 on the EASTERN_RE GION database, the tags have no meaning. Serious consequences can result.

Figure 12-2 Label Tags in a Distributed Database

Text description of the illustration ols ag020.gif

When retrieving labels from a remote system, you should return the character string representation (rather than the numeric label tag), unless you are using the same numeric labels on both databases.

If you allow Oracle Label Security to automatically generate labels on different databases, the label tags will not be identical. Character strings will have meaning, but the numeric values will not, unless you have predefined labels with the same label tags on both instances.

To avoid the complexities of label tags, you ca n simply convert labels to strings upon retrieval (using LABEL_TO_CHAR) and use CHAR_TO_LABEL when you store labels. Operations will succeed as long as the component names are the same.

Setting Numeric Form of Label Components in a Distributed Environment

In a distributed environment you should use the same relative ranking of the numeric fo rm of the level component, in order to ensure proper sorting of the labels.

In the followin g example, the levels in the two databases are effectively the same. Although the numeric form is different, the relative ranking of the levels' numeric form is the same. As long as the relative order of the components is the same, the labels are perceived as identi cal.

Figure 12-3 Label Components in a Distr ibuted Database

Text description of the illustration olsag021.gif

Oracle Label Security supports all standard Oracle9i distributed configurations. Whether or not you can access protected data depends on the policies installed in each d istributed database.

Be sure to take into account the relationships between databases in a distributed environment:

• If the same application runs on two d atabases, and you want them to have the same protection, you must apply the same Oracle Label Security policy to both the local and t he remote database.
• If the local and remote databases have a policy in common, then your local session label and row label will override the default labels for the remote user.
• < a name="1012495">If the remote database has a different policy from the local database, then the remote policy can restrict acces s to the data independent of your local policies. On the other hand, when you make a connection as a remote user who has authorizatio n on the remote policy, you can access any data to which the remote user has access--regardless of your local authorizations.

If the remote database has no policy applied to it, you can access its data just as you woul d with a standard distributed database.

Consider a situation in which three databases exist , with different Oracle Label Security policies in force:

Database 1 has Po licy A and Policy B

Database 2 has Policy A

Dat abase 3 had Policy C

Users authorized for Policy A can obtain protected data from Database 1 and Database 2. If the remote user is authorized for Policy C, this user can obta in data from Database 3 as well.

Using Replication with Oracle Label Security

This section explains how to use the replication option with tables protected by Oracle Label Security policies. It contains these t opics:

• Introduction to Replicat ion Under Oracle Label Security
• Contents of a Materialized View
• Requirements for Creat ing Materialized Views Under Oracle Label Security
• How to Refresh Materialized Views

  See Also: For a complete explanation of replication in Oracle9i, and how to set up the replica tion environment, see Oracle Database Advanced Re plication. For general information about using materialized views, see Oracle Database Concepts and Oracle Data Warehousing Guide

Introduction to Replication Under Oracle Label Security

This section introduces the use of replication under Oracle Label Security. It contains the following topics:

• Replication Functionality Supported by Oracle Label Security
• Row Level Security Restriction on Replication Under Oracle Label Security

Replication Functionality Supported by Oracle Label Security

Oracle Label Security supports standard replication and Advanced Replication, including multimaste r replication and updatable materialized views (snapshots).

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

As illustrated in Figure 12-4, a master table is a table you wish to replicate, on a node that you designate as the master no de. Using a dblink account (such as REPADMIN), you can create a materialized view of the table in a different database. (This can als o be done in the same database, and on the same machine.) You can select rows from the remote master table, and copy them into the lo cal materialized view. Here, mvEMP represents the materialized view of table EMP, and mlog$_EMP represents the materialized view log.

Figu re 12-4 Use of Materialized Views for Replication



Text description of the illustration olsag018.gif

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

Row Level Security Restriction on Replication Under Oracle Label Security

An Oracle Label Security policy applies Row Level Security (RLS) to a table if READ_CONT ROL 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 releva nt 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 thus 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/or t he materialized view owner) must have READ or FULL privilege. As a result, the queries used to perform the replication will not be mo dified by RLS.

See Also: Oracle Database Concepts





Contents of a Materialized View

This section discusses the contents of materialized views.

• How Materialized View Contents Are Determined
• Complete Materialized Views
• < /a>Partial Materialized Views

How Materialized View Contents Are Determined

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 mate rialized view, the result is a complete materialized view of the master table. To ensure that the materialized view is complete, ensu re that you have read access to all of the data in the master table and write access in the local database to all labels at which dat a is stored in the master table.

Note: Never revoke privileges that you granted when you created the materialized view. If you do, you may not be able to perform a replication refresh.



Partial Materialized Views

A partial materialized view is created when you specify a WHERE clause in the materialized view definit ion. This is a convenient way to pass subsets of data to a remote database.

Note: To create a partial materialized view you must have write acces s to all the rows being replicated.





Requirements for Creating Materialized Views Under Oracle Label Se curity

Requirements for creating a materialized view depend upon the type of materialized view you are creating.

• Requirements for the REPADMIN Account
• Requirements for the Owner of the Materialized View
• Requirements for Creating Partial Multilevel Materialized Views
• Requirements for Creating Complete Multilevel Materialized Views

Requirements for the REPADMI N Account

Requirements for the REPADMIN account vary depending on th e 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 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.

  See Also: Oracle Database Advanced Replication

Requirements for the Owner of the Materialized View

Remember that the privileges belonging 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
• Additio nal privileges must be granted to the owner of the materialized view.

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

CREATE MATERIALIZED VIEW mve
mp 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 depend ing on whether Oracle Label Security policies are applied on the materialized view, what privileges are granted to the owner of the m aterialized 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 priv ilege 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 ca n also be applied on the materialized view.

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

Requirements for Creating Partial Multilevel Materialized Views

To create a partial materialized view that includes only some of the rows in a remote master table protected by Oracle Label S ecurity, you must have sufficient privileges to WRITE in the local database at every label retrieved by your query.

Requirements for Creating Complete Multilevel Materialized Views

To create a complete material ized view that includes every row in a remote master table protected by Oracle Label Security, you must be able to WRITE in the local database at the labels of all of the rows retrieved by the defined materialized view query.

How to Refresh Materialized Views

If the contents or definition of a master table changes, refresh the materialized v iew so that it accurately reflects the contents of the master table. To refresh a materialized view of a remote multilevel table, you must also have privileges to write in the local database at the labels of all of the rows that the materialized view query retrieves

Warning: A materialized view can potentially contain outdated rows if you refresh a partial or full mate rialized view but do not have READ access to all of the rows in the master table, and consequently do not overwrite the rows in the o riginal materialized view with the updated rows from the master table.





To ensure an accurate materialized view refresh, use the optional materialized view background processes , SNPn, to refresh the views automatically. These processes must have sufficient privileges both to read all of the rows in the master table and to write those rows to the materialized view, ensuring that the view is completely refreshed. Rem ember that the privileges used by these processes are those of the materialized view owner.

See Also: For informati on about SNPn background processes, see Oracle Database Administrator's Guide