PeopleSoft Query- and Row-Level Security

• Query is a PeopleTool that helps you build SQL queries to retrieve information from your application tables.
• Query takes advantage of user’s security settings, row-level security, and primary permission list.
• For each Query user, you can specify the records they are allowed to access when building and running queries. We can achieve this by creating Query Access Groups in the Query Access Group Manager, and then you assign users to those groups with Query permissions.

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.
  • Records represent a PeopleSoft record definition.
• Structure:
  • Always use the ACCESS_GROUP Tree Structure.
  • Do not use SetID or UKV/BU.
  • Do not have Details.
  • Do not use Levels.
  • No Branches.
• Requirements:
  • The Root Node is always a group.
  • Groups must be unique in a given Tree while records definitions can be repeated.
  • Groups and records could have Child Groups and Child Records.
  • Each record needs a unique fully qualified path in the tree. You can’t add the same record under the same parent node (group or record).
• To open an existing Query Access Group Tree, Select PeopleTools, Security, Query Access Manager.
• Create custom Query Access Group suitable to your organization. Create functional group names and add records under the group.
• To Add the Query Access Groups to user:
  • Open the primary Permission List for the user
  • Go to ‘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 the permission List.

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.

Applying 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.