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.
- User Security Data
- Transaction 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.
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|
– Contingent Worker
– POIs with jobs
– POIs without jobs
· POI_TYPE + USINESS_UNIT
· POI_TYPE + COMPANY
|Job Openings||HRS_JOB_OPENING||· COMPANY
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.
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:
- 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:
|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.|
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|
|All data types of People||· JOB
|SJT_DEPT||Data type of Departments||· DEPT_TBL||DEPTID|
|HRS_SJT_JO||Data type of Job Openings||· HRS_JOB_OPENING
- 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_OPR_CLS||Data Permission lists granted to users||· PSOPRDEFN
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.