SQLSERVER2008 new Audit/Audit features

Most of the time we need to audit/audit database or database server instances

For example, Audit failed logins, DDL statements on a database, audit of DELETE statements in a database table

In fact, our audit needs are basically for one purpose: anti-hacker

These audit requirements are nothing more than to see who is trying to invade the database server , if there is a drop table After the intrusion, whether there is delete data

The options available in SQLSERVER2008 and previous versions are

1. Server-level DDL triggers and database-level DDL triggers (SQL2005 and later) and DML triggers

2, read the operation records from the transaction log, the authoritative book will say the transaction log is not an audit tool, the general large database will be set to Simple mode, transaction log truncation

3, relying on SQL Server errorlog to check the login audit, resulting in SQL Server errorlog login-related Log flooding caused the difficulties

4. Event Notification: http://www.cnblogs.com/gaizai/p/3473553.html
5. Change Tracking: http://www.cnblogs.com/gaizai/p/3482579.html
6. Change data Capture (CDC): http://www.cnblogs.com/gaizai/p/3479731.html

We will generally put C2 audit trail and login audit only successful login to prevent SQL Errorlog log flooding, because the server is a long time to restart once, if not to make changes can easily cause disk full

--Disable C2 audit trail and only successful login exec sys.sp_configure n ' c2 audit mode ', n ' 0 ' goreconfigure with overridegouse [Master]goexec xp_ Instance_regwrite n ' HKEY_LOCAL_MACHINE ', n ' software\microsoft\mssqlserver\mssqlserver ', n ' AuditLevel ', REG_DWORD, 1GO

SQLSERVER2008 New Auditing Features

With the addition of audit features in sqlserver2008, you can audit/audit the server-level and database-level operations, and I think the audit function is a unified approach to many of the solutions above

Let's take a look at how the audit is used

Audit object

Step One: Create audit objects, audit objects are associated with the save path, so if you need to save the audit action log to a different path, you need to create a different audit object

We save the audit operation log in the file system, before we create the relevant path to create a good saved folder, we first create the Sqlaudits folder in the D disk, and then execute the following statement

--Need to switch to master database before creating Audit object use [Master]gocreate SERVER AUDIT Myfileaudit to FILE (filepath= ' D:\sqlaudits ')-- The specified folder cannot specify a file, and the resulting file will be saved in this folder go

In fact, we can specify audit options while creating audit objects, and the following are the relevant scripts

The advantage of putting logs on disk is that you can use the new Tvf:sys. [Fn_get_audit_file] to filter and sort the audit data, if the audit data saved in the Windows Event log query is very troublesome

Use [master]gocreate SERVER AUDIT Myfileaudit to FILE (filepath= ' D:\sqlaudits ', maxsize=4gb,max_rollover_files=6) with ( on_failure=continue,queue_delay=1000); ALTER SERVER AUDIT Myfileaudit with (state =on)

MAXSIZE: Indicates that the maximum size of each audit log file is 4GB

Max_rollover_files: Indicates the number of scrolling files, similar to SQL errorlog, how many files have been reached after deleting the previous history file, here are 6 files

On_failure: Indicates the action when the audit data error occurs, this is the continuation of the audit, if you specify shutdown, then the entire instance will be shutdown

Queue_delay: Indicates the delay time of the write of the audit data, here is 1 seconds, the minimum is 1 seconds, if the specified 0 means real-time write, of course, performance has some impact

State: Indicates the startup audit function, state this option cannot be shared with other options, so you can only have a single sentence

When you modify audit options, you need to disable auditing before you turn on auditing

Alter server AUDIT Myfileaudit with (state =off) alter server AUDIT Myfileaudit with (Queue_delay =1000) alter server AUDIT My Fileaudit with (state =on)

Audit specification

In SQL Server audit there is the concept of audit specifications, an audit object can only bind one audit specification, and an audit specification may be bound to multiple audit objects

Let's take a look at the script

CREATE Server AUDIT Specification captureloginstofilefor server AUDIT myfileauditadd (failed_login_group), ADD ( Successful_login_group) with (state=on) gocreate server AUDIT myappaudit to Application_loggoalter server AUDIT Myappaudit with (state =on) ALTER Server AUDIT specification Captureloginstofile with (State=off) goalter SERVER AUDIT speci Fication captureloginstofilefor SERVER AUDIT myappauditadd (Failed_login_group), ADD (Successful_login_group) with ( State=on) GO

We create a server-level audit specification Captureloginstofile, and then create one more Audit object Myappaudit, which will save the audit log to the Windows Event log application log

We disable the audit specification Captureloginstofile, modify the audit specification Captureloginstofile belongs to the Audit object Myappaudit, modify the success

If you want to bind multiple audit specifications to the same audit object, you will get an error.

CREATE Server AUDIT Specification captureloginstofileafor server AUDIT myfileauditadd (failed_login_group), ADD ( Successful_login_group) with (state=on) gocreate Server AUDIT specification captureloginstofilebfor server AUDIT Myfileauditadd (Failed_login_group), ADD (Successful_login_group) with (state=on) go--msg 33230, Level 16, State 1, line 86th-Audit ' The Myfileaudit ' audit specification already exists.

Here to say: Audit objects and audit specification changes, whether auditing objects or audit specifications, before modifying their relevant parameters, he must first disable, then modify, and then enable

--Disable Audit object alter server AUDIT Myfileaudit with (state =off)--Disable server level audit specification Alter Server AUDIT specification Captureloginstofile With (state=off) go--Disable database-level audit specification ALTER DATABASE AUDIT specification Capturedbloginstofile with (State=off) go--related Modify options action--Enable Audit object alter server AUDIT Myfileaudit with (state =on)--Enable server level audit specification Alter Server AUDIT specification Captureloginstofile with (state=on) go--enable database-level audit specification ALTER DATABASE AUDIT specification Capturedbloginstofile with (state= ON) GO

Audit server-level events

Audit Service level events, we generally use the most is to audit logon failure events, the following script is to audit logon success events and logon failure events

CREATE Server AUDIT Specification captureloginstofilefor server AUDIT myfileauditadd (failed_login_group), ADD ( Successful_login_group) with (State=on) GO

Modify the Audit specification

--As with auditing objects, you must disable ALTER Server AUDIT specification Captureloginstofile with (state =off) ALTER server AUDIT When you change the audit specification Specification Captureloginstofileadd (LOGIN_CHANGE_PASSWORD_GOURP), DROP (successful_login_group) ALTER SERVER AUDIT Specification Captureloginstofile with (state =on) GO

Audit Action Group

Each audit action group corresponds to an operation in which there are 35 operations groups in SQLSERVER2008, including backup and restore operations, changes to database ownership, adding or removing logged-in users from server and database roles

To add an Audit action group, you only need to use Add in the audit specification, and the following statement adds an action group to log on the user to modify the password operation

ADD (LOGIN_CHANGE_PASSWORD_GOURP)

Audit Database-level events

Database audit specifications exist in their database and cannot be audited for database operations in tempdb

CREATE DATABASE AUDIT specification and alter DATABASE AUDIT specification

Work in the same way as the server audit specification

There's a total of 15 database-level operations groups in SQLSERVER2008
7 database-level audit actions are: SELECT, insert,update,delete,execute,receive,references

The relevant scripts are as follows:

--Create Audit object use [master]gocreate server AUDIT mydbfileaudit to FILE (filepath= ' D:\sqldbaudits ') goalter  SERVER AUDIT  Mydbfileaudit with (state=on) go--CREATE database-level audit specification use [sss]gocreate database AUDIT specification Capturedbactiontoeventlogfor SERVER AUDIT Mydbfileauditadd (Database_object_change_group), ADD (SELECT, Insert,update , DELETE on schema::d bo by Public   ) with (state =on)

We'll first create the Sqldbaudits folder on the D drive.

The first Action group records the DDL statement Create,alter,drop, and so on for all objects in a database
The second statement monitors DML operations made by any public user (that is, all users) against any object of the DBO schema

You can see the relevant audits in SSMs after you've created them.

Database Audit Specification

Server audit specifications and audit objects

View Audit Events

Audit files that are logged to the file system are not stored in a text file that can be opened with Notepad, but in a binary file

Here's one, when the disk space is low, you can delete these sqlaudit files directly

If you are using DDL triggers: Http://www.cnblogs.com/gaizai/p/3363220.html?ADUIN=1815357042&ADSESSION=1387155615&ADTAG=CLIENT. qq.5275_.0&adpubno=26274

Typically, a table is created in the database to hold the audit data, but when the amount of data in the table reaches a lot, the DBA needs to maintain the table

The workload has increased, and you might say that I don't have a lot of audits, so there's not much data to audit, but for some big companies

The data they want to audit is very numerous, some need to be archived, and some do not need to be archived

I prefer this way of not having to archive audit data, deleting the oldest audit file when there is not enough disk capacity.

We have two ways to view audit logs

Method One: Object Explorer-"security-" audit-"Select an Audit object-right-" view audit log

Auditing items include: date, timestamp, server instance name, Operation ID, class type, serial number, success or failure, column permissions, database principal ID, server principal name,

Server principal SID, actual statement executed (or attempted), etc.

Method Two: Use the new table-valued function, sys. [Fn_get_audit_file] ()

This function accepts parameters for one or more audit files (using wildcard pattern matching)

and two additional parameters can be used to specify the starting file to be processed, and to begin the read audit of the known offset location

Both parameters are optional, but must still be specified with the keyword default, which then reads the binary data from the file and formats the auditing entries

Server-level auditing

Query the information in this file based on the Sqlaudit file of the most recent time

SELECT [event_time] as ' trigger audit date and time ', sequence_number as ' record order in single audit record ', action_id as ' Operation ID ', SU Cceeded as ' action succeeded ', Permission_bitmask as ' permission Mask ', is_column_permission as ' is column-level permission ', Sessio N_ID as ' the ID of the session where the event occurred ', server_principal_id as ' execution login context id ', database_principal_id as ' execution of the database user context ID of the operation ', target_server_principal_id as ' performs grant/deny/revoke operation of the server body ', target_database_principal_id as ' execution GRAN        T/deny/revoke the database principal ', object_id as ' the ID of the entity audited (server object, DB, Database object, schema Object) ', Class_type as ' auditable entity type ', Session_server_principal_name as ' session server principal ', server_principal_name as ' current login name ', Server_principal_sid as ' Current login SID ', database_principal_name as ' current user ', target_server_principal_name as ' operation Target login ', target_  Server_principal_sid as ' target login Sid ', target_database_principal_name as ' operation target user ', server_instance_name as        ' Name of the server instance being audited ',database_name as ' the database context in which this operation occurred ', schema_name as ' The schema context for this operation ', object_name as ' the name of the entity audited ', statement        As ' TSQL statement (if present) ', additional_information as ' unique information for single event, returned as XML ', file_name as ' the path and name of the audit log file from the source of the record ',        Audit_file_offset as ' contains the buffer offset in the file for Audit Records ', user_defined_event_id as ' as ' user-defined event ID passed as sp_audit_write parameter ', User_defined_information as ' to record any additional information that the user wants to record in the audit log by using the Sp_audit_write stored procedure ' from sys. [Fn_get_audit_file]                                (' D:\sqlaudits\MyFileAudit_F0BCDC6F-0A89-459D-B345-9DDEB036CC39_0_130595725124220000.sqlaudit ',   Default, default) WHERE [Event_time] between ' 2014-11-04 11:02:00 ' and ' 2014-11-04 11:18:00 '

Database-level Auditing

Execute the following script to query some data first

Use [Sss]goselect * from [dbo]. [Nums]

SELECT [event_time] as ' trigger audit date and time ', sequence_number as ' record order in single audit record ', action_id as ' Operation ID ', SU Cceeded as ' action succeeded ', Permission_bitmask as ' permission Mask ', is_column_permission as ' is column-level permission ', Sessio N_ID as ' the ID of the session where the event occurred ', server_principal_id as ' execution login context id ', database_principal_id as ' execution of the database user context ID of the operation ', target_server_principal_id as ' performs grant/deny/revoke operation of the server body ', target_database_principal_id as ' execution GRAN        T/deny/revoke the database principal ', object_id as ' the ID of the entity audited (server object, DB, Database object, schema Object) ', Class_type as ' auditable entity type ', Session_server_principal_name as ' session server principal ', server_principal_name as ' current login name ', Server_principal_sid as ' Current login SID ', database_principal_name as ' current user ', target_server_principal_name as ' operation Target login ', target_  Server_principal_sid as ' target login Sid ', target_database_principal_name as ' operation target user ', server_instance_name as        ' Name of the server instance being audited ',database_name as ' the database context in which this operation occurred ', schema_name as ' The schema context for this operation ', object_name as ' the name of the entity audited ', statement        As ' TSQL statement (if present) ', additional_information as ' unique information for single event, returned as XML ', file_name as ' the path and name of the audit log file from the source of the record ',        Audit_file_offset as ' contains the buffer offset in the file for Audit Records ', user_defined_event_id as ' as ' user-defined event ID passed as sp_audit_write parameter ', User_defined_information as ' to record any additional information that the user wants to record in the audit log by using the Sp_audit_write stored procedure ' from sys. [Fn_get_audit_file]                                (' D:\sqldbaudits\MyDBFileAudit_698BA060-CC40-4A3C-B19D-12B370712404_0_130595753193920000.sqlaudit ', Default, default)

The benefit of saving audit logs to the file system is that the audit data can be filtered and sorted using the where and order by in TVP

and audit-related views

--Query Audit related view SELECT * FROM sys. [Server_file_audits] SELECT * FROM Sys. [Server_audit_specifications] SELECT * FROM Sys. [Server_audit_specification_details] SELECT * FROM Sys. [Database_audit_specifications] SELECT * FROM Sys. [Database_audit_specification_details] SELECT * FROM Sys. [Dm_server_audit_status] SELECT * FROM Sys. [Dm_audit_actions] SELECT * FROM Sys. [Dm_audit_class_type_map]

Delete Related objects

--Delete order--Delete database audit specification use [Sss]goalter database AUDIT specification [Capturedbactiontoeventlog] with (State=off) godrop DATABASE AUDIT Specification [capturedbactiontoeventlog]go--Delete server audit specification use [Master]goalter server  AUDIT Specification [Captureloginstofile] with (state=off) godrop SERVER AUDIT specification [Captureloginstofile] go--Delete Audit objects alter server AUDIT [Myfileaudit] with (state=off) goalter SERVER AUDIT [Myappaudit] with (state=off) Goalter SERV  ER AUDIT [Myeventlogaudit] with (state=off) godrop server AUDIT [myappaudit]godrop server AUDIT [Myfileaudit]godrop Server] AUDIT [Myeventlogaudit]go

Summarize

This article summarizes the new auditing function of SQLSERVER2008, and the topic of "Audit" in SQL Server Forum is a lot of people ask.

Hope that through this article, can let you know the new audit function, in the production environment encountered problems can also communicate with each other

The biggest benefit of auditing is that you use a self-built audit table to hold audit data, and if a smart hacker breaks your database instance, he can naturally take your audit table

Drop off, you also can't find any trace of the hacker, and the audit is different, he put the audit data outside SQL Server, unless your company's SA and DBA security awareness

Very weak, hackers have the opportunity to delete the disk files, or it is still possible to find clues to the hacker to prevent!!

SQLSERVER2008 new Audit/Audit features