Sql-server – SQL Server 2016 Audit Actions, Can DatabaseAuditSpecification capture param values used in statement

auditsql serversql-server-2016

I am using Schema_Object_access_Group audit action type. When I run a select, insert, update SQL statement, it records all statements nicely.

For example, captured Sql statement looks like :

UPDATE TOP (200) SomeTable 
SET ColName1 = @ColName1
WHERE (ColName2 = @ColName2) AND ...

It is not explaining what was updated value and which row was updated.

I want to capture updated actual value used in SET ColName1 = @ColName1, is there an audit action type to accomplish the job. I want a way to capture parameters used in any select, update, insert statements.

Willing to use audit events as change log, to track who changed what without using the following:

Trigger approach to log all changed values
Change Data Capture "CDC"

Best Answer

There is no audit action to capture this information, it is a limitation of SQL Server Auditing. Basically it captures the statements after the query optimiser has already parameterised the query.

There are two open requests that I know of to alter this behaviour (here and here) that you could vote for, however, this limitation has been around for some time and doesn't seem likely to change. Note that this is apparently different in Azure SQL Database, however, I haven't tested this to confirm.

Your options for this are triggers, CDC, SQL Trace or third-party auditing tools.

Related Solutions

Can this be accomplished with Oracles Fine Grained Auditing

Get the client to call dbms_session.set_identifier('PHILTEST');. This will then be set in the CLIENT_ID audit field. Obviously you'l need to call it first from the client whenever a new connection is pulled from the pool.

For example:

PHIL@PHILL11G2 > conn / as sysdba
Connected.
Loading glogin.sql

Session altered.

SYS@PHILL11G2 AS SYSDBA> audit all by phil by access;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY ACCESS;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> truncate table aud$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> TRUNCATE TABLE fga_log$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> conn phil/phil

PHIL@PHILL11G2 > exec dbms_session.set_identifier('PHILTEST');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 > select count(*) from fgatest;

  COUNT(*)
----------
     1

PHIL@PHILL11G2 > conn / as sysdba

SYS@PHILL11G2 AS SYSDBA> select CLIENT_ID from dba_audit_trail;

CLIENT_ID
----------------------------------------------------------------
PHILTEST
PHILTEST

2 rows selected.

SYS@PHILL11G2 AS SYSDBA>

Sql-server – SQL Server database level audit filtering does not work

In my case, MYDOMAIN\myuser was a server principal, not a database principal. Only the latter ones can be used in database audit definitions.

Database principals can be either database users, or roles (see documentation). I created a new database role to be used in the audit definition. Then I created the logins for the users in question and chose the apt role membership under User mapping.

Hint: For some reasons, it seems necessary to define the role prior to adding the logins for the users. Adding existing logins as role members did not work for me.

Best Answer

Related Solutions

Can this be accomplished with Oracles Fine Grained Auditing

Sql-server – SQL Server database level audit filtering does not work

Related Question