FDI Data Security: Implementation of Fusion HCM Person Security Profile Custom Criteria in FDI

The Oracle Fusion Data Intelligence (FDI) platform can translate the prebuilt Fusion Applications security to the analytics layer. However, in cases where customers implement customized security in Fusion Applications, a custom security setup is required in FDI to meet those specific security needs.

This article demonstrates a method to implement data security in FDI, where Fusion HCM person records are secured using a Person Security Profile with custom criteria or SQL query logic.

Use Case

In this example, consider the following SQL query criteria used to secure person records with a custom Person Security Profile, which allows a person to view data for executives and top management, but excludes their own record.

exists (
select ASG.PERSON_ID
     from PER_ALL_ASSIGNMENTS_M ASG, PER_PERSONS PN where PN.PERSON_ID = ASG.PERSON_ID
     and TRUNC(SYSDATE) between LEAST(TRUNC(SYSDATE),ASG.EFFECTIVE_START_DATE) and  ASG.EFFECTIVE_END_DATE
     and ASG.EFFECTIVE_LATEST_CHANGE='Y'
     and ASG.ASSIGNMENT_TYPE in ('E','C','N','P')
     and ASG.ASSIGNMENT_STATUS_TYPE in ('ACTIVE','SUSPENDED')
     and ASG.EMPLOYEE_CATEGORY in ('FR_03','FR_04','FR_05')
     and (PN.PERSON_ID <> (select NVL(HRC_SESSION_UTIL.GET_USER_PERSONID,-1) from DUAL))
     and ASG.PERSON_ID=&table_alias.person_id )

Challenges

The above security logic can be implemented by applying equivalent conditions on the respective semantic model tables or using a session variable as a data filter. However, it becomes very challenging when the custom criteria involves any of the following complexities:

• If the custom criteria are highly complex (e.g., involves multiple tables and intricate join conditions), it may not be feasible to translate the same logic to FDI.
• If the list of values exceeds 1000, it hits the IN clause limit of 1000 literals, which rules out the session variable approach.

Solution

This solution describes an alternative approach to applying data security by extending the semantic model with a security table or view object and constructing data filter logic using the extended entity columns.

Here are the high-level steps that need to be performed to address the above challenges:

1. Create an application role in FDI
2. Map the application role to the appropriate group
3. Create a view with custom criteria SQL security logic.
4. Extend the semantic layer with the security view object and create a column to use in data filters.
5. Add the data security step and publish the semantic model.
6. Test the security.

 1. Create an application role in FDI

Create the following application role in FDI with the role type set to “Data Role” to manage data access for logged-in users.

a. On the FDI Console page, click Security, and then Application Roles.

b. Click New Application Role.

c. Enter Application Role Name as “HCM Custom – Executive Data” and add a description.

d. Select Role Type, Manage row-level data access (Data Role),  and Click Save.

2. Map the application role to the appropriate group

 Map the HCM Custom – Executive Data application role to the HCM Custom – Executive Group group.

a. On the FDI Console page, click Security, and then Groups.

b. Search for “HCM Custom – Executive Group” and click Application Roles on the right-side. 

c. Click Add Mapping, and under Application Role, select HCM Custom – Executive Data.

d. Click OK.

Note: Here, it’s assumed that this group is loaded from HCM Cloud to FDI and tagged to a custom person security profile.

3. Create a view with custom criteria SQL logic

Create the following database view object with equivalent data security logic in the OAX_USER schema or a custom schema.

create or replace view X_HCM_EXECUTIVE_SECURITY_VW AS
select 
     T4466.PERSON_ID,
     T4466.WORKER_TYPE,
     T4466.WORKER_CATEGORY
from 
     OAX$OAC.DW_WRKFRC_ASG_F T4466 
where  
     T4466.EFFECTIVE_LATEST_CHANGE = 'Y'  
     and trunc(sysdate) between T4466.EFFECTIVE_START_DATE and T4466.EFFECTIVE_END_DATE
     and T4466.PER_SYSTEM_STATUS in ('ACTIVE', 'SUSPENDED') 
     and T4466.WORKER_TYPE in ('C', 'E', 'N', 'P')
     and T4466.WORKER_CATEGORY in ('FR_03', 'FR_04', 'FR_05');

grant select on X_HCM_EXECUTIVE_SECURITY_VW to OAX$OAC;

4. Extend the semantic layer with the security view object

a. On the FDI Console page, click Semantic Model Extension, and then User Extensions.

b. Click Create Sandbox to begin customizing the semantic model by creating a sandbox.

c. Click Perform Action, then select Manage Logical Star, then Edit Logical Star, and then Out of the box.

d. Select HCM – Workforce Core subject area and Fact – Assignment.

e. Identify the Dim – Worker object and right-click it to select Manage Extension.

f. Click Extend Dim under Manage Extension to add the security view object.

g. Provide the appropriate join conditions to the existing “Dim – Worker” dimension table.
Here’s the Person Identifier key column:

h. Click Finish.

i. Create a new derived column with following logic using Add Columns under Manage Extension.

j. Now add the new derived column to the subject area presentation layer.

k. Click Perform Action, then Manage Subject Areas, then Modify Subject Area, and then HCM – Workforce Core.

l. Continue by adding the Secured Person column to the existing Worker presentation folder.

m. Apply the sandbox changes and merge to the main sandbox.

5. Add data security step and publish semantic model

a. On the Semantic Model Extension page, click Security Configuration.

b. Click + Data Security Step.

c. In this step, choose the custom data application role you created earlier.

d. Construct the data filter expression below to retrieve only the person records that satisfy the SQL criteria, excluding the record of the logged-in person. This same filter expression should be applied to the Dim – Worker and Fact – Assignment logical tables.

e. Click Finish to publish the semantic model.

6. Test Security

a. Log in to FDI with a user belonging to the HCM Custom – Executive Group group.

b. Create an ad-hoc report using the HCM – Workforce Core subject area to validate if the report fetches person records where employee category is FR_03,FR_04 and FR_05.

The session query applies the following filters according to the security data filter logic (below is a portion of the SQL code from the FDI session log).

T231.PERSON_ID not in (-99999.0) and T1347.PERSON_ID not in (-99999.0)

and case when not T630164.PERSON_ID is null then ‘Y’ else ‘N’ end  = ‘Y’ 

Call to Action

For more information on FDI Security, see Manage Users, Groups, Application Roles, and Data Access.

Now that you’ve read this article, try it yourself and let us know your results in the Oracle Analytics Community, where you can also ask questions and post ideas. It’s free and easy to join!