Row Level Security using ADF Business Componenets

VPD and Row Level Security

Virtual Private Database (VPD) masks data in a larger database so that the security allows the use of apparently private data. VPD actually applies some extra filtering conditions to the query issued by the database user. VPD’s row-level security allows user to restrict access to records based on a security policy implemented in PL/SQL. Security Policy describes the rules governing access to the data rows. This process is done by creating a PL/SQL function that returns a String. The function is then registered against the tables/views or synonyms, the user want to protect (Use DBMS_RLS package to register). When a query is issued against the protected object, Oracle effectively appends the String returned from the function to the original SQL statement, thereby filtering the database records.

Though Row level security can be achieved by means of Virtual Private Database, similar can be achieved using ADFC Business Components without using any PL/SQL functions. In this example, we have achieved the Row Level Security based on the user logged in. If the user role is a Manager, some of the rows in the table are displayed, for other roles they are not.

Demo Data Model

DEMO_PRP_HEADER and DEMO_PRP_LINES are two tables which share a master child relationship. DEMO_PRP_HEADER contains the selling price of an item where as the DEMO_PRP_LINES have information about the several price elements that are associated with the item.

DEMO_PRP_HEADER:



 

 

 

 

 

 

In the Lines table, there are multiple price elements, which include the Selling Price, Adjustments, Discounts offered along with the original cost and the margin of the item sold. In the Demo, we will be displaying the COST and MARGIN records of the lines table only to the user who is associated to a Manager Role.

Created a UI to display the results and configured security for the application. Two roles are created :MANAGER and SALESPERSON.Created two users:  brianadams_man is a MANAGER where as mangoman_supp is a SALESPERSON. Following are the Screenshots before configuring Row Level Security.Note that the user with SALESPERSON role is able to view the records related to COST and MARGIN:

Steps to achieve Row Level Security using ADFC Business Components

Secure Access to the entire EO

Enable security on the Lines EO. For this demo, we have enabled the security on “read” operation for the LinesEO. Right Click on the EO and in the security category, enable the "read" operation.

Grant Privileges to the Roles for the Secured Entity Object

Select the EO and on the Structure Pane, right click on the EO and select Edit Authorization.  Grant Privileges to both the roles to view the data.Though we have provided grants to both the roles, only MANAGER should be able to view the records related to COST and MARGIN.

Configure adf-config.xml for Row Level Security

Configure the adf-config file for row level security

Add a dataSecurityProviderClass to this file to handle all the security configured Entity Objects. This class should extend AbstractDataSecurityProviderImpl.java.

 

 

AbstractDataSecurityProvider in turn implements the interface DataSecurityProvider which has two methods “checkPrivilege” and “getPrivilegeFilterWhereClause”.

Method checkPrivelege Method  verifies the row instance privileges  while updating or deleting a row. Method  “getPrivilegeFilterWhereClause” gets the where clause for read restriction.In the data security provider, implement the row level security.

 

 

 

 

After this setup if the user without any manager role logs in to the application, a dynamic where clause will be built around the sql to retrieve the data and it doesn’t fetch the COST or MARGIN records.

Following are the screenshot of the testcases after implementing the row-level security at the middle tier:

Configure adf-config.xml for Row Level Security

 

Configure the adf-config file for row level security. Add a dataSecurityProviderClass to this file to handle all the security configured Entity Objects. This class should extend AbstractDataSecurityProviderImpl.java.