Row Level Security

Oracle delivers a myriad of row level security options for their PeopleSoft HCM Product.  But recently we came across a scenario that the product did not have a delivered configuration that meet our need.  We needed to secure our Job Data for our regional HR users that would allow them to only see people within there region, but not in there own department.  Here in lies the issue, we have departments that cross regions and if we granted them access to these departments via the department security tree (PPLJOB/001), then they would have access to a bunch of people they should not get to see.  We tried then to use Job Location (PPLJOB/001), this eliminated the people outside their region, but it prevented us from securing their own department.  What we need was Job Department & Location row level security.

So we added a new Security Access Type (PPLJOB/041)-Job-Deptid non Tree-Loc

Security Type SQL tab displays all the sql used by the SJT process to build the row level security.  For the most part the default SQL was all I needed.  However, I did not need to select from anything other than the Job record so I modified the following SQL Objects that were built.

SEC_041_FLDVAL

, JOB.EMPLID , JOB.POI_TYPE , JOB.PER_ORG , JOB.EMPL_RCD , JOB.BUSINESS_UNIT , JOB.SETID_DEPT , JOB.DEPTID , JOB.SETID_LOCATION , JOB.LOCATION , JOB.COMPANY , JOB.REG_REGION , JOB.APPT_TYPE , JOB.HR_STATUS , JOB.EMPL_STATUS , ' ', JOB.REPORTS_TO , JOB.SUPERVISOR_ID , ' ' , ' ', JOB.SETID_JOBCODE , JOB.JOBCODE ,JOB.MAIN_APPT_NUM_JPN, 0, ' ', JOB.PAY_SYSTEM_FLG, JOB.GP_PAYGROUP, JOB.PAYGROUP, ' ',' ',' ',JOB.EFFDT, JOB.EFFSEQ

SEC_041_FROM

PS_JOB

SEC_041_WHERE

JOB.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE JOB.EMPLID = JOB2.EMPLID AND JOB.EMPL_RCD = JOB2.EMPL_RCD AND JOB2.EFFDT <= %CurrentDateIn) AND JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB.EMPLID = JOB3.EMPLID AND JOB.EMPL_RCD = JOB3.EMPL_RCD AND JOB.EFFDT = JOB3.EFFDT)


SEC_041_FUTWHERE

PA.EMPLID = JOB.EMPLID AND PA.EMPL_RCD = JOB.EMPL_RCD AND JOB.EFFDT > %CurrentDateIn AND JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB.EMPLID = JOB3.EMPLID AND JOB.EMPL_RCD = JOB3.EMPL_RCD AND JOB.EFFDT = JOB3.EFFDT)

Now that our Security Access Type is built all we need to do in Navigate to the Security by Permission List Component and configure our Permission List's Access then run the Refresh Trans. SJT table and enjoy our new security access type.

For my next post I plan to demonstrate how to automate the assignment of row level security for this new access type.