Getting Started on a PeopleSoft 7'x Audit

1
Getting Started on a PeopleSoft (7.x) Audit

• Session T3
• Tuesday, March 18, 2003
• HEUG 2003 Conference - Dallas

2
Agenda

• Introduction
• Stanford Us PeopleSoft environment
• Session Objectives (highlights)
• Prepare you for a basic Peoplesoft 7.6 audit
• PeopleSoft Architecture, Overview of menus and
  panels
• Audit Program Sample queries
• Conclusion

3
IntroductionPresenter Background

• Present Position - Information Technology
  Controls Specialist, Stanford. Responsible for
  performing IT controls audits, application
  reviews, pre-implementation reviews.
• Over 10 years of combined Internal Audit (Banks)
  and External Audit (CL) experience.
• No intensive PeopleSoft training. Learnt
  PeopleSoft on the job. Big 6 firm did the
  Pre-implementation Review of PeopleSoft Student
  Administration, HR and Payroll. Tasked to
  follow up on these recommendations. Part of the
  learning process was building on that base.

4
Stanfords PeopleSoft Environment

• Stanfords PeopleSoft (7.6) suite
• Student Administration, HRMS
• PeopleTools 7.62
• UNIX Sun Solaris 2.7
• DBMS Oracle 8.1.6.3
• Plans to upgrade to PeopleSoft Version 8.
• Currently implementing Oracle Financials 11i.
  (PeopleSoft Payroll and HR have to interface with
  Oracle Financial Tables)

5
Session Objectives

• This presentation assumes that all attendees
  have conducted audits before and are familiar
  with basic Information Controls Audit
  methodology. This session will
• Give you ideas on How to Get Started if you have
  never had any training on PeopleSoft, and prepare
  you for a basic PeopleSoft Audit (7.6)
• Briefly explain PeopleSoft Architecture
• Snapshot views of the PS Security Administrator
  (7.6)
• Share the Audit Program and queries I used
• Overview of PeopleSoft panels and tables
• Sample findings
• Briefly discuss high level differences between
  7.x and 8.x

6
How to Get Started

• Check out the following resources
• PSSAC-L_at_listserv.it.northwestern.edu
• little 10 edp auditors discussion list
  ltEDP10_at_LIST.UVM.EDUgt
• Request your PeopleSoft Security administrator to
  give you read only access to Security
  Administrator, PS Query tool, and Application
  designer.
• Investigate the 4 As Architecture,
  Authentication, Authorization, and Auditing
• Get familiar with the online PeopleBooks manual
  for PeopleSoft Applications and PeopleTools.
• Read PeopleSoft Books and reference documentation
  on PeopleSoft Customer Connection Web site.
• Articles on http//www.theiia.org/itaudit/
  there are at least one or 2 articles on the
  subject.

7
PeopleSoft Architecture

• Layers of Security Look behind all the doors
  before you even
• get to the PeopleSoft door

----------------------Physical
Security------------------------
8

PeopleSoft Architecture 3 tier 7.x and 8.x
9
GogtPeopletoolsgtSecurity Administrator
Security Administrator Menu Table - PSOPERDEFN
Views of the Security Administrator Menu Table -
PSOPERDEFN
Security Administrator look up an operator
10
xx
The Classes this operator (me) belongs to Table -
PSOPRCLS
Primary Class other associated classes provide
the user cumulative privileges to
11
What Menu Items /Panels I have access to Table
- PSAUTHITEM
12
Audit Program- Risk Based
Risks Some Examples. Discuss with owners what
keeps them up at night

• Unauthorized users have access to the PeopleSoft
  application
• Lack of accountability for transactions -
  inadequate or no audit trail
• Users are granted access to business processes
  and functional areas not required for their job
  duties
• Unauthorized creation or modification of queries
  within the PeopleSoft application
• Access to sensitive information and information
  not required to perform job functions
• Unauthorized access to payroll and personnel
  information
• Inadequate segregation of duties
• Inadequate monitoring and exception reporting
  mechanism
• Unauthorized changes to programs and table
  structures

13
Audit Program- Objectives

• Audit objectives should be no different in
  PeopleSoft. For example in an PeopleSoft HR
  /Payroll Audit
• separation of duties (can someone hire a new
  employee and process the payroll?)
• access controls (is salary information
  confidential and restricted?)
• management controls (can payroll clerks
  authorize pay rate changes?)
• To satisfy the above objectives and document the
  controls to mitigate the risks identified, you
  have to develop an audit program

14
Audit Program

• The audit program will generally have the
  following sections.
• Application Security
• Physical Security
• IT Change Management (change request from cradle
  to grave)
• Backup and Recovery
• Systems Interfaces Controls
• Business Process Controls
• Todays Session focus Application Security

15
Audit Program - Prepare

• Obtain or prepare a diagram showing the current
  components that make up the PeopleSoft
  architecture. Obtain or draw a flowchart
  including components such as, development,
  testing, and production servers application
  servers, database servers, and WEB servers, as
  well as University backbone routers, bridges and
  gateways that connect the various PeopleSoft
  components
• Gather documents related to
• Security policies and charter
• Security administration
• Request forms

16
Audit Program - Prepare

• Request access to PeopleSoft production
• SQL access (Select Row only access to the DBMS
  tables)
• Display-only to panels
• Sit with functional experts and document the
  mission critical business processes and the
  determine if they audit the critical tables.
  PeopleSoft has the ability to audit at Field or
  Records level.
• Keep in mind that the default is no auditing.

17
Audit Program

• Important PeopleSoft Security Tables (These
  tables should only be updated by Security
  administrator)
• PSOPRDEFN Operator ID and password
  information. Passwords stored are encrypted.
  (Note Operator in PeopleSoft is USER).
• PSAUTHITEM Menus, panels and items an Operator
  is authorized to access.
• PSAUTHSIGNON Records sign-on times for all
  Operators.
• PSAUTHPRCS Records process groups for all
  Operators.

18
Audit Program

• Important PeopleSoft Security Tables (These
  tables should only be updated by Security
  administrator)
• PSACCESSPRFL Security table used to allow a
  super-user to create an access profile that
  would contain an Access ID and Access Password.
  Access ID and Passwords are used to connect
  PeopleSoft to the underlying database. (only in
  version 7 and above).
• PSOPRCLS Maps one Operator to multiple Operator
  Classes. (only in version 7 and above).

19
Audit Program

• Other tables depending on the module that you may
  be interested in
• HR/Payroll tables

20
Audit Program
Source SQL script against the PeopleSoft Table
(PSOPERDEFN) Purpose To get a list of all the
operators and users - review the PSOPERDEFN Table
- password, timeout, oprids, operator classes
spool path_desc\PSOPRDEFN.out SELECT
SUBSTR(OPERPSWD,1,length(OPERPSWD))''SUBSTR(A
UTHITEMCOUNT,1,length(AUTHITEMCOUNT))''SUBSTR
(OPRCLASS,1,length(OPRCLASS))''SUBSTR(OPRDEFN
DESC,1,length(OPRDEFNDESC))''SUBSTR(OPRID,1,l
ength(OPRID))''SUBSTR(TIMEOUTMINUTES,1,length
(TIMEOUTMINUTES))''SUBSTR(OPRTYPE,1,length(OP
RTYPE))'' 'l' FROM PSOPRDEFN spool off
21
This is the actual table layout for PSOPERDEFN
PSOPRCLS
22
Audit Program

• Known Password Deficiencies in 7.x
• User password and ID can be the same-logon screen
  bypassed
• Users dont have to change default passwords
• Passwords dont expire
• No minimum length, no integrity checks
• No log-off after failed attempts
• Permits a previously used password
• The above is addressed in 8.x

23
Audit Program

• Operators/users can belong to multiple operator
  classes. How do you get this list? SQL script to
  get all classes from -PSOPRCLS

spool path_desc\PSOPRCLS.out SELECT
SUBSTR(OPRCLASS,1,length(OPRCLASS))''SUBSTR(O
PRID,1,length(OPRID))'' 'l' FROM PSOPRCLS
spool off GET THE CRITICAL CLASSES FROM BUSINESS
CONFER DEGREE, PAY PEOPLE AND THEN SEE WHO ALL
BELONG TO THOSE CLASSES
24
Audit Program

• Operators/users can belong to multiple operator
  classes. How do you get this list? SQL Query to
  get all classes from PSOPRCLS
• Who are in the following operator classes
  ALLPANLS, PSADMIN and any other PS delivered
  classes ?
• Are these individuals monitored ?
• Between PSOPERDEFN and PSOPRCLS you should also
  check the following
• Sign on times assigned
• Timeout is appropriate
• Since passwords do not expire does a third-party
  handle it?

25
Audit Program
Source SQL script against the PeopleSoft Tables
(PSAUTHITEM) Purpose To review authorized
actions and access of operator classes spool
path_desc\PSAUTHITEM.out SELECT
SUBSTR(AUTHORIZEDACTIONS,1,length(AUTHORIZEDACTION
S))''SUBSTR(BARITEMNAME,1,length(BARITEMNAME)
)''SUBSTR(BARNAME,1,length(BARNAME))''SU
BSTR(DISPLAYONLY,1,length(DISPLAYONLY))''SUBS
TR(MENUNAME,1,length(MENUNAME))''SUBSTR(OPRID
,1,length(OPRID))''SUBSTR(PNLITEMNAME,1,lengt
h(PNLITEMNAME))'' 'l' FROM PSAUTHITEM where
OPRID in ('ALLPANLS','ITUSR','SUPRUSER','VIEWONLY'
,'SA types only or substitute with classes you
are interested in) spool off
26
Audit Program
Above table decodes Authorized Actions in the
PSAUTHITEM table Purpose To review who has
Correction mode What is Correction mode With
correction mode you can change history Note this
should be limited. There should be policy
surrounding this. For what conditions do you need
correction mode Also there should be some
understanding on who gets what need to know
basis
27
Audit Program

• Query Security Who has it
• How is it managed
• Ad-hoc queries
• Public queries
• Number of joins/unions
• Limit rows returned

28
PeopleTools / Utilities / Use / Query Security
29
Audit Program

• spool path_desc\PS_SCRTY_QUERY.out
• SELECT SUBSTR(OPRID,1,length(OPRID))''
  SUBSTR(QRY_MAX_JOINS,1,length(QRY_MAX_JOINS))'
  ' 'l' FROM PS_SCRTY_QUERYspool off

This query will tell you which classes have PS
Query
30
Overview of PeopleSoft Panels and Tables
31
Type in the Emplid and click search
32
(No Transcript)
33
(No Transcript)
34
File open
35
Go Peopletools Application Designer File Open
36
View of the Panel. Place cursor on the field and
right click. For eg Compensation Rate
37
Double click on View definition
38
Brings up the table sitting behind the panel.
Table includes all the field names and type.
Double click on the field that you are interested
in
39
This particular field is audited. You have to ask
your business owners what level of auditing they
are expecting
40
Audit Program

• To find out what tables are being audited the
  following SQL query
• SELECT DISTINCT RECNAME FROM PSAUDIT ORDER BY
  RECNAME
• For example the following tables could be audited
  in the Stanford environment
• ADJ_TERM_TBL
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• CLST_MAIN_TBL
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• GL_INTERFACE
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• GL_INT_DT_TBL
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• JOB
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• JOB_REQUISITION
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• PAY_OTH_EARNS
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• RQ_MAIN_TBL
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• Find out who is reviewing these tables, how
  often, and purge/archive process (if any)

41
Audit Program

• Additional security considerations
• Row-Level Security restricts what a user may
  select to access within the database (Department
  Tree in PeopleSoft HRMS)
• Process Groups security - restricts which
  processes a user can run using the Process
  Scheduler
• Field Level Security restricts access to
  specific sensitive data fields.
• Object Level Security- uses the operator classes
  and operator IDs to restrict object level
  security. PeopleSoft is delivered with no
  application object security. All users granted
  PeopleTools access have complete access to all
  PeopleTools objects. This is more relevant for
  the IT folks

42
Audit Program-Sample Findings
43
Audit Program-Sample Findings
44
Major differences between 7.x and 8

• More Compatible with E-commerce Apps
• Not Platform Dependent
• Individual Desktops aren't configured, just
  require Internet Browser
• Wider User Base Availability
• Has Password Management features

45
Major differences between 7.x and 8

• Requires a Web Server and Report Server
• Requires a more Robust Application Server
• New Security Issues due to increased availability
  of access
• PS7 can assign multiple roles to a user but each
  role has one permission list (class)
• PS8 can assign multiple roles to a user and
  multiple permission lists to a role. You will
  need to obtain and review a list of permissions
  granted in PSCLASSDEFN. Panels/menus called
  pages.
• PS8 permits dynamic roles granting access based
  on position or a business role

46
Session Objectives- Recap

• This presentation assumes that all attendees
  have conducted audits before and are familiar
  with basic Information Controls Audit
  methodology. This session will
• Give you ideas on How to Get Started if you have
  never had any training on PeopleSoft, and prepare
  you for a basic PeopleSoft Audit (7.6)
• Briefly explain PeopleSoft Architecture
• Snapshot views of the PS Security Administrator
  (7.6)
• Share the Audit Program and queries I used
• Overview of PeopleSoft panels and tables
• Sample findings
• Briefly discuss high level differences between
  7.x and 8.x

47
Conclusion Questions

• PeopleSoft is a complicated animal due to its
  integrated nature. Literally thousands of tables.
  
• Basic query on these tables - help the business
  owners and the security administrator by taking
  an independent look at access control,
  segregation of duties etc.
• Willing to share more queries for HR/Payroll and
  Student Systems. Can send via email

48
Ranjita ChakravartyInformation Technology
Controls Specialist, Stanford Universityranjita_at_
stanford.eduPhone (650-725-4428)HTTP//HEUG.OR
G (attendees may download HEUG2003 presentations
from the archives at this location)
Contact Information