Data Change Auditing


Overview

In BBj 13.0 and higher, Data Change Auditing is the process of building an audit trail via Change Audit Jobs. Each job consists of a job name, location for the audit log database(s), a list of one or more directories and/or data files to be monitored, and the frequency at which the log database should rollover.

When an application changes a monitored data file using direct file access calls from a BBj program or via SQL, the auditing system logs the change and type of change to an “audit log database.” At any time, an administrator can query the log database using the interface built into the Enterprise Manager or query the log database tables directly using SQL.

When using change auditing, overhead is typically minimal since in most cases, administrators configure jobs to use the default asynchronous mode. In asynchronous mode, the audit system adds audit details to a background queue rather than waiting for the log operation to complete before continuing. In synchronous mode (not recommended) it completes the logging of the audit details before allowing the original write or remove operation on the file to complete.

Create an Audit Logging Job

To create an audit logging:

1.  Double-click on the “Audit Logging Jobs” item under the “File System” node in the Enterprise Manager navigator.

2.  On the Audit Logging Jobs panel, click the [+] button to open the new job wizard.

Basic Information


Audit Job Name

Description

DB Root Directory

The location used to store the audit logging databases. This must be an initially empty directory with write permissions for the user running BBjServices. Logged operations are written to the active audit database which is a BBj database containing a number of standard tables accessible via SQL. The name of each audit database contains the name of the job followed by the date the audit database was created and became active (see Viewing Audit Log Data below).

Job Type

Audit an entire configured database which includes the data dictionary files, data files, stored procedure definitions, etc. or audit a manually configured list of data files and directories.

Database To Audit

If a “Audit a Named Database Instance” was selected on the first page of the wizard, select the database to audit. On subsequent pages, the wizard provides the option to modify the list of files being audited by adding to the included files list or adding items to the excluded files list.

Other Job Settings


Rollover Type

How often the audit database should rollover to a new database. When rollover occurs, the existing audit database is left in tact and a new database is created and becomes active. Rollover frequency options are none, daily, weekly, monthly, or yearly.

Rollover Frequency

Number of units before rolling over to a new audit database. For example, for rollover type weekly, and a rollover frequency of 2, the audit database would rollover every 2 weeks.

Advisory Locking

Should the audit job use advisory locking.

Synchronous

With synchronous enabled, all write or remove operations will wait to return until the audit database is updated. This can drastically slow down write and remove operations in applications so it is typically not enabled.

Included Directories and Files

Provide the list of directories and/or files to be audited. Any file or directory in the list will be monitored for write operations (read operations are not monitored). When a WRITE or REMOVE BBj call occurs, or an INSERT, UPDATE, or DELETE SQL operation occurs on a monitored file, that information is written to the audit database. This list is updatable after the job has been created.

Excluded Subdirectories and Files

The Excluded Subdirectories and Files page provides a way to exclude certain files and directories from being audited. This list is updatable after the job has been created.

Audit Jobs and Current Status

The Audit Logging Jobs page displays a list of all currently configured audit jobs and their current status.

Name

Displays the unique name of the job.

Processed Through

Shows the last time the audit job was caught up in processing audited operations.

Status

Indicates whether the job is running or if an error has occurred that needs attention.

Editing an Existing Job

To edit an existing job, double-click on the job in the Audit Logging Jobs list. Note that not all settings are updatable after creating the job.

Please see the above section Create an Audit Logging Job for information regarding each item on this page. Once changes are saved to the existing audit job, the changes will take effect without the need to restart BBjServices.

Viewing Audit Log Data

Audit logging data resides in a BBj ESQL database so there are two ways to access the data: the Audit Log Viewer in the Enterprise Manager, or querying the database directly using SQL. To access the data via SQL, locate the audit database in the standard list of databases, making note of the date extension. The auditing system logs each type of operation to a different table in the audit database. However, the Audit Log Viewer makes it easier to quickly search and access this data without the need for SQL.

To access the audit log viewer for a job:

  1. Select the job from the list of audit jobs.

  2. Click the magnifier button in the toolbar to open the viewer.

 

Log Database

The specific audit log database to query. This provides the ability to view information from past audit databases still on the system.

File

Optionally limit the query to look at audit information for a particular file being audited. Leave it blank to include data from all files being audited.

User(s)

Optionally limit the query to one or more users. Use the [ ... ] button to open a list of all user accounts to choose from.

Operation Type

Optionally limit the query to look for specific types of operations. For example, select DELETE and REMOVE to only display SQL DELETE and BBj REMOVE operations. Leave this field blank to include all write operations. Use the [ ... ] button to open a list of operation types.

Start/End Date

Specify the range of dates to examine.

Timestamp

Displays the date and time the audited operation took place.

User

Displays the user who performed the audited operation.

File

Displays the file on which the operation occurred.

Operation Type

Displays the type of operation which occurred.

Program

In BBj 17.12 and higher, if the operation was performed from a BBj program (that is, not from a 3rd party ODBC or JDBC application), this indicates the name of the program.

In addition to general information about each audited operation, the viewer provides drill-down support to further investigate the details of each change. Double-clicking on an operation in the viewer opens another dialog that displays the record details. For example, an UPDATE_RECORD operation shows the old record and the new record after the change, while an INSERT_RECORD operation shows only the new record added to the file. A string template entry box makes it easier to evaluate the record data since the audit operation stores the record data in its raw format.

Benefits of an SQL Log Database

Using a BBj ESQL database for the log gives the administrator the ability to configure user access permissions to the audit database in the same way one would configure user permissions on any other BBj database. In addition, using iReport or BBJasper enables administrators to create more robust, limited, and/or customized reporting for others to view in an external application without the need to grant them access to the Enterprise Manager.



______________________________________________________________________________________

Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks.