PeopleSoft Query Security - Explaination

If a PeopleSoft record built through Application designer is not listed in PS-Query tool for reporting, it means that the particular PeopleSoft record is not available for the user to access through PS-Query.

For example, one has built a custom record PS_Z_ABC through Application Designer, then the record - Z_ABC won't be available under for PS-Query for any user. The record has to be added to a query tree.

Why to add a record to a Query Tree?How to add a record to Query Tree?
Well, PeopleSoft Queries are used to build SQL queries and retrieve information from application tables. For each PeopleSoft Query user, you can specify the records the user is allowed to access when building and running queries. This is done by creating query access groups in PeopleSoft Tree Manager and then assigning users to these query access groups with PeopleSoft Query security. PeopleSoft Query security is enforced only when using PeopleSoft Query; it does not control runtime page access to table data.

Once a decision is made on what kind of users(based on Permission lists) must have access to the record, then the record has to be attached to the query tree associated with the permission list. This can be done under:
PeopleTools > Security > Query Security > Query Access Manager
Most often, it is better to run Query Access Cache process immediately after making changes to any query tree.


How to associate a Query Tree with a user/OPRID?
This is done based upon the Permission lists attached to the users' roles. To associate a Permission List with a particular Query Tree, at first place the query tree with required access groups must be created. Once a query tree with required access groups is available then associate this query tree with a permission list under: PeopleTools > Security > Permissions and Roles > Permission Lists > Query 

In this navigation, click on "Access Group Permission"; then chose the Tree name and the access group in the tree for which you want to give access to. 



How to identify which user/permission list has access to which tree?

1. We can verify this by clicking "Access Group Permission" in the navigation: PeopleTools > Security > Permissions and Roles > Permission Lists > Query
2. Alternatively, we can check this through database as well. PeopleSoft has a delivered record named - SCRTY_ACC_GRP which maintains the permission lists and its associated query trees along with the access group and access properties. The structure of this record PS_SCRTY_ACC_GRP is as below:
     1. CLASSID               Key field
     2. TREE_NAME         Key field
     3. ACCESS_GROUP  Key field
     4. ACCESSIBLE        Non-Key field with default value as 'Y'