PeopleSoft Security related Queries (Permission List, Roles etc)

Component Permission List Query: This query identifies the permission lists and its description associated with component. SELECT MENU.MENUNAME, COMPDFN.PNLGRPNAME, AUTH.CLASSID PERMISSION_LIST, CLASS.CLASSDEFNDESC PERMISSION_DESC FROM PSAUTHITEM AUTH, PSMENUDEFN MENU, PSMENUITEM MENUITM, PSPNLGROUP COMP, PSPNLGRPDEFN COMPDFN, PSCLASSDEFN CLASS WHERE MENU.MENUNAME = MENUITM.MENUNAME AND MENUITM.PNLGRPNAME = COMP.PNLGRPNAME AND COMPDFN.PNLGRPNAME = COMP.PNLGRPNAME AND COMPDFN.PNLGRPNAME LIKE UPPER (:COMPONENT_NAME) AND AUTH.MENUNAME = MENU.MENUNAME AND AUTH.BARNAME = MENUITM.BARNAME AND AUTH.BARITEMNAME = MENUITM.ITEMNAME AND AUTH.PNLITEMNAME = COMP.ITEMNAME AND AUTH.CLASSID = CLASS.CLASSID GROUP BY MENU.MENUNAME, COMPDFN.PNLGRPNAME, AUTH.CLASSID, CLASS.CLASSDEFNDESC ORDER BY MENU.MENUNAME, COMPDFN.PNLGRPNAME, PERMISSION_LIST; Content Reference accessed by a permission list: This query identifies Content references accessed by Permission List. SELECT a.portal_label AS PORTAL_LINK_NAME, a.portal_objname, a.portal_name, a.portal_reftype FROM psprsmdefn a, psprsmperm b, psclassdefn c WHERE a.portal_reftype = 'C' AND a.portal_cref_usgt = 'TARG' AND a.portal_name = b.portal_name AND a.portal_reftype = b.portal_reftype AND a.portal_objname = b.portal_objname AND c.classid = b.portal_permname AND a.portal_uri_seg1 <> ' ' AND a.portal_uri_seg2 <> ' ' AND a.portal_uri_seg3 <> ' ' AND c.classid = :permissionlist AND a.portal_name = :portalname ORDER BY portal_label; Page Access By Permission List: SELECT B.MENUNAME, B.BARNAME, B.BARITEMNAME, B.PNLITEMNAME AS PAGENAME, C.PAGEACCESSDESCR, DECODE (B.DISPLAYONLY, 0, 'NO', 1, 'YES') AS DISPLAYONLY FROM PSCLASSDEFN A, PSAUTHITEM B, PSPGEACCESSDESC C WHERE A.CLASSID = B.CLASSID AND A.CLASSID = :1 AND B.BARITEMNAME > ' ' AND B.AUTHORIZEDACTIONS = C.AUTHORIZEDACTIONS; PeopleTools Accessed By a Permission List: SELECT DISTINCT B.MENUNAME FROM PSCLASSDEFN A, PSAUTHITEM B WHERE A.CLASSID = B.CLASSID AND ( B.MENUNAME = 'CLIENTPROCESS' OR B.MENUNAME = 'DATA_MOVER' OR B.MENUNAME = 'IMPORT_MANAGER' OR B.MENUNAME = 'APPLICATION_DESIGNER' OR B.MENUNAME = 'OBJECT_SECURITY' OR B.MENUNAME = 'QUERY' ) AND A.CLASSID = :PERMISSIONLIST; Roles Assigned to a Permission List: SELECT B.ROLENAME, B.CLASSID AS PERMISSION_LIST FROM PSCLASSDEFN A, PSROLECLASS B WHERE A.CLASSID = B.CLASSID AND A.CLASSID = :PERMISSIONLIST; User IDs assigned to a Permission List: SELECT C.ROLEUSER AS USER_IDS FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C WHERE A.CLASSID = B.CLASSID AND B.ROLENAME = C.ROLENAME AND A.CLASSID = :PERMISSIONLIST GROUP BY C.ROLEUSER;