Database Auditing in Ms-SQL 2008
Overview
In SQL Server 2008 Enterprise Edition, Instance and Database level audit is now a built in function of the Database Engine with its own set of instance and database level objects, Server Audit and Server Audit Specification at the instance level and Database Audit Specification at the Database level (on a side note I'm not sure why they used the prefix Server since these are Instance level objects)
These new objects also have their own DDL commands (CREATE, ALTER, DROP) which we will examine in the later articles. The generation of audit events is extremely lightweight compared to previously available mechanisms and is based on the new Extended Events infrastructure which is designed to have an extremely low overhead even for large numbers of events. It also allows much finer grained filtering of events.
Whilst we had the ability to audit a large number of DDL actions in SQL Server 2005 using the Event Notification infrastructure (I have a sample tool that allows auditing of all schema changes across multiple instances and centralised storage and reporting available) not all actions were auditable, it was not that straightforward to configure and there was no tool support within SSMS (SQL Server Management Studio).
In SQL Server 2008, all events are auditable including those not available via Event Notifications and configuration is creatly simplified. As we'll see later on, there is also built in tool support for this in SSMS. The diagram below gives an overview of the various audit objects
Server Audit objects define the properties of an audit (Queue Delay, Action on Audit Failure) as well as the output Target (File, Windows Application Log or Windows Security Log). You can create multiple Server Audits each of which defines its own Target.
Server Audit Specification objects define the audit action groups that you want to audit at the Instance level and the Server Audit it belongs to. There can be a maximum of 1 Server Audit Specification per Server Audit. You can create multiple Server Audit Specifications as long as each one uses a separate Server Audit.
Database Audit Specification objects define the individual audit actions or action groups that you want to audit at the Database level including any filters and the Server Audit it belongs to. There can be a maximum of 1 Database Audit Specification per Database per Server Audit. You can create multiple Database Audit Specifications for the same database but they need to belong to separate Server Audits.
Example Walkthrough
In the following examples we will create Database Audit Specifications for the Adventureworks database using a new file based Server Audits. You will need the latest version of Adventureworks which can be downloaded from www.Codeplex .com.
1. For the first example, we will create a Database Audit Specification to audit all DML on the Sales.CreditCard and HumanResources.EmployeePayHistory tables. First we will create a new file based Server Audit that we will use for our Database Audit Specification
CREATE SERVER AUDIT [AWDMLAudit]
TO FILE
( FILEPATH = 'C:\Audit\DB\Adventureworks\AWDMLAudit'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 2000
,ON_FAILURE = CONTINUE
)
2. We will now create a new Database Audit Specification to audit the DML events for the Sales.CreditCard and HumanResources.EmployeePayHistory tables. Notice that as well as the audit action groups available in Server Audit Specifications, we can also specify individual audit actions and filter them based on individual objects and the user or role accessing them. In this example we will specify the public database role so that DML for all users is captured.
CREATE DATABASE AUDIT SPECIFICATION AWSensitiveDMLAudit
FOR SERVER AUDIT AWDMLAudit
ADD (SELECT , INSERT , UPDATE, DELETE
ON HumanResources.EmployeePayHistory
BY PUBLIC),
ADD (SELECT , INSERT , UPDATE, DELETE
ON Sales.CreditCard
BY PUBLIC)
WITH (STATE = ON)
GO
3. Now lets generate some events and then view the audit file to ensure that the events have been audited
USE AdventureWorks
GO
SELECT TOP 10 * FROM Sales.CreditCard
GO
BEGIN TRAN
UPDATE Sales.CreditCard
SET ExpYear = 2009,ModifiedDate = GETDATE()
WHERE CreditCardID = 6
ROLLBACK TRAN
GO
SELECT * FROM HumanResources.EmployeePayHistory
GO
USE master
GO
-- get the audit file
DECLARE @filepattern VARCHAR(300)
DECLARE @folder VARCHAR(255)
DECLARE @auditguid VARCHAR(36)
SELECT @auditguid = audit_guid,@folder = log_file_path
FROM sys.server_file_audits WHERE name = 'AWDMLAudit'
SELECT @filepattern = @folder + '*_' + @auditguid + '*'
-- view the results
SELECT a.name AS Action,c.class_type_desc AS ObjectType,
f.server_principal_name,f.schema_name,f.OBJECT_NAME,f.statement
FROM fn_get_audit_file(@filepattern,NULL,NULL) AS f
JOIN sys.dm_audit_class_type_map c ON f.class_type = c.class_type
JOIN sys.dm_audit_actions a ON f.action_id = a.action_id
AND c.securable_class_desc = a.class_desc
WHERE f.action_id <> 'AUSC'
ORDER BY event_time DESC,sequence_number
4. There are a couple of interesting points raised by the results. As you can see, the update statement is present (twice in fact since the table needs to be read to be able to qualify rows for the update which is why poth the SELECT and UPDATE appear for the UPDATE statement) even though the transaction it was in was rolled back. Also notice that the statement for the update is the auto parameterised version rather than the actual statement text. Database audits do not capture any before or after data images for DML actions.
5. Here are a few more examples demonstrating the additional filtering available for Database Audit Specifications. Note that if you try and create these without deleting the previous Database Audit Specification they will fail because of the limit of 1 Database Audit Specification per Database per Server Audit. Also note that in order to DROP or ALTER a Database Audit Specification if must first be disabled. You can of course simly alter the existing Database Audit Specification by adding the new audit actions and groups.
-- some more examples of database audit specifications
USE AdventureWorks
GO
-- audit all execution of stored procedures
CREATE DATABASE AUDIT SPECIFICATION Example1
FOR SERVER AUDIT AWDMLAudit
ADD (EXECUTE
ON DATABASE::AdventureWorks
BY PUBLIC)
WITH (STATE = ON)
GO
-- audit all updates in the Sales schema
CREATE DATABASE AUDIT SPECIFICATION Example2
FOR SERVER AUDIT AWDMLAudit
ADD (UPDATE
ON SCHEMA::Sales
BY PUBLIC)
WITH (STATE = ON)
GO
-- audit all schema changes in the database
CREATE DATABASE AUDIT SPECIFICATION Example3
FOR SERVER AUDIT AWDMLAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO
6. As for Server Audit Specifications, there is built in tool support for Database Audit Specifications in SSMS. If you expand a database node in Object Explorer you will see the new Database Audit Specifications node. To create a new Database Audit Specification, right click on the Database Audit Specifications node and choose New Database Audit Specification as shown below
This will bring up the Create Database Audit Specification dialog which allows you to select the audit actions and groups and any applicable filtering
That concludes our examination of Database Audit Specifications and the new audit features of SQL Server 2008 Enterprise Edition. As you have seen, it is extremely easy to configure and administer via TSQL or SSMS. It is also designed to be extremely lightweight and have less overhead than the existing mechanisms (SQL Trace, DDL Triggers, Event Notifications).