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 Professional users, Self Service 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 the application 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 application must be monitored
• Change logging is configurable by table and by column
• Change logging is done where auditing is required as follows:
  • In the application, all of the change logging is done via Java code in the middle tier
  • 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
• Sessions are fully tracked and more extensively audited than in the past
• 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

• 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 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 within the application by the use of the Show Change History icon

Implementing Detailed Change Auditing#

• Detailed Change Auditing can be turned on or off for a table or individual column by using the ‘Logging Not Allowed” toggles in IMTD and IMCD.
• If clients do not want changes to be captured in certain tables or columns the Logging Not Allowed toggle should be turned ON.
• If logging is currently enabled and users wish to disable it, the ‘Disable Logging’ should be turned ON.
• There are certain tables that have been predetermined that Logging Not Allowed due to the volume of data that these tables can hold. A few examples of these tables are P2K_PR_PAY_PC_AMOUNTS, P2K_PR_PAY_LINES, and P2K_PR_PAY_LINE_DETAILS.

View Change Logs and Audit Details#

VMCL#

• The VMCL screen is used to view the Data Change Logs per table. The header section of the form displays the date the change was made, which user made the change, what action was done and in what function.
• Context 1 will hold (in XML format) the Parent Context information; this is based on the Unique Keys of the changed record and its parents.
• Context 2 will hold (in XML format) all columns and values except those with domains of VARCHAR2000 & VARCHAR4000.
• The details section shows which columns were changed, when the change took place and what the before and after values are.
• When a change takes place, but before the values are committed to the database, a Change Log record is created without the Context columns populated. When the changes are committed to the database, the context columns are added to the new Change Log Record, and the Change Log Details records are created for every changed column and added to the database.
• If logging is turned on for a table and turned off for a changed column no Change Log Details record will be created.
• If there is an exception between the time the Change Log is created and before the Change Log Details and context information are created, then Context 1 will hold a message indicating that Auditing was not complete.
• The Logged in As will display the IMUS user name associated to the employee if logged into the application while making changes. If logged into Self Service while making the change the user’s Person Code will be displayed. Changes mapped to roles will display the role name. If a change is made in Self Service that is tied to a Personnel Action, the IMUS user name of the individual who processes the PA will display in the Logged In As field.
• Context 1 will show the person code, candidate code or recruiter code of the user logged in making the change.

Show Change History Dialog#

• It is possible to view the historical changes associated with each record within a screen. This can be done via the “Show Change History’ dialog. This dialog is displayed by pressing the CHANGE_HISTORY.JPG icon found in the header of all forms or by pressing F10.
• The dialog will display the following information for each column within the record in focus: time the change was made, what action was done, in what function, who made the change and what the before and after values are.
• A derived field was added to the Change History dialog to display the System Accessor code for each change log detail. Therefore, if a Professional user is logged in and has made a change the user name will be displayed as the one who made the change, if logged into Self Service while making the change the person code will be displayed, changes mapped to roles will display role name, recruiter will display the recruiter code and candidates will display candidate code.
• The second tab will display the change history for any user fields associated to the table.

Show Audit Details#

• The Show Audit Details dialog displays the current information for each record on the table that is currently in focus.
• The dialog can be called up by pressing F12 or by pressing the AUDIT_DETAILS.JPG icon
• The user can navigate through the list of column names to determine the changed value for each column on the table in focus.
• The dialog can be used to determine the row id of the parent table as well as the dependent table.
• The user can determine when the last change was made to the table and by whom by reviewing the Change Date and Change User columns.
• The dialog also displays a preference value should one exist for a column within the table in focus.