About Enabling Data Security Policy for a Database Table or View

The XS_DATA_SECURITY.APPLY_OBJECT_POLICY procedure applies a data security policy on a table or view.

This section includes the following topics:

Enabling Real Application Security Using the APPLY_OBJECT_POLICY Procedure

Use the XS_DATA_SECURITY.APPLY_OBJECT_POLICY procedure to enable Real Application Security for a database table or view. Example: Using XS_DATA_SECURITY.APPLY_OBJECT_POLICY enables the ORDERS_DS data security policy for the OE.ORDERS table. See “APPLY_OBJECT_POLICY Procedure” for more information.

Example: Using XS_DATA_SECURITY.APPLY_OBJECT_POLICY

BEGIN  SYS.XS_DATA_SECURITY.APPLY_OBJECT_POLICY(policy=>'ORDERS_DS',
                                       schema=>'OE',
                                       object=>'ORDERS');
END;

This section includes the following topic: About Applying Multiple Policies for a Table or View.

About Applying Multiple Policies for a Table or View

You can apply multiple data security policies for a table or view. When a table or view is protected by multiple data security policies, an application user has access to only those rows that are allowed by all the policies. So, for example, if the data realm for Policy 1 includes a row, but the data realm for Policy 2 does not include the same row, the application user would be unable to access the row.

Column security works similarly. Consider the case where column Col1 is protected by multiple policies: Policy1 protects it with Priv1, Policy2 protects it with Priv2, and so forth. Then an application user must have been granted all application privileges (Priv1, Priv2, and so forth) to access Col1.Thus, for columns protected by column policies, an application user must have been granted access by all policies protecting the column.

About How the APPLY_OBJECT_POLICY Procedure Alters a Database Table

The following table, OE.ORDERS, shown earlier under “About Understanding the Structure of a Data Realm”, has been enabled with XS_DATA_SECURITY.APPLY_OBJECT_POLICY. It shows the addition of the hidden SYS_ACLOID column. This column, whose data type is NUMBER, lists application user-managed ACL identifiers. The following table contains the application user-managed ACL identifier 500, which is a direct grant on the object instance identified by the order ID 2356.

Note: The SYS_ACLOID hidden column can be enabled by passing the value XS_DATA_SECURITY.APPLY_ACLOID_COLUMN for the apply_option parameter when invoking the XS_DATA_SECURITY procedure. Real Application Security allows only one ACLID to be added to the SYS_ACLOID column.

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

The system-managed static ACL identifiers, are stored in a Materialized View (MV).

TABLEROWID	ACLIDLIST
`AAAO/8AABAAANrCABJ`	`60FB8AAA40D46C9EE040449864653987`
`AAAO/8AABAAANrCAB`L	`60FB8AAA40D46C9EE040449864653987`

To find detailed information on the data realms or data realm constraints associated with a table, query the DBA_XS_REALM_CONSTRAINTS data dictionary view. See “DBA_XS_REALM_CONSTRAINTS” for more information.

About How ACLs on Table Data Are Evaluated

When Oracle Database evaluates a set of ACLs, it stops the evaluation when it finds the first grant or deny. For this reason, it is important to plan the order of ACLs carefully. The ACLs associated with each row in a table are evaluated in the following order:

The ACLs from grants directly on object instances (that is, application user-managed ACL identifiers) are evaluated first. See “About Configuring Access Control Lists” for more information about creating an ACL and adding it to the object instance.
The ACLs from static data realm constraint grants are evaluated next, after application user-managed ACLs. If you have multiple static data realms, they are evaluated in the order of their physical appearance in the data security policy. See “About Using Static Data Realms” for more information about static data realms.
The ACLs from dynamic data realm constraint grants are evaluated last. If you have multiple dynamic data realms, they are evaluated in the order of their physical appearance in the policy. See “About Using Static Data Realms” for more information about dynamic data realms.