Enhance data security by using VPD and EBS Profile Options to prevent viewing of sensitive data.  In this example we will look at blocking employee SSN or National Identifier, as they are called in EBS, in PER_ALL_PEOPLE_F .  As most people familiar with Oracle EBS know, there are a number of delivered views that have embedded security.  Take a look at the SQL behind the PER_PEOPLE_F view.  In the WHERE clause you will notice:

WHERE DECODE(HR_SECURITY.VIEW_ALL ,’Y’ , ‘TRUE’, HR_SECURITY.SHOW_PERSON (pap.person_id, pap.current_applicant_flag ,pap.current_employee_Flag, pap.current_npw_flag ,pap.employee_number, pap.applicant_number, pap.npw_number)) = ‘TRUE’ AND decode(hr_general.get_xbg_profile,’Y’,pap.business_group_id , hr_general.get_business_group_id) = pap.business_group_id;

Unfortunately, this only applies to VIEWS as the security flags are embedded in the view definition.

Challenge:
Prevent all users of the EBS system from seeing employee SSN from queries against EBS base tables.  This restriction should be controlled through EBS Responsibility security controls.

Solution:
Use VPD and EBS Profile Options to apply security setting by Responsibility

Approach

(note: design may not be the best, but it works):

1. Create a FUNCTION that will get the profile option value

CREATE OR REPLACE FUNCTION ssn_policy_rule RETURN VARCHAR2 AS
/**************************************************************
* FUNCTION: ssn_policy_rule                                                                    *
* DESCRIPTION: Get profile option SSN_SECURITY value                 *
**************************************************************/
—
rslt VARCHAR2(240) := ‘Y’;
  BEGIN
    BEGIN
      select decode(FND_PROFILE.VALUE(‘SSN_SECURITY’),’Y’,’N’,null) into rslt from dual ;
      RETURN rslt;
      EXCEPTION

        WHEN NO_DATA_FOUND THEN

        rslt := ‘Y’;

        RETURN rslt;

        WHEN OTHERS THEN
          rslt := ‘Y’;
          RETURN rslt;
    END;
END;

2. FUNCTION that will callthe policy rule

CREATE OR REPLACE FUNCTION ssn_policy_fn (object_schema VARCHAR2, object_name VARCHAR2) RETURN VARCHAR2 AS
/**********************************************************
* FUNCTION: ssn_policy_fn                                                                *
* RETURN: calls ssn_policy_rule function                                         *
**********************************************************/
—
pol VARCHAR2(240) ;
—
BEGIN
  pol := ‘ssn_policy_rule = ”N”’;
  RETURN pol;
END;

3. Turn on the VPD Policy.

BEGIN

  DBMS_RLS.ADD_POLICY (object_schema     => ‘HR’,

                                                      object_name       => ‘PER_ALL_PEOPLE_F’,

                                                      policy_name       => ‘coa_ssn_policy’,

                                                   function_schema   => ‘apps’,

                                                      policy_function   => ‘coa_ssn_policy_fn’,

                                                    sec_relevant_cols => ‘NATIONAL_IDENTIFIER’,

                                            sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);

END;

and in the event that you need to DROP the VPD Policy use this:

BEGIN

  DBMS_RLS.DROP_POLICY (object_schema     => ‘apps’,

                        object_name       => ‘PER_ALL_PEOPLE_F’,

                        policy_name       => ‘coa_ssn_policy’);

END;

The only thing left to do at this point is to create an EBS Profile Option:

Resp: Application Developer
Function: Profile
Name: SSN_SECURITY
Application: Application Object Library
User Profile Name: SSN_SECURITY
Description: SSN_SECURITY
Hierarchy Type: Security

 

 

 

 

 

You can test this in SQL Developer by setting your session context variables to emulate various EBS Responsibilities that you have applied to the Security Policy.  Here is the code for that:

— Purpose: To set the context in SQL Developer so you can view SSN

— Enter the responsibility and your Oracle ID

declare

x_resp_name       varchar2(80) := ‘US Super HRMS Manager’;

x_your_Oracle_id  varchar2(80) := ‘#####’;

x_user_id   number;

x_resp_id   number;

x_appl_id   number;

begin

  select responsibility_id, application_id into x_resp_id, x_appl_id

  from fnd_responsibility_vl

  where responsibility_name = x_resp_name;

 

  select user_id into x_user_id

  from fnd_user

  where user_name = x_your_Oracle_id;

 

  begin

    fnd_global.APPS_INITIALIZE(user_id=>      x_user_id,

                                                             resp_id=>      x_resp_id,

                                                  resp_appl_id=> x_appl_id );

    commit;

  end;

end;

A simple: SELECT * FROM per_all_people_f should be sufficient for testing this.  Now go into your EBS instance, and check your results.

There you have it.  Or so it seems.

There are a few considerations to take into account before implementing this.

1. Viewing the SSN is now controlled by Oracle EBS Responsibility
2. Any reports, forms, etc that use the PER_ALL_PEOPLE_F table will be impacted
3. Schedule Concurrent Request under responsibilities where the policy is applied with be impacted
4. Make sure you have an Organizational policy to support excluding data
5. Check out a few more comments by Steven Chan

Enjoy!