User Security Queries for PeopleSoft

Post Level         : Advanced/Good to Know

Target Readers : Beginners to Experts

1. Users for a particular component.

Select distinct UR.ROLEUSER, PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, MI.PNLGRPNAME, PC.PNLITEMNAME
from PSROLEUSER UR, PSROLECLASS RP, PSAUTHITEM PC,
PSMENUITEM MI
where UR.ROLENAME = RP.ROLENAME
and RP.CLASSID = PC.CLASSID
and PC.MENUNAME = MI.MENUNAME
and PC.BARNAME = MI.BARNAME
and PC.BARITEMNAME = MI.ITEMNAME 
and MI.PNLGRPNAME = '<Component Name>'
order by UR.ROLEUSER, PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, PC.PNLITEMNAME;

Sample Output:

5 Good and Bad Practices

 5 Good Practices

1.     Save as a copy before making changes to a complex page, records and other objects that you can not easily revert in case something goes wrong.

2.     Define a common prefix for all custom objects you are going to create. Preferablly, it should be clients initials e.g. if you are creating a page for Xavier University name the page as XU_PAGE_NAME_PG.

People Books

PeopleTools Release 8.48 (PT)
PeopleTools Release 8.49 (PT)
PeopleTools Release 8.50 (PT)
PeopleTools Release 8.51 (PT)

Get Navigation Using Component Name

Select distinct rtrim(reverse(sys_connect_by_path(reverse(portal_label), ' > ')), ' > ') path

from psprsmdefn

where portal_name = 'EMPLOYEE'

and portal_prntobjname = 'PORTAL_ROOT_OBJECT'

start with portal_uri_seg2 LIKE upper('<Component Name>')

connect by prior portal_prntobjname = portal_objname

Note: This query doesn't work with DB2 Database.

Sample Output: