Appendix: PeopleSoft Query Security

With PeopleSoft Query security, you can control the query operations that users can perform and the data to which they have access.

This chapter discusses how to:

• Use query access group trees.

• Use row-level security and query security record definitions.

• Use query profiles.

Using Query Access Group Trees

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

• Create query trees.

• Grant and restrict access to groups.

• Enable the query access list cache.

Understanding Query Access Group Trees

PeopleSoft Query uses query access group trees to control security of the tables in your PeopleSoft database. You define a hierarchy of record components, based on logical or functional groupings, and then give users access to one or more branches of the tree. Users can use PeopleSoft Query to retrieve information only from those tables whose record definitions they have access to.

Using PeopleSoft Query Access Manager, you can create, view, and update query access group trees.

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

• Groups represent of a set of child groups or records.

• Records represent a PeopleSoft record definition.

Creating Query Trees

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

Use the Query Access Manager page to create query trees or search for existing query trees.

PeopleSoft provides sample trees with all of its applications. You can configure these trees; however, because these trees may get replaced when you upgrade to subsequent application releases, you should create your own query trees based on your organization’s needs.

In your query trees, include all record components that you want users to be able to query. Note that you do not have to put all record components in the same query tree. Instead, you can use the sample query trees to provide access to the standard PeopleSoft record definitions, but create additional query trees for record definitions that you want to add while adapting your system. This strategy enables you to take advantage of the sample trees but avoid overwriting your changes during future application upgrades.

How you organize the contents of your query trees depends on the needs of your organization and users. For example, for nontechnical or casual users, you might want to create small trees that are not intimidating.

To simplify the trees, you can create separate trees that contain subcategories of each function. For example, you could create separate trees for human resources, general ledger, and projects record components so that users in each region can access only those record components that they use.

When creating your tree, you should also have an access group that includes all components of the tree. This enables you to give users access to all tables more easily—you need only to add one row on the Access Groups tab in Query Security.

Note. Consider adding record components to your query trees in a hierarchy that matches the parent/child relationship of records in your database. Although you do not have to organize records in this way—PeopleSoft Application Designer actually controls the parent/child hierarchy in your database—you will probably find it helpful to keep your query trees consistent with your database structure.

Granting and Restricting Access to Groups

After you have built a query tree, you must give users access to one or more of its access groups. They can then generate queries on any tables in the access groups that are accessible to them.

To create new queries, or even to run existing ones, users must have access rights to the record components that are used in the queries. After you have built your query trees, you must grant users access to them. You can grant and restrict access to entire query trees or portions of them through the Permission List Access Groups page (by selecting PeopleTools, Security, Permission & Roles, Permission Lists, Query, Access Group Permissions).

See Also

Setting Query Permissions

Enabling the Query Access List Cache

An optional batch process is available for users who work with Query Manager, Crystal Reports, and PS/nVision. The system can much more quickly retrieve the queries that match the designated search criteria if the query access list cache is enabled.

This example shows the Query Access List Cache page (RUN_QRYACCLIST) :

To enable the query access list cache:

1. Select PeopleTools, Security, Query Security, Query Access List Cache.

2. Select the Enable Access List Cache option.

3. Click the Run button to run the process.

   If the process does not finish, the cache will be disabled.

Note. When the Enable Access List Cache option is selected and roles of a user Profile or permission list of a role has been modified, which affect the Query Access List Cache, you must rerun the QRYACCLIST AE process to properly update the cache. Otherwise, the Query Access List Cache is not up-to-date and will be switched off automatically.

Rerun the process when changes have been made to Query Access Groups or Query Access Group settings on Roles or Permission Lists.

Using Row-Level Security and Query Security Record Definitions

This section discusses how to:

• Enforce row-level security.

• Use query security record definitions.

Enforcing Row-Level Security

By default, when you give PeopleSoft Query users access to a record definition, they can access all the rows of data in the table that were built using the associated record definition. In some cases, though, you may want to restrict users from seeing some of those data rows. For example, you may not want your human resources staff to access compensation data for vice presidents or above. That is, you want to enforce the row-level security feature that is offered by many PeopleSoft applications.

Row-level security enables users to access a table without accessing 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 departments but not for employees in other departments. To accomplish this, you would give everyone access to the PERSONAL_DATA table, but would enforce row-level security so that users could see only the rows where the DEPTID matches their own.

Note. PeopleSoft Query row-level security is enforced only when you are using PeopleSoft Query or Scheduled Query; it doesn't control runtime page access to table data.

PeopleSoft applications implement row-level security by using a query security record (typically a view) that is specified on the record definition 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 security record view and the base table (rather than searching the table directly). The view adds a security check to the search, based on the criteria that you have set up for row-level security. For example, to restrict users to seeing only data from their own departments, the view would select from the underlying table only those rows where the DEPTID matches the user’s DEPTID. You can specify the query security record by selecting an appropriate view from the Query Security Record drop-down list box on the Record Properties dialog box for any record definition.

This is an example of the Record Properties dialog box with Query Security Record is set to QE_PERS_SRCH:

Note. Process and role queries override the automatic row-level query security logic that is applied to all other types of queries. For this reason, you should restrict access to creating these types of queries to administrative types of roles and not include any sensitive data columns in the select list for these types of queries. You can restrict access to creating and modifying these queries based on query profile settings that are assigned to a permission list. Note that Workflow queries also override the row-level security logic.

Securing Data Through the Search Record

To secure data through the query security record view, create a query security record that has both of the following criteria:

• The same key field as the base record that you are securing.

• One of the following three row-level security fields as a key field and not as a list box item:

  • OPRID (User ID).

  • OPRCLASS (Primary Permission List).

  • ROWSECCLASS (Row Security Permission List).

When you add one of the preceding fields as a key field, Enterprise PeopleTools automatically adds a WHERE clause when it does a select through the record. This forces the value to be equal to the current user’s value.

See PeopleSoft Query Security.

Using Query Security Record Definitions

Implement row-level security by having PeopleSoft 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 pages. Just as a search record definition determines what data the user can display in the page, the query security record definition determines what data the user can display with PeopleSoft Query.

To get PeopleSoft Query to retrieve data by joining a security record definition to the base table, specify the appropriate query security record when you create the base table’s record definition.

Note. The PeopleSoft row-level security views restrict users from seeing certain rows of data. If you specify a query security record for a given base record definition, PeopleSoft Query adds a qualifier to the WHERE clause of each query, instructing the system to retrieve only rows in organizational entities to which you have been granted access. If you perform a historical query—for example, a query asking for the employees in your department as of last year—you may not get the results that you expect. Because the system is enforcing row-level security, PeopleSoft Query returns only those employees who were in the department last year and who are currently in a department to which you have access.

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

Using Query Profiles

Use the Query Profile page to determine users' level of access when they work with PeopleSoft Query.

This example shows the Security - Query Profile page (SCRTY_QUERY_PROF) (PeopleTools, Security, Permissions and Roles, Permission Lists, Query tab):

To use query profiles:

1. Select PeopleTools, Security, Permissions & Roles, Permission Lists.

2. Select the Query tab.

3. Click the Query Profile link.

Query profiles specify the type of access that users have when they work with PeopleSoft Query. For example, you may want certain users to run only existing queries, not create new ones. You might also want to restrict the types of queries that users can create, limit the number of rows returned, or set the time that a query can run before it times out.

Note. If a user has more than one permission list, PeopleSoft Query uses the highest time-out value as defined in the Time-out Minutes section of the user's permission lists.

All time-outs are globally enabled and disabled in Query Administration.

When you select the Allow creation of Role, Process and Archive Queries check box, you also allow the user to create role, process, and archive queries.

See Understanding Query Administration, Setting General Permissions.

You can also determine the output options that users have for generating their queries. The first level of security is access to PeopleSoft Query itself. If you don't give users access to PeopleSoft Query when you define their user IDs, they can't create or run queries. Not all users needs to create their own queries.

PeopleSoft tests query profile settings across all of the permission lists to which a user has access. For example, each permission list that is granted to a user or role has the possibility to add capabilities. However, the Only Allowed to Run Queries option is unique because when you select this option, capabilities are removed. So if you want to prevent users from updating queries (and allow them only to run them), then you must ensure that none of the permission list settings grant the update capabilities by clearing the Only Allowed to Run Queries option.

By default, the query profile gives users access to all PeopleSoft Query features—assuming, of course, that you gave them access to PeopleSoft Query.

See Also

Implementing Query Security