About Creating Real Application Security Policies on Master-Detail Related Tables

This section includes the following topics:

• About Real Application Security Policies on Master-Detail Related Tables

• About Understanding the Structure of Master Detail Data Realms

• Example of Creating a Real Application Security Policy on Master-Detail Related Tables

For more information about master-detail tables, see the chapter about creating a master-detail application using JPA and Oracle ADF in Oracle AI Database Get Started with Java Development.

About Real Application Security Policies on Master-Detail Related Tables

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.

About Understanding the Structure of Master Detail Data Realms

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: A Master Detail Data Realm 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.

Example: A Master Detail Data Realm

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

Example of Creating a Real Application Security Policy on Master-Detail Related Tables

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)

Real Application Security Data Security Policy Created on Master-Detail Related Tables 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 Real Application Security Data Security Policy Created on Master-Detail Related Tables, 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.

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

Description of the illustration dspolicy_master_detail.png

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