PeopleSoft Security & Navigation Path

PeopleSoft Security:

5 core tables in the Security:

PSOPRDEFN - Stores user information

PSROLEUSER -  Stores user roles

PSROLECLASS — Permission Lists in roles

PSAUTHITEM — Menu items granted security by permission lists

PSAUTHBUSCOMP — Access to Component Interfaces

Master definition tables:

PSROLEDEFN — Role header table

PSCLASSDEFN — Permission List header table

User profiles define individual PeopleSoft users.Roles are intermediate objects that link user profiles to permission lists.Permission lists are groups of authorizations that you assign to roles.

PeopleSoft User security is organized as below.

User profiles (PSOPRDEFN) ->  Roles (PSROLEUSER) ->  Permission lists (PSROLECLASS).

Security Hierarchy:

One user can have multiple roles; one role can have multiple Permission lists. 

Objects Hierarchy:

Pages are attached to Components; Compoents are attached to Menu and Menus are assigned to Permission Lists. 

Tips:

1) How to reset the password from the back-end database - SQL:

update psoprdefn set OPERPSWD = '', acctlock = 0  WHERE oprid = '' ;

I store my ecnrypted password from another environment where my ID is working such as "DygLnRmZYrP43R6Dr02N", so my SQL will be,

update psoprdefn set OPERPSWD = 'DygLnRmZYrP43R6Dr02N', acctlock = 0  WHERE oprid = 'GGTESTID' ;

2) Password reset can be done via Data Mover Script:

update PSOPRDEFN
set OPERPSWD = 'new_password', ENCRYPTED = 0
where OPRID = '';

encrypt_password YOUR_USER_OPRID;

Advantage with Data Mover script is that you can use the readable password instead of encrypted in SQL above.

Find the page navigation:

If you know the Page or Component name but want to find the navigation path to access via PIA, use the following, 

Main Menu > Enterprise Components > Find Object Navigation.

Main Menu > Enterprise Components > Find Object Navigation.

Also, we can use the below query for the same.

SELECT DISTINCT REVERSE(LTRIM(SYS_CONNECT_BY_PATH 
(REVERSE(PORTAL_LABEL),' >- '),' >- ')) "NAVIGATION" 
FROM PSPRSMDEFN P 
WHERE PORTAL_OBJNAME = 'PORTAL_ROOT_OBJECT' 
START WITH PORTAL_URI_SEG2 = 'Component Name' 
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME 
AND PORTAL_NAME = PRIOR PORTAL_NAME 
AND PORTAL_REFTYPE = 'F'