Monday, November 11, 2013

PeopleSoft: SQL to know the Users, who can access the Page




SELECT DISTINCT B.MENUNAME, B.BARNAME, B.BARITEMNAME, D.PNLNAME, C.PAGEACCESSDESCR, B.DISPLAYONLY, F.ROLEUSER
FROM PSROLECLASS A, PSAUTHITEM B, PSPGEACCESSDESC C, PSPNLGROUP D, PSMENUITEM E, PSROLEUSER F
WHERE  B.MENUNAME  =  E.MENUNAME
     AND B.BARNAME =  E.BARNAME
     AND B.BARITEMNAME =  E.ITEMNAME
     AND D.PNLGRPNAME = E.PNLGRPNAME
     AND A.CLASSID =  B.CLASSID
     AND B.MENUNAME NOT IN ('APPLICATION_DESIGNER','OBJECT_SECURITY','QUERY','PERFMONPPMI','IMPORT_MANAGER','CLIENTPROCESS','DATA_MOVER')
     AND B.MENUNAME NOT LIKE 'WEBLIB%'
     AND B.AUTHORIZEDACTIONS = C.AUTHORIZEDACTIONS
     AND A.ROLENAME = F.ROLENAME
     AND D.PNLNAME = 'PAGE_NAME'
  ORDER BY 2

No comments:

Post a Comment