PeopleSoft Query Security

PeopleSoft Query Tools helps user to select the data from base table. This also provides different help for grouping and Join. Using Query Security we can restricts the user to query certain records.
Here we should differentiate between Restricting the data and restricting the records for query.
User can create Query Access Group Manager and then attach the Query Access Group to user profile with Query permission. These restrictions are only valid when you query the data and it will not restrict on component search.

Query Tree

:-PeopleSoft Query uses query access group trees to control the access of the tables in the database. Query Access Manager provides the flexibility to Create/Modify the query tree. Tree should contain Access Group and Record definition. Record that user wants to query should be in Query tree.

Understanding Query Access Group Trees

Nodes: - Nodes can be either groups or records

Structure:- uses ACCESS_GROUP as Tree Structure. It never uses ACCESS_GROUP. User Should not use Details, Level or Branches

For PeopleSoft Query Tree the Root Node is always a group. Groups are always unique in a given Tree while records definitions can be repeated. Groups and records can have Child Groups and Child Records. Each record needs a unique fully qualified path in the tree.

Structure ID:- The Structure ID is read Only and reads ACCESS_GROUPS for Query access trees.

Defining Row-Level Security and Query Security Records

By default, when Admin gives Query users access to a record definition, they will have access to all the rows of data in the table. In many cases, Admin wants to restrict users from seeing some of those data rows. Admin want to enforce row-level security, (also called data permission security).

Row-Level Security

Using row-level security, users can access to a table without having access to all rows on that table. This type of security is used on tables that houses sensitive data. PeopleSoft applications implement row-level security by using view that joins the data table with an Security table. When a user finds for data in the base table, the system performs a related join between the view and the base table instead of searching the table directly.

Query Security Record Definitions

Admin can 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 definition determines what data the user can display with Query. You need to specify the appropriate Query Security Record when you create the base table's record definition.
To apply row level security:
1. Open the record on which need to apply row-level security.
2. Properties button == Use tab from the Record Properties.
3. Select the security record definition in the Query Security Record list box.
4. click OK to close the Record Properties dialog box, and save the record definition.

Row-Level (Data Permission) Security Views

You can restrict data by:

1. User (OPRID field)
2. Primary permission list (OPRCLASS field)
3. Row security permission list (ROWSECCLASS field)

To implement row-level security through a security view

1. Insert one of the three fields in record (OPRID, OPRCLASS, and ROWSECCLASS)
2. Make the field as Key. Uncheck he List box property.
3. Build the view and make this record as Search record or define this record as Query Security record.

PeopleSoft system dynamically adds a WHERE clause -to filter the rows specific to current user.