September 27, 2006

Employee Rows Lost to Security

Has a user ever asked you why data seems to have disappeared from view? There are many possible causes, of course, but row-level security is usually a top suspect. In the HRMS/HCM product, you might have a problem with your department tree, or maybe the SJT tables are not up to date, or there is a SETID problem, etc. Then employee data could drop off of queries or disappear from online pages.

Here is a quick test you can try to see if this situation is occurring in your system. Log in under an ID that should have access to all employee data. Go into PeopleSoft Query and build a query for a list of all employee IDs. Save the query and copy the SQL.

In your SQL tool (Query Analyzer, Toad or whatever you use), write a query to select all employee IDs and then exclude all of the IDs that would be selected by the PS Query version:

  SELECT ...
  FROM   PS_JOB
  WHERE  ...
  AND    EMPLID NOT IN (
         (your PS query SQL here)
  )

This should give you a list of all employee IDs that exist in the database but would not be selected by a query constrained by row-level security. Hopefully you will not see any IDs like this. If you do, a little research should lead you to the cause of your row-level security problems.

Until next time...