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 Name Secured List View Data Security Privilege Roles having privilege PER_ALL_PEOPLE_F PER_PERSON_SECURED_LIST_V Report Person Data Documents of Record Transaction Analysis duty role
Payroll Transaction Analysis duty role
Workforce Transaction Analysis duty rolePER_PERSONS PER_PUB_PERS_SECURED_LIST_V Report Person Deferred Data Documents of Record Transaction Analysis duty role
Payroll Transaction Analysis duty role
Workforce Transaction Analysis duty rolePER_ALL_ASSIGNMENTS_M PER_ASSIGNMENT_SECURED_LIST_V Report Assignment Data Human Resource Analyst job role HR_ALL_ORGANIZATION_UNITS_F PER_DEPARTMENT_SECURED_LIST_V Report Department Data Absence Management Transaction analysis duty role
Payroll Transaction Analysis duty role
Vacancy Transaction Analysis duty role
Workforce Transaction Analysis duty roleHR_ALL_ORGANIZATION_UNITS_F PER_LEGAL_EMP_SECURED_LIST_V Report Legal Employer Data Payroll Transaction Analysis duty role HR_ALL_POSITIONS_F PER_POSITION_SECURED_LIST_V Report Position Data Absence Management Transaction analysis duty role
Payroll Transaction Analysis duty role
Vacancy Transaction Analysis duty role
Workforce Transaction Analysis duty rolePER_LEGISLATIVE_DATA_GROUPS PER_LDG_SECURED_LIST_V Report Legislative Data Group Data Payroll Transaction Analysis duty role PAY_ALL_PAYROLLS_F PER_PAYROLL_SECURED_LIST_V Report Payroll Definition Data Payroll Transaction Analysis duty role CMP_SALARY CMP_SALARY_SECURED_LIST_V Report Salary Data Compensation Transaction Analysis duty role PER_JOBS_F PER_JOB_SECURED_LIST_V Report HR Job Data Absence Management Transaction analysis duty role
Payroll Transaction Analysis duty role
Vacancy Transaction Analysis duty role
Workforce Transaction Analysis duty rolePER_LOCATIONS PER_LOCATION_SECURED_LIST_V Report Location Data Absence Management Transaction analysis duty role
Payroll Transaction Analysis duty role
Vacancy Transaction Analysis duty role
Workforce Transaction Analysis duty rolePER_GRADES_F PER_GRADE_SECURED_LIST_V Report Assignment Grade Data Absence Management Transaction analysis duty role
Payroll Transaction Analysis duty role
Vacancy Transaction Analysis duty role
Workforce Transaction Analysis duty role
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
Table | Data Security Privilege |
---|---|
PER_ADDRESSES_F | Report Person Address |
PER_CONTACT_RELSHIPS_F | Report Person Contact |
PER_DRIVERS_LICENSES | Report Driver License |
PER_EMAIL_ADDRESSES | Report Person Email(Work Email is not protected) |
PER_NATIONAL_IDENTIFIERS | Report Person National Identifier |
PER_PASSPORTS | Report Person Passport |
PER_PERSON_DLVRY_METHODS | Report Person Communication Method |
PER_PHONES | Report Person Phone(Work Phone is not protected) |
PER_VISAS_PERMITS_F | Report 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.