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

[hochit]

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?

[hochit]

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) ;

[jkat54]

Yes, enable it for the entire database instead:

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

Or the entire server:
http://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/SQLServerconfiguration#Example_of_c...

These are from the link you posted.

[hochit]

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

[hochit]

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

[jkat54]

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.

[hochit]

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!