II 9. Securing Query Access

A.     Introduction to PeopleSoft Queries

As discussed in the earlier chapters, PeopleSoft stores all the application data in database tables, often referred to as records in PeopleSoft environment. Users can query these database tables to retrieve information or extract data based on specified conditions. Typically such queries are executed directly on the database using SQL tools like sqldeveloper, TOAD, SQL Analyzer, etc. However, these tools require high level skills in writing sql scripts with the right joins and conditions. Unlike the PeopleSoft system which restricts the user to only see the rows of data that he/she has been granted access, SQL tools also do not enforce data level security. For these reasons, PeopleSoft provides an easy to use graphical interface to query PeopleSoft tables without having to write complicate SQL statements. The PeopleSoft query tool also automatically enforces the row level security when the user queries sensitive data tables.

Refer to PeopleSoft Query PeopleBook for more information on creating and running queries.

  1. Access to Query Manager, Query Viewer and Schedule Query pages: Query Manager allows the user to create new queries or run existing queries. Query Viewer allows the user to view and run existing queries. The Schedule Query page allows users to schedule any accessible query to run at a particular time, either as a one time job or on a repetitive schedule. Grant access to these pages in the same way as other PeopleSoft page permissions.
  2. Query access groups: All PeopleSoft tables that the users may need to query are added to Query Trees. Query trees are hierarchical groupings of PeopleSoft records grouped by tree branches or access groups. Similar tables are added to an access group, while access groups with tables of one module are added under one query tree. This modular design helps in managing the tables’ access while providing a way to separate more sensitive tables from others. The list of tables that the user can add to their query is restricted by their query security access groups. The users would also be able to see the public queries that use the tables that are accessible to them.
  3. Query Profile: Query profile controls the user’s ability to save queries as public queries or create more complex queries. The ability to restrict the users to a smaller number of joins, unions and/or sub-queries, enables the administrators to grant query access more freely to the users without adding significant risk to the system’s performance. The more advanced users and IT support teams can be granted these advanced features to create complex queries required for data analysis and save them as public queries or share with other users as appropriate.

 

B.     Maintain Query Access Groups (TREE_ACCESS_VW/PSTREENODE):

Navigation: PeopleTools >> Security >> Query Security >> Query Access Manager

  1. Navigate to the Query Access Manager page, and click on search.
  2. In the list of available trees, click on the appropriate tree name.
  3. On the Query Access Manager page, each folder (tree branch) corresponds to a query access group. Click on the appropriate tree branch where you want to add a new record.
  4. Click on ‘Insert new child record’ icon on the appropriate access group.
  5. In the ‘Insert Child Record’ page, enter the name of the record you are adding to the query tree, and click search. The record gets added to the tree at the selected node.
  6. Save and close the query tree.

24

Figure III‑23 Query Access Manager

25

Figure III‑24 Query Access Manager – Adding New Record

** PSTREENODE table lists the nodes for all trees in the system. Join TREE_ACCESS_VW to show query trees.

C.      Assign Access Groups to Permission List (PS_ SCRTY_ACC_GRP):

  1. Navigate to Permission Lists page and open the permission list you want to assign the query access to.
  2. Once in the Permission list, go to the Query tab.
  3. In the Query Permissions section, click on the ‘Access Group Permissions’ link.
  4. In the Permission List Access Groups page, add the Tree Name of the query tree which has the required access group.
  5. In the Access Group column, select the appropriate access group that you want to assign to this permission list and select the ‘Accessible’ check box.
  6. Click OK and save the permission list.

26

Figure III‑25 Permission List – Query

27

Figure III‑26 Permission List – Query – Access Groups

** When you grant access to an Access Group, all of the child records directly under that tree node as well as any other child node become available to the users of the permission list. Query Tree should be designed with this security in consideration.

** If you are adding a parent access group but do not want to include one of the child access groups for this permission list, add another row for that access group and un-check the accessible check box.

D.     Assigning Query Profile permissions (SCRTY_QUERY):

When the user goes to Query Manager and attempts to create or edit queries, his/her access to create certain types and complexity of the queries is restricted through the Query Profile permissions defined in the permission list. Click on the Query Profile hyperlink in the Query permissions section of the Query tab in the permissions list, and select the appropriate access for the permission list.

28

Figure III‑27 Permission List – Query – Query Profile

Below is the list of various aspects of query that can be controlled by query profile.

  1. Only Allowed to run Queries: If this check box is enabled, the user will not be able to create new queries or edit existing queries, even if the user has access to the Query Manager page.
  2. Allow Creation of Public Queries: PeopleSoft queries are generally created as ‘Private’ queries which are only accessible to the user who created them. If this check box is enabled, the user will be able to save the query as ‘Public’. Public queries are available to all users of query manager and query viewer pages as long as they have access to the tables used in the queries, through the query access groups.
  3. Allow creation of Role, Process and Archive Queries: These are special query types that are used in processes providing other system functionalities like dynamic roles, data archival, etc., and as such are not appropriate for regular end-users. These queries should be treated similar to application code and changes should be processed via the appropriate change control process. Enable this check box only for the technical team responsible for maintaining these special of queries.
  4. Maximum Rows Fetched: This is a system control to restrict the users from creating large queries that may cause significant burden on the application server. If the query returns more rows than the limit specified here, the user will be notified at run time that there were more rows of data but was limited by his/her query profile permissions. No value or a “0” in this field indicates unlimited row fetch.
  5. Maximum Run Time in Minutes: This is another system control to restrict the user from creating large queries that run for a long time causing significant burden on the application server. The system automatically cancels the query execution once the query run time passes the time specifies in the query profile permissions. No value or a “0” in this field indicates unlimited run time (up to session time out setting).
  6. Maximum Joins Allowed: As with SQL scripts, PeopleSoft query allows users to join two or more records to extract appropriate information. However joining multiple tables with large amounts of data, especially without proper criteria, can result in high system utilization. Use this field to restrict the less experienced query users to a manageable number of joins. Enter “9” for unlimited joins for expert query users.
  7. Maximum ‘In Tree’ Criteria: PeopleSoft query lets users to create query criteria against values in various trees in the system. This enables the users to use a valid range of data already available in Tree definitions, without having to enter each tree node individually. As with the number of joins, ‘In Tree’ criteria requires significant system resources and hence, should be allowed with caution.
  8. Advanced Query Options: Each of the options is advanced query function and should be restricted to query super users.
  • Allow Use of Distinct: Allows the users to use the ‘DISTINCT’ clause in the query
  • Allow Use of ‘Any Join’: Allows the users to join type of ‘Any Join’ when joining tables in Query
  • Allow Use of SubQuery/Exists: Allows the users to use the EXISTS clause in the conditions for the query
  • Allow Use of Union: Allows the users to use UNION statement to combine results from different queries
  • Allow Use of Expressions: Allows the users to create and use expressions for complex clauses/conditions that are not available in the selection options.