Page Nav
- User Tables
- Roles
- Permission Lists
- PeopleSoft Login Details and Authorization
- Query Security Tree
- Example SQL Queries
Security is a very important part of any PeopleSoft application. It determines what pages users can see all the way down to which rows. It may seem overwhelming at first but don’t worry; it’s pretty straightforward once you learn all the pieces and how they’re connected.
Below you will find a list of the Main PeopleTools Security Records along with a brief summary of their purpose. Knowing these tables can prove useful to quickly troubleshoot, determine which users have what security or even to audit user security regularly.
At the bottom of the page, you will find some example SQLs which are related to PeopleSoft security.
User Tables
| Record | Description |
|---|---|
| PSOPRDEFN | Stores all operators (users) in the PeopleSoft system. Also stores their employee ID (EMPLID), encrypted password, primary permission list, default navigator home page, process profile permission list and row security permission list. |
| PSROLEUSER | The highest level of security access is defined by roles (think of them as groups). This table stores the roles the user belongs to. |
| PSOPRCLS | Roles link together permission lists which are the security objects that define access to components, pages, and other areas of the system. This view returns the permission lists that a user has access to via their roles. Note that prior to PeopleTools 8, permission lists were synonymous with classes and most of the security tables still use this convention. |
| PSOPRALIAS | Aliases can be mapped to a particular operator ID (user). The obvious alias is employee ID (EMPLID) but others include external organisation ID (EXT_ORG_ID) and customer ID (CUST_ID). All ways of referring to the same entity. |
| PSOPRALIASTYPE | This is the setup table for operator aliases |
| PSOPRALIASFIELD | This is the setup table that maps operator aliases to records & fields |
| PSUSERATTR | User attributes store the a hint password question & response for a user (if this is enabled) |
| PSUSEREMAIL | Email addresses for users. |
Roles
| Record | Description |
|---|---|
| PSROLEDEFN | Stores roles and their properties. Roles can be assigned dynamically through Query, PeopleCode or LDAP. Roles are also used in conjunction with Workflow and routing. |
| PSROLECLASS | Roles are made of up of one or permission lists, and this table links the two together. Very handy. |
Permission Lists
| Record | Description |
|---|---|
| PSCLASSDEFN | Permission lists are where the security really happens. They provide access to menus, components and pages and a host of other security including PeopleTools, Process security, Component Interfaces, Web Libraries, Web Services, Personalisations, Query and Mass Change. |
| PSAUTHITEM | The link between permission lists and menus |
| PSAUTHBUSCOMP | The link between permission lists and component interfaces and their methods |
| PSAUTHOPTN | The link between permission lists and personalisations |
| PSAUTHPRCS | The link between permission lists and process groups |
| PSAUTHSIGNON | The link between permission lists and signon times |
| PSAUTHWEBLIBVW | A view linking permission lists and access to web libraries (really just Menus in PSAUTHITEM that begin with WEBLIB_). |
| PSAUTHWS | The link between permission lists and web services (service operations) |
| PS_SCRTY_ACC_GRP | The link between permission lists, trees and query access groups |
| PS_MC_OPR_SECURITY | The link between permission lists and mass change templates. This is an odd table, it uses the field OPRID but really it links permission lists |
PeopleSoft Login Details and Authorization
| Record | Description |
|---|---|
| PSACCESSLOG | Login and logout information of the users |
| PSACCESSPRFL | Contains the symbolic id,accessid/password details. |
| PSCLOCK | Login fails if the table is empty |
Query Security Tree
| Record | Description |
|---|---|
| PSTREENODE | Holds the query security tree records, which tree and tree node they belong to. |
Example SQL Queries
Get a list of users who logged in on a particular day
SELECT distinct a.oprid,
b.oprdefndesc AS "EMPLOYEE NAME",
to_char(a.logindttm, 'Month DD, YYYY') AS "LOGIN DATE"
FROM psaccesslog a
INNER JOIN psoprdefn b
ON (a.oprid not in ('PS', 'PSEM', 'PTWEBSERVER') and a.oprid = b.oprid)
WHERE trunc(a.logindttm) = '28-JUN-17';
Get a list of a users roles and associated permission lists
SELECT
a.oprid,
b.rolename,
d.classdefndesc
FROM psoprdefn a
INNER JOIN psroleuser b
ON (a.OPRID = 'PS508' and a.OPRID = b.roleuser)
INNER JOIN psroleclass c
ON b.rolename = c.rolename
INNER JOIN psclassdefn d
ON c.classid = d.classid;
Get a list of user roles, permissions and page access with menu path
SELECT DISTINCT d.roleuser,
a.rolename,
b.classid AS PERMISSION_LIST,
b.menuname,
b.baritemname AS "COMPONENT NAME",
b.pnlitemname,
c.descr AS "PAGE DESCRIPTION",
Decode(b.displayonly, 1, 'Y', 'N') AS "DISPLAY ONLY?",
e.navigation
FROM sysadm.psroleclass a,
sysadm.psauthitem b,
sysadm.pspnldefn c,
sysadm.psroleuser d,
(SELECT LEVEL0.portal_label
|| ' > '
|| LEVEL1.portal_label
|| ' > '
|| LEVEL2.portal_label
|| ' > '
|| level3.portal_label AS navigation,
level3.portal_uri_seg2 AS component
FROM psprsmdefn level3,
psprsmdefn level2,
psprsmdefn level1,
psprsmdefn LEVEL0
WHERE level3.portal_prntobjname = level2.portal_objname
AND level2.portal_prntobjname = level1.portal_objname
AND level1.portal_prntobjname = LEVEL0.portal_objname
AND level3.portal_name = level2.portal_name
AND level2.portal_name = level1.portal_name
AND level1.portal_name = LEVEL0.portal_name) e
WHERE a.classid = b.classid
AND b.pnlitemname = c.pnlname
AND a.rolename = d.rolename
--AND d.roleuser = 'USER'
--AND b.baritemname = 'COMPONENT NAME'
--AND d.rolename = 'ROLENAME'
--AND b.classid = 'MENU NAME'
AND e.component = b.baritemname
ORDER BY 1, 2, 3, 4, 5, 6;
Get Permission list sign on times in a human readable format:
select
CLASSID as "PERM LIST",
decode(dayofweek,
0, 'SUNDAY',
1, 'MONDAY',
2, 'TUESDAY',
3, 'WEDNESDAY',
4, 'THURSDAY',
5, 'FRIDAY',
6, 'SATURDAY'
) as "DAY",
to_char(to_date(STARTTIME*60, 'SSSSS'), 'HH12:mi AM') as "START TIME",
to_char(to_date(ENDTIME*60, 'SSSSS'), 'HH12:mi AM') as "END TIME"
from PSAUTHSIGNON