<< Click to Display Table of Contents >> Navigation:  Database > Auditing

Overview

Auditing in an EQuIS Online (EOL) Environment

Enable Auditing

Advanced Auditing Configuration

Remove Auditing

XT_AUDIT and VW_AUDIT

Enable Auditing on Large Tables

Adding Columns after Auditing Enabled

Native SQL Server Functionality

General Notes and Recommendations

 

Overview

 

EQuIS provides optional data auditing functionality to 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 or editing data). Inserting and deleting/rolling back records via EDP will create auditing records. EarthSoft advises assessing your auditing needs carefully and only enabling auditing after careful consideration.

 

 

Auditing in an EQuIS Online (EOL) Environment

 

Contact EarthSoft Support to enable auditing or modify existing auditing on EQuIS Online hosted databases.

 

Enable Auditing

 

The equis.add_audit stored procedure requires two input parameters:

 

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

2.@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.

 

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 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 record for the VALIDATOR_QUALIFIERS field (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';

 

Remove Auditing

 

To permanently remove auditing, delete the triggers that were added by enabling auditing, then update the XT_COLUMN table accordingly:

1.Expand the triggers on the table where auditing was added, right-click on the triggers that were added due to auditing, and select 'delete'.

2.The XT_COLUMN table must also be updated, otherwise the auditing triggers will be regenerated at the next schema refresh (such as during an database upgrade). To do this, a database administrator must:

a. set the audit_insert, audit_update, and/or audit_delete fields to 0 for the appropriate entries. Consult the XT_TABLE table via SQL Server Management Studio (SSMS) or the EQuIS SQL Form  as needed to determine the correct records, e.g.:
select tbl_id from XT_TABLE where TABLE_NAME = '[table name]';
 

b.refresh the schema via the equis.schema_refresh stored procedure, e.g.
exec equis.refresh_schema 'dbo', '[table name]';

 

To temporarily remove auditing, triggers can be deleted as in Step 1 above, or disabled by a database administrator by executing the following command:

disable trigger [insert trigger name] on [table name]

 

For example, to disable the trg_dt_facility_after_delete trigger added on the DT_LOCATION table:

 

disable trigger dbo.trg_dt_facility_after_delete on dbo.dt_facility;

 

 

XT_AUDIT and VW_AUDIT

 

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

 

 

Example

 

 

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). To view audit data, use the VW_AUDIT view, which is accessible in EQuIS Professional and includes the appropriate joins/functions to show meaningful text values like table name, column name, and user name.

 

To see records from rollbacks, filter the OLD_VALUE column by the EBATCH value for the EDD that was rolled back.

 

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.

 

To decrease auditing's impact upon performance, employing advanced auditing may be helpful on larger, high-activity tables. Use professional judgment in selecting the appropriate columns of a table to include or exclude from auditing. Below is a generalized approach that an EQuIS administrator could use to determine which columns to audit.

1.In EQuIS Professional, open the relevant table with all facilities, using one of the following means:

a. open the table in Database Mode. A large table may take a long time to load.

b.b) via the EQuIS SQL Form in EQuIS Professional:
select * from [table];
 
For example:
select * from DT_RESULT;
 
The above statement structure could also be run in SSMS for a SQL user, then exported for review in another program.

2.In the EQuIS Professional output, choose the "Hide Blanks" column option in the Column Chooser of the toolbar if you wish to hide columns not containing data.

3.Review remaining columns to decide:

a.which would be appropriate to audit

b.which operations (such as Update or Delete) should be auditing for each column. The "Insert" operation will only record the insertion of the EUID column.

 

If the Filter row is enabled, the dropdown menu for each column will list all the unique values within that column.

 

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).

 

 

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';

 

 

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.

 

General Notes and Recommendations

 

•Auditing can have impacts on performance. In order to balance  individual data integrity and performance requirements, organizations may wish to only enable auditing on certain tables. columns, or operations.

•The audit functionality does not work for BLOB fields. Note that SDT_ tables, ST_MODULE, and DT_FILE contain some BLOB fields; changes to these fields would not be recorded.

•The ST_USER_ROLE table no longer exists; this functionality is handled by XT_AUTH_MEMBER_OF.

•XT_ tables do not support full auditing, but XT_AUTH_MEMBER_OF.GRANTOR_ID keeps track of who added a user to a role.

•Determine if auditing is appropriate for new DT_ and AT_ tables added to the EQuIS Schema on an individual basis.