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_descFROM psauthitem auth,psmenudefn menu,psmenuitem menuitm,pspnlgroup comp,pspnlgrpdefn compdfn,psclassdefn CLASSWHERE menu.menuname = menuitm.menunameAND menuitm.pnlgrpname = comp.pnlgrpnameAND compdfn.pnlgrpname = comp.pnlgrpnameAND compdfn.pnlgrpname LIKE UPPER (:component_name)AND auth.menuname = menu.menunameAND auth.barname = menuitm.barnameAND auth.baritemname = menuitm.itemnameAND auth.pnlitemname = comp.itemnameAND auth.classid = CLASS.classidGROUP BY menu.menuname, compdfn.pnlgrpname, auth.classid, CLASS.classdefndescORDER 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_reftypeFROM psprsmdefn a, psprsmperm b, psclassdefn cWHERE a.portal_reftype = 'C'AND a.portal_cref_usgt = 'TARG'AND a.portal_name = b.portal_nameAND a.portal_reftype = b.portal_reftypeAND a.portal_objname = b.portal_objnameAND c.classid = b.portal_permnameAND a.portal_uri_seg1 <> ' 'AND a.portal_uri_seg2 <> ' 'AND a.portal_uri_seg3 <> ' 'AND c.classid = :permissionlistAND a.portal_name = :portalnameORDER 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.menunameFROM psclassdefn a, psauthitem bWHERE a.classid = b.classidAND ( 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_listFROM psclassdefn a, psroleclass bWHERE a.classid = b.classid AND a.classid = :permissionlist;6. User IDs assigned to a Permission List:SELECT c.roleuser AS USER_IDsFROM psclassdefn a, psroleclass b, psroleuser cWHERE a.classid = b.classidAND b.rolename = c.rolenameAND a.classid = :permissionlistGROUP BY c.roleuser;