DETAILED CHANGE AUDITING#
Detailed change auditing was created to fulfill the need to identify “who” has changed “what” data, “when” and “how”. The rules of what data to log are configurable by table and column. The scope of detailed change auditing embraces all changes made by P2K users, eP users, SS users, Candidates and external users (e.g. SQL*Plus).
Features#
Session Identification#
- All interactive and batch sessions will be tracked in the P2K_AM_SESSION_AUDITS table. The ID column of this table uniquely identifies a session.
- A Session Audit record is created when a user logs in and is closed when a user logs out or times out.
- All “audit related” messages written to the J2EE server logs will show this session ID.
- Additional information is added to the Session Audit table to identify more types of users, the database roles that have been granted, the SS focus role (if applicable) and the source of the session.
- Update processes done via Oracle reports will be treated as separate sessions.
- To enforce tight audit controls, sessions that are initiated outside ePersonality and P2K that must update the database, will be forced to do the following:
- Request update access to the database by invoking a PL/SQL procedure called P2K_PMSESSION.LOGIN
- A Session Audit record will be automatically created
- Provide an additional password (assigned to the “EXTUSER” user name)
- Notify the system when they are finished by invoking a PL/SQL function called P2K_PMSESSION.LOGOFF
- The associated Session Audit record will be closed
- If external users do not log off, their session will time out after a specified amount of time, if possible
Change Monitoring#
- All database changes made inside and outside the ePersonality and P2K applications must be monitored
- Change logging is configurable by table and by column
- Change logging is done where auditing is required as follows:
- In ePersonality, all of the change logging is done via Java code in the middle tier
- In P2K, all change logging is done via database triggers (created by P2K_SMLTT) which have been enhanced to prevent un-authorized changes from taking place.
- In external applications, all change logging is done via database triggers; changes will only be permitted if the user has logged in and has a Session Audit record
- ePersonality sessions are fully tracked and more extensively audited than in the past
- P2K sessions will be tracked and audited the same as in the past with enhancements to say that they are from Reports or Forms, etc.
- External sessions like SQL*Plus will be tracked and audited in a limited manner.
Auditing#
- Who made the change?
- Could be any of the following:
- eP user - has a User Name on IMUS and should have a Person Code on IEID
- SS user - has a Person Code and User Profile on IEID
- Candidate - has a Candidate Code on IRCA
- P2K user - has a User Name on IMUS (may or may not have a Person Code on IEID)
- Batch Process user - has a User Name on IMUS
- Oracle user - if change is made outside the application
- Recruiter – has a Recruiter code on IRRE
- Could be any of the following:
- What was changed?
- Described by a table & row (MTD_ID and row reference ID) and a list of columns (MCD_ID)
- Will capture the before and after value of all changes, the after value of all inserts and the before value of all deletes
- Context information required for all parent key information will be stored in XML format
e.g. Distribution 1
of Assignment Detail effective 01 Jan 2006
of Assignment 01
of Employment with High Line
for Person # 000001
- Context information required on deletes for all child information will be stored in XML format
- Where was the change made?
- The user session - may be related to P2K, eP, SS, CSS, other
- The function being used - e.g. IEAS
- The action performed - Insert, Date Sensitive Change, Normal Change, Delete
- When was the change made?
- The timestamp of the change will be captured
~Change Logs
- A change log header will be created immediately for every change; each header will identify the associated Session Audit record
- When deletes occur, the system will cascade through the child records and audit the child information if auditing is turned on for the child table (database cascade deletes do not fire the triggers)
- The change logs are searchable by user, function, table, timeframe, etc.
- It is possible to view the historical changes associated with each block on every screen of eP by the use of the Show Change History icon
