کد:
http://www.sqldbatips.com/showarticle.asp?ID=136
Overview
In this article I'll cover how to create Database Audit Specifications to audit database level events. This article is part of a series so if you missed Part I you can find it here and Part II here.
Download Code for this Article
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 also specify individual audit actions and filters. 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 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).

Download Code for this Article



موضوعات مشابه: