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 and is identified by the primary key value of the row in the storage table of the object. 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 5-2 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 5-2 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 5-2 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:

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.

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

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);

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.

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

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.

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:

1. 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.

2. 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.

3. 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.

You can create a data security policy that can be used for master-detail related tables. Typically, you may want the same policy that protects the master table to protect its detail tables. Creating a Real Application Security policy for master-detail tables enables anyone accessing these tables to do so under a uniform policy that can be inherited from master table to detail table.

The possible inheritance paths for policies and master-detail tables are as follows:

• Multiple detail tables can inherit policies from one master table.

• Detail tables can inherit policies from other detail tables.

• One detail table can inherit policies from multiple master tables.

If any one of the policies in the master table is satisfied, then application users can access the corresponding rows in the detail table.

To create a Real Application Security policy for master-detail related tables, you must create a data security policy for each table. In each data security policy for the detail tables, you indicate the master table from which the detail table inherits by including master detail data realms. Steps 4, 6 and 7 in the procedure under "Example of Creating a Real Application Security Policy on Master-Detail Related Tables" shows examples of creating and using master-detail data realms and creating and applying master-detail data security policies to master-detail tables.

Example 5-6 shows a sample master detail data realm.

In this specification:

• when_condition specifies a predicate for the detail table, similar to a WHERE clause, to filter data. If when_condition evaluates to true, then Oracle Database applies the master policy. This element is optional.

• parent_schema specifies the name of the schema that contains the master table.

• parent_object specifies the name of the master table.

• primary_key specifies the primary key from the master table.

• foreign_key specifies the foreign key of the detail table.

  realm_cons :=  XS$REALM_CONSTRAINT_TYPE
                 (parent_schema=> 'OE',
                  parent_object=> 'CUSTOMERS',
                  key_list=> XS$KEY_LIST(XS$KEY_TYPE(primary_key=> 'CUSTOMER_ID',
                                                 foreign_key=> 'CUSTOMER_ID',
                                                 foreign_key_type=> 1)),
                  when_condition=> 'ORDER_STATUS IS NOT NULL')

This example uses the SH sample schema. The SH schema has a table called CUSTOMERS, which is the master table. The master table CUSTOMERS has a detail table called SALES, and another detail table called COUNTRIES. The following example demonstrates how to enforce a Real Application Security policy that virtually partitions the customer and sales data along their regional boundary defined in the COUNTRIES table for read access of the CUSTOMERS and SALES tables. In addition, there is a requirement to mask out data on the columns CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT to users, except for those users who need full table access for business analysis, such as the business analyst.

Note:

All administrative commands in this example can be performed by a database user, such as the SYSTEM account who has the DBA roles in the database, because the DBA role has been granted appropriate privilege for Real Application Security administrative tasks. In addition, because security classes, ACLs, and data security policies are schema qualified objects, you must explicitly use the intended schema name when these objects are specified in the APIs, so they will not be resolved to objects under the database session default schema of SYSTEM.

The descriptions for the three tables, which are all in the same schema (SH), are as follows:

-- SH.CUSTOMERS in the master table.
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUST_ID                                   NOT NULL NUMBER
 CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                            NOT NULL VARCHAR2(40)
 CUST_GENDER                                        CHAR(1)
 CUST_YEAR_OF_BIRTH                                 NUMBER(4)
 CUST_MARITAL_STATUS                                VARCHAR2(20)
 CUST_STREET_ADDRESS                       NOT NULL VARCHAR2(40)
 CUST_POSTAL_CODE                          NOT NULL VARCHAR2(10)
 CUST_CITY                                 NOT NULL VARCHAR2(30)
 CUST_STATE_PROVINCE                                VARCHAR2(40)
 COUNTRY_ID                                NOT NULL CHAR(2)
 CUST_MAIN_PHONE_NUMBER                             VARCHAR2(25)
 CUST_INCOME_LEVEL                                  VARCHAR2(30)
 CUST_CREDIT_LIMIT                                  NUMBER
 CUST_EMAIL                                         VARCHAR2(30)

-- SH.SALES is a detail table.
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                   NOT NULL NUMBER(6)
 CUST_ID                                   NOT NULL NUMBER
 TIME_ID                                   NOT NULL DATE
 CHANNEL_ID                                NOT NULL CHAR(1)
 PROMO_ID                                  NOT NULL NUMBER(6)
 QUANTITY_SOLD                             NOT NULL NUMBER(3)
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

-- SH.COUNTRIES is a detail table.
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COUNTRY_ID                                NOT NULL CHAR(2)
 COUNTRY_NAME                              NOT NULL VARCHAR2(40)
 COUNTRY_SUBREGION                                  VARCHAR2(30)
 COUNTRY_REGION                                     VARCHAR2(20)

Figure 5-2 shows an overview of the completed Real Application Security data security policies created and applied to the master-detail related tables (CUSTOMERS - SALES - COUNTRIES) that are described as an overview in the following steps and in more detail in the steps that follow this figure.

1. Create the principals, an application role and an application user, for each of four geographic regions: Europe, Americas, Asia, and Africa, in addition to a business analyst role and an associated application user.

2. Create the VIEW_SENSITIVE_INFO privilege and create the SH.CUST_SEC_CLASS in which to scope the privilege.

3. Grant the VIEW_SENSITIVE_INFO privilege to the business analyst role.

4. Define a data realm constraint with a rule that parameterizes regions in order for the system to recognize the string &REGION, which will later be used in a policy.

5. Create a column constraint to secure the two columns, CUST_INCOME_LEVEL and CUST_CREDIT_LEVEL using the VIEW_SENSITIVE_INFO privilege.

6. Create the data security policy SH.CUSTOMER_DS specifying the data realm constraint and the column constraint that was previously created.

7. Register the name and data type of the parameter in the rule for the SH.CUSTOMER_DS data security policy.

8. Create the ACLs for each region to authorize read access to the respective roles needing read access. For example for the Europe region, you grant SELECT privilege to the Europe_sales role and grant SELECT and VIEW_SENSITIVE_INFO privileges to the Business_Analyst role.

9. Associate each ACL in each region with the rows that satisfy the rule where the value of the parameter REGION is equal to region name, for example, Europe. You do this for each of the four regions, and then add this ACL to the SH.CUSTOMER_DS data security policy.

10. Create the data realm constraint for the master-detail tables, so users can access a record in the SALES detail table only if a user is authorized to access its parent row in the CUSTOMERS master table.

11. Create the SH.SALES_DS data security policy to enforce this data realm constraint.

In Figure 5-2, the master-detail tables also show the primary key (PK) fields and foreign key (FK) fields and a number of additional fields that are used in creating the data realm constraints and column constraints. Using these PK and FK relationships, the same data security policies that apply to the master table also apply to the detail tables. In this particular case, for example, all ACLs granting SELECT privilege to the CUSTOMERS master table and enforced by the SH.CUSTOMER_DS data security policy, also applies to the SALES detail table.

Figure 5-2 Real Application Security Data Security Policy Created on Master-Detail Related Tables

Description of "Figure 5-2 Real Application Security Data Security Policy Created on Master-Detail Related Tables"

To create a Real Application Security policy for these master-detail tables, follow these steps:

1. Create the roles and users needed for each country, (role Europe_sales, user SMITH), (role Americas_sales, user JAMES), (role Asia_sales, user MILLER), (role Africa_sales, user MARTIN), and (role Business_Analyst, user TURNER), who is the only user who will have full table access.

   BEGIN
      sys.xs_principal.create_role(name => 'Europe_sales', enabled => TRUE);
      sys.xs_principal.create_role(name => 'Americas_sales', enabled => TRUE);
      sys.xs_principal.create_role(name => 'Asia_sales', enabled => TRUE);
      sys.xs_principal.create_role(name => 'Africa_sales', enabled => TRUE);
      sys.xs_principal.create_role(name => 'Business_Analyst', enabled => TRUE);
    
      sys.xs_principal.create_user(name => 'SMITH', schema => 'SH');
      sys.dbms_xs_principals.set_password(username => 'SMITH',
                                          password => 'password',
                                          type => XS_PRINCIPAL.XS_SHA512);
      sys.xs_principal.grant_roles(grantee => 'SMITH', role => 'Europe_sales');
    
      sys.xs_principal.create_user(name =>' JAMES', schema => 'SH');
      sys.dbms_xs_principals.set_password(username => 'JAMES',
                                          password => 'password',
                                          type => XS_PRINCIPAL.XS_SHA512);
      sys.xs_principal.grant_roles(grantee => 'JAMES', role => 'Americas_sales');
    
      sys.xs_principal.create_user(name => 'MILLER', schema => 'SH');
      sys.dbms_xs_principals.set_password(username => 'MILLER',
                                          password => 'password',
                                          type => XS_PRINCIPAL.XS_SHA512);
      sys.xs_principal.grant_roles(grantee => 'MILLER', role => 'Asia_sales');
    
      sys.xs_principal.create_user(name => 'MARTIN', schema => 'SH');
      sys.dbms_xs_principals.set_password(username => 'MARTIN',
                                          password => 'password',
                                          type => XS_PRINCIPAL.XS_SHA512);
      sys.xs_principal.grant_roles(grantee => 'MARTIN', role => 'Africa_sales');
    
      sys.xs_principal.create_user(name => 'TURNER', schema=> 'SH');
      sys.dbms_xs_principals.set_password(username => 'TURNER',
                                          password => 'password',
                                          type => XS_PRINCIPAL.XS_SHA512);
      sys.xs_principal.grant_roles(grantee => 'TURNER', role => 'Business_Analyst');
   END;
   

2. Define the SH.CUST_SEC_CLASS security class for the privilege, VIEW_SENSITIVE_INFO to protect the sensitive columns.

   The row level privileges to access data security protected objects for query and DML are predefined in the Security Class DML under the SYS schema.

   DECLARE
     pr_list  XS$PRIVILEGE_LIST;
   BEGIN
   -- Let's call the new privilege VIEW_SENSIATIVE_INFO
     pr_list := XS$PRIVILEGE_LIST(XS$PRIVILEGE(name => 'VIEW_SENSITIVE_INFO'));
    
     sys.xs_security_class.create_security_class(
              name => 'SH.CUST_SEC_CLASS', 
              description => 'Security Class to protect CUSTOMERS and SALES data',
              parent_list => XS$NAME_LIST('SYS.DML'),
              priv_list => pr_list);
   END;
   

3. Define the data realm constraint with a rule that parameterizes regions, then define the column constraint and specify the name of the two columns, CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT, to be secured by the VIEW_SENSITIVE_INFO privilege. Then, create a SH.CUSTOMER_DS data security policy and register the name and data type of the parameter in the rule.

   The security policy requires that regional customers and sales data be partitioned with different ACLs. One way to achieve this is to define as many data realms as regions and do this for both tables. However, in this example, another way is shown. That is, to parameterize the region in a data realm with a single rule and use the master-detail relationship to simplify the administrative tasks.

   So, instead of creating many constraints for the policy, it is more efficient to create only one constraint with the following rule that parameterizes the region:

   COUNTRY_ID in
    (select COUNTRY_ID from SH.COUNTRIES where COUNTRY_REGION = &REGION)
   

   In order for the system to recognize that the string &REGION in the rule is indeed a parameter, you must invoke the xs_data_security.create_acl_parameter procedure to register the parameter name after the policy is created. In addition, you must specify the data type of the parameter value. Since regions are stored as character string data, the XS_ACL.TYPE_VARCHAR macro is used for this example. Another supported data type is XS_ACL.TYPE_NUMBER for numbers.

   DECLARE
     rows_secs XS$REALM_CONSTRAINT_LIST;
     cols_secs XS$COLUMN_CONSTRAINT_LIST;
   BEGIN
   -- Define the realm constraint with a rule that parameterizes regions.
     rows_secs := xs$REALM_CONSTRAINT_LIST(
             XS$REALM_CONSTRAINT_TYPE(
               realm => 'COUNTRY_ID in (select COUNTRY_ID from SH.COUNTRIES ' ||
                        'where COUNTRY_REGION = &' || 'REGION)'));
    
   -- Define the column constraint to secure CUST_INCOME_LEVEL and
   -- CUST_CREDIT_LIMIT columns by using the VIEW_SENSITIVE_INFO privilege.
     cols_secs := XS$COLUMN_CONSTRAINT_LIST(
           XS$COLUMN_CONSTRAINT_TYPE(
             column_list => XS$LIST('CUST_INCOME_LEVEL', 'CUST_CREDIT_LIMIT'),
             privilege => 'VIEW_SENSITIVE_INFO'));
    
   -- Create the data security policy.
     sys.xs_data_security.create_policy(
             name => 'SH.CUSTOMER_DS',
             realm_constraint_list => rows_secs,
             column_constraint_list => cols_secs,
             description => 'Policy to protect sh.customers table');
    
   -- Register the name and data type of the parameter in the rule.
     sys.xs_data_security.create_acl_parameter(
              policy => 'SH.CUSTOMER_DS',
              parameter => 'REGION',
              param_type => XS_ACL.TYPE_VARCHAR);
   END;
   

4. Create ACLs to authorize read access for each region. For the Europe region, grant SELECT to the Europe_sales role. In addition, SELECT and VIEW_SENSITIVE_INFO privileges are granted to the Business_Analyst role so that the grantee of the role has full table access and is able to see data in the columns of CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT as well.

   DECLARE
     ace_list XS$ACE_LIST;
   BEGIN
     ace_list := XS$ACE_LIST(
                 XS$ACE_TYPE(privilege_list => XS$NAME_LIST('SELECT'),
                             granted => true,
                             principal_name => 'Europe_sales'),
                 XS$ACE_TYPE(privilege_list =>
                               XS$NAME_LIST('SELECT', 'VIEW_SENSITIVE_INFO'),
                             granted => true,
                             principal_name => 'Business_Analyst'));
    
     sys.xs_acl.create_acl(name => 'View_Europe_sales',
                     ace_list => ace_list,
                     sec_class => 'SH.CUST_SEC_CLASS',
                     description => 'Authorize read access for the Europe region');
    
   -- The ACL must be associated with rows that satisfy the rule where the value
   -- of the parameter REGION is equal to Europe. For example the constraint 
   -- rule becomes the COUNTRY_ID in 
   --  (select COUNTRY_ID from SH.COUNTRIES where COUNTRY_REGION = 'Europe').
    
     sys.xs_acl.add_acl_parameter(acl => 'View_Europe_sales',
                              policy => 'SH.CUSTOMER_DS',
                              parameter => 'REGION',
                              value => 'Europe');
   END;
   

5. Create ACLs to authorize read access for the other three regions, Americas, Asia, and Africa.

   DECLARE
     ace_list XS$ACE_LIST;
   BEGIN
     ace_list := XS$ACE_LIST(
                 XS$ACE_TYPE(privilege_list => XS$NAME_LIST('SELECT'),
                             granted => true,
                             principal_name => 'Americas_sales'),
                 XS$ACE_TYPE(privilege_list =>
                               XS$NAME_LIST('SELECT', 'VIEW_SENSITIVE_INFO'),
                             granted => true,
                             principal_name => 'Business_Analyst'));
    
     sys.xs_acl.create_acl(name => 'View_Americas_sales',
                   ace_list => ace_list,
                   sec_class => 'SH.CUST_SEC_CLASS',
                   description => 'Authorize read access for the Americas region');
    
     sys.xs_acl.add_acl_parameter(acl => 'View_Americas_sales',
                              policy => 'SH.CUSTOMER_DS',
                              parameter => 'REGION',
                              value => 'Americas');
   END;
   
   DECLARE
     ace_list XS$ACE_LIST;
   BEGIN
     ace_list := XS$ACE_LIST(
                 XS$ACE_TYPE(privilege_list => XS$NAME_LIST('SELECT'),
                             granted => true,
                             principal_name => 'Asia_sales'),
                 XS$ACE_TYPE(privilege_list =>
                               XS$NAME_LIST('SELECT', 'VIEW_SENSITIVE_INFO'),
                             granted => true,
                             principal_name => 'Business_Analyst'));
    
     sys.xs_acl.create_acl(name => 'View_Asia_sales',
                   ace_list => ace_list,
                   sec_class => 'SH.CUST_SEC_CLASS',
                   description => 'Authorize read access for the Asia region');
    
     sys.xs_acl.add_acl_parameter(acl => 'View_Asia_sales',
                              policy => 'SH.CUSTOMER_DS',
                              parameter => 'REGION',
                              value => 'Asia');
   END;
   
   DECLARE
     ace_list XS$ACE_LIST;
   BEGIN
     ace_list := XS$ACE_LIST(
                 XS$ACE_TYPE(privilege_list => XS$NAME_LIST('SELECT'),
                             granted => true,
                             principal_name => 'Africa_sales'),
                 XS$ACE_TYPE(privilege_list =>
                               XS$NAME_LIST('SELECT', 'VIEW_SENSITIVE_INFO'),
                             granted => true,
                             principal_name => 'Business_Analyst'));
    
     sys.xs_acl.create_acl(name => 'View_Africa_sales',
                   ace_list => ace_list,
                   sec_class => 'SH.CUST_SEC_CLASS',
                   description => 'Authorize read access for the Africa region');
    
     sys.xs_acl.add_acl_parameter(acl => 'View_Africa_sales',
                              policy => 'SH.CUSTOMER_DS',
                              parameter => 'REGION',
                              value => 'Africa');
   END;
   

6. Apply the SH.CUSTOMER_DS policy created in Step 3 to protect read access to the CUSTOMERS table.

   BEGIN
     sys.xs_data_security.apply_object_policy(
              policy => 'SH.CUSTOMER_DS',
              schema => 'SH',
              object => 'CUSTOMERS',
              statement_types => 'SELECT',
              owner_bypass => true);
   END;
   

7. Create the data realm master-detail constraint to protect the SALES table. This master-detail constraint utilizes the same regional partitioning policy as previously described in Steps 3 through 6. This means that a user can access a record in the SALES detail table only if that user is authorized to access its parent row in the CUSTOMERS master table.

   DECLARE
     rows_secs XS$REALM_CONSTRAINT_LIST;
   BEGIN
   -- Define the master-detail constraint.
     rows_secs := xs$REALM_CONSTRAINT_LIST(
       XS$REALM_CONSTRAINT_TYPE(
         parent_schema => 'SH',
         parent_object => 'CUSTOMERS',
         key_list => xs$key_list(xs$key_type(primary_key => 'CUST_ID',
                                             foreign_key => 'CUST_ID',
                                             foreign_key_type => 1))));
    
   -- Create a policy to enforce the constraint.
     sys.xs_data_security.create_policy(
            name => 'SH.SALES_DS',
            realm_constraint_list => rows_secs,
            column_constraint_list => null);
    
   -- Apply the policy to protect read access of the SALES table.
     sys.xs_data_security.apply_object_policy(
              policy => 'SH.SALES_DS',
              schema => 'SH',
              object => 'SALES',
              statement_types => 'SELECT',
              owner_bypass => true);
   END;
   

8. Grant object level SELECT privilege to PUBLIC for users to perform a query.

   GRANT SELECT ON sh.customers TO PUBLIC;
   GRANT SELECT ON sh.countries TO PUBLIC;
   GRANT SELECT ON sh.sales TO PUBLIC;
   

9. Connect as user MARTIN and perform a query to display user MARTIN's sales data for the Africa region and to show the masking of the sensitive sales information for the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns.

   CONNECT MARTIN/welcome
   
   SELECT c.COUNTRY_NAME, c.COUNTRY_ID, ct.CUST_FIRST_NAME, PROD_ID, QUANTITY_SOLD
    FROM sh.customers ct, sh.sales s, sh.countries c
    WHERE  ct.CUST_ID = s.CUST_ID AND
           ct.COUNTRY_ID = c.COUNTRY_ID;
    
   COUNTRY_NAME         CO CUST_FIRST_NAME         PROD_ID QUANTITY_SOLD
   -------------------- -- -------------------- ---------- -------------
   South Africa         ZA Forrest                    8050             2
   South Africa         ZA Mitch                     17505            11
   South Africa         ZA Murry                     32785             7
   South Africa         ZA Heath                      3585            12
   

The following database users can bypass the security checks of a Real Application Security Policy:

• User SYS

• Database users who have the EXEMPT ACCESS POLICY system privilege

• The owner of the object to which the policy is applied.

  If the data security policy is applied to an object with the owner bypass specification, the owner of the object may bypass such policy. By default, owner bypass is not allowed.

  The object owner also can create another view on the same table and assign this view a different Real Application Security policy.