All Apps and Add-ons

Splunk Add-on for Microsoft SQL Server: How to create audit object for ALL tables at once, not individually?

Path Finder

http://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/SQLServerconfiguration

I'm following this guide to create table audit for each table. However, I have a question: What if we have >100 tables. Is there any short hand that can create audit table for ALL tables in one step?

0 Karma

Path Finder

I verified it myself. We can indeed create audit spec on schema insteawd of each table to quickly add auditing on all tables. Defining all actions including select, insert, update, delete are still required.

ALTER DATABASE AUDIT SPECIFICATION MSSQL_Table_Specification  
FOR SERVER AUDIT MSSQL_Table_Audit 
ADD (select ON SCHEMA::dbo BY dbo),
ADD (insert ON SCHEMA::dbo BY dbo),
ADD (update ON SCHEMA::dbo BY dbo),
ADD (delete ON SCHEMA::dbo BY dbo)
WITH (STATE = ON) ;
0 Karma

SplunkTrust
SplunkTrust
0 Karma

Path Finder

My understanding database/server level audit can only capture actions like login, permission change, etc, but not table access (CRUD). Am I correct?

I wonder in such case we should run a short script retreiving all tables and create audit spec on each of them.

(presudo)
foreach

CREATE DATABASE AUDIT SPECIFICATION MSSQL_Table_Specification 
FOR SERVER AUDIT MSSQL_Table_Audit 
ADD (UPDATE ON <table> BY <schema> ) WITH (STATE = ON) ;

Is it possible? Thanks

0 Karma

Path Finder

Another hard question from customer, would database audit above impact DB performance?

0 Karma

SplunkTrust
SplunkTrust

Auditing absolutely will impact performance, especially if its a heavily used database/table/etc and then even more so if you're writing audits to a slow disk.

Best Practices
-Write audit logs to a centralized location
-To facilitate processing of the audited data, load the logs into a database
-Use a file as a target for optimal performance
-Use targeted auditing to minimize the collected data and better performance
-When writing to the Windows logs, ensure that the roll-over policy of the Windows Logs, coincides with that of your audit strategy
- See more at: http://www.sqlshack.com/understanding-sql-server-audit/#sthash.71JR77T3.dpuf

As for your previous question, it's best to go to a SQL forum to ask that as I'm not a DBA.

0 Karma

Path Finder

It's chicken and egg problem... With audit logging (and also Splunk), we can have more insight what impact DB performance on the other hand. So probably the impact of introducing audit is a positive trade-off. I hope my customer would understand this.

Thanks, appreicated!

0 Karma