Sql-server – Is it possible to audit query results in SQL Server

auditloggingsql server

I want to find out if it is possible to audit the results of any query that gets executed on an instance of SQL Server. We currently have C2 auditing enabled, but this only showed what query was executed, not what was returned.

Since this project is health-information related, we want to ensure that we are auditing to the most granularity we can obtain to meet HIPPA requirements. Previously this was not required, but the nature of our project is changing.

The goal is: if a user logs into our system and executes a query, we want to know every patient that was returned from that query.

Is there either a built-in feature (triggers?) or a third-party application that we can use to achieve this? We looked into ApexSQL, but their customer service relayed that their software does not audit at that granularity.

Could this audit be restricted to the Patients table only?

Unfortunately no, due to the fact that if you were to query a different table, there is patient-related information; so if you happen to know that patient's foreign key, you could "get around" the audit by looking up individual clinical records.

We're not concerned about space or speed at this point in time.

Best Answer

You could (a) force data access through a stored procedure, then (b) that stored procedure would dump the results to a #temp table before returning them to the user, then (c) the procedure would log the results to some background auditing table.

You'd want to rotate that though because it's going to get very big very fast, not to mention this will not exactly make queries faster. - aaron-bertrand

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.

Related Question