PeopleSoft Query Security - Identify the Permission List associated with a Query Tree Table.

A very common issue faced by IT/Functional folks right after go live - Identify why a user can access a table in a query and another cannot. PeopleSoft handles Query Security using Query Trees & Access Groups within the trees. There are multiple blogs which talk about the process of setting up trees, access groups and then assigning them to the users; so I'll straightaway go to the issue what we faced today.

Issue : User 1 has access to a particular table via PS query and User 2 does not.

Step 1 - Ran the below sql to identify the permission lists associated with the table :

SELECT a.classid AS PERMISSION_LIST, A.RECNAME AS RECORD_NAME

  FROM PSQRYACCLSTRECS A

 WHERE A.recname = 'SPECIFY TABLE_NAME_HERE';

Step 2 - Using the permission list/s retrieved from step 1, identify the role/s which hold the permission list for USER 1.

SELECT DISTINCT A.rolename AS ROLE_TO_BE_ASSIGNED
  FROM PSROLEUSER A, PSROLECLASS B
 WHERE     A.ROLENAME = B.ROLENAME
       AND B.CLASSID = 'SPECIFY PERM_LIST FROM STEP 1 HERE'
       AND A.ROLEUSER = 'SPECIFY USER WHO HAS THE ACCESS TO THE TABLE';

Step 3 - Add the role/s identified in step 2 to the USER 2 (who cannot access the table).

P.S - You may certainly use your sql skills to combine the 2 sql's above into one.