PeopleSoft Security with Navigation Paths for a Permission List, Role or User

This SQL allows you to view permissions and the associated navigation.  You can provide a Permission List name, Role name or User ID, or any combination of the those items to filter your results.  You should be prompted for the bind variables when run in SQL Developer.  If not values are provided it would run for all users and permission lists in your environment, which I would not recommend.

This is something that comes in handy when reviewing or auditing a user's permissions and providing the relevant navigation makes a lot more sense to most people than providing just the menu and component would.  There would obviously be better, and more efficient, ways to report this information, but for a one-off request or troubleshooting I like the speed and flexibility of SQL.  This code can also be adapted into many other navigation and security queries. 

-- ALL NAVIGATION FOR A GIVEN SECURITY ROLE, CLASSID or USER
SELECT DISTINCT RC.ROLENAME, RC.CLASSID, AI.MENUNAME, AI.BARITEMNAME, MI.PNLGRPNAME, CRPATH.NAVPATH
,DECODE(AI.DISPLAYONLY, 0, 'N', 1, 'Y') AS "Display Only"
,CASE AI.AUTHORIZEDACTIONS
    WHEN 1 THEN 'Add'
    WHEN 2 THEN 'Update/Display'
    WHEN 3 THEN 'Add, Update/Display'
    WHEN 4 THEN 'Update/Display All'
    WHEN 5 THEN 'Add, Update/Display All'
    WHEN 6 THEN 'Update/Display, Update/Display All'
    WHEN 7 THEN 'Add, Update/Display, Update/Display All'
    WHEN 8 THEN 'Correction'
    WHEN 9 THEN 'Add, Correction'
    WHEN 10 THEN 'Update/Display, Correction'
    WHEN 11 THEN 'Add, Update/Display, Correction'
    WHEN 12 THEN 'Update/Display All, Correction'
    WHEN 13 THEN 'Add, Update/Display All, Correction'
    WHEN 14 THEN 'Update/Display, Update/Display All, Correction'
    WHEN 15 THEN 'Add, Update/Display, Update/Display All, Correction'
    ELSE 'SPECIAL' END AS "Authorized Actions"
FROM PSROLEUSER RU, PSROLECLASS RC, PSAUTHITEM AI
LEFT OUTER JOIN PSMENUITEM MI
  ON MI.MENUNAME = AI.MENUNAME
  AND MI.BARNAME = AI.BARNAME 
  AND MI.ITEMNAME = AI.BARITEMNAME
LEFT OUTER JOIN 
 (SELECT CONTREFROOT, CONTREFURISEG2, CONTREFURISEG3, LISTAGG(PORTAL_LABEL,' > ') WITHIN GROUP (ORDER BY CBLEVEL DESC) AS NAVPATH 
  FROM (
    SELECT LEVEL AS CBLEVEL
    , CONNECT_BY_ROOT PR.PORTAL_OBJNAME AS CONTREFROOT
    , CONNECT_BY_ROOT PR.PORTAL_URI_SEG2 AS CONTREFURISEG2
    , CONNECT_BY_ROOT PR.PORTAL_URI_SEG3 AS CONTREFURISEG3
    , CASE WHEN PR.PORTAL_LABEL = ' ' THEN '[no label]' ELSE PR.PORTAL_LABEL END AS PORTAL_LABEL
    FROM PSPRSMDEFN PR
    START WITH PR.PORTAL_REFTYPE = 'C'
      AND PR.PORTAL_NAME = 'EMPLOYEE'  -- PORTAL NAME     
      AND PR.PORTAL_CREF_USGT = 'TARG'
    CONNECT BY NOCYCLE PRIOR PR.PORTAL_PRNTOBJNAME = PR.PORTAL_OBJNAME
      AND PR.PORTAL_NAME = 'EMPLOYEE') 
    GROUP BY CONTREFROOT, CONTREFURISEG2, CONTREFURISEG3
 ) CRPATH
 ON CRPATH.CONTREFURISEG2 = MI.PNLGRPNAME
WHERE 
-- THESE ARE THE PROMPTED VALUES
(RU.ROLEUSER LIKE coalesce(:roleuser,'%') and RC.ROLENAME LIKE coalesce(:rolename,'%') AND RC.CLASSID LIKE coalesce(:classid,'%'))
AND AI.CLASSID = RC.CLASSID
AND RC.ROLENAME = RU.ROLENAME
ORDER BY RC.ROLENAME, RC.CLASSID, AI.MENUNAME, AI.BARITEMNAME, MI.PNLGRPNAME;