VI. 2. Designing Row Level Security in HCM

In the previous chapter, you learned how the data security is enforced in PeopleSoft HCM application and the Security Join Tables that are used to store required security information about the transactional data and user’s access to these data elements. In this chapter, you will look at the steps of implementing the data security in HCM.

Steps to Implementing Data Security in PeopleSoft HCM are:

  1. Review Security Sets
  2. Enable Security Types
  3. Setup Department Security Tree
  4. Create Permission lists (ROWSECCLASS and CLASSID)
  5. Setup Security by Department Tree
  6. Setup Security by Permission List
  7. Add CLASSIDs to Roles
  8. Grant data permissions access to Users
    1. Add ROWSECCLASS to the Users’ Profile
    2. Add Roles with Data Permissions to Users’ role access
  9. Refresh and schedule appropriate SJT Refresh Processes


A.     Security Sets (PS_ SCRTY_SET_TBL)

Security set corresponds to the different Data Types discussed in the previous chapter. Different transactional data is used to secure the information in the security sets as the data is comes from different primary tables. Different Security Join Tables are used for transaction security data depending upon the data type for the security set.

Navigation: Setup HRMS à Security à Core Row Level Security à Security Sets

The Security Sets page is mostly view only and provides information about the Security Join Table and other information helpful for designing the data security for the implementation. The Security access type grid shows the access types enabled for the security set (done in the next step).

Use the scroll buttons on the top to navigate to the other security sets.


Figure VI‑3 Data Security – Security Sets – Security Set Table

The ‘Security Update Groups’ tab shows the list of options that are available to select when running the Refresh SJT processes. The screen shot below shows that when running the Refresh SJT process for DEPT security set, the user can select to refresh by a specific security type or department in addition to be able to refresh the entire table.

Figure VI‑4 Data Security – Security Sets – Security Update Groups

PeopleSoft delivers five security sets, each corresponding to a data type being secured:

Security Set Data Type Secured SJT Table Used
PPLJOB People with Jobs SJT_PERSON
PPLUSF People with Jobs – US Federal SJT_PERSON_USF
PPLPOI People without Jobs SJT_PERSON
DEPT Departments SJT_DEPT


** Though PeopleSoft allows addition or deletion of Security sets, it is not recommended to make changes to the security sets information.

B.     Security Access Types (PS_ SCRTY_TYPE_TBL)


Security Access types define how the data in the different Security sets are secured. PeopleSoft delivers several different access types, as appropriate, for each security set that define how the data will be secured. Users can enable the access types that are appropriate for their data security implementation or add new data types if required.

Navigation: Setup HRMS à Security à Core Row Level Security à Security Access Types

Figure VI‑5 Data Security – Security Access Types

The Security type page is mostly view only and provides information about the security set that is being secured, the primary transaction table where data is maintained, the name and key structure for the SJT table. In addition to this information, the security type page has three check box options:

Enabled: Select this check box to enable the security access type for the security set type.

Include Future Dates: Select this check box if you want the SJT tables to include the future dated rows to be accessible for the users.

Use Dept Sec Tree: Select this check box if you want the security administrators to be able to grant access to the security set using department tree.

Security Type SQL: This tab shows the SQL condition statements used by the system when determining user access to the data type in the security set.

** User Dept Sec Tree is always selected for access types using department tree. This check box is not available for certain security types that do not have department information in the transaction data.

** For most implementations, it is not recommended to make any changes to the SQLs in the Security Type SQL tab. If absolutely necessary, only technical users with expert knowledge in SQL as well as PeopleSoft row level security should be granted this access.

When multiple access types are enabled, the application grants the cumulative maximum of the data permissions granted to the user through ROWSECCLASS and through CLASSID permission lists granted through roles. For this reason, it is important to enable only appropriate access types while designing the data security model. Also, each enabled access type will result in multiple rows of data per transaction data, one each for access type enabled, in the Security Join Tables, affecting the system performance.

Below is the Security Access types delivered by PeopleSoft. You should choose the ones relevant to your data security design model, and enable only those required:

Security Type Cd Description Security Set Transaction Table
021 Departments by Tree DEPT DEPT_TBL
022 Departments – non Tree DEPT DEPT_TBL
023 Departments by Setid DEPT DEPT_TBL
001 Job Department Tree PPLJOB JOB
002 Job Location PPLJOB JOB
003 Job Business Unit PPLJOB JOB
004 Job Company PPLJOB JOB
005 Job Reg Region PPLJOB JOB
014 Job Salary Grade PPLJOB JOB
015 Person Organization PPLJOB PER_ORG_ASGN
025 Job – Deptid – non Tree PPLJOB JOB
032 Job Company/Paygroup PPLJOB JOB
009 Person of Interest PPLPOI PER_POI_TYPE
016 US Federal Department Tree PPLUSF GVT_JOB
017 US Federal Location PPLUSF GVT_JOB
018 US Federal Company PPLUSF GVT_JOB
019 US Federal Business Unit PPLUSF GVT_JOB
020 US Federal Salary Grade PPLUSF GVT_JOB
031 Recruiting Team RSOPN HRS_JO_RTEAM_VW
034 Template  Category TBHTMPL HR_TBH_TMPL_CFG
035 Person Organisation TBHTMPL HR_TBH_TMPL_CFG


C.      Setup Department Security Tree

Department Security is a depiction of organizational department hierarchy in a tree format. This allows the security administrators to grant access to multiple departments by selecting the hierarchical parent node on the department tree instead of having to include each individual department. Any particular department that is under the parent node that should not be included in the access can be specifically excluded from the permission list’s access.

** The department security tree does not have completely match the organization’s reporting hierarchy but it is recommended to be as close to reporting hierarchy as possible for ease of administration.

The permission lists using the department tree for data security access can be only used as ROWSECCLASS assigned directly on the user profile. The same permission list can be used to grant other non-tree based access and assigned as CLASSIDs through roles but the tree based access only works when granted as ROWSECCLASS.

** If the organization uses multiple SETIDs, separate Department Security trees have to be created to hold the department hierarchy for each SETID.

Figure VI‑6 Data Security – Tree Manager

The Department Security tree is initially created either manually or automatically uploaded from external files. All nodes and levels in the department security tree correspond to a department ID in the departments table. In PeopleSoft, all organizational entities from the lowest individual department to a high level division comprising of several departments are all stored as departments with unique ID.

  • Creating Department Security tree manually:

Navigation: Tree Manager à Tree Manager

  1. Ensure all departments and roll-up divisions are entered in the departments table.
  2. Using PeopleSoft Tree Manager, create a new tree and add individual departments to the tree.
  3. One highest level node is created for holding the company’s department ID under which all other divisions are added.
  4. Use the hierarchy in tree manager to add the sub-divisions as child levels to the parent division level. Individual departments are added as nodes under the appropriate level.
  5. Once all departments are added in appropriate heirarchial order, save the tree as DEPT_SECURITY. Setup the following properties on the tree definition and save the tree:


Field Value
Effective Date The date when the new department tree becomes effective
Status Active
Description Descriptive name for the tree
Category Select tree category from prompt button. Typically HR
Use of Levels Select how strictly to enforce hierarchial structure when adding departments or making changes:

·         Strictly enforce: Only Level 2 depts can report to Level 1 dept. Level 3 depts can only report to Level 2 depts

·         Loosely enforce: Combination of Level 2 or level 3 (or lower) can report to level 1 dept.

·         Not enforced: Reporting heirarchy is flat


** The Department security tree should always be saved as DEPT_SECURITY (one each for the different SETID, if multiple SETIDs are used).

** Structure ID for Department security tree should always be DEPARTMENT.

** Refer to PeopleTools PeopleSoft Tree Manager PeopleBook for more details on creating and  modifying Trees in PeopleSoft.

  • Creating Department Security tree automatically:

PeopleSoft provides a way to import the department information directly into staging tables and creating the department security tree automatically from the data imported into the staging tables while also updating the departments tables with the information to keep it in sync with the tree structure.


The steps to create department security tree automatically from external data are:

  1. Create a spreadsheet or data file with information about all deparments including the reports_to hierarchy, status and effective date information.
  2. Import the data file (using any approved data import tool) into R_PER507 temporary table.
  3. Run PER507 SQR process: This updates the R_PER507 table with sturctured Organization code for each department based on the reports_to hierarchy, taking the status and effective dates into consideration.
  4. Run PER508 SQR process: Creates the department security tree with effective date as of latest effective date of the departments in R_PER507 temporary table.
  5. Run PER509 SQR process: Enters the department data into the departments table.
  6. Run PTUGAPTR SQR process: Re-numbers the tree nodes and inserts unused gaps in numbers to system use during tree modifications later.
  7. Run PER506 SQR: Audits the tree structure against department table to identify inconsistencies between them. Any entries need to be corrected manually.

** All processes listed above are run only once when creating the department security tree except PTUGAPTR process which is run periodically to re-number the nodes and insert gaps if prompted by errors when updating the department security tree and PER506 to periodically audit the tree structure for inconsistencies.


D.     Create Permission lists (ROWSECCLASS and CLASSID)

** Refer to Maintain Permission Lists section in this book.


E.      Security by Department Tree (PS_SCRTY_TBL_DEPT):

Navigation: Setup HRMS à Security à Core Row Level Security à Security by Dept Tree

  1. Create the Permission list in PeopleTools permission list page.
  2. Navigate to the Security by Dept Tree page in Setup HRMS menu and search for the permission list to grant department access to.
  3. Enter the current date (or future date to use future dated department tree(s)) in the ‘Refresh Tree Effdts by’ field and click ‘Refresh Tree Effective Dates’ button.
  4. In the ‘Define Security Profile’ grid, Select the SETID and DEPTIDs for the departments which should be allowed for this permission list. The ‘Access Code’ is set to ‘Read/Write’ by default.
  5. The ‘Effective date of tree’ is automatically populated from the DEPT_SECURITY table for the SETID used.
  6. If the added department is the hierarchial parent node, all sub-divisions and departments under this node in the DEPT_SECURITY tree are allowed for this permission list.
  7. If a particular sub-division under the parent division granted to the permission list should be disallowed for this permission list, add a new row for that sub-division and change the ‘Access Code’ to ‘No Access’.

Figure VI‑7 Data Security – Security by Dept Tree

** The access granted to the data permission lists through department security tree can only be assigned to the users through the ROWSECCLASS on User Profile. Users will NOT inherit this access even if the permission list is a part of the role assigned to the user.


F.      Security by Permission List (PS_SJT_CLASS):

Navigation: Setup HRMS à Security à Core Row Level Security à Security by Permission List

  1. Create the Permission list in PeopleTools permission list page.
  2. Navigate to the Security by Permision List page in Setup HRMS menu and search for the permission list to grant department access to.
  3. Enter the ‘Security Set’ value for the security set for the access type to be granted to the permission list.
  4. In the Security Type grid, select the security access type from the prompt button. Only the access types enabled in the Access Types page will be available for selection. Access type is populated in the field.
  5. Depending upon the access type selected, the KEY FIELDS used the appropriate SJT table appear in the grid. Select the appropriate value in each of the Key Field available.
  6. Use + button in Security type to add more rows for the same Security Set.
  7. If more than one Security Set is to be granted to the permission list, use the + button on the main grid (or the scroll buttons to see already granted) to add permissions for a different security set.


Figure VI‑8 Data Security – Security by Permission LIst

G.     Add CLASSIDs to Roles


** Refer to Roles –Permission Lists section in this book.


H.     Grant data permissions access to Users


** Refer to User Profile – General Information section.


I.        Refresh and schedule appropriate SJT Refresh Processes

** Refer to Refreshing SJT Tables section for more information on which SJT Refresh Processes need to be run for each of type data permission security change.