PeopleSoft Permission List Queries

1. Component Permission List Query:
This query identify 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;

2. 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;

FRMT = Frame Template

HPGC = Pagelet

HPGT = Homepage Tab

HTMT = HTML template

LINK = Content Reference Link

3. 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;

4. 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;

5. 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;

6. 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;