SQL Server – How to Store Audit Data in Table

auditsql server

Is there a standardised way to store auditing information constantly to a table for further analyse?

Best Answer

This really depends on what type of information you want to audit. There are a number of ways to collect audit data.

I did an article that overviews audit options here.

But in summary:

Successful/Failed logins: This is an instance level setting and is saved to the logs.
C2 Auditing and Common Criteria Compliance: This goes a bit overboard in my opinion. It audits a LOT of information. You'll know if you actually need it.
Audit columns: These are columns in your table that give you information. CreateDate & ModifiedDate for example
Triggers: These are good if you want to log specific information when doing an insert, update or delete on a table. There can be a lot of overhead here and they can be tricky to get right.
Tracing: Otherwise known as profiler. This is an older technology that has been deprecated but is still hanging on and will for who knows how long. You can collect all kinds of information here about what was run, by whome, how long did it take, who logged in, who logged out, just about anything happening on your instance. Remember though that the more you collect the more overhead.
Extended Events: This is the replacement for traces. Basically the same but with less overhead and more options for data you can collect.
Audits: These can be created at a database or server level. They are based on Extended Events but have a different set of information. For example shutting down the instance can be logged. These are meant for when you need to collect audit information in the traditional sense. The data that is collected for an auditor.

You can get a bit more detail from the link above and it has further links in at least a few places for even more detail.

Related Solutions

SQL Server – Data Change Audit Plan

Don't bother tracking audit data on a per-field basis. It's much easier to have an audit table with the same structure as the main table and timestamp and user information tracked on the table.

This architecture has a few advantages:

You can easily create a view across the main table and audit table to show a complete history includint current versions.
It is relatively simple to implement the audit triggers - in fact if you hae a large number of tables you can write a utility to generate them from the system data dictionary.
The audit tables can be put on a separate disk to reduce the I/O load on the disk with the main tables.
It's very easy to reconstruct an as-at version of the record, rather than having to apply a list of field-level changes in reverse.

Audit information should include at least: user who created/changed the record, date/time of the creation/change, nature of change (insert, update, delete). You may want to use logical deletion (i.e. a 'Deleted' flag) if you have the option of doing so. Otherwise you need to capture the user and date/time from the session and put these on the deletion record, probably along with the last state of the record. If this is not available from the connection (often the case on N-tier apps where the user is not being impersonated at the database level) then you need to find some other way to get it.

Audit select queries against specified tables by specified users

I have found a way to prevent duplicate rows in FGA audit trail with parallel query

First create a function that return 1 if AUTHENTICATION_METHOD is PQ_SLAVE in USERENV context (coordinator process gets 0)

create or replace function is_pg_slave return integer as
begin
  if (sys_context('USERENV', 'AUTHENTICATION_METHOD') = 'PQ_SLAVE') then
    return 1;
  else 
    return 0;
  end if;
end;
/

Then add policy to table

begin
      dbms_fga.add_policy(
        object_name       => 'table_name',
        policy_name       => 'noaudit_pq_slave',
        audit_condition   => 'is_pg_slave = 0',
        statement_types   => 'SELECT');
end;
/

Now only the query coordinator is audited, so no matter what the parallel degree is, only one row is inserted into fga audit trail.

I have not noticed any performance issues with this approach compared to normal auditing or no auditing.

Best Answer

Related Solutions

SQL Server – Data Change Audit Plan

Audit select queries against specified tables by specified users

Related Question