A high level overview of data auditing with ApexSQL Trigger (4211387)

Data auditing is something usually that we apply to larger, enterprise level applications, but adding an audit trail to smaller applications, especially shrink wrapped commercial applications can be a huge value add; especially if it can be done quickly and easily

Think of an application you are building for a client. Wouldn’t it be cool to deliver it self-auditing? The commercial tool you ship to a larger client base or sell to the public, could also be made self-auditing, so end users would have a full history of all changes to sensitive tables.

Despite the obvious appeal, integrated auditing into a rapidly changing, dynamic application can be a real pain. Each time a table changes, triggers must be recreated or you risk errors or worse missing audit data, that was never applied to new columns.

ApexSQL Trigger was designed to as a Rapid Application Development (RAD) tool for quickly and easily applying a data change audit trail to any SQL Server database.

ApexSQL Trigger ecosystem

The ApexSQL Trigger application ecosystem consists of the following

1. Architecture objects – these are tables for storing audit data aka repository and the views and stored procedures that allow for querying and reporting off of the repository. These objects can be installed in the database you are auditing or (as we would recommend) a separate database.

2. Trigger template – this is the DNA of your auditing triggers. Each will be created exactly the same based on the template. The template can be edited and changed at any time and ApexSQL Trigger even has a self-contained IDE for managing templates.

   

   See Using the template editor in ApexSQL Trigger for how to manage templates in the ApexSQL Trigger IDE

3. Project file – Once you have used ApexSQL Trigger to create your auditing plan, hit save and you will create a project with all of your table selections and configuration settings. This project can be re-opened again, at any time, to regenerate triggers for the database (more on project files below)

   

4. Triggers – Triggers are the output of ApexSQL Trigger. When you load a project, specify a template and run it against a database, it will produce a script that will generate all of the triggers required to create that audit plan.

5. ApexSQL Trigger application – this application can consume and execute projects, configure and store settings, create triggers and even allow for editing and executing templates and SQL code from within the application

   

6. ApexSQL Trigger console application – this application has a command line interface and allows for running ApexSQL Trigger automatically and unattended. See ApexSQL Trigger Command Line Interface (CLI) switches for a complete reference of the CLI

   

Projects

The stored project contains some critical information, to allow the auditing plan to be re-executed, the same way, every time

1. Configuration options – there are various settings that control the behavior of the application and how the triggers are created

   

2. Table selections – these are the tables specifically selected for auditing. These are generally “sensitive” tables that have transactional information, such as an Invoice table where new invoices and changes to existing ones were stored. An audit trail on this information would be very helpful.

   

3. Column selections – individual columns can be selected in the profile as well

   

4. Trigger types – for each table you can select Insert, Update and/or Delete triggers
5. Advanced elements like Watches and Lookups that can be created and added to the profile

6. A data source and credentials

   

Projects offer a very convenient way to store all of your selections and configuration settings which makes it very convenient for unattended execution, because all you have to do is call ApexSQL Trigger console application from the command line and pass it a project

Auditing architecture

The architecture of a database audited with ApexSQL Trigger is very simple

1. Tables – these store the audit data. These objects include:
• dbo.AUDIT_LOG_DATA
• dbo.AUDIT_LOG_DDL
• dbo.AUDIT_LOG_TRANSACTION[list]
2. Views and procedures – these allow for reporting against the repository. These objects include:
• AUDIT_prc_AggregateReport
• AUDIT_prc_Analyze
• AUDIT_prc_DDLReport
• AUDIT_prc_DeleteArchitecture
• AUDIT_prc_Purge_AUDIT_LOG
• AUDIT_prc_ReportingAddFilterValue
• AUDIT_prc_ReportingEnd
• AUDIT_prc_ReportingStart
• AUDIT_prc_StandardReport [list]
3. Triggers – these fire on DML events and write the change detail to the repository

A key design decision is whether to log audit information in the audited database or to put it in a separate database. ApexSQL Trigger makes this very easy, in that you can specify which option to choose and even specify the name of the audit database and the insert statements in the triggers will be automatically updated to append the database qualifier, so the data will be written to that database

Inside the trigger you will see the SQL for inserting a record into the repository

See Installing audit tables on another database for how to implement this approach

We recommend to use a separate database to store audited information. That allows you to audit multiple databases but have a single repository. It also makes continuous integration and delivery easier if you don’t have to worry about managing an auditing repository along with your core database.

Putting it all together

Once you understand the product and the approach you can start auditing your database. See the following articles to get started

• How to track DDL/schema changes
• How to manage triggers in ApexSQL Trigger

As well as some advanced features

• Using the template editor in ApexSQL Trigger
• How to use the Watches feature in ApexSQL Trigger
• How to use the Lookup feature in ApexSQL Trigger

And reporting

• How to send email alerts when a change is made to a database record
• Auditing reports in ApexSQL Trigger
• How to customize an auditing report
• Schedule ApexSQL Trigger report generation

Automating the process

Once you have created your first audit plan and successfully executing it, it is time to automate the process

You can set up a simple batch file or PowerShell script to instantiate the ApexSQL Trigger console application, supply it with a project file and execute it. In this way you can automatically regenerate triggers every night or integrate this as part of a continuous integration or delivery process

See below for a simple example

ApexSQLTrigger.com /project:MyAuditPlan.axap

• Obviously, other switches and combinations of switches can be used. Full documentation, along with examples of the ApexSQL Trigger CLI can be found here ApexSQL Trigger Command Line Interface (CLI) switches

See also

• Save project as batch file