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 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#

  • 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#

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 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 an eP 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 the icon or by pressing F12.
  • 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 dependant 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.

This particular version was published on 26-Nov-2021 10:22 by JEscott.Top