About Designing Data Realms

This section includes the following topics:

About Understanding the Structure of a Data Realm

A data realm is a collection of one or more object instances. An object instance is associated with a single row in a table or view. A table can have both static and dynamic data realms defined for it at the same time. As described earlier, an ACL defines the application privilege grants for the data realm.

A data realm constraint is used to associate a data realm with an ACL. Example: Components of a Data Realm Constraint creates a data realm constraint called realm_cons. The data realm constraint includes a membership rule to create a data realm. The data realm includes rows where DEPARTMENT_ID is 60 or 100. realm_cons also declares an ACL, called HRACL, to associate with the data realm.

The membership of the object instances within a data realm is determined by a rule in the form of a SQL predicate, which must be applicable to the WHERE clause of a single-table query against the storage table of the object. The SQL predicate in Example: Components of a Data Realm Constraint is DEPARTMENT_ID in (60, 100).

If the SQL you write causes errors, such as ORA-28113: policy predicate has error, then you can use trace files to find cause of the error. See “Using Trace Files to Check for Policy Predicate Errors” for more information.

Example: Components of a Data Realm Constraint uses a single ACL called HRACL. A data realm can be associated with multiple ACLs, and the same ACL can be used across multiple data realms.

Consider the following columns from the ORDERS purchase order table in the OE sample schema:

ORDER_ID	CUSTOMER_ID	ORDER_STATUS	SALES_REP_ID	ORDER_TOTAL
2354	104	0	155	46257
2355	104	8	NULL	94513.5
2356	105	5	NULL	29473.8
2357	108	5	158	59872.4
2358	105	2	155	7826

Each row in the ORDERS table is an object instance in the purchase order object. The number listed in the ORDER_ID column is the primary key used to uniquely identify a particular purchase order object instance. For example:

A data realm comprised of one object instance, that is, one row. For example, you could use the WHERE predicate of ORDER_ID=2354.
A data realm comprised of multiple object instances. For example, you could have multiple rows using the WHERE predicate of CUSTOMER_ID=104.
A data realm comprised of the entire contents of the table, defined by the WHERE predicate of 1=1.

Examples of ways to define data realms are as follows:

Use valid SQL attributes such as columns in a table.

In this case, you are using WHERE predicates such as the following:
```
CUSTOMER_ID=104
```
Changes made to the data in the rows and columns are automatically reflected in the data collected by the data realm.
Use parameters in the WHERE predicate.

You can parameterize an data realm, for example:
```
CUSTOMER_ID=&PARAM
```
This example assumes that the parameter PARAM has been associated with different customer IDs. When you grant permissions in this situation, you need to grant the permission to the specific parameter value. You must specify the values of the parameters in the ACL associated with the data realm that contains this type of WHERE predicate. This enables you to create the grant based on customer IDs without having to create many customer ID-specific data realms.
Use a membership rule based on runtime application session variables or subqueries.

An example of this type of membership rule is:
```
CUSTOMER_ID=XS_SYS_CONTEXT('order', 'cust_id')
```
However, be careful about creating membership rules that are based on session variables or subqueries. For example, suppose you wanted to use the session variable USER, which reflects the current application user, in the membership rule col=USER. Oracle Database cannot pre-compute the resultant row set because the result is not deterministic. Application user SCOTT and application user JSMITH may have a different result for the same row. However, the membership rule col='SCOTT' works because the rule is always evaluated to the same result for any given row.

See “About Using Static Data Realms” for more information about creating data realms. See also “XS_SYS_CONTEXT Function” for more information about XS_SYS_CONTEXT.

Example: Components of a Data Realm Constraint

realm_cons := XS$REALM_CONSTRAINT_TYPE(realm=> 'DEPARTMENT_ID in (60, 100)',
                                       acl_list=> XS$NAME_LIST('HRACL'));

About Using Static Data Realms

In a static data realm, Oracle Database evaluates changes to data affected by a data realm when the data is updated. You can use static data realms with tables, but not with views.

To set an data realm to be static, set its is_static attribute to true. The following example creates a static data realm:

realm_cons := XS$REALM_CONSTRAINT_TYPE(realm=> 'DEPARTMENT_ID in (60, 100)',
                                       acl_list=> XS$NAME_LIST('HRACL'),
                                       is_static=> TRUE);

Materialized Views (MVs) will be used to maintain the binding between rows in the protected table and the ACLs that protect them. They will be generated automatically whenever static data realms are included in the data security policy. These MVs will support complete refresh only and will allow up to 125 ACLs to be associated with any single row.

The MV that is generated will be of the form mv(TABLEROWID, ACLIDLIST) where TABLEROWID refers to a row in the table being protected and ACLIDLIST is a list of ACLID values stored in a RAW type column. The individual 16-byte values will be concatenated to form the list.

Oracle Database evaluates dynamic data realms each time the application user performs a query on the data realm data. You can use dynamic data realms to protect rows for both tables and views. A dynamic data realm has the most flexibility, because it is not bound by the requirements needed for static data realms. Be aware that an overly complex rule within the dynamic data realm definition may affect performance.

If the base table update is infrequent or the data realm member evaluation rule is complex, then you should consider using static data realms to protect the base table. A frequently updated base table may be constantly out of sync with the ACLIDS storage MV, unless the MV is refreshed accordingly. The administrator should make the decision based on the base table statistics and performance requirements of the system.

To set a data realm constraint to be dynamic, set its is_static attribute to FALSE, or omit the is_static attribute. The following example creates a dynamic data realm:

realm_cons := XS$REALM_CONSTRAINT_TYPE(realm=> 'DEPARTMENT_ID in (60, 100)',
                                       acl_list=> XS$NAME_LIST('HRACL'),
                                       is_static=> FALSE);

Using Trace Files to Check for Policy Predicate Errors

If the SQL defined in the realm element causes an ORA-28113: policy predicate has error or similar message, then you can use trace files to find the cause of the error. The trace file shows the actual error, along with the VPD view showing the reason for the problem. Often, the syntax of the view has a trivial error, which you can solve by analyzing the SQL text of the view.

To enable tracing, log into SQL*Plus as a user who has the ALTER SESSION privilege.

If you want to dump all the data realm constraint rules (with their parameter values resolved) into the trace file, enter the following statement:

ALTER SESSION SET EVENTS 'TRACE[XSXDS] disk=high';

If you want to dump the VPD views of the XDS-enabled table during the initial (hard) parse of a query, enter the following statement:

ALTER SESSION SET EVENTS 'TRACE[XSVPD] disk=high';

Alternatively, you can enable tracing by adding the following lines to the initialization file for the database instance:

event="TRACE[XSXDS] disk=high"
event="TRACE[XSVPD] disk=high"

You can find the location of this trace file by issuing the following SQL command:

SHOW PARAMETER USER_DUMP_DEST;

If you need to disable tracing, issue the following statements:

ALTER SESSION SET EVENTS 'TRACE[XSVPD] off';
ALTER SESSION SET EVENTS 'TRACE[XSXDS] off';

See Also:

“About Data Security (XSXDS and XSVPD) Event-Based Tracing”
Oracle AI Database Administrator’s Guide for more information about using trace files