We have already seen how a PS Query is created through PIA in PeopleSoft in the following article - Creating Query Report with PS Query in PeopleSoft.
When we create a PS query which involves those records that has employee ID field then we see System automatically adds a row level security criteria in the generated SQL which prevents unauthorized access to the employee data. Ideally speaking, this extra security measure is indeed very significant because otherwise, everybody can access everything through PS Query. However, this additional security measure can sometimes prove to be hurdle in the way and apparently I have dealt with such an hurdle once upon a time.
There was a requirement where we had to create a ePerformance report that would generate list of employees who have gone in an assignment to UK from India and the performance document has been created for them. Well, so far there wasn't any issue though until we realized that the report should be accessible to India HR only.We got down to work and created such a report using PS Query but when we ran it as HR it didn't fetch any employee.
We were surprised to see that and were very curious to know what went wrong. We then opened the PS Query definition through Query Manager and checked the generated SQL:
The highlighted area above is the real culprit which is preventing the HR to see those employee's data who went to UK for assignments. Let me put it in another way -
The HR is of the region IND and the employee they are trying to fetch the details are now of the region GBR because they have gone to UK for assignment and since HR is not authorized to see the cross country information hence system has automatically joined row level security search records PS_EMPLMT_SRCH_QRY and PS_PERALL_SEC_QRY in the query appropriately to prevent the potential unauthorized access and it cannot be deleted from the PS Query.
So what are we gonna do now because we want that the India HR should be able to get the report on UK employees, here is the solution.
When we create a PS query which involves those records that has employee ID field then we see System automatically adds a row level security criteria in the generated SQL which prevents unauthorized access to the employee data. Ideally speaking, this extra security measure is indeed very significant because otherwise, everybody can access everything through PS Query. However, this additional security measure can sometimes prove to be hurdle in the way and apparently I have dealt with such an hurdle once upon a time.
There was a requirement where we had to create a ePerformance report that would generate list of employees who have gone in an assignment to UK from India and the performance document has been created for them. Well, so far there wasn't any issue though until we realized that the report should be accessible to India HR only.We got down to work and created such a report using PS Query but when we ran it as HR it didn't fetch any employee.
We were surprised to see that and were very curious to know what went wrong. We then opened the PS Query definition through Query Manager and checked the generated SQL:
The HR is of the region IND and the employee they are trying to fetch the details are now of the region GBR because they have gone to UK for assignment and since HR is not authorized to see the cross country information hence system has automatically joined row level security search records PS_EMPLMT_SRCH_QRY and PS_PERALL_SEC_QRY in the query appropriately to prevent the potential unauthorized access and it cannot be deleted from the PS Query.
So what are we gonna do now because we want that the India HR should be able to get the report on UK employees, here is the solution.
- Instead of creating a PS Query, first create a view (For Example - EPERF_DATA_VW) from the same records and the same SQL you have used in the PS Query that will fetch the desired employees.
- then create the PS Query (EPERF_REPORT) from this View EPERF_DATA_VW.
- Finally create the report from the PS Query EPERF_REPORT.
Good idea!!
ReplyDeleteSuperb !!
ReplyDelete