HCM Security: Security Join Tables

PeopleSoft stores security data in transaction and user security join tables (SJTs).

When you access a transaction component with a security search view, the security view references the transaction and user security join tables to determine which rows of data the user can access.

Transaction Security Join Tables

Each transaction security join table stores the transaction data required to secure each row of data. The security join tables store one row of data for each unique combination of key fields.

There are four transaction security join tables:

1) SJT_PERSON and SJT_PERSON_USF: Contains transaction data for the people (employees, contingent workers, POIs with jobs, and POIs without jobs) in your HCM system. SJT_PERSON is used by customers using the core job data components and SJT_PERSON_USF is used by customers using the USF job data components.

2) SJT_DEPT: Contains the transaction data for the HCM departments.

3) HRS_SJT_JO: Contains the transaction data for the job openings in your system.

Transaction SJT	Stores Data From	Key Fields
SJT_PERSON SJT_PERSON_USF	JOB JOB_JR PER_ORG_ASGN PER_POI_SCRTY	SCRTY_TYPE_CD SCRTY_KEY1 SCRTY_KEY2 SCRTY_KEY3 EMPLID
SJT_DEPT	DEPT_TBL	SCRTY_TYPE_CD SCRTY_KEY1 SCRTY_KEY2 SCRTY_KEY3 SETID DEPTID
HRS_SJT_JO	HRS_JOB_OPENING HRS_JO_RTEAM_VW	SCRTY_TYPE_CD SCRTY_KEY1 SCRTY_KEY2 SCRTY_KEY3 HRS_JOB_OPENING_ID

Loading Transaction SJT Tables
 
Note: SCRTY_TYPE_CD (security access type code) indicate which field is used for transaction security data. When you first enable a security access type, load the transaction data into transaction security join tables using the SJT Refresh process.

User Security Join Tables

The user security join tables store the user security data required to determine users' data permission. The security join tables store one row of data for each unique combination of key fields.

There are two user security join tables:

1) SJT_CLASS_ALL: Contains the data permission information for all the permission lists that are given data access on the Security by Dept Tree page or Security by Permission List page.

2) SJT_OPR_CLS: Contains the user IDs of people with data permission and the permission lists with data permission that are assigned to them.

User Security Join Table	Stores Data From	Key Fields
SJT_CLASS_ALL	SCRTY_TBL_DEPT SJT_CLASS	CLASSID SCRTY_SET_CD SCRTY_TYPE_CD SCRTY_KEY1 SCRTY_KEY2 SCRTY_KEY3
SJT_OPR_CLS	PSOPRDEFN PSROLEUSER PSROLECLASS	OPRID CLASSID

Note: SCRTY_SET_CD (security set code) is a set of data secured in HCM. For example, PPLJOB is the security set for the data of people with jobs and DEPT is the security set for the department data. Each security set has security access types.

Loading SJT_CLASS_ALL Table

Loading SJT_ OPR_CLS Table

Note: You can enable the USER_PROFILE message and the local subscription HCM_Refresh_SJT_OPR_CLS and the ROLE_MAINT message and the local subscription HCM_Role_Refresh_SJT_OPR_CLS to automatically update SJT_OPR_CLS. 
PeopleSoft does not deliver the system with these messages enabled.

The Refresh SJT_OPR_CLS process loads the OPRID and ROWSECCLASS values from PSOPRDEFN directly into SJT_OPR_CLS, saving the row security permission list as CLASSID.
To establish the relationship between user profiles and role-based permission lists, the process first loads the OPRID and ROLENAME from PSROLEUSER and the ROLENAME and CLASSID from PSROLECLASS into a temporary table (ROLEUSER_ROLECLASS) and then loads the OPRID and CLASSID, and the relationship between them, into SJT_OPR_CLS.

Note: The SEC_RSC_FLG field indicates if the CLASSID was originally a ROWSECCLASS permission list by flagging it with a value of Y.