You are currently viewing Applying Fusion Application Data Security to BI Publisher Reports
Applying Fusion Application Data Security to BI Publisher Reports

In this article we will look into an interesting concept of applying data security to the BI Publisher Reports.

We all know that OTBI Analytics provides output based on the Data Security access, the user has. However the BI tables doesn’t automatically inherit the data security of the logged in user. We will have to explicitly join the Secured Views to implement this data security.

Here is the complete list of tables and security views available in fusion along with their corresponding Data Security Privilege which provides access to that security View and the Duty role which has that Data Security Privilege added.

Table NameSecured List ViewData Security PrivilegeRoles having privilege
PER_ALL_PEOPLE_FPER_PERSON_SECURED_LIST_VReport Person DataDocuments of Record Transaction Analysis duty role
Payroll Transaction Analysis duty role
Workforce Transaction Analysis duty role
PER_PERSONSPER_PUB_PERS_SECURED_LIST_VReport Person Deferred DataDocuments of Record Transaction Analysis duty role
Payroll Transaction Analysis duty role
Workforce Transaction Analysis duty role
PER_ALL_ASSIGNMENTS_MPER_ASSIGNMENT_SECURED_LIST_VReport Assignment DataHuman Resource Analyst job role
HR_ALL_ORGANIZATION_UNITS_FPER_DEPARTMENT_SECURED_LIST_VReport Department DataAbsence Management Transaction analysis duty role
Payroll Transaction Analysis duty role
Vacancy Transaction Analysis duty role
Workforce Transaction Analysis duty role
HR_ALL_ORGANIZATION_UNITS_FPER_LEGAL_EMP_SECURED_LIST_VReport Legal Employer DataPayroll Transaction Analysis duty role
HR_ALL_POSITIONS_FPER_POSITION_SECURED_LIST_VReport Position DataAbsence Management Transaction analysis duty role
Payroll Transaction Analysis duty role
Vacancy Transaction Analysis duty role
Workforce Transaction Analysis duty role
PER_LEGISLATIVE_DATA_GROUPSPER_LDG_SECURED_LIST_VReport Legislative Data Group DataPayroll Transaction Analysis duty role
PAY_ALL_PAYROLLS_FPER_PAYROLL_SECURED_LIST_VReport Payroll Definition DataPayroll Transaction Analysis duty role
CMP_SALARYCMP_SALARY_SECURED_LIST_VReport Salary DataCompensation Transaction Analysis duty role
PER_JOBS_FPER_JOB_SECURED_LIST_VReport HR Job DataAbsence Management Transaction analysis duty role
Payroll Transaction Analysis duty role
Vacancy Transaction Analysis duty role
Workforce Transaction Analysis duty role
PER_LOCATIONSPER_LOCATION_SECURED_LIST_VReport Location DataAbsence Management Transaction analysis duty role
Payroll Transaction Analysis duty role
Vacancy Transaction Analysis duty role
Workforce Transaction Analysis duty role
PER_GRADES_FPER_GRADE_SECURED_LIST_VReport Assignment Grade DataAbsence Management Transaction analysis duty role
Payroll Transaction Analysis duty role
Vacancy Transaction Analysis duty role
Workforce Transaction Analysis duty role
Table, Security View and Role Mapping

We need to add these security views to the main tables while building the query in order to apply the data security policies and return only the users which the logged in user has access to.

Table of Contents

Sample SQL using the Security Views:

select distinct PNI.NATIONAL_IDENTIFIER_NUMBER, 
PNI.NATIONAL_IDENTIFIER_TYPE,
PNI.person_id,
ppnf.DISPLAY_NAME,
papf.person_number
from 
PER_NATIONAL_IDENTIFIERS PNI, 
PER_PERSON_NAMES_F ppnf,
per_person_secured_list_v papf
WHERE PNI.NATIONAL_IDENTIFIER_TYPE = 'ORA_HRX_IN_PAN'
and papf.person_id = ppnf.person_id
and PNI.PERSON_ID = ppnf.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
and SYSDATE BETWEEN papf.EFFECTIVE_START_DATE(+) AND papf.EFFECTIVE_END_DATE(+)
and SYSDATE BETWEEN ppnf.EFFECTIVE_START_DATE(+) AND ppnf.EFFECTIVE_END_DATE(+)

The above query will only return the PAN data for the employees that the logged in user has access to. If the logged in user doesn’t have any data access, then he won’t get any data.

Apart from the security views, we have Personally identifiable information (PII) tables which are secured at database level using the virtual private database (VPD) policies. Only authorized users can report on data in PII tables. This restriction also applies to Oracle Business Intelligence Publisher reports. The data in PII tables is protected using data security privileges that are granted by means of duty roles in the usual way.

Tables Containing PII Information and the Data Security Privilege Mapping

TableData Security Privilege
PER_ADDRESSES_FReport Person Address
PER_CONTACT_RELSHIPS_FReport Person Contact
PER_DRIVERS_LICENSESReport Driver License
PER_EMAIL_ADDRESSESReport Person Email(Work Email is not protected)
PER_NATIONAL_IDENTIFIERSReport Person National Identifier
PER_PASSPORTSReport Person Passport
PER_PERSON_DLVRY_METHODSReport Person Communication Method
PER_PHONESReport Person Phone(Work Phone is not protected)
PER_VISAS_PERMITS_FReport Person Visa

All of these privileges are accessible using the Workforce Confidential Reporting duty role, which the Human Resource Analyst job role inherits.

if you don’t have the right job role, you might not be able to check the data from the backend tables as well.

Hope this information will be useful when you work on implementing security in BI Reports.