Using Row-Level Security and Query Security Record Definitions
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 on the Record Properties dialog box for any record definition.
Image: Record Properties dialog box - Use tab
This example illustrates the Record Properties dialog box - Use tab with the Query Security Record field 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).
Note: These security criteria are applied for all definitions, including multiple query security record definitions and single query security record definitions.
When you add one of the preceding fields as a key field, 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.
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.
You are able to add multiple query security records for a record definition, including:
Associating multiple query security records with a single record.
Specifying the fields (including non-key fields from the query security record) and the base record that will be used to secure the data.
Adding Multiple Query Security Records for Record Definitions
Query Administrator uses the Advanced Query Security Record Mapping dialog box and the Add Query Security Record dialog box in Application Designer to add multiple query security records for a record definition. Note that each base record can have one query security record defined in the Record Property dialog box and as many as five additional query security records defined in the Advanced Query Security Record Mapping dialog box. When you add more than five query security records using the Advanced Query Security Record Mapping dialog box, an error message appears to alert you that the maximum limit has been reached.
Navigation
Open the Record Properties dialog box in Application Designer.
Click the Advanced Query Security button.
In the Advanced Query Security Record Mapping dialog box, click the Add button.
Image: Advanced Query Security Record Mapping dialog box
This example illustrates the Advanced Query Security Record Mapping dialog box in Application Designer.
Image: Add Query Security Record dialog box
This example illustrates the Add Query Security Record dialog box in Application Designer.
|
Field or Control |
Definition |
|---|---|
| Secured Field |
Select a field from the available fields in the base record. |
| Query Security Record |
Select a record that you have permission to access. Note: Dynamic views, derived or work records, subrecords, and temporary records cannot be used as query security records. These records are excluded from the Query Security Record drop-down list. |
| Field Name |
Select a field from the selected query security record. |
To add multiple query security records for record definitions:
In Application Designer, open the Record Properties dialog box.
Click the Advanced Query Security button.
The Advanced Query Security Record Mapping dialog box appears with the list of query security records that are already set to the current record.
Click the Add button to access the Add Query Security Record dialog box.
Use the Add Query Security Record dialog box to enter additional query security records and their corresponding field mappings.
Removing Query Security Records from Record Definitions
To remove query security records from record definitions:
In Application Designer, open the Record Properties dialog box.
Click the Advanced Query Security button.
The Advanced Query Security Record Mapping dialog box appears with the list of query security records that are already set to the current record.
Select a row in the Query Security Record section.
Click the Delete button.
A warning message appears.
Click the Yes button to confirm the deletion.