Securing Advanced Queries

Advanced Query Security is a framework that allows authorized users to set up secured fields for PeopleSoft queries. Authorized users can associate different types of security records and specify which fields (including non-key fields) are to be used to secure data.

Note: Oracle’s PeopleSoft recommends that PeopleTools Application Designer - Query Security Record is not used when securing General Ledger ChartFields.

Page Name

Definition Name

Usage

Query Security Field Name Page

QUERY_SEC_CATEGORY

Determine secured fields for the query.

Query Security - Security Types Page

QUERY_SEC_METHOD

Determine the type of security for one or more fields and run the Apply Security process.

Query Security - Query Record Page

QUERY_SEC_FIELDS

Map secured query records for one or more fields and run the Apply Security process.

Set Filter Page

QUERY_SEC_FILTER

Determine filters for the secured query record.

Use the Query Security Field Name page (QUERY_SEC_CATEGORY) to determine secured fields for the query.

Note: ChartFields and other fields are delivered as system data.

Navigation:

Set Up Financials/Supply Chain > Security > Advanced Query Security > Advanced Query Security Field

This example illustrates the fields and controls on the Query Security Field Name page.

Query Security Field Name page

All ChartFields and some other fields such as business unit, SetID, ledger, and so on are delivered as system data. You cannot delete these delivered fields. However, you can add more fields per your requirements.

Note: If a specific field is not defined within a Secure Field, then you cannot apply query security to that field.

Field or Control

Description

Secure Field

Displays ChartFields and some other fields such as business unit, SetID, ledger and so on. This field is also known as the Category.

Field Name

Displays or add specific field names that are secured when the Secure Field is selected on the Query Security - Security Types Page.

Use the Query Security - Security Types page (QUERY_SEC_METHOD) to determine the type of security for one or more fields and run the Apply Security process.

Navigation:

Set Up Financials/Supply Chain > Security > Advanced Query Security > Apply Advanced Query Security

This example illustrates the fields and controls on the Query Security - Security Types page.

Query Security - Security Types page

Field or Control

Description

Security Type

Select one of the following options:

  • No Security: Select to indicate that no security is used.

  • User ID: Select to indicate that User security is used.

    This is the default.

  • Role: Select to indicate that Role security is used.

  • Permission List: Select to indicate that Permission List security is used.

Apply Security

Click this button to begin a batch process that applies the query security.

Sync Chartfield Sec (synchronize ChartField security)

Click this button to synchronize ChartFields from the Security Options - ChartField Security Page.

Secure Fieldname

Select the secure fieldname (also known as Category) for the fields that you want to secure. If the secured fieldname you are looking for does not appear, verify that it is created on the Query Security Field Name Page. When you select the Secure Field, all field names included in secure field are secured.

Note: Selecting a Secure Fieldname on this page allows you to select the fields within that category on the Query Security - Query Record Page.

Use the Query Security - Security Types page (QUERY_SEC_FIELDS) to map query secured records for one or more fields and run the Apply Security process.

Navigation:

Set Up Financials/Supply Chain > Security > Advanced Query Security > Apply Advanced Query Security > Query Record

This example illustrates the fields and controls on the Query Security - Query Record page.

Query Security - Query Records page

Field or Control

Description

Apply Security

Click this button to begin the batch Query Security process (SEC_QUERY).

Status

Displays Not Applied or Applied. When Applied is displayed, the you cannot run the Query Security process.

Filter

Click this link to access the Set Filter page.

Active

Select to indicate that this query record is processed by the apply Query Security application engine (SEC_QUERY).

Exceed Limit

Displays as selected when you have selected more than five secure fields. Therefore, you cannot select more than five fields.

To resolve the limit, deselect records so that five or less are selected.

Note: If there is no Exceed Limit error, then the label does not appear on the page.

Conflict

Displays as selected when there are security records defined (such as through application designer) and the record doesn’t exist in the framework. When this check box is selected, the system does not proceed further.

Note: If there is no Conflict error, then the label does not appear on the page.

Conflict Overrides

Select to clear a conflict while running the Advanced Query Security process. Selecting this check box deletes all current entries defined for Advanced Query Security under record properties and applies new entries as defined in the framework.

This label only appears when there is a Conflict error to clear.

Important! Oracle’s PeopleSoft recommends that you always use the framework to define any security to any query record, and not add it using application designer.

Select

Select a row to turn security on or off for a secured field.

Secure Field Name

Enter or select the name of the field that is to be secured.

User ID Record

Enter or select the name of the User ID record to be secured.

Role Record

Enter or select the name of the Role record to be secured.

Permission List Record

Enter or select the name of the Permission List record to be secured.

Field Name

Enter or select the name of the field that is mapped to the secured field.

Security Applied

Displays as selected when security has been applied to the Secure Field Name. A check in this check box indicates that the Query Security process ran successfully for this field.

After security is applied, you cannot delete that particular row unless you deselect the security from that field and run the process again.

Note: This field is display only.

More

Click this link to add more joins. This is useful when securing ChartFields.

Financial Impact

Click the button to view the Impacted Records window. This window displays all Business Intelligence Publisher (BIP) reports, Pivot Grids, and PeopleSoft Queries that will be impacted if you change the security of the query record.

Populate Lines

Click this button to populate all security records for ChartFields and any other fields for which a prompt is defined and corresponding security records exist.

When Implementing Advanced Query Security for Non-ChartFields

Follow these steps when implementing Advanced Query Security for non-ChartFields:

  1. Enter the Query Record value.

  2. Select the Field Name you want to secure in the Secure Field column and enter its corresponding security record.

    Oracle’s PeopleSoft recommends that you enter all three security records if available: User ID record, Role record, and Permission List record. If a security record is not available (your are not going to use that type of security), then leave it blank.

  3. If your scenario requires a composite join, then click the More button.

  4. Verify that you have selected the Active check box at the Query Record level, and check its corresponding secure fields that you wan to secure.

When Implementing Advanced Query Security for ChartFields

Follow these steps when implementing Advanced Query Security for ChartFields:

  1. Create a view based on the record, that you want to secure( or PSQUERY record).

  2. Add PRODSRCID as an additional field to this view.

  3. Hard-code your Product Source code in the view text.

  4. Create a Composite key with PRODSRCID field, for example, join PRODSRCID of base view with that of ChartField security view by clicking on the More link.

Use the Set Filter page (QUERY_SEC_FILTER) to determine filters for the query record.

Navigation:

Set Up Financials/Supply Chain > Security > Advanced Query Security > Apply Advanced Query Security > Query Records. Click the Filter link.

This example illustrates the fields and controls on the Set Filter page.

 Set Filter page

Field or Control

Description

Apply

Click this button to apply the filters to the query record.

Errors

Select an option that shows all rows or one of the following:

  • Show All Rows: Select to show all rows including errors.

  • All Errors: Select to show rows that have errors.

  • Exceed Limits: Select to show rows that have exceeded limits, such as a limit of five secure records.

  • Conflicts: Select to show query records that have conflicts, such as mapping record conflicts.

Product Filter

Select the PeopleSoft products that to filter the query record.