Before and after auditing tracks changes to data, showing the old and new values after each change. This data can be re-constructed to show an entire history of row changes and is important for forensic auditing in the case of malicious or inadvertent data changes.
In ApexSQL Audit, before and after auditing is implemented via CLR triggers. For more information on what CLR triggers are see the links at the bottom of this article.
ApexSQL Audit before after data auditing is designed to ensure strong 256-bit data encryption from the moment the data change is captured by the CLR trigger and all the way until the audited data change values are stored in the central repository database. In this way, the auditing process meets strict compliance and security auditing requirements.
Note: Auditing before and after values is in many cases an obligatory type of audit. However, before and after auditing should be carefully and selectively implemented for sensitive data only, which is generally both important but also infrequently updated. Before and after auditing when implemented randomly or for high activity tables, can occupy, due to the underlying technology involved, a large amount of audited data store, with possibility to create in some cases significant overhead on SQL Server performance. So, the important rule with before and after auditing is to apply it selectively.
To setup before and after auditing for specified SQL Server in ApexSQL Audit, after selecting the Before-after tab, select the SQL Server that will be audited and add database. Afterwards, select the tables for which Before-after is to be enabled and then select the columns and events (insert/update/delete) to monitor for.
|
Quick tip: If SQL Server replication is in use for the selected database, before-after auditing should not be established and enabled. Before and after data auditing doesn’t support SQL Server replication. More details can be found in the Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION MSDN article. This is valid for both types, Transactional and Merge replication. Although those two types are very different, triggers behave almost the same for both. |
Now the user can select what tables to audit for each added database, what columns for selected table and to select what DML operation (insert, update and delete) to audit per table. ApexSQL Audit permits auditing of before and after data changes for each table column individually, and thus ensure a level of granularity to accommodate most if not all compliance and/or internal auditing requirements.
The Add table button will open the dialog where user can select the tables that will be audited for before after changes.
|
Quick tip: Make sure that selected table names don’t contain the following special characters to avoid errors while auditing: ? |” \ / : * |
Once the databases, tables, columns and operation types e.g. update are selected, pressing apply on the yellow ribbon signaling filter change will create the appropriate CLR triggers for selected tables/columns and before and after auditing will start.
Here we can see the triggers that were created on the audited table
The tool is designed to be resilient to audited tables schema changes, which mean that the trigger code is optimized to ensure normal operations on the table where schema change occurred (not quite possible with DML Triggers). Also, the user can easily handle and adopt application to any schema changes of audited tables. When any such DDL change has occurred, the application has an option to reload the tables via the Before-after tab using the Reload schema button. The button will initiate reloading the already added tables and if any schema (DDL) change has been detected, the new table schema will be displayed and the table name will be highlighted.
Now, in case that schema changes do not affect what was already set for auditing, just press Apply in the notification bar, and the appropriate CLR triggers will be recreated in the target database to accommodate the new table structure.
The before and after auditing report is the part of standard ApexSQL Audit reporting including the ability to use appropriate dedicated filters for narrowing down the report results
To preview of the before and after report, use the Before-after auditing, Complete audit trail or DML changes reports. The Before-after checkbox from the Event source group in the reports must be selected. If only Before-after report should be displayed, then checkboxes for Trace and Internal must be unchecked in Complete audit trial and in the DML changes report only Trace has to be unchecked.
Below is an example of before and after changes audited by ApexSQL Audit and displayed in the Common report.
For those who prefer to create specific reports for before-after changes, full customization is available. Below is one example of a custom report condition that will display the before after data changes occurred in the audited database/table.
The user has an option to create and save multiple specific reports to show only some specific audited changes and/or to create and save a global report that will allow reviewing of all before after changes in a single place
One of the very important auditing requirements is to be notified about any inadvertent or unplanned changes to audited tables. ApexSQL Audit has an alerting engine that is capable of sending alerts on tracked before and after changes in real time, including the notification via email. For that purpose, the dedicated Before-after alerts are implemented.
After selecting Before-after alert from the New dropdown menu, the new wizard will be started.
The Basic settings window is the first step of the wizard and it is fully customizable to match the user preferences. A various variable that can be used in the message subject and body will ensure that user will have all the necessary information when receive the alert, which can make possible to perform immediate reactions to potential threats
Unlike the regular Auditing alerts, the Before-after alerts are crafted to suit the specific before and after auditing needs, and therefore some different condition categories are introduced in the Conditions step of the wizard.
The filter categories highlighted on the above image allows the user to set up a very precise alerting conditions that will ensure tracking even the specific data values of a particular interest for the user
In the Actions step of the wizard the sending alert to an email address for immediate notifications or writing the alert into a Windows Event Log (more precisely in Application Log) for users that are using SIEM/SOC or SCOM solution in their enterprises.
For users where CLR trigger based before and after data auditing is not preferable, they should consider another ApexSQL auditing tool – ApexSQL Log. ApexSQL Log is non-intrusive auditing solution, with zero overhead on audited SQL Server, thanks to the fact that it is based on the direct SQL Server transaction log file reading. This enables the full before and after data auditing even for the data changes occurred way before the ApexSQL Log is deployed as the auditing solution.
ApexSQL Audit CLR trigger based auditing for data changes where the immediate notification and appropriate reaction is needed and ApexSQL Log for deep forensics including full row history, can be complimentary approaches.
For more information on transaction log based before and after auditing see Tracking DDL changes in SQL Server – the ‘Trouble with Triggers
Resources:
CLR Triggers
Create CLR Triggers
How to: Create and Run a CLR SQL Server Trigger
Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)
Detailed SQL Server data audit through a .NET (CLR) trigger
February 25, 2016
