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'
