WIXLIB

Advanced PeopleSoftSecurity AuditDavid PigmanSpearMC Consultingwww.spearmc.com1

AGENDA About SpearMCObjectivesUser Profile Flow / RecordsApplication Object Hierarchy / RecordsPortal ObjectsSample QueriesQ&A5/27/201222

ABOUT SPEARMCSpearMC is a full-service consulting andtechnology services firm with specific focus onPeopleSoft FinancialsOur consultants and network of PeopleSoftAnalysts, Technical Leads and Project Managersaverage fifteen years of PeopleSoftexperienceWe are North America’s leading provider ofcustom-tailored PeopleSoft Financial trainingsolutions and educational content development.It is our mission to provide the highest levels ofprofessional service at competitive rates3

About the AuthorDavid Pigman, Technical Architectdpigman@spearmc.com866-SPEARMC x802

OBJECTIVES Learn the record definitions and views that support PeopleSoftsecurityResolve highly complex security data into views for use withPeopleSoft Query5

USER PROFILE FLOWUser Profile(UserID/OPRID)Record - PSOPRDEFNPrimary Permission List andRow Security Permission List(Row Level Security)Record - PSOPRDEFNProcess Profile Permission ListRecord - PSOPRDEFNRolesJoin Record – PSROLEUSERRecord - PSROLEDEFNPermissions ListsJoin Record - PSROLECLASS6

USER PROFILE FLOWUser Profile(UserID/OPRID)Record - PSOPRDEFNPrimary Permission List andRow Security Permission List(Row Level Security)Record - PSOPRDEFNProcess Profile Permission ListRecord - PSOPRDEFNRolesRecord - PSROLEUSER7PeopleSoft determines which datapermissions to grant a user bylooking at the user's PrimaryPermission List and Row SecurityPermission List.Which one is used varies byapplication and data entity(Employee, Customer, Vendor,Business Unit, etc.) PeopleSoftdetermines Mass Change, andObject Security permissions fromthe Primary Permission List.

USER PROFILE FLOWPermissions ListsJoin Record – PSROLECLASSRecord - PSCLASSDEFNApplication DesignerRecord - PSAUTHITEMSign-OnRecord - PSAUTHSIGNONMessage MonitorRecord - PSAUTHCHNLMONPages/Menu ItemsRecord - PSAUTHITEMComponent InterfaceRecord - PSAUTHBUSCOMPProcess GroupRecord - PSAUTHPRCSMisc. ToolsQueryRecord - SCRTY QUERY8

RECORD DEFINITIONS – PEOPLESOFT SECURITYOperator Definition (PSOPRDEFN)Role Definition (PSROLEDEFN)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)ROLENAME (Role Name)ROLETYPE (Role Type) - U-User or Q-Query to routeWorkflowLASTUPDOPRID (Last Update User ID)LASTUPDDTTM (Last Update Date/Time)Role User (PSROLEUSER)ROLEUSER (User/Operator ID) - based on OPRIDROLENAME (Role Name)DYNAMIC SW (Dynamic)9

RECORD DEFINITIONS – PEOPLESOFT SECURITYRole 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)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)Process Profile (PSPRCSPRFL)CLASSID (Permission List)SRVRDESTFILE (Server File Destination)SRVRDESTPRNT (Server Print Destination)10

RECORD DEFINITIONS – PEOPLESOFT SECURITYAuthorized Process Groups (PSAUTHPRCS)PS/Query Profile (SCRTY QUERY)CLASSID (Permission List)PRCSGRP (Process Definition Group)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)11

RECORD DEFINITIONS – PEOPLESOFT SECURITYAuthorized Menu Items (PSAUTHITEM)Access Group Security (SCRTY ACC GRP)CLASSID (Permission List)TREE NAME (Tree Name)ACCESS GROUP (Access Group)ACCESSIBLE (Accessible)CLASSID (Permission List)MENUNAME (Menu Name) - prompts PSMENUDEFNBARNAME (Bar Name)BARITEMNAME (Bar Item Name)PNLITEMNAME (Page Item Name)DISPLAYONLY (Display Only)AUTHORIZEDACTIONS (Authorized Actions)Component Interface Security(PSAUTHBUSCOMP)CLASSID (Permission List)BCNAME (Business Component Name)BCMETHOD (Method)AUTHORIZEDACTIONS (Authorized Actions)12

QUERY DEFINITION: SMC CO USPMRL – USERIDS ROLES PERMSChosen Record sFieldsPSOPRDEFN (Operator Definition)PSROLEUSER (Role User)PSROLECLASS (Role Classes)PSCLASSDEFN (Permission Lists Definition)PSROLEDEFN (Role Definition)ROLEUSER (UserID)OPRDEFNDESC (User ID Descr)ROLENAME (Role Name)DESCR (Role Descr)CLASSID (Permission List)CLASSDEFNDESC (Perm List Descr)13Order123

QUERY DEFN: SMC CO USPMRL – USERIDS, ROLES &PERMISSIONSQuery Criteria14

QUERY DEFN: SMC CO USPMRL – USERIDS, ROLES &PERMISSIONSPrompt Edit - ROLEUSER15

QUERY RESULTS: SMC CO USPMRL – USERIDS, ROLES &PERMS16

PERMISSION LIST – CHECK BOXESNavigation: PeopleTools - Security - Permission & Roles - Permission Lists. Selectthe PeopleTools TabPeopleTools PermissionsMenu Names (PSAUTHITEM.MENUNAME)DATA MOVER – Data Mover AccessAPPLICATION DESIGNER – Application Designer AccessOBJECT SECURITY – Definition Security AccessQUERY MANAGER – Query AccessPERFMONPPMI – Performance Monitor PPMI AccessData ArchivalFields for Record PS ARCH SECURITYARCH SEC EDIT - Run SQLARCH SEC RUN – Edit SQL17

PERMISSION LIST – CHECK BOXESNavigation: PeopleTools - Security - Permission & Roles - PermissionLists. General TabPermission List General/Time-out MinutesFields for Record PSCLASSDEFNSTARTAPPSERVER – Can Start Application Server?ALLOWPSWDEMAIL– Allow Password to be Emailed?SERVERTIMEOUT – Never Time-out &Specific Time-out (minutes)18

PERMISSION LIST – CHECK BOXESPermission List Query ProfileFields for Record SCRTY QUERYNavigation: PeopleTools - Security - Permission &Roles - Perm Lists. Select the Query Tab and ClickQuery ProfileQRY RUN ONLY - Only Allowed to run QueriesQRY CREATE PUBLIC - Allow create of Public QueriesQRY CREATE WFLOW - Allow create of Workflow QueryQRY MAX FETCH - Maximum Rows FetchedQRY MAX RUN - Maximum Run Time in MinutesQRY ADV DISTINCT - Allow use of DistinctQRY ADV ANY JOIN - Allow use of 'Any Join'QRY ADV SUBQUERY - Allow use of Subquery/ExistsQRY ADV UNION - Allow use of UnionQRY ADV EXPR - Allow use of Expressions19

SCM CO DATA MOVER PM – DATA MOVER ACCESS PMRecord s DefinitionsFieldsPSAUTHITEM (Authorized Menu Item)PSCLASSDEFN (Permission Lists Definition)CLASSID (Permission Lists)CLASSDEFNDESC (Permission List Descr)MENUNAME (Menu Name)Query Criteria20Order

QUERY RESULTS: SMC CO DATA MOVER PM – DATA MOVER ACCESS PM21

APPLICATION OBJECT HIERARCHYMenu Group – PSMENUDEFN (Record)Name: Administer WorkforceComponent/Page– PSPNLGROUP (Record)Keys: Component/PageTable used to join Components to PagesMenu Name– PSMENUDEFNObject: – MAINTAIN VENDORSDescr: – (Blank)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 – CorrectionMenu Item– PSMENUITEMKeys: Menu, Menu Bar, Menu Item, ComponentMenu: MAINTAIN VENDORS/(blank)Menu Bar: USE/UseMenu Item: VENDOR INFORMATION/Vendor InformationComponent: VNDR ID/VendorsPage – PSPNLDEFNObject: VNDR ID SUM/Vendor Summary22

RECORD DEFINITIONS – APPLICATION OBJECTSMenu Definition (PSMENUDEFN)Menu Item (PSMENUITEM)MENUNAME (Menu Name) - prompts Menu Definition (PSMENUDEFN)BARNAME (Menu Bar Name)ITEMNAME (Item Name)*** Links to PSAUTHITEM.BARITEMNAMEITEMNUM (Item Number)ITEMTYPE (Item Type)PNLGRPNAME (Component Name) *** Links to PSPNLGROUP.PNLGRPNAMEMARKET (Market)BARLABEL (Menu Bar Label)ITEMLABEL (Menu Item Label) *** Label for ITEMNAME - shows in the NavigationXFERCOUNT (Page Transfer Count)SEARCHRECNAME (Search Record Name)23 MENUNAME (Menu Name) MENUGROUP (Menu Group) MENULABEL (Menu Label)

RECORD DEFINITIONS – APPLICATION OBJECTSComponent Group Definition (PSPNLGRPDEFN)PNLGRPNAME (Component Name)MARKET (Market)SEARCHRECNAME (Search Record Name)ACTIONS (Actions)24

RECORD DEFINITIONS – APPLICATION OBJECTSComponent Group (PSPNLGROUP)Page Definition (PSPNLDEFN)PNLGRPNAME (Component Name) - base d onComponent 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)PNLNAME (Page Name)LANGUAGE CD (Language Code)PNLTYPE (Page Type)25

APPLICATION OBJECT – VENDOR PAGE26

APPLICATION OBJECT – VENDOR PAGE – PRESS CTRL - J27

APPLICATION OBJECT – VENDOR PAGE PEOPLETOOLS OBJECTSMenu: MAINTAIN VENDORSComponent: VNDR ID28

SMC PMAUTH VW (SPEARMC CUSTOM VIEW)Resolves the Actions that have been granted to amenu/bar/item/component/page for a particular permission listBARITEMNAME changed to ITEMNAME for intuitive table joins29

SMC PMAUTH VW (SPEARMC CUSTOM VIEW)SpearMC PSAUTHITEM (SMC PMAUTH 131415Update/DisplayUpdate/Display arMCCodeACTIONTYPEAUDA UDUDAA UDAUD UDAA UD UDACACUD CA UD CUD CA UD CUD UDAA UD UDAV (DisplayOnly)

SMC PMAUTH VW (SPEARMC CUSTOM VIEW)SQL DefinitionSELECT 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 6THEN '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 15THEN 'A UD UDA C' ENDFROM PSAUTHITEM31

SMC MENU PIA VW (SPEARMC CUSTOM VIEW) Resolves the Object Hierarchy for use in Reporting Turns encrypted Action numbers into legible codesAction 15 is resolved to A UD UDA C for Add – Update Display – Update DisplayAll -Correction Two custom fields SMC PIA PATH and SCM PIA LBL PATHprovide object and object label navigation pathsMAINTAIN VENDORS -- USE -- VENDOR INFORMATION -- VNDR IDAdminister Procurement -- &Maintain Vendors -- &Use -- Vendor &Information-- VNDR ID32

SMC MENU PIA VW (SPEARMC CUSTOM VIEW)PIA Navigation (SMC MENU PIA ENUGROUPMENULABELITEMLABELBARLABELSMC PIA PATHSMC PIA LBL ayUpdate/Display arMCCodeACTIONTYPEAUDA UDUDAA UDAUD UDAA UD UDACACUD CA UD CUD CA UD CUD UDAA UD UDA

SMC MENU PIA VW (SPEARMC CUSTOM VIEW)SQL DefinitionSELECT 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 ' -- ' %ConcatRTRIM(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 UDC' 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.PNLGRPNAMEGROUP BY MD.MENUNAME, MI.BARNAME, MI.ITEMNAME, PG.PNLGRPNAME, PG.MARKET, GD.ACTIONS, MD.MENUGROUP,MD.MENULABEL, MI.BARLABEL, MI.ITEMLABEL34

SMC MENU PIA VW (SPEARMC CUSTOM VIEW) RESULTS35