Getting Started with SQL Server auditing: SQL Server Auditing (SQL Server Audit)

Introduction

Audit is a feature after SQL Server 2008 that tells you "who did what when?" Specifically, auditing an instance of the SQL Server database engine or a separate database involves tracking and documenting events that occur in the database engine. Its bottom-level is based on extended events (Extented event), so its performance and flexibility are relatively good. Audit data can be exported to audit files, Windows security logs, and application logs.

Audit all need to create an instance-level SQL Server audit, and then you can create a "server audit specification" and a "database audit specification" that belong to it. You can understand that SQL Server auditing is the top-level container for auditing, and these two specifications are the specific content that defines what to audit.

General procedures for creating and using auditing:

1. Create an audit and define the target.

2. Create a server audit specification or database Audit specification that is mapped to an audit, and enable the audit specification.

3. Enable auditing.

4. Read the audit events by using the Windows Event Viewer, log File Viewer, or the Fn_get_audit_file function.

Implement

To create a test database and data:

Use mastergocreate database Audittestgouse audittestgocreate table TB (ID int primary key, name varchar (), Weight decimal ( 10,2)); INSERT into TB values (1, ' John ', 70.2), (2, ' Ted ', 80.8), (3, ' Clark ', 75.1) go

1. Create audits and define goals.

Creating an audit can be accomplished using SSMS or a statement. The goal is to audit where the output is stored, similar to the target of the extended event.

650) this.width=650; "width=" 558 "height=" 341 "title=" clip_image001 "style=" border-top-width:0px;border-right-width : 0px;border-bottom-width:0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M00/5A/1A/ Wkiom1t2ft6bmqqwaagmiyenn5y511.jpg "border=" 0 "/>

The corresponding statement is created:

--create server audituse [master]gocreate server AUDIT [audit-audittest]to FILE (FILEPATH = N ' D:\share ', MAXSIZE = MB, Max_files = ten, reserve_disk_space = ON) with (Queue_delay = +, on_failure = CONTINUE--audit_guid=uniqueidentifier)--W Here object_name= ' TB ' go--enable server Auditalter server audit [Audit-audittest] with (State=on)

Briefly explain the relevant parameters:

To file: Specifies the output to the audit file, or it can be specified as security log and application log.
FILEPATH: The directory address of the audit file.

MaxSize: The maximum capacity of a single audit file.
MAXSIZE: Similar to trace, specifies that rollover allows the maximum number of files.
Reserve_disk_space: Pre-allocating audit files to maxsize. Personal referrals are enabled.
Queue_delay: Specifies the millisecond interval at which an event occurs to a forced audit. A synchronization audit is specified as 0.
On_failure: The next behavior to take when auditing an up-file write data failure: CONTINUE | SHUTDOWN | Fail_operation.
Audit_guid: for database Mirroring. Similar to the SID role of login, the main library of the mirroring session if there is an audit, the same GUID is required to create the corresponding audit in the Mirror library.

WHERE: equivalent to predicate in an extended event to specify a filter condition.

2. Create a database audit specification and enable it

Specifies from which SQL Server to audit and define what to audit. It can be created through SSM or statements. The following is an audit specification for test table TB: Anyone who has DML and table structure modifications to the TB table is audited.

650) this.width=650; "Width=" 834 "height=" "The Title=", "clip_image002", style= "Border-top-width:0px;border-right-width" : 0px;border-bottom-width:0px; "alt=" clip_image002 "src=" Http://s3.51cto.com/wyfs02/M01/5A/1A/wKiom1T2ft_ Tc-yjaail2mzulgq270.jpg "border=" 0 "/>

The corresponding statement:

--create and enable database Audit Specificationuse [audittest]gocreate database Audit Specification [ Databaseauditspecification-audittest]for SERVER AUDIT [Audit-audittest]add (Schema_object_change_group), ADD (SELECT , Delete,insert,update on object::[dbo]. [TB] by [public]) with (State=on) GO

With SSMs, the meaning of the parameter is clear. For more detailed information: CREATE DATABASE AUDIT Specification

Audit activity type (Audit action type) reference: SQL Server Audit Action Group and actions

3. Enable auditing

SQL Server is not enabled by default when it creates audit and audit specifications and needs to be explicitly enabled. In the first two steps, I have explicitly enabled.

4. test and view audit data

Use Audittestgoselect * from Dbo.tbwhere id=1;update dbo.tbset name= ' ted_new ' where Id=2;alter table dbo.tb alter column NA me varchar; ALTER TABLE DBO.TB add newcol varchar; go

In the previous target definition file, an audit file is generated, file name: [SQL Server audit name]_[Audit Guid]_*.sqlaudit.

This file can also be played with other text editors, but it is inconvenient to read. It is usually read using the system function Sys.fn_get_audit_file.

SELECT Event_time, action_id, succeeded, session_id, Session_server_principal_name, object_name, state ment, file_name, Audit_file_offsetfrom sys.fn_get_audit_file (' d:\share\* ', Default,default)

650) this.width=650; "width=" 815 "height=" 118 "title=" clip_image003 "style=" border-top-width:0px;border-right-width : 0px;border-bottom-width:0px; "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M02/5A/1A/ Wkiom1t2fucr8buraagbarrnziy142.jpg "border=" 0 "/>

From the results you can get: who did what at what time . The SELECT and ALTER statements have only one record. Update has two records, a select and an update. Passing the value of Audit_file_offset to Fn_get_audit_file as the third parameter value can be achieved by reading the audit file from the specified offset. Note: The event_time output is UTC time.

Of course, you can also view directly:

650) this.width=650, "width=" 402 "height=", "title=" clip_image004 "style=" border-top-width:0px;border-right-width : 0px;border-bottom-width:0px; "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M00/5A/1A/ Wkiom1t2fucjzoysaac6no5ursk440.jpg "border=" 0 "/>

5. Try the server audit specification

Audit Create and delete logins, and log audit content to the application log.

Use [Master]gocreate SERVER AUDIT [audit-audittest]to application_logwith (queue_delay = 1000,on_failure = CONTINUE) Goalter server AUDIT [Audit-audittest] with (state=on) gocreate Server AUDIT Specification Sas_createdroploginfor server Audit [Audit-audittest]add (Server_principal_change_group) with (state=on) gocreate login xx with password= ' [email Protected] ';d ROP login xx;

From the Event Viewer in Windows, you can view the 33205 event. Personally feel that when viewing data detail events, use XML format to better understand some.

650) this.width=650; "width=" 583 "height=" "title=" clip_image005 "style=" border-top-width:0px;border-right-width : 0px;border-bottom-width:0px; "alt=" clip_image005 "src=" http://s3.51cto.com/wyfs02/M02/5A/17/wKioL1T2f_ Xyldu5aacasssbwye487.jpg "border=" 0 "/>

Summary

1. SQL Server audits are relatively less performance-affected and have a very flexible audit granularity.

2. The review is for "events" and answers "who did what when", but the tracking of the changes in the data itself is weak.

3. The outcome of the audit, whether it's an audit file or an application log, requires additional processing and analysis to get what you want.

This article is from "Joe TJ" blog, be sure to keep this source http://joetang.blog.51cto.com/2296191/1617176

Getting Started with SQL Server auditing: SQL Server Auditing (SQL Server Audit)