SQL query to find out list of users who have access to a Page and information on which Permission list enables the user to access the page:

SELECT DISTINCT oprid, oprclass FROM psoprcls WHERE oprclass IN (SELECT DISTINCT classid FROM psauthitem WHERE pnlitemname = :1)

This query can be modified as below to see if a user has access to a particular component and if yes through which permission list.

SELECT DISTINCT oprid, oprclass FROM psoprcls WHERE oprclass IN ( SELECT DISTINCT classid FROM psauthitem WHERE pnlitemname = :1--required component name) AND oprid = :2 --required userid

How to Query Which Components Are Accessed by Which Roles? For example, for component "JOB_DATA", to find out which role should be given to a user which gives them access to that component or page use the following SQL.

SELECT * FROM psauthitem A, psroleclass B, psroleuser C WHERE A.baritemname IN ( :1 ) AND A.classid = B.classid AND C.rolename = B.rolename AND C.roleuser = 'PS'

SQL Query to find out navigation in Peoplesoft application using the Component Name

SELECT A.PORTAL_OBJNAME COMPONENT, DECODE(E.PORTAL_LABEL, 'Root', 'Home > ', '', '', E.PORTAL_LABEL || ' > ') || DECODE(D.PORTAL_LABEL, 'Root', 'Home > ', '', '', D.PORTAL_LABEL || ' > ') || DECODE(C.PORTAL_LABEL, 'Root', 'Home > ', '', '', C.PORTAL_LABEL || ' > ') || DECODE(B.PORTAL_LABEL, 'Root', 'Home > ', '', '', B.PORTAL_LABEL || ' > ') || A.PORTAL_LABEL NAVIGATION FROM SYSADM.PSPRSMDEFN A, SYSADM.PSPRSMDEFN B, SYSADM.PSPRSMDEFN C, SYSADM.PSPRSMDEFN D, SYSADM.PSPRSMDEFN E WHERE A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME(+) AND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME(+) AND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME(+) AND D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME(+) AND (A.PORTAL_NAME = 'EMPLOYEE' OR A.PORTAL_NAME IS NULL) AND (B.PORTAL_NAME = 'EMPLOYEE' OR B.PORTAL_NAME IS NULL) AND (C.PORTAL_NAME = 'EMPLOYEE' OR C.PORTAL_NAME IS NULL) AND (D.PORTAL_NAME = 'EMPLOYEE' OR D.PORTAL_NAME IS NULL) AND (E.PORTAL_NAME = 'EMPLOYEE' OR E.PORTAL_NAME IS NULL) AND A.PORTAL_OBJNAME like UPPER('%COMPONENT NAME%') ORDER BY 2;