VI. 1. Security Join Tables

Security Join tables (SJT) combine the information from the user’s security access and transactional security data for the sensitive data to be secured and make them available for the component search to present the secured data filtered by the user’s access. The Security Join Tables allows the application to retrieve the information from pre-built tables with all appropriate information instead of having to query multiple tables at run time, thus improving the application’s performance.

The data in Security Join Tables is built by joining two different security data sets that are required by the system to determine the right set of rows to be accessible by the user.

  1. User Security Data
  2. Transaction Security Data

 

A.     User Security Data

User security data is the row security access granted to the user. A user can be granted row security access either via ‘Row Security Permission List’ assigned directly to the user profile or through the row security permission lists that are assigned to the roles which are in-turn granted to the user profile.

** Refer to ‘Security by department tree’ and ‘Security by Permission List’ sections for more information on granting row security access to permission lists.

The data permission list assigned directly to the user profile is referred to as ROWSECCLASS and data permission list assigned to the user via roles is referred to as CLASSID. ROWSECCLASS can only have security based upon department tree and CLASSIDs can only have row security not based on department tree. Both these types of permission lists are created using the same PeopleTools pages as regular function security permission list. Once created in PeopleTools, they are granted the appropriate row security access through the Setup HRMS pages.

6.2

Figure VI‑2 Data Security – User Security Data

B.     Transactional Security Data

In any given table with sensitive data, there could potentially be several fields not all of which can be used as criteria for security access. Transactional Security data is the data in the fields of the security record that are used are matching criteria against the user security data for presenting the appropriate rows on the search pages. In the example above (Figure VI-1 Data Security in PeopleSoft HCM) the values in the “Dept” field were used as the transactional security data. Only the rows of data whose “Dept” field value matched the “Dept” values in the user security data were presented to the user on the search component.

In PeopleSoft HCM, the transactional security fields available for securing data is determined by the data type being secured. The table below shows the different data types and the fields available for use as transactional security data:

Data Type Primary Record Transactional security fields
Departments DEPT_TBL ·         SETID

·         DEPTID

People
– Employees
– Contingent Worker
– POIs with jobs
JOB ·         COMPANY

·         BUSINESS_UNIT

·         DEPTID

·         LOCATION

·         PAYGROUP

·         PER_ORG

People
– POIs without jobs
PER_POI_SCRTY ·         POI_TYPE

·         POI_TYPE + USINESS_UNIT

·         POI_TYPE + COMPANY

Job Openings HRS_JOB_OPENING ·         COMPANY

·         BUSINESS_UNIT

·         DEPTID

·         LOCATION

 

When accessing data from the components with secured data, the search views use the information about the data type in the component to determine the security fields that are used to match with the user’s security access depending upon the security type enabled in the system.

C.      Security Join Tables

When a user accesses a component with a security view, the system uses the Security Join Tables to determine which rows to return in the search results. Security join tables contain one row per data which uniquely identifies the key fields for each security access type enabled in the system. This allows the system to query a pre-built data set for determining security access instead of having to query multiple tables to determine, first the user security data and then transactional security data.

As with the security data types, there are two different types for SJT tables that store the unique rows for each key field, access type combination:

  1. Transaction Security Join Tables: Transaction Security Join tables contain data required to security transactional security data, one unique row per key field combination. There are four different Transaction SJT tables (SJT_PERSON, SJT_PERSON_USF, SJT_DEPT and HRS_SJT_JO) which are appropriately used depending upon the data type being secured.

All Transactional SJTs have the following key fields in common:

KEY FIELD DESCRIPTION
SCRTY_ACCESS_CD  Indicates which field is used in transaction security data
SCRTY_KEY1 Key Security fields that uniquely identify transaction data. Not all three KEY fields are always required to uniquely identify the data.
SCRTY_KEY2
SCRTY_KEY3

 

In addition to these key fields, each transactional SJT has additional key fields, specific to the data type they are used to secure:

Transactional SJT Description Primary Table/s Key Field/s
SJT_PERSON/

SJT_PERSON_USF

All data types of People ·         JOB

·         PER_ORG_ASSIGN

·         PER_POI_SCRTY

EMPLID
SJT_DEPT Data type of Departments ·         DEPT_TBL DEPTID
HRS_SJT_JO Data type of Job Openings ·         HRS_JOB_OPENING

·         HRS_JO_RTEAM_VW

HRS_JOB_OPENING_ID

 

  1. User Security Join Tables: User Security Join tables contain information about the users’ data security access, one unique row per key field combination. There are two User SJTs (SJT_CLASS_ALL and SJT_OPR_CLS) that store the security information:
User SJT Description Primary Table/s Key Field/s
SJT_CLASS_ALL Data security access granted to permission lists ·  SCRTY_TBL_DEPT

·   SJT_CLASS

CLASSID

SCRTY_SET_CD

SCRTY_TYPE_CD

SCRTY_KEY1

SCRTY_KEY2

SCRTY_KEY3

SJT_OPR_CLS Data Permission lists granted to users ·         PSOPRDEFN

·         PSROLECLASS

·         PSROLEUSER

OPRID

CLASSID

D.     Refreshing SJT Tables

The data in the various Security Join Tables has to be kept in sync with the transactional data in the application pages for the Transactional SJT tables as well as any data security access changes for User security SJT tables. PeopleSoft uses real-time PeopleCode updates to automatically update certain SJT tables while requiring certain refresh processes to be run to update other SJT tables with the most current information.

The table shows the various methods PeopleSoft uses to synchronize the data in the SJT tables:

Security Join Table Method of Update When?
All Transactional SJTs Real-time online PeopleCode updates the transactional SJTs when changes are made on application pages used to maintain transaction data (ex: Job, Person, Department, Job Openings Real-time automatic
  Refresh Trans. SJT Tables process

Setup HRMS à Security > Core Row Level Security > Refresh Trans. SJT Tables

As required to capture changes not processed through PeopleCode update
  Nightly SJT Refresh Process

Setup HRMS à Security > Core Row Level Security > Nightly SJT Refresh Process

Nightly to capture effective dated rows that are now current
SJT_CLASS_ALL Real-time online PeopleCode updates the SJT_CLASS_ALL table when changes are saved on Security by Permission List page Real-time automatic
  Refresh SJT_CLASS_ALL process

Setup HRMS > Security > Core Row Level Security > Refresh SJT_CLASS_ALL

When a Security Type is modified or added, when a Security Tree is modified or added, or when a ROWSECCLASS is modified or added
SJT_OPR_CLS Refresh SJT_OPR_CLS process

Setup HRMS > Security > Core Row Level Security > Refresh SJT_OPR_CLS

Row Security Permission list is added to or deleted from a user profile.

Data permission list is added to or deleted from a role.

Role with data permission is added to or removed from a user profile.

New user profile, role or permission list is created with data permission access.