SQL 2012 Security Audit 2 (Database Audit)
After mastering SQL Server Audit creation the next item is the database audit.
As the name indicates, the Database Audit is Database scoped, so it will focus to the database level actions. This can be useful if you have a single database on the server, or perhaps even a single schema or even table you want to isolate for auditing. The choice you make between “Database-Level Audit Action Group” and “Database-Level Audit Actions” will identify the scope within the database. The difference between the two is very subtle when reading the docs or setting it up the first time.
The initial apparent difference in the documentation is only the word Group, on the contrary. A Database-Level Audit Action Group cannot be set to audit a single securable, where a Database-Level Audit Actions (SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, REFERENCES) can. A Database-Level Audit Action Group will capture every occurrence of an action for the entire database.
For instance, if you are looking to monitor deletes on one object or on one securable, Database-Level Audit Action is the way to go. If you need to monitor DML change for every object in a database, Database-Level Audit Action Group is the way to go.
Here is the t-SQL Syntax difference between the two:
Database-Level Audit Action Groups
CREATE DATABASE AUDIT SPECIFICATION [AW_Audit_DML]
FOR SERVER AUDIT [Server_Audit_AW]
ADD (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE =ON)
Database-Level Audit Actions
CREATE DATABASE AUDIT SPECIFICATION [AW_Audit_HR_Employee_DML]
FOR SERVER AUDIT [Server_Audit_AW]
ADD(SELECT,INSERT,UPDATE,DELETE,EXECUTE ON HumanResources.Employee BY sys)
WITH (STATE =ON)
The two examples essentially perform the same function, one is on the entire database and the other on just the HumanResources.Employee table. After you have done this a couple of times
the differences become more obvious.
Now, the order of operation. You will notice below that the Server Audit Specification is optional. A Server Audit is required since that defies the location and parameters of the audit. One of the interesting nuances of the Specification is you are allowed to have 1 Server Specification and 1 Database specification assigned to a Server Audit, but you cannot have 2 Server specifications nor 2 Database specifications. On the bright side you can add as many Actions to either as you want, and you can add more Server Audits as well.
- Create a server audit
- Turn on the audit
- (Optional) Create a Server Audit Specification
- Create the Database Audit
- View the wonder of your work
--Step 1 and 2
USE [master]
GO
CREATE SERVER AUDIT [Server_Audit_AW]
TO FILE
( FILEPATH = 'C:\SQLAudit'
)
GO
ALTER SERVER AUDIT [Server_Audit_AW] WITH (STATE = ON)
GO
--Step 3
--Optional Server Specification
--Step 4
CREATE DATABASE AUDIT SPECIFICATION [AW_Audit_DML]
FOR SERVER AUDIT [Server_Audit_AW]
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE =ON)
--Step 5
SELECT * FROM fn_get_audit_file('c:\SQLAudit\*.sqlaudit',default,default);
GO
This should get you running on database audit. Review the considerations form the first post on SQL Server Audit, they all still apply. The next post will cover the predicate and a few other nuggets …
Shep
CREATE SERVER AUDIT (Transact-SQL)
https://msdn.microsoft.com/en-us/library/cc280448.aspx
CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)
https://msdn.microsoft.com/en-us/library/cc280767.aspx
CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)