PSQuery Security

Query is a PeopleTool that helps you build SQL queries to retrieve information from your application tables.

For each Query user, you can specify the records they are allowed to access when building and running queries by creating Query Access Groups in the Query Access Group

Query Access Group Trees:

Trees are a graphical way of presenting hierarchical information.

PeopleSoft Query uses query access group trees to control the access of the tables in your PeopleSoft database.

Define a hierarchy of PeopleSoft record definitions, based on logical or functional groupings, and then give users access to one or more nodes of the tree.

Users can retrieve information only from those tables whose record definitions to which they have access.

 Nodes: Query access group trees contain two types of Nodes: groups and records.

• Groups are a logical representation of a set of child groups or records. It is similar to folder in Windows.It will be another Accessgroup.
• Records represent a PeopleSoft record definition.

Navigation : Query Access Group Tree, Select PeopleTools, Security, Query Access Manager.

To Add the Query Access Groups to user: Open the primary Permission List for the user--> ‘Query’ Tab-->Click on Access Group Permissions.
Add the tree name, select the proper Access Group, Select ‘Accessible’ button. Repeat to add more Access groups.-->Save

Example:Suppose in query access tree we have two groups each is having two records :Grp1 (Rec1,Rec2) & Grp2 (rec3,rec4).

And in permission list ptptptest1 we have included grp1 of access group tree & in ptpttest2 we have added grp2 of access grp tree .Then user having permission list ptpttest1 can access only rec1 & rec2 while user having pemission list ptpttest2 can access rec3 & rec4.If we will add both grp1 & grp2 then user can access all the 4records

Using Query Profile page, user level security can be set. Query profile specify which type of access a user can have to work on PS Query
Navigation Path: Main Menu -> People Tools -> Security -> Permissions and Roles -> Permission Lists -> Query Tab -> Query Profile Link

Row Level Security:

• By default, when you give Query users access to a record definition, they have access to all the rows of data in the table built using the associated record definition.
• With row-level security, users can have access to a table without having access to all rows on that table.
• This type of security is typically applied to tables that hold sensitive data.
• For example, you might want users to be able to review personal data for employees in their own department, but not for people in other departments. You would give everyone access to the PERSONAL_DATA table, but would enforce row-level security so that they could only see rows where the DEPTID matches their own.
• PeopleSoft applications implement row-level security by using a SQL view that joins the data table with an authorization table.
• When a user searches for data in the data table, the system performs a related record join between the view and the base table rather than searching the table directly.

Query Security Record Definitions:

• You implement row-level security by having Query search for data using a query security record definition. The query security record definition adds a security check to the search.
• Query security record definitions serve the same purpose as search record definitions do for panels. Just as a panel’s search record definition determines what data the user can display in the panel, the query security record definition determines what data the user can display with Query.
• To get Query to retrieve data by joining a security record definition to the base table, you specify the appropriate Query Security Record when you create the base table’s record definition.

To apply row level security:

1. Select PeopleTools, Application Designer to open the Application Designer, and open the record on which you want to apply row-level security.
2. With the record definition open in the Application Designer, click the Properties button, and select the Use tab from the Record Properties dialog box.
3. Select the security record definition (usually a view) in the Query Security Record list box.

4. Once you’ve set the query security record definition, click OK to close the Record Properties dialog box, then save the record definition. If you’ve already used SQL Create to build a table from this record definition, you don’t need to rebuild it.

Note. PeopleSoft row-level security views restrict users from seeing certain rows of data. To secure data through the search record, simply put one of the three Row Level Security fields on your record as a Key, not a List Box Item. The three Row Level Security fields are OPRID (User ID), OPRCLASS (Primary Permission List), and ROWSECCLASS (Row Security Permission List). If one of these fields is on the search record as a Key, not a List Box Item, PeopleTools does the following. PeopleTools adds a WHERE clause when it performing a SELECT through the record forcing the value to be equal to the current user’s value.