Database Auditing

<< Click to Display Table of Contents >>

EQuIS 7  >>  Professional > Databases > Admin >

Database Auditing

EQuIS Auditing

 

EQuIS provides optional data auditing functionality that will track changes to individual values in the database. By default, auditing is not enabled, but may be enabled on the desired table(s) using the equis.add_audit stored procedure. Auditing may be enabled on as many tables as desired; however, auditing does increase database load (particularly while loading/editing data). EarthSoft advises assessing your auditing needs carefully and only enable auditing after careful consideration.

 

The equis.add_audit stored procedure requires two input parameters:

@schema_name – The name of the Schema in which the table resides.

@table_name – The name of the table for which auditing will be enabled.

 

For example, to enable auditing on the DT_LOCATION table, a database administrator would execute the following command:

exec equis.add_audit 'dbo','dt_location';

 

The equis.add_audit stored procedure will do the following:

Ensure that the EUID column on the table is populated for all records in the table.

Make the EUID column a required column.

Configure auditing as follows for the specified table:

oINSERT – Only the EUID column (for performance and storage purposes, will likely not want to audit INSERT for all columns in the table).

oUPDATE – All columns in the table.

oDELETE – All columns in the table.

 

This configuration is applied by adding the following triggers to the table:

trg_{table_name}_instead_of_insert: Ensures that the EUID column is always populated (assigns new EUID values, if necessary).

trg_{table_name}_after_insert: Adds record(s) to the XT_AUDIT table whenever records are inserted in the target table.

trg_{table_name}_after_update: Adds record(s) to the XT_AUDIT table whenever records are updated in the target table.

trg_{table_name}_after_delete: Adds record(s) to the XT_AUDIT table whenever records are deleted from the target table.

 

XT_AUDIT

 

The XT_AUDIT table contains the audit information that is generated by the audit triggers (as explained above).

 

Column

Data Type

Description

AUDIT_TYPE

char(1)

The type of operation being performed on the database (i.e. I = Insert,
D = Delete, U = Update).

AUDIT_UTC_DATE

datetime2

The date the operation was performed in UTC time.

OBJECT_EUID

int

The EUID of the record that has been inserted, deleted or modified.

COL_ID

smallint

A number that identifies the column on which the operation was performed (foreign key to XT_COLUMN).

USER_ID

int

The USER_ID (foreign key to ST_USER) of the user who initiated the operation.

IPV4

int

A four-byte integer that represents the four bytes of the IPv4 address of the machine from which the operation was performed. This value may be converted to a text representation of the IP address by calling equis.int_to_ipv4.

OLD_VALUE

varchar(4000)

The previous value of the column (always null for insert).

NEW_VALUE

varchar(4000)

The new value of the column (always null for delete).

 

Example

 

AUDIT_TYPE

AUDIT_UTC_ DATE

OBJECT_ EUID

COL_ID

USER_ID

IPV4

OLD_VALUE

NEW_VALUE

I

2013-06-14 21:44:56.643

125

531

1412

2130706433

 

UT

I

2013-06-14 21:44:56.643

125

532

1412

2130706433

 

UTA

U

2013-06-14 21:49:13.920

125

532

1412

2130706433

UTA

Utah

D

2013-06-15 00:21:26.157

125

531

1412

2130706433

UT

 

D

2013-06-15 00:21:26.157

125

532

1412

2130706433

Utah

 

 

The XT_AUDIT table is optimized for performance, and uses some integer values instead of meaningful text values (e.g. COL_ID instead of table/column name, USER_ID instead of user name). When viewing audit data, it may be simpler to use the VW_AUDIT view, which includes the appropriate joins/functions to show meaningful text values (e.g. table name, column name, user name).

 

Enable Auditing on Large Tables

 

Invoking equis.add_audit on a large table (e.g. DT_RESULT) may take a long time (several minutes or even hours) and may affect other concurrent database activity. EarthSoft advises that auditing be enabled during off peak hours, so other users are not adversely affected.

 

Adding Columns after Auditing Enabled

 

If a new field is added to a table after auditing has already been enabled on that table, the triggers will not automatically handle the new field. Because the new field is not listed in the triggers, data cannot be inserted into that field (nor will data for that field be audited). The following script illustrates this scenario (do not run this script in a production database).

 

-- add auditing to a table

exec equis.add_audit 'dt_location'

 

-- add field AFTER auditing is turned on

alter table dt_location add custom_field_6 varchar(100)

 

-- value inserted into new field is NOT actually inserted into the table because of the INSTEAD OF INSERT trigger

insert dt_location ( facility_id, sys_loc_code, custom_field_6 ) values ( 1, 'XYZ2', 'CUSTOM FIELD 6')

 

-- CUSTOM FIELD 6 is not populated in the target table nor in XT_AUDIT

select facility_id, sys_loc_code, custom_field_6 from dt_location

select * from xt_audit

 

To recreate/refresh the triggers on a given table, use the equis.refresh_schema stored procedures as follows:

 

exec equis.refresh_schema 'dbo','dt_location';

 

Note: The EQuIS database update process automatically calls equis.refresh_schema for all tables at the end of the update process.

 

Advanced Auditing Configuration

 

Starting with EQuIS 7, auditing can be enabled per column (within a table) and per operation (e.g. audit UPDATE but not DELETE). This advanced configuration is controlled by the XT_COLUMN table, which contains a record for every column in the database. The AUDIT_INSERT, AUDIT_UPDATE, and AUDIT_DELETE columns are bit values where 0 means auditing is off for that column/operation, and 1 means auditing is on for that column/operation:

AUDIT_INSERT – If enabled on a given column, then any time a new record is inserted into the table with a new value in this column, the new non-null value will be added to the XT_AUDIT table.

AUDIT_UPDATE – If enabled on a given column, then any time an existing record is updated and the value in this column changes, the old value and new value will be added to the XT_AUDIT table.

AUDIT_DELETE – If enabled on a given column, then any time an existing record with a value in this column is deleted, the deleted non-null value will be added to the XT_AUDIT table.

For example, to turn on auditing of the UPDATE operation for the DT_RESULT.VALIDATOR_QUALIFIERS field, do the following:

 

1.Ensure that the DT_RESULT table has EUID values by invoking the equis.populate_euid stored procedure: exec equis.populate_euid 'dbo','dt_result';

2.Look in the XT_TABLE table and find DT_RESULT (Note: The TBL_ID value will not necessarily be the same from one database to the next).

3.Open the XT_COLUMN table and find the VALIDATOR_QUALIFIERS record (based on the column name and the TBL_ID value from Step 1).

4.Change the AUDIT_UPDATE value from 0 to 1 (be sure to save the change).

5.Invoke the equis.refresh_schema stored procedure to recreate the triggers: exec equis.refresh_schema 'dbo','dt_result';

 

Native SQL Server Functionality

 

Various versions and editions of SQL Server include various types of functionality for tracking data changes, including Change Tracking, Change Data Capture, and Temporal Tables. A detailed explanation of each of these functions is outside the scope of this article. Each function captures different levels of auditing data in different ways. EQuIS data auditing is designed to capture pertinent information and to work on all supported editions of SQL Server.