PeopleSoft Query Security

Question:

What do u mean by query security.How it is implemented in peoplesoft?

Answer:

When you talk about PeopleSoft Query Security, these are things that are taken into consideration:

1. Query Profiles...Specifies what query operations, such as create and run, are available to users. For example, you may want certain users to run only existing queries, not create new ones.

2. Query Access Group and Trees....Collections of record definitions in a query tree. After you build a query tree, you can query any table in the access groups that are accessible to you.

3. Define row-level security and query security records....Provides data permission security when you access data using the PeopleSoft Query tool.

All of these are utilized by PeopleSoft Query through user's security settings, row-level security, and primary permission list. Query Profiles are setup in Permission List (PTPT1000) , under Query Tab. Query Access Groups are attached to Permission List (HCSPQUERY), under Query Tab.

About Query Security Record:

One of the Security Feature in PeopleSoft is Data permission security, or row-level security

• It Controls access to user data on an individual or group basis.
• It Specifies the rows of data that user is permitted to retrieve.

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.

Since there is Query Tool, through which you can retrieve sensitive data, You can 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.

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.

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

4.Once you’ve set the query security record definition, click OK to close the Record Properties dialog box, then save the record definition.

The PeopleSoft system automatically adds a WHERE clause to the SQL, joining the user’s user ID, primary permission list, or row security permission list to the following key fields if they are present:

• OPRID
• OPRCLASS
• ROWSECCLASS

For Example, If you want to add a Query Security to PS_JOB record, you need to check the record properties (the query security comes pre-delivered)

and here's what the EMPLMT_SRCH_QRY sql looks like: