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;