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';
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.
