11/24/2015 © SpearMC Consulting 1© 2014 SpearMC Consulting
Advanced PeopleSoft Security Audit
By David Pigman
© 2014 SpearMC Consulting
2
Your Presenter
David PigmanTechnical [email protected] x804www.SpearMC.com
Certified professional with over ten years of consulting experience in all phases of
large scale implementations
Leads SpearMC’s Application Development and Technical Infrastructure initiatives.
PeopleSoft Integration Expert and Developer
Agenda
About SpearMC
Objectives
User Profile Flow / Records
Application Object Hierarchy / Records
Portal Objects
Sample Queries
© 2014 SpearMC Consulting
3
© 2014 SpearMC Consulting
5
Founded in 2004, SpearMC is a technology and professional services firm specializing in
PeopleSoft Financials and Supply Chain Management
PeopleSoft Human Capital Management and Payroll
ERP Architecture and Application Development
Project and Program Management (PMO)
Change Management
ERP Training
Mobile Technology
About SpearMC
© 2014 SpearMC Consulting
6
We focus on local market delivery and capability development in line with industry focus areas unique to the regions we serve.
Regional offices in Chicago, Oklahoma City, Seattle and San Francisco
About SpearMC
Our local and well-connected network of top-tier business analysts, technical leads, developers and project managers keep travel cost to a minimum.
© 2014 SpearMC Consulting
8
Each member of our Leadership Team leverages over 20 years of experience in Technology, Financial and Business Transformation.
About SpearMC
© 2014 SpearMC Consulting
9
Industry focus areas include:
Energy
Transportation
Healthcare/Biotech
Government
Media/Technology
Consumer Products
Financial Services
Higher Education
About SpearMC
© 2014 SpearMC Consulting
10
Oracle Gold Partner since 2008:
Gold Level Partner
PeopleSoft Specialization
Applications Reseller
Hardware Reseller
About SpearMC
© 2014 SpearMC Consulting
12
Objectives
• Learn the record definitions and views that support
PeopleSoft Security
• Resolve highly complex security data into views for
use with PeopleSoft Query
© 2014 SpearMC Consulting
14
User Profile Flow
User Profile(UserID/OPRID)
Record - PSOPRDEFNPrimary Permission List andRow Security Permission List
(Row Level Security) Record - PSOPRDEFN
Process Profile Permission ListRecord - PSOPRDEFN
RolesJoin Record – PSROLEUSERRecord - PSROLEDEFN
Permissions ListsJoin Record - PSROLECLASS
© 2014 SpearMC Consulting
15
User Profile Flow
User Profile(UserID/OPRID)
Record - PSOPRDEFN
Primary Permission List andRow Security Permission List
(Row Level Security) Record - PSOPRDEFN
Process Profile Permission List
Record - PSOPRDEFN
RolesRecord - PSROLEUSER
PeopleSoft determines which data permissions to grant a user by looking at the user's Primary Permission List and Row Security Permission List.
Which one is used varies by application and data entity (Employee, Customer, Vendor, Business Unit, etc.) PeopleSoft determines Mass Change, and Object Security permissions from the Primary Permission List.
© 2014 SpearMC Consulting
16
User Profile Flow
Permissions ListsJoin Record – PSROLECLASS
Record - PSCLASSDEFN
Sign-OnRecord - PSAUTHSIGNON
Pages/Menu ItemsRecord - PSAUTHITEM
Process GroupRecord - PSAUTHPRCS
QueryRecord - SCRTY_QUERY
Application DesignerRecord - PSAUTHITEM
Message MonitorRecord - PSAUTHCHNLMON
Component InterfaceRecord - PSAUTHBUSCOMP
Misc. Tools
© 2014 SpearMC Consulting
17
Record Definitions PeopleSoft Security
Operator Definition (PSOPRDEFN)
OPRID (User ID)EMPLID (EmplID)OPRCLASS (Primary Permission List)ROWSECCLASS (Row Security Permission)PRCSPRFLCLS (Process Profile Permission List)LASTUPDOPRID (Last Update User ID)LASTUPDDTTM (Last Update Date/Time)
Role Definition (PSROLEDEFN)
ROLENAME (Role Name)
ROLETYPE (Role Type) -U-User or Q-Query to route Workflow
LASTUPDOPRID (Last Update User ID)
LASTUPDDTTM (Last Update Date/Time)
Role User (PSROLEUSER)
ROLEUSER (User/Operator ID) -based on OPRID
ROLENAME (Role Name)
DYNAMIC_SW (Dynamic)
© 2014 SpearMC Consulting
18
Record Definitions PeopleSoft Security
Permission Lists Definition (PSCLASSDEFN)
CLASSID(Permission List)
CLASSDEFNDESCR (Permission List Description)
TIMEOUTMINUTES (Time-out Minutes)
STARTAPPSERVER (Can Start Application Server)
ALLOWPSWDEMAIL (Allow Password to be EMailed)
LASTUPDOPRID (Last Update User ID)
LASTUPDDTTM (Last Update Date/Time)
Role Classes (PSROLECLASS)
ROLENAME (Role Name)
CLASSID (Permission List)
Authorized Signon Period (PSAUTHSIGNON)
CLASSID (Permission List)
DAYOFWEEK(Day Of Week)
STARTTIME (Start Time)
ENDTIME (End Time)
Process Profile (PSPRCSPRFL)
CLASSID (Permission List)
SRVRDESTFILE (Server File Destination)
SRVRDESTPRNT (Server Print Destination)
© 2014 SpearMC Consulting
19
Record Definitions PeopleSoft Security
Authorized Process Groups (PSAUTHPRCS)
CLASSID (Permission List)
PRCSGRP (Process Definition Group)
PS/Query Profile (SCRTY_QUERY)
CLASSID (Permission List)
QRY_RUN_ONLY (Only Allowed to run Queries)
QRY_CREATE_PUBLIC (Allow create of Public Queries)
QRY_CREATE_WFLOW (Allow create of Wrkflw Query)
QRY_MAX_FETCH (Maximum Rows Fetched)
QRY_MAX_RUN (Maximum Run Time in Minutes)
QRY_ADV_DISTINCT (Allow use of Distinct)
QRY_ADV_ANY_JOIN (Allow use of Any Join)
QRY_ADV_SUBQUERY (Allow use of Subquery/Exists)
QRY_ADV_UNION (Allow use of Union)
QRY_ADV_EXPR (Allow use of Expressions)
QRY_MAX_JOINS (Maximum Joins Allowed)
© 2014 SpearMC Consulting
20
Record Definitions PeopleSoft Security
Access Group Security (SCRTY_ACC_GRP)
CLASSID (Permission List)
TREE_NAME (Tree Name)
ACCESS_GROUP (Access Group)
ACCESSIBLE (Accessible)
Component Interface Security (PSAUTHBUSCOMP)
CLASSID (Permission List)
BCNAME (Business Component Name)
BCMETHOD(Method)
AUTHORIZEDACTIONS (Authorized Actions)
Authorized Menu Items (PSAUTHITEM)
CLASSID (Permission List)
MENUNAME (Menu Name) -prompts PSMENUDEFN
BARNAME (Bar Name)
BARITEMNAME (Bar Item Name)
PNLITEMNAME (Page Item Name)
DISPLAYONLY (Display Only)
AUTHORIZEDACTIONS (Authorized Actions)
© 2014 SpearMC Consulting
21
Query Definition XXPT_RLPMUSRoles, Perms & User Profiles
Chosen Record s
PSOPRDEFN (Operator Definition)
PSROLEUSER (Role User)
PSROLECLASS (Role Classes)
PSCLASSDEFN (Permission Lists Definition)
PSROLEDEFN (Role Definition)
Fields Order
ROLEUSER (UserID) 1
OPRDEFNDESC (User ID Descr)
ROLENAME (Role Name) 2
DESCR (Role Descr)
CLASSID (Permission List) 3
CLASSDEFNDESC (Perm List Descr)
© 2014 SpearMC Consulting
22
Query Definition XXPT_RLPMUSRoles, Perms & User Profiles
Query Criteria
© 2014 SpearMC Consulting
23
Query Definition XXPT_RLPMUSRoles, Perms & User Profiles
Prompt Edit -ROLEUSER
© 2014 SpearMC Consulting
25
Permission List – Check Boxes
Navigation: PeopleTools - Security - Permission & Roles - Permission Lists. Select the PeopleTools Tab
PeopleTools PermissionsMenu Names (PSAUTHITEM.MENUNAME)
DATA_MOVER –Data Mover Access
APPLICATION_DESIGNER –Application Designer Access
OBJECT_SECURITY –Definition Security Access
QUERY_MANAGER –Query Access
PERFMONPPMI –Performance Monitor PPMI Access
Data ArchivalFields for Record PS_ARCH_SECURITY
ARCH_SEC_EDIT - Run SQL
ARCH_SEC_RUN –Edit SQL
© 2014 SpearMC Consulting
26
Permission List – Check Boxes
Navigation: PeopleTools - Security - Permission & Roles - Permission Lists. General Tab
Permission List General/Time-out MinutesFields for Record PSCLASSDEFN
STARTAPPSERVER –Can Start Application Server?
ALLOWPSWDEMAIL–Allow Password to be Emailed?
SERVERTIMEOUT –Never Time-out &
Specific Time-out (minutes)
© 2014 SpearMC Consulting
27
Permission List – Check Boxes
Navigation: PeopleTools -> Security -> Permission & Roles -> Perm Lists. Select the Query Tab and Click Query Profile
Permission List Query ProfileFields for Record SCRTY_QUERY
QRY_RUN_ONLY -Only Allowed to run Queries
QRY_CREATE_PUBLIC -Allow create of Public Queries
QRY_CREATE_WFLOW -Allow create of Workflow Query
QRY_MAX_FETCH -Maximum Rows Fetched
QRY_MAX_RUN -Maximum Run Time in Minutes
QRY_ADV_DISTINCT -Allow use of Distinct
QRY_ADV_ANY_JOIN -Allow use of 'Any Join'
QRY_ADV_SUBQUERY -Allow use of Subquery/Exists
QRY_ADV_UNION -Allow use of Union
QRY_ADV_EXPR -Allow use of Expressions
© 2014 SpearMC Consulting
28
XXPT_DATA_MOVER_PMData Mover Access PM
Record s Definitions
PSAUTHITEM (Authorized Menu Item)
PSCLASSDEFN (Permission Lists Definition)
Fields Order
CLASSID (Permission Lists)
CLASSDEFNDESC (Permission List Descr)
MENUNAME (Menu Name)
Query Criteria
© 2014 SpearMC Consulting
31
Application Object Hierarchy
Menu Group – PSMENUDEFN (Record) Name: Administer Workforce
Menu Name– PSMENUDEFNObject: – MAINTAIN_VENDORSDescr: – (Blank)
Menu Item– PSMENUITEMKeys: Menu, Menu Bar, Menu Item, Component Menu: MAINTAIN_VENDORS/(blank)Menu Bar: USE/UseMenu Item: VENDOR_INFORMATION/
Vendor InformationComponent: VNDR_ID/Vendors
Component – PSPNLGRPDEFNPNLGRPNAMEObject/Descr: VNDR_ID1_SUM/Vendor SummaryVNDR_ID1/Vendor IDVNDR_ADDRESS/Vendor AddressVNDR_CONTACT/(blank)VNDR_LOC/(blank)VNDR_CUSTOM/User Definable Vendor FieldsEtc...ACTION - Add - Update/Display –Update/Display All – Correction
Component/Page– PSPNLGROUP (Record)Keys: Component/PageTable used to join Components to Pages
Page – PSPNLDEFNObject: VNDR_ID_SUM/Vendor Summary
© 2014 SpearMC Consulting
32
Record DefinitionsApplication Objects
Menu Item (PSMENUITEM)
MENUNAME (Menu Name) -prompts Menu Definition (PSMENUDEFN)
BARNAME (Menu Bar Name)
ITEMNAME(Item Name) *** Links to PSAUTHITEM.BARITEMNAME
ITEMNUM (Item Number)
ITEMTYPE (Item Type)
PNLGRPNAME (Component Name) *** Links to PSPNLGROUP.PNLGRPNAME
MARKET (Market)
BARLABEL (Menu Bar Label)
ITEMLABEL (Menu Item Label) *** Label for ITEMNAME -shows in the Navigation
XFERCOUNT (Page Transfer Count)
SEARCHRECNAME (Search Record Name)
Menu Definition (PSMENUDEFN)
• MENUNAME (Menu Name)
• MENUGROUP (Menu Group)
• MENULABEL (Menu Label)
© 2014 SpearMC Consulting
33
Record DefinitionsApplication Objects
Component Group Definition (PSPNLGRPDEFN)
PNLGRPNAME (Component Name)
MARKET (Market)
SEARCHRECNAME (Search Record Name)
ACTIONS (Actions)
© 2014 SpearMC Consulting
34
Record DefinitionsApplication Objects
Component Group (PSPNLGROUP)
PNLGRPNAME (Component Name) -base d on Component
Definition (PSPNLGRPDEFN)
MARKET (Market)
PNLNAME(Page Name) -base d on Page Definition
(PSPNLDEFN)
SUBITEMNUM (Sub Item Number)
ITEMNAME (Item Name)
ITEMLABEL (Menu Item Label)
FOLDERTABLABEL (Folder Tab Label)
HIDDEN (Hidden)
Page Definition (PSPNLDEFN)
PNLNAME (Page Name)
LANGUAGE_CD (Language Code)
PNLTYPE (Page Type)
© 2014 SpearMC Consulting
37
Application Object Vendor Page PeopleTools Objects
Menu: MAINTAIN VENDORS
Component: VNDR_ID
© 2014 SpearMC Consulting
39
XXPT_PMAUTH_VW(SpearMC Custom View)
Resolves the Actions that have been granted to a
menu/bar/item/component/page for a particular permission list
BARITEMNAME changed to ITEMNAME for intuitive table joins
© 2014 SpearMC Consulting
40
XXPT_PMAUTH_VW(SpearMC Custom View)
SpearMC PSAUTHITEM (XXPT_PMAUTH_VW)
CLASSID
MENUNAME
BARNAME
ITEMNAME
PNLITEMNAME
DISPLAYONLY
AUTHORIZEDACTIONS
ACTIONTYPE
Add Update/Display
Update/Display -All
Correction SpearMC Code
ACTIONTYPE1 X A2 X UD3 X X A UD4 X UDA5 X X A UDA6 X X UD UDA7 X X X A UD UDA8 X C9 X X A C10 X X UD C11 X X X A UD C12 X X UD C13 X X X A UD C14 X X X UD UDA15 X X X X A UD UDA
V (Display Only)
© 2014 SpearMC Consulting
41
XXPT_PMAUTH_VW(SpearMC Custom View)
SQL Definition
SELECT CLASSID, MENUNAME
, BARNAME, BARITEMNAME
, PNLITEMNAME, DISPLAYONLY
, AUTHORIZEDACTIONS
, CASE AUTHORIZEDACTIONS WHEN 1 THEN 'A' WHEN 2 THEN 'UD' WHEN 4 THEN 'UDA' WHEN 8 THEN 'C'
WHEN 3 THEN 'A UD' WHEN 5 THEN 'A UDA' WHEN 9 THEN 'A C' WHEN 6 THEN 'UD UDA' WHEN 10 THEN
'UD C' WHEN 12 THEN 'UDA C' WHEN 7 THEN 'A UD UDA' WHEN 11 THEN 'A UD C' WHEN 13 THEN 'A UDA
C' WHEN 14 THEN 'UD UDA C' WHEN 15 THEN 'A UD UDA C' END
FROM PSAUTHITEM
© 2014 SpearMC Consulting
42
XXPT_PMAUTH_VW(SpearMC Custom View)
• Resolves the Object Hierarchy for use in Reporting
• Turns encrypted Action numbers into legible codes Action 15 is resolved to A UD UDA C for Add – Update Display – Update
Display All -Correction
• Two custom fields XX_PIA_PATH and
XX_PIA_LBL_PATH provide object and object label
navigation pathsMAINTAIN_VENDORS --> USE --> VENDOR_INFORMATION --> VNDR_ID
Administer Procurement --> &Maintain Vendors --> &Use --> Vendor &Information
--> VNDR_ID
© 2014 SpearMC Consulting
43
XXPTMENU_PIA_VW(SpearMC Custom View)
PIA Navigation (XXPTMENU_PIA_VW)
MENUNAME
BARNAME
ITEMNAME
PNLGRPNAME
MARKET
ACTIONS
MENUGROUP
MENULABEL
ITEMLABEL
BARLABEL
XX_PIA_PATH
XX_PIA_LBL_PATH
ACTIONTYPE
Add Update/Display
Update/Display -All
Correction SpearMC Code
ACTIONTYPE1 X A2 X UD3 X X A UD4 X UDA5 X X A UDA6 X X UD UDA7 X X X A UD UDA8 X C9 X X A C10 X X UD C11 X X X A UD C12 X X UD C13 X X X A UD C14 X X X UD UDA15 X X X X A UD UDA
© 2014 SpearMC Consulting
44
XXPTMENU_PIA_VW(SpearMC Custom View)
SQL Definition
SELECT MD.MENUNAME , MI.BARNAME , MI.ITEMNAME, PG.PNLGRPNAME , PG.MARKET
, GD.ACTIONS , MD.MENUGROUP , MD.MENULABEL , MI.BARLABEL , MI.ITEMLABEL
,'c/' %Concat RTRIM(MD.MENUNAME) %Concat '.' %Concat RTRIM(PG.PNLGRPNAME) %Concat '.' %Concat RTRIM(PG.MARKET) AS URL_1
, RTRIM(MD.MENUNAME) %Concat ' --> ' %Concat RTRIM(MI.BARNAME) %Concat ' --> ' %Concat RTRIM(MI.ITEMNAME) %Concat ' --> '
%Concat RTRIM(PG.PNLGRPNAME)
, RTRIM(MD.MENULABEL) %Concat ' --> ' %Concat RTRIM(MI.BARLABEL) %Concat ' --> ' %Concat RTRIM(MI.ITEMLABEL) %Concat ' --> '
%Concat RTRIM(PG.PNLGRPNAME)
, CASE GD.ACTIONS WHEN 1 THEN 'A' WHEN 2 THEN 'UD' WHEN 4 THEN 'UDA' WHEN 8 THEN 'C' WHEN 3 THEN 'A UD' WHEN 5 THEN 'A
UDA' WHEN 9 THEN 'A C' WHEN 6 THEN 'UD UDA' WHEN 10 THEN 'UD C' WHEN 12 THEN 'UDA C' WHEN 7 THEN 'A UD UDA' WHEN 11
THEN 'A UD C' WHEN 13 THEN 'A UDA C' WHEN 14 THEN 'UD UDA C' WHEN 15 THEN 'A UD UDA C' END AS ACTIONTYPE FROM
PSMENUDEFN MD , PSMENUITEM MI , PSPNLGROUP PG , PSPNLGRPDEFN GD WHERE MD.MENUNAME = MI.MENUNAME AND
MI.PNLGRPNAME = PG.PNLGRPNAME AND MI.MARKET = PG.MARKET AND PG.PNLGRPNAME = GD.PNLGRPNAME
GROUP BY MD.MENUNAME, MI.BARNAME, MI.ITEMNAME, PG.PNLGRPNAME, PG.MARKET, GD.ACTIONS, MD.MENUGROUP,
MD.MENULABEL, MI.BARLABEL, MI.ITEMLABEL
© 2014 SpearMC Consulting
47
Query Definition: XXPT_AUTHITEM_PM PSAUTHIEM by PM
Record s Definitions
XXPT_PMAUTH_VW (Component Security)
XXPTMENU_PIA_VW (Menu PIA)
PSPNLGROUP –Panel Group
Fields Order
CLASSID (Permission List) 1
MENUNAME (Menu Name) 2
PNLGRPNAME (Component Name) 3
PNLNAME (Panel Name) 4
ACTIONTYPE (Action Type)
ACTIONTYPE (Action Type)
XX_PIA_PATH (PIA Navigation)
XX_PIA_LBL_PATH (PIA Label Navigation)
© 2014 SpearMC Consulting
49
Query Definition: XXPT_AUTHITEM_PMAUTHITEM by PM
Prompt Edit -MENUNAME Prompt Edit -PNLGRPNAME