Providing Security to PS Queries

PS Query is user friendly (at least for developers J) to pull out and see data residing in database tables. Usually business users are given access to PS Query components to create and view new/existing queries. These data they will be able to be downloaded to their system in different formats. This posses a critical risk on your sensitive data. Displaying or exposing data to a wrong set of users is a real threat and if sometimes auditors will catch it, it can affect your company certification as well other than the data exposure threat.

PeopleSoft has provided 3 different levels of control to restrict your data to only the right set of users. I will try to provide a brief idea on the various security levels available for PS Queries.

1.       Component Security

You will get delivered Query Manager, Query Viewer and Schedule Query components with your application package.  Not all of your business users may need to access data other than from the transaction components. Usually very few core business users would need transaction data’s that need to be exported and processed further. So restrict the access to these components via permission list and assign the permission list to only the right set of users. This should be your first level of security to prevent the data explosion risk.

2.       Query Security Trees

You may have different set of users acting on the system. For example if you consider the financial domain, you will have accountants who deals with the accounting entry and you may have a separate procurement team who will be concerned only with procuring goods for the company. Both the team might have requirement to access PS Queries. So you will be providing access to the PS Query components. But if you look further, the procurement team should not be given the opportunity to view accountant’s data at general ledger and vice versa. To tackle this situation, PeopleSoft has provided another layer called Query Security Trees. Basically at this level you will be restricting the access to each back end tables (records) to the concerned groups only. If you do not add a record to a query security tree which is assigned to a user via permission lists, that user will not be able to view or create a new query containing that particular record.

Steps to be followed to provide this level of access is as follows.

a.       Go To: PeopleTools > Security > Query Security > Query Access Manager. Open a query tree corresponding to your module. If it doesn’t exists, go ahead and create one.

b.      Add your record as a node to the tree and save.

c.       Now you should associate the Query Tree with a permission list. For that open the permission list (People Tools > Security > Permission & Roles > Permission List) and go to the Query Tab.

d.      Click on the Access Group Permissions and provide your Tree and Branch names and save it.

e.      Assign the permission list to a role and the role to a user.

With the above steps you have successfully provided the second level security for the data, i.e individual record level security.

3.       Query Security Record

Implementing the above will put in place most of the security to the data that you require. But there are some cases where you want to add more security. As per the setting above, the user has full data access to the records assigned to the query security tree. But what about the data inside the record? Consider the case of HRMS where you have sensitive data. For example take the PERSONAL_DATA record. The user getting access to other people’s personal details is not acceptable. To overcome this situation PeopleSoft has delivered the third layer of security via Query Security Records also called as row level security.  Query security records are views which will select the data from the base table and also adds additional where clause to restrict the data fetched.

You can follow the below steps to implement row level security for PS Query output.

a.       Create a view which fetches the desired data from the base table

b.      Add all the keys of underlying record to the view

c.       Add one more additional key from OPRID, OPRCLASS & ROWSECCLASS. PeopleSoft will automatically create the where clause for this extra field.

d.      Now add the record created to the Query Security Record field of the underlying records property in the Use tab. PeopleSoft will automatically join this record with the underlying record when the Query is executed. You can verify the same by looking at the SQL statement of the PS Query.

Once you have done with these three steps, you are done with all the three layers of security. Want more? Do you want to hide the data based on values of multiple columns? As per my best knowledge there are no other options now.  J  You can leave the comments, if you know some methods. Otherwise let’s wait until People Tools brings up row level security to multiple fields.