New in SQL 2008: Server and Database Auditing Part III

In this post I'll cover how to create Database Audit Specifications to audit database level events. This post is part of a series so if you missed Part I you can find it here and Part II here.

Database Audit Specification

Database Audit Specifications define the audit action groups, individual audit actions and filters that you use to audit events at the database level 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 a database as long as each one uses a separate Server Audit.

At the instance level you can specify one or more audit action groups (for Database Audit Specifications you can specify individual audit actions and filters as well). Note that actions that modify the audit itself (e.g. disabling or altering audit objects) are automatically audited. There are a large number of audit action groups, to find details on all of them check Books Online.

Example Walkthrough

In the following examples we will create Database Audit Specifications for the Adventureworks database using a new file based Server Audits (I won't cover creating the Server Audits in any detail, if you want more details on Server Audits see Part I). You will need the latest version of Adventureworks which can be downloaded from Codeplex here.

  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 brings us to the end of this introduction to the new built in auditing in SQL Server 2008. 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).

All the code examples are attached to this post.

Published Friday, June 27, 2008 6:15 PM by sqldbatips
Filed under: , ,

Comments

# Books and Magazines Blog &raquo; Archive &raquo; New in SQL 2008: Server and Database Auditing Part III

Pingback from  Books and Magazines Blog  &raquo; Archive   &raquo; New in SQL 2008: Server and Database Auditing Part III