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. In Application Designer, open the record on which you want to apply row-level security.

  2. With the record definition open in 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.