Peoplesoft Row Level Security Example

If an organization we are working with is using PeopleSoft as it's ERP solution thendepending upon what role we play we do online transactions through various components in PeopleSoft. For example, HR users usually work in Production environment hence use components under Workforce Administration, Organizational Development, Workforce Development, Benefits etc… in order to carry out their daily business.

Peoplesoft Row Level Security Configuration

Overview of Row Level Security. Controls data access by the institution/college, business unit, department, etc. An application user belongs to. This term could be used differently in. PeopleSoft determines Row Level Security by using settings associated with both the Primary and Row Security Permission Lists. Earlier versions only supported Department Tree based security. You can design special types of SQL views -security views- to control access to individual rows of data stored within your application database tables. Your PeopleSoft applications are delivered with built-in, row-level security functions, tailored to that specific application.

A Consultant (Technical or Functional) however will mostly works in Non-Production environment hence the components under the menus – Workforce Administration, Set Up HRMS, PeopleTools etc… are the ones where they visits most often to do transactions as required. A consultant at times needs to play various roles in order to expedite certain tasks part of their assignment. For example:

Resolving a production issue by simulating it into Non Prod environment
Validating a test scenario which is part of implementation
Fig-Gap analysis

Etc…
Please also see
Simplified Way to Provide a Page Access in PeopleSoft
Understanding Dynamic prompts in PeopleSoft
Implementing parallel processing using Application Engine in PeopleSoft
Adding and Maintaining Person Of Interest in PeopleSoft
PeopleSoft Set Control Field
Peoplesoft Row Level Security Search Records
SQL Query for Max Effective (MAX (EFFDT) dated row from JOB table
PeopleSoft Set Control Field Concept and Tableset Sharing
Understanding Future dated security in PeopleSoft
PeopleSoft 9.1 Person Model
Creating Query Report with PS Query in PeopleSoft
PeopleSoft HRMS Online Training

If we just forget about the role that one plays and list out few most common components a user deals withto carry out various transactions then the list will contain:

·JOB_DATA (Navigation: Main > Workforce Administration > Job Information > Job Data)

·JOB_DATA_CURRENT (Navigation: Main > Workforce Administration > Job Information > Current Job)

·ADD_PER_ORG_ASGN(Navigation: Main > Workforce Administration > Job Information > Add Additional Assignment)

·ADD_HOST_ASSIGN(Navigation: Main > Workforce Administration > Global Assignments > Track Assignment > Add a Host Assignment)

Transactions on Personal data

·PERSONAL_DATA (Navigation: Main > Workforce Administration >Personal Information > Modify a Person)

·DEPEND_BENEF (Navigation: Main > Workforce Administration >Personal Information > Personal Relationships >Dependent Information)

·EMERGENCY_CONTACT (Navigation: Main > Workforce Administration >Personal Information > Personal Relationships > Emergency Contact)

·DISABILITY(Navigation: Main > Workforce Administration >Personal Information > Disabilities)

Hold on.. Why am I explaining all this when our topic here is “Row Level Security Search Records in PeopleSoft”?

Well, because all the components mentioned above, use security views as their search record and that is to ensure that a particular user who is doing transactions through these components, can see only those employees that they are allowed to see.I would like to list down those security views along with the components where they are used as search record:

·Current Job (JOB_DATA_CURRENT ) - EMPLMT_SRCH_COR

·Add Additional Assignment (ADD_PER_ORG_ASGN ) - PERS_SRCH_GBL

·Add a Host Assignment (ADD_HOST_ASSIGN ) - PERS_SRCH_GBL

Components for Personal data transactions

·Dependent Information (DEPEND_BENEF) - PERS_SRCH_GBL

·Emergency Contact (EMERGENCY_CONTACT) - PERS_SRCH_ALL

Etc…

As you can see, there are various security views created and are used in these components. Based on the component requirement, some of these views search for the employee data rows by EMPLID and EMPL_RCD and others just by the EMPLID.

For example, The view EMPLMT_SRCH_GBL used in Job data component considers EMPL_RCD field while searching the data row when a user hits on the “Search” button in the component search page whereas the view PERS_SRCH_ALL used in Personal Data component doesn’t consider EMPL_RCD filed while searching the data rows.

One easy way to find out the search record for a component, Just open the menu definition under which the component exists, in the Application Designer and locate your component, then right click on it and open the “Menu Item Properties”. Check what the search record for the component is and whether it has been overridden.

So, when the so called “Row Level Security Search Records” come in picture and how they secure the data from a user (HR Admin, Consultant etc…). This is also called Row Level Security in PeopleSoft.

Let’s take any one of the component mentioned earlier. A new employee “ST01ST20” has been hired in the organization but when HR tries to open their job data through JOB_DATA component:

Oops..! looks like employee doesn’t exist.

Is that what it looks like?No, instead the HR user is not able to see the newly hired employee because they don't have access to them and this restricted access has been enforced with the use of the row level security view EMPLMT_SRCH_GBL as search record in this component.

Please also see
'Override Position Data' and 'Use Position Data' Option in Job Data Page
Hiring a Person in PeopleSoft
How to find the List of Users Assigned to a Role
How the Various Date Fields on Employment Data Page are Updated
Simplified Way to Provide a Page Access in PeopleSoft
How to Resolve a Row Level Security Issue in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
SQL Query to Find Direct Reports for a Manager in PeopleSoft
Understanding Component Interface in PeopleSoft
How the FTE is Calculated in PeopleSoft
Business Unit, Company and Regulatory Region in PeopleSoft

So let’s find out what exactly happens behind the scene and how the security search view works to ensure the unintended employee data shouldn’t be displayed to HR user.

In the search page, when we enter the EMPLID ‘ST01ST20’ and hit enter:

Hope everything is clear so far and if it is then let’s give data access to the HR user so that they can see the data rows of employee ST01ST20. Thereare various different ways such access can be given and for now we will just add business unit “AUS01” to the Permission list TEST_PERMSN (Navigation: Main > Set Up HRMS > Security > Core Row Level Security > Security By Permission list).

Note that, this access can also be given through “Security by Dept Tree” component which exists under the same menu. In this method, the HR users are given access to the intended department defined in a Department tree where a particular employee belongs.
After the access is given to HR user let’s see what happens:

Please also see
Simplified Way to Provide a Page Access in PeopleSoft
PeopleSoft Set Control Field
Adding and Maintaining Person Of Interest in PeopleSoft
Unable to See Future dated transactions in Job Data page in PeopleSoft
Hiring a Person in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
Understanding Component Interface in PeopleSoft
SQL Query to Find Direct Reports for a Manager in PeopleSoft
How to find the List of Users Assigned to a Role
How the FTE is Calculated in PeopleSoft
Understanding Future dated security in PeopleSoft
How to Resolve a Row Level Security Issue in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
'Override Position Data' and 'Use Position Data' Option in Job Data Page
How the Various Date Fields on Employment Data Page are Updated
Process Security in PeopleSoftMost people prefer using SQL queries if they need to find out what are all the users have access to a particular employee's data in PeopleSoft and that's because either they are extremely comfortable with SQL or they don't know how to do this through an inquiry page in PIA.

Let's say an HR user reported you an issue that they can't see one employee's data in Job Data page. I would say this is one of the most occurred issues a PeopleSoft security consultant deals with as part of their daily work.
Anyway, from further inquiry you came to know that the employee's data row wasn't future dated because if it was then there is a separate security setup has to be done in order to allow users to see future dated transaction data in Job Data page.
Please also see
Simplified Way to Provide a Page Access in PeopleSoft
Understanding Dynamic prompts in PeopleSoft

Peoplesoft Row Level Security Configuration

Implementing parallel processing using Application Engine in PeopleSoft
Adding and Maintaining Person Of Interest in PeopleSoft
PeopleSoft Set Control Field
Peoplesoft Row Level Security Search Records
SQL Query for Max Effective (MAX (EFFDT) dated row from JOB table
PeopleSoft Set Control Field Concept and Tableset Sharing
How to find the List of Users Assigned to a Role
PeopleSoft 9.1 Person Model
PeopleSoft HRMS Online Training

So what are we gonna do now ? well, there are ways to analyze and resolve the issue but I am gonna tell you few very simple steps to the same through PIA:

Step 1: Inquire the security data for the employee

Open the Security Data Inquiry page (Navigation: Main > Setup HRMS > Security > Core Row Level Security > Security Data Inquiry) and enter the employee ID for which the issue has been reported and then click on the button 'Show Security Definitions' to open the security configuration.

Here you will see all the Security Types which have access to the employee's data records. Note that if employee has more than one EMPL_RCD then it will show the security access for all of them.

Select all the rows and click on the button 'Show Permission List' to open all the permission lists those have access to the selected Security Type.

Select all the permission lists and then click on the button 'Show Users' which will finally open the list of users who have above permission lists assigned in their user profile respectively.

So what do we finally have with us ?

We have list of users who have access to the employee data for which the issue has been reported. Now, we have to find out whether in this list the HR user who reported the issue, exists or not. If they doesn't exist then it means we have to update the security setup for this HR user so that they get access to the employee's data.
Click on Find link and search the HR user id if it exists.
Please also see
'Override Position Data' and 'Use Position Data' Option in Job Data Page
Hiring a Person in PeopleSoft
How to find the List of Users Assigned to a Role
How the Various Date Fields on Employement Data Page are Updated
Hiring a Person in PeopleSoft
FTE For Multiple Jobs in PoeopleSoft
SQL Query for Max Effective (MAX (EFFDT) dated row from JOB table
SQL Query to find all the Direct Reports for a Manager
Understanding Future dated security in PeopleSoft
How the FTE is Calculated in PeopleSoft
Business Unit, Company and Regulatory Region in PeopleSoft

In case, the HR user exists in the list then it means that they have access to the employee's data and it is perhaps a cache issue which could be resolved if we just log out of the system, clear the browser cache and log in back again.
However, if the HR user is not there in the list then we have got to update the security configuration for the user in order to provide access to employee's data hence go to the step 2.

Step 2: Update the Row Level Security Configuration

Once we are certain that the HR user cannot see the employee's data in Job Data page because their row level security configuration is not updated properly then the only area we have to focus on is, how and where to update the row level security configuration. Please note that, the terms 'Row Level Security' and 'Data Security' both are same in this context.

I would like to underline this point that, the row level security configuration is assigned to a permission list and this permission list can be:

The 'Row Security Permission List' which exists on the 'General' tab of User Profile page (Navigation: Main > Setup HRMS > PeopleTools > Security > User Profiles > User Profile).
Any permission list added to a particular role which exists on the 'Roles' tab of User Profile page (Navigation: Main > Setup HRMS > PeopleTools > Security > User Profiles > User Profile).

In either case, the security configuration on the permission list is added/updated from below two places:

1. Security by DEPT tree (Navigation: Main > Setup HRMS > Security > Core Row Level Security > Security By Dept Tree)

This page uses Department Tree to enforce the row level security in PeopleSoft. for this, the department tree has to be maintained properly i.e, it should be refreshed frequently so that it does reflect the actual department hierarchy of the organization.

2. Security by permission list (Navigation: Main > Setup HRMS > Security > Core Row Level Security > Security By Permission List)

Security configuration in this page is done based on the Security Sets and their Security Types. In this page, we only setup what are all the Security Types under a particularity Security Set that a permission list will have access to. The further access to employee records by Security Types is defined in transaction SJT table SJT_PERSON.
Most organizations use this page to configure the data or row level security so I will take this as reference when explaining how to resolve the data security issue further.
Lets get back to the issue at hand

By the end of Step 2 we would be certain that the HR user doesn't have access to the employee' data and its not a cache issue.So, how to resolve this now ? Let's go to Step 3.

Step 3: Assign the data/row security to the HR user

We got to be careful here while doing any changes. You must follow below steps:

Find if there is any existing relevant role which has access to that employee and can be assigned to the HR user. If there is any such role then before assigning it to the HR user, make sure that the role doesn't have access to unintended data which HR user will also get access to if assigned. If everything looks green, assign the role to HR user.
If there is no such role, then check if any Permission List exist which has the same access. Also, make sure it doesn't have access to unintended data just like the way we analysed for existing role in above step. If all OK then find out an existing role to which this permission list can be assigned. If such role is found then assign the permission list to the role and then assign the role to HR user. Again, before assigning the role to the user, make sure it doesn't impact the other users access for which you first need to see what are all the users this role is tagged to -
How to find the List of Users Assigned to a Role
If there is no such permission list also, then as the last option we have to create a new permission list, then assign it to either existing or a new role then finally assign this role to the HR user.

All done, check if HR user got the required access.

However, I would like to reiterate few things here - Before making any changes in the security setup (As explained in the above steps), you must keep in mind that the changes shouldn't impact the existing data accesses i.e the data/row security access defined for other users shouldn't be impacted.

Please also see
Simplified Way to Provide a Page Access in PeopleSoft
PeopleSoft Set Control Field

Peoplesoft row level security example for kids

Adding and Maintaining Person Of Interest in PeopleSoft
Unable to See Future dated transactions in Job Data page in PeopleSoft
Hiring a Person in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
Understanding Component Interface in PeopleSoft
SQL Query to find all the Direct Reports for a Manager
How to find the List of Users Assigned to a Role
How the FTE is Calculated in PeopleSoft
Understanding Future dated security in PeopleSoft
FTE For Multiple Jobs in PoeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
'Override Position Data' and 'Use Position Data' Option in Job Data Page
How the Various Date Fields on Employment Data Page are Updated