Back in June, I wrote a blog post on scrambling HR data in our EBS instances . Although effective, it was a bit of a kludge – it involved an excel spreadsheet, and giving everyone the same salary and banking info.

As we went with this solution in our development and test environments, we ran into some issues where the salary data would totally screw up the benefits data as it’s calculated as a percentage of salary. The solution was effective at keeping the data secure, but it wasn’t optimal. After some investigation, we turned to Oracle VPD – Virtual Private Database – functionality. With this we are able to restrict access to certain columns (such as salary or national identifiers) to all but necessary users. With an EBS database, where every connection is connect as APPS, this poses special considerations.

I’ll cover the technical details of implementing VPD in an EBS environment. Then I’ll talk about the changes you need to make to keep things functional for your business analysts and yet keep the data secure.

First it was necessary to create a policy function. In our case this is very generic, basically just returning the predicate.

CREATE OR REPLACE FUNCTION “APPS”.”LUM_HIDE_HR_COLS” (schema in varchar2, tab in varchar2)

return varchar2 as predicate varchar2(8) default ‘1=2’;

begin

return predicate;

end;

/

Next we add a policy on the column we want to restrict access to point it at the policy function we created

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PER_ALL_PEOPLE_F’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘NATIONAL_IDENTIFIER,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;

/

In this case we’re ADDing a policy and ENABLing it, applying the LUM_HIDE_HR_COLS function on the NATIONAL_IDENTIFIER column of table PER_ALL_PEOPLE_F in the HR schema, preventing users from SELECTing data and stating that this is for ALL_ROWS.

Once we issued that, all users (besides SYS and SYSTEM) will get NULLs when they select NATIONAL_IDENTIFIER from that table. That took care of our social security number concern.

We also set up additional policies on other data:

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PER_PAY_PROPOSALS’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘PROPOSED_SALARY_N,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;

/

The above policy restricts access to PROPOSED_SALARY_N column of HR.PER_PAY_PROPOSALS. That took care of our salary data concern.

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PAY_EXTERNAL_ACCOUNTS’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘SEGMENT3,SEGMENT4,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;
/

The above policy restricts access to the SEGMENT3 and SEGMENT4 columns of HR.PAY_EXTERNAL_ACCOUNTS. That took care of our banking data concern.

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PER_ADDRESSES’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘ADDRESS_LINE1,ADDRESS_LINE2,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;
/
The above policy restricts access to ADDRESS_LINE1 and ADDRESS_LINE2 columns of HR.PER_ADDRESSES. That took care of our concern of employees looking up addresses of other employees.

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PER_ALL_ASSIGNMENTS_F’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘GRADE_ID,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;
/

The above policy restricts access to the GRADE_ID column of HR.PER_ALL_ASSIGNMENTS_F. That took care of our concern about employees looking up the pay grade of other employees.

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PAY_ELEMENT_ENTRY_VALUES_F’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘SCREEN_ENTRY_VALUE,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;
/

The above policy restricts access to the SCREEN_ENTRY_VALUE column of HR.PAY_ELEMENT_ENTRY_VALUES_F. That took care of our figuring out salary based on insurance coverage concern.

That’s all there is to it. You can just issue the code above in an Apps 11i instance and at that point no user besides SYS and SYSTEM can see that data.
Now that you’ve handled the technical details, there’s the issue of cloning the instances and being able to test HR functionality in development environments while still restricting the data. Here’s what we do.
In PROD, in addition to the function and policies listed above, we create a read-only user calls APPSHR:

create user APPSHR identified by XXXXXXXXXXXXX;
GRANT ALTER SESSION TO “APPSHR”;
GRANT CREATE DATABASE LINK TO “APPSHR”;
GRANT CREATE PUBLIC SYNONYM TO “APPSHR”;
GRANT CREATE SESSION TO “APPSHR”;
GRANT CREATE SYNONYM TO “APPSHR”;
GRANT EXECUTE ANY PROCEDURE TO “APPSHR”;
GRANT SELECT ANY DICTIONARY TO “APPSHR”;
GRANT SELECT ANY SEQUENCE TO “APPSHR”;
GRANT SELECT ANY TABLE TO “APPSHR”;
GRANT UNLIMITED TABLESPACE TO “APPSHR”;
GRANT “RESOURCE” TO “APPSHR”;
ALTER USER “APPSHR” DEFAULT ROLE ALL;
That APPSHR user now has the ability to select any data in the system but it’s read only (no updating).
We then exempt the APPS and APPSHR user in PROD ONLY from the policies we created:

GRANT EXEMPT ACCESS POLICY to APPS;
GRANT EXEMPT ACCESS POLICY to APPSHR;

At this point only users who connect at the database level as APPS (that would be all forms based users) and APPSHR (our HR analysts) can see the restricted data. The APPS password in our PROD environment is known only to the DBAs. The APPSHR password is known only to the HR Business Analysts / HR Developers / DBAs. All other business analysts have access to another read-only account called APPSXXXX that is NOT exempt from the security policies. With that APPSXXXX account, the regular business analysts and developers can query the database directly for all but the restricted data and can access any data via the forms that their forms responsibilities allow.
When we clone an instance, we don’t have to do any scrambling. For all of our DEV, TEST and PSUP (Production Support) instances we merely have to change the APPS password to a commonly known password and issue

REVOKE EXEMPT ACCESS POLICY FROM APPS;

Now everyone can read and write data as APPS, but because APPS now is restricted by the policies we put in place, APPS can’t see the sensitive data. The only time this causes a problem is for the HR Business Analyst or HR Developers who need a non-PROD place to work issues or develop code. For them we created a special HR cloned instance with the same security setup as PROD but with the APPS password known to the HR Analysts and HR Developers.

This solution has worked out much better for us than the scrambling. Give it a shot and let me know how it goes for you in the feedback!