Implementing Query Security

This chapter discusses how to:

• Define query profiles.

• Build query access group trees.

• Work with query trees.

• Define row-level security and query security records.

Note. You perform these setup tasks using the Query Access Manager, Application Designer, and permission lists. After you define Query Access Group trees, you provide user access using the Query tab in Permission Lists.

Defining Query Profiles

Query takes advantage of user's security settings, row-level security, and primary permission list. Query Manager helps you build SQL queries to retrieve information from your application tables. For each Query Manager or Query Viewer user, you can specify the records they are allowed to access when building and running queries.

You do this by creating Query Access Groups in the Query Access Group Manager, and then you assign users to those groups with Query permissions. Keep in mind that Query permissions are enforced only when using Query; it doesn't control run-time page access to table data.

Building 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 the PeopleSoft database. You 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.

You create and update query access group trees using Query Access Manager. To get you started, we’ve included some sample query access group trees with the PeopleSoft applications. Which trees you have depend on which PeopleSoft applications you’ve installed. Each tree contains access groups and record definitions categorized by function.

Access groups mark and define a functional group of records or other access groups—in other words, they are descriptive placeholders used to categorize actual record definitions in a logical, hierarchical format. When you define users’ security rights to a tree, you specify which access groups they are permitted to query.

This section explains how to create query access group trees. It assumes that you’re familiar with the concept and terminology of PeopleSoft trees.

Query Access Group Tree Considerations

You should create query access group trees based on your organization’s needs and on any customizations you’ve made. Remember that the sample trees we provide may be replaced when you upgrade to a subsequent PeopleSoft release, so if you modify the samples rather than create your own trees, you may lose your customizations.

Every record definition that you want users to be able to query must be in a query tree. However, they don’t all have to be in the same query tree. One strategy is to use the sample query trees to provide access to the standard PeopleSoft record definitions, but create separate query trees for record definitions that you add in the course of customizing the system. This way, you take advantage of the sample trees but avoid overwriting your changes during future upgrades.

How you organize the contents of the query tree depends on the needs of your organization and your users. For example, you might want to create small trees that are not intimidating to non-technical or casual users. The sample query trees provided in the PeopleSoft application are divided by functions, but to simplify the trees, you may want to create separate trees that contain subcategories of each function. For example, you could create separate trees for U.S. and Canadian record components to grant users in each region security access to only the record components they should use.

Note. You should consider adding record definitions to the query trees in a hierarchy that matches the parent/child relationship of records in your database. Though you don’t have to organize records this way—Application Designer actually controls the parent/child hierarchy in your database—you’ll probably find it helpful to keep the query trees consistent with your database structure.

Working with Query Trees

This section provides an overview of Query access group trees and discusses how to:

• Open Query access group trees.

• Define the Query tree.

• View and modify definitions.

Understanding Query Access Group Trees

If you have worked with Tree Manager or trees, take a moment to review the following information describing the differences between typical trees and the Query access group trees.

Nodes

Regarding nodes, consider the following points:

• 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, similar to folders in Microsoft Windows.

• Records represent a PeopleSoft record definition.

Structure

Regarding structure, consider the following points:

• Always use the ACCESS_GROUP Tree Structure.

• Do not use SetID or UKV/BU.

• Do not have Details.

• Do not use Levels.

• Do not use Branches.

Requirements

Regarding requirements, consider the following points:

• 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).

Opening Query Access Group Trees

Access the Query Access Manager page (PeopleTools, Security, Query Security, Query Access Manager.).

Before you can view and modify a Query access group tree definition, you need to locate the correct tree definition.

To open a query tree definition:

1. On the Basic Search page select your search criteria.

   You can search by Tree Name, Tree Category, Tree Description, Group Name used in a Tree, or Record Name used in a Tree.

2. Click Search.

   After clicking Search, a list appears containing the definitions that meet your criteria.

3. Click the tree name link.

   The search page also enables you to delete or copy a tree. Click the Delete or Copy link to perform the desired task. If you click Delete, the system prompts you to confirm the action, and if you click Copy, the system displays the Copy Tree page where you can enter the name for the copied tree.

   Some of the trees in the grid may appear with no Copy and Delete buttons visible. In this situation, Definition Security settings are such that you have only read-only access to these trees.

Defining the Query Tree

Access the Tree Definition and Properties page (Click the Create a New Tree link on the Basic Search page).

Before you can insert nodes for access groups and record components, you must first define a number of important characteristics for the tree.

Tree Name	For the tree name, we recommend that you start the name with QRY_ so that you can easily identify the tree as a custom query tree. The standard query trees we deliver with the system start with QUERY_.
Structure ID	The Structure ID is read only and always reads ACCESS_GROUPS for Query access trees.
Description	The description appears with the name and effective date in the list box when you select from a list of trees.
Effective Date	The status default is set to Active. Query trees are available immediately if the effective date is active; you don’t need to run an SQR utility like you do for organizational security trees.
Category	If necessary add a category, which are groupings of the definitions.
Item Counts	Item Counts shows the number of nodes within the access group.

Once you've completed the tree definition, click OK. On the Enter Root Node for Tree page, select an existing Access Group using the Lookup Access Group control, or create a new one.

Viewing and Modifying Definitions

This section describes the controls you use to modify Query Access Group Trees after you have opened one from the search page.

Effective Date	Shows the current effective date.
Status	Shows either Active or Inactive.
Tree Name	Shows the name of the current tree.
Save, Save As	These are the two save options. Each option appears only if it relates to the current activity. Save enables you to save your changes to the database. Save As enables you to clone tree definitions at save time.
Close	Closes the definition and returns you to the search page.
Tree Definition	Shows the Tree Definition and Properties page that you modified when you created the definition.
Display Options	Shows the Configure User Options page where you can adjust the presentation of the trees. For example, you can choose whether the Node ID appears and how many lines of the definition appear at a time. Most of these don’t apply for Query Access Trees so they’re disabled.
Print Format	Displays a print preview of the tree definition.
Bread Crumbs	Once you have drilled down into a definition, a “bread crumb” view appears just above the Collapse/Expand All controls to provide orientation, especially within large trees.
Collapse All	Collapses all nodes of the tree into their parent groups so that you see only the root node and the first layer of child groups.
Expand All	Expands all nodes of the tree so that each child object is visible.
Find	If you are looking for a specific access group or a record you can use the Find Value page rather than drilling down into the tree. You specify an access group or a record or its description. You can select a case sensitive search and specify that an exact match must be found. You can use pattern search option by deselecting the Exact Matching check box. This performs platform independent search for the Record/Group starting from the specified pattern. If you want to perform pattern search not starting from the beginning of Record/Group name, specify a platform dependent wildcard character at the beginning of the pattern. For example, to find all occurrences of ‘TBL’ in the Records, you specify %TBL as a search condition (for Microsoft SQL Server database). If you specify both Group and Record search conditions the search is performed on Group condition. If you specify both Group/Record ID (name) and Description conditions the search is performed on ID/name condition. Note. Always save modifications to the tree prior to using the Find feature.

Node/Record Controls

When you have a node or record selected, the actions you perform are controlled by the icons that appear to the left and right of the definition. The descriptions of the actions are below. You can pass the mouse pointer over an icon to reveal its label.

Defining Row-Level Security and Query Security Records

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. In some cases, though, you want to restrict users from seeing some of those data rows. For example, you might not want your human resources staff to have access to compensation data for vice presidents or above. In other words, you want to enforce row-level security, (also called data permission security) which is offered by many PeopleSoft applications.

This section describes the relationship between row-level security and Query security record definitions.

Row-Level Security

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. The view adds a security check to the search, based on the criteria you’ve set up for row-level security. For example, to restrict users to seeing data from their own department, the view would select from the underlying table just those rows where the DEPTID matches the user’s DEPTID.

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.

   Note. You use this dialog box to set a number of different aspects of the record definition. The only item related to Query security is Query Security Record list box.

3. Select the security record definition (usually a view) in the Query Security Record list box.

   Each PeopleSoft product line comes with a set of views for implementing its standard row-level security options. See the product documentation for details.

   Note. The Parent Record list box is also relevant to Query. It identifies a record definition that is the current definition’s parent, meaning that it holds related data and that its keys are a subset of the current record definition’s keys. If you designate a parent record, Query automatically knows what fields to use when you join these two tables for a query.

   Typically, the Query Security Record definition you’ll want to select is the same one you use as the search record definition for the panel that manages this table. If you’re enforcing one of the standard row-level security options from a PeopleSoft application, select the PeopleSoft-supplied security view for that option. See the application documentation for a list of the available views. If you’ve designed your own security scheme, select a record definition that appropriately restricts the rows a query will return.

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 the table or view from this record definition, you don’t need to rebuild it.

Row-Level (Data Permission) Security Views

Using PeopleSoft row-level security views enables you to restrict users from seeing certain rows of data. You can restrict data by:

• User, by using the OPRID field.

• Primary permission list, by using the OPRCLASS field.

• Row security permission list, by using the ROWSECCLASS field.

To implement row-level security through a security view:

1. In Application Designer, insert one of the three row-level security fields (OPRID, OPRCLASS, ROWSECCLASS) into the record definition.

2. Configure the field as a Key, but not a List Box Item.

3. Save the record and build the view.

4. Use the record as the search record or query security record.

Now, when the user searches, the system dynamically adds a WHERE clause — that incorporates the security field — to the search SELECT statement. The value of the security field is based on the current user.