By | 04 October 2012

Data Security and Self Service Search Records

Data security should be your top priority when coding any PeopleSoft component. One of the key ways to lock down data is the “Search Record” on the component. A search record controls what data a user can see in the detail component and pages.

  • You may want to restrict viewing of data to a subset of information for the user (Row Level Security).
  • You may want to restrict viewing of data to the user’s employee ID (Employee Self-Service).
  • You may want to restrict viewing of data to the user’s own direct and indirect reports (Manager Self-Service).

We will cover how secure a component for Employee Self-Service using search records. The exact same method applies to manager self-service as well.

Option 1 – OPRID in Search Record

The best way to secure data to a self-service component is to include OPRID as a key in the search record but NOT a “search key”. This assumes the user’s OPRID is tied to an EMPLID in the system (PSOPRDEFN.EMPLID). The search record is a join between PSOPRDEFN with whatever tables will list the data they should see in the component. Let’s look at an example search record from Campus Solutions: LS_SS_PERS_SRCH.

Field Key (Y/N) Search Key (Y/N) List Box Items
EMPLID Y Y Y
OPRID Y N N
NAME_DISPLAY N N N

The SQL behind the LS_SS_PERS_SRCH search view in the current release I have in front of me is:

SELECT P.EMPLID 
 , O.OPRID 
 , N.NAME_DISPLAY 
FROM PS_SCC_PERS_SA_VW P 
  , PSOPRDEFN O 
  , PS_PERSON_NAME N 
WHERE O.EMPLID = P.EMPLID 
   AND O.EMPLID = N.EMPLID

There is some magic happening here because OPRID is on the search record defined as a Key (not a search key). OPRID is a “magic field” in PeopleTools. The component processor will always append in the current user’s OPRID into the where clause. This restricts the current user to only seeing EMPLID data for the EMPLID attached to the current users OPRID.

When the component processor runs it will automatically append in this where clause WHERE OPRID = 'JDOE', if user JDOE were logged in. The user never has a chance to insert another OPRID. If you were to accidentally make OPRID a “Search Key” then the behavior would be much different. For example, take a look at the search record for component USERMAINT which has a search record of PSOPRDEFN_SRCH. The record PSOPRDEFN_SRCH has OPRID defined as a search key. In this component you can choose the OPRID you want to look at in the component. This is NOT what you want for a self-service page.

If you choose this method then only the OPRID tied to an EMPLID can view the data in that Menu and component. If you need the same component visible to other non-self-service users, then one technique is to put the same component on a different menu. Then apply a search record override at the menu level. This will allow the same component to be used by different populations and you do not have to maintain several components for each population. You need to be cautious not to make use of the %EMPLOYEEID system variable in the underlying component peoplecode. If you need the current EMPLID being viewed, you should get it from some level zero record that was populated by the component processor.

If the OPRID is not tied to an EMPLID, then this method is not effective unless you can tie their OPRID to the data you want them to see with a SQL join.

Option 2 – EMPLID Based Search Record

Another way to secure the data would be through a search record that is just EMPLID search key based. You then place some searchInit code on the search record to force the current user to only see their own EMPLID. You just have to be careful because the SearchInit event will be bypassed if data is passed in the query string of the URL. You can ensure that searchInit always fires by:

  • Setting the Component Property – “Force Search Processing” to ON or checked
    • This will ensure that the SearchInit event is not bypassed which can happen by passing parameters in at the query string.
  • At a minimum you want this code in the SearchInit event
/* Place in SearchInit Event */
GetRecord().EMPLID.Value = %EmployeeId;
GetRecord().EMPLID.Visible = False;	

This code will make sure that the user is only viewing data for their own EMPLID. Since we hide the EMPLID with VISIBILE = False the user will never be able to choose a different EMPLID.

Option 3 – Installation Based Search Record

The last option is to use a search record that has no search keys which will effectively by-pass the search record. Your component then has some code in the PostBuild event that will load the data based on the current user’s EMPLID using the %EMPLOYEEID system variable.

The drawback to this method is that the component can only be used by the employee. I personally like all my self-service pages to be accessible by some administrator so if the employee calls with a problem, the administrator can see exactly what they are seeing. If your component loads data using the %EMPLOYEEID system variable then you cannot put the component on an alternative menu with a different search record for the administrator as described in option 1 above.