Audit


Intro

A new version of audit was introduced in version 6.2, replacing the old audit. There are a few significant changes but the most important one is that the audit is now fully integrated into the AGR Core and comes ready to be used and fully configured when you install or upgrade AGR. There is no longer a separate audit database but instead the data and configuration is contained within the database itself.

The audit consists of 2 main parts:

  • Content tracking - this is the tracking of data within tables
  • Object tracking - this is the tracking of objects within the database such as tables, view, procdures, triggers etc.

Content tracking

The content tracking part allows you to monitor the data for vital tables, such as the settings table. Out of the box AGR is already monitoring a number of tables. inst

  • To start auditing a table, simply add the name of the table to the core.audit_table table
    • When audit starts, a TABLE_AUDIT_STARTED event will occur, recording the initial status of the table
    • It is possible to exclude certain columns in a table from being audited by using the excluded_columns column. There you can enter a comma separated list of columns to exclude. This is helpful if a table has columns that are of no interest, like in cor.estg_element the last runtimes columns
  • To stop auditing a table, simply remove the name of the table from the core.audit_table table
    • When audit stops, a TABLE_AUDIT_STOPPED event will occur
  • The audit data can be viewed using the core.audit_event_list procedure inst

Object tracking

The object tracking keeps track of the following object types:

  • procedure
  • function
  • table
  • view
  • synonym
  • schema
  • trigger (table and database)

When an object of the above type is created, altered or dropped an event is written to the core.audit_event table. As with content tracking you can use the core.audit_event_list procedure to list changes to a specific object

In addition to the regular object tracking there is a table, core.audit_object_hash that contains more detailed information on each object in the database such as the original object definition and information on how many times this object has been modified. This can be helpful during updates to check if any of the core objects have been modified.

inst

Advanced stuff

All of the audit is done via triggers.

  • When a table is audited a trigger is added to that table. These triggers can easily be identified by the name, as it has a prefix of tr_audit inst
  • All of the object tracking is done with a database trigger tr_ddl_audit inst
  • The content tracking results are stored in JSON format. Although there is a bit of an overhead to this method it allows for greater flexibilty when it comes to changes to the underlying table structure. If a table changes and a column is dropped the data for that column is not lost, as the JSON result is independant of the current structure. However, if you need to retrieve data for dropped columns you will need to do so manually since the core.audit_event_list procedure will only display the current columns.

The procedure core.audit_event_list has a few tricks up its sleeve:

  • If the object you request is a table you will get both content and object tracking information in the same list.
  • The list generated is ordered so the latest changes come first
  • The parameter has a few extra useful (optional) parameters:
    • from_date & to_date - for filtering the results by date and time, pretty basic
    • filter - here you can filter the results by any column in the result set. This includes the columns of the table, if the object is a table. Note that when filtering on strings you need to double-quote the string. See examples below
    • rows - if you are getting too many results you can use this to enter the maximum number of rows returned
    • simple - by default, you get a lot of un-interesting columns such as the IP number and program name. Setting this to true (1) will give you a simpler result set
    • debug - enabling the debug will print out a query to work on instead of a result set

inst

Permissions

The ip_address column in core.audit_event is populated by using information from a DMV view that requires the VIEW_SERVER_STATE permissions. If the user doing the audit does not have this permission the ip address will show N/A