SQL Server Audit (Audit)--Create a database-level audit

SQL Server Audit (Audit)--Create a database-level audit

Task 1: Create an audit

Step 1 : open SSMs, log on to the specified instance, expand the "Security", "audits" node.

Step 2 : on the audits node, right-select the "New Audit ..." option.

Step 3 : in the "Create Audit" window, enter the following parameters.

? Enter Audit-adventureworks2012-create/alter/drop in "Audit name".

? Select "File" in "Audit destination"

? Enter D:\MSSQL\DATA\Audit_logs in "File path".

Step 4 : Click OK to complete the setup.

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M00/57/84/wKiom1ScuxiCmJT9AAPztowP4GY469.jpg "border=" 0 "height=" 680 "/>

Step 5 : Right-click on the audit "Audit-adventureworks2012-create/alter/drop" you just created and select the "Enable Audit" option.

650) this.width=650; "title=" clip_image002 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M01/57/84/wKiom1ScuxmyqAgxAAC2NzWrG_c744.jpg "border=" 0 "height=" 253 "/>

Task 2 : Create a database-level audit specification – Monitor the creation, deletion, and modification of database objects.

Step 1 : in the "Object Explorer" window, expand "Databases", "AdventureWorks2012", "Security", "Database Audit Specifications" node.

650) this.width=650; "title=" clip_image003 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M01/57/84/wKiom1ScuxnyDJ8NAADfJrBfTig035.jpg "border=" 0 "height=" 307 "/>

Step 2 : in the Database Audit specifications node, right-click to select the Create Database Audit specification option.

Step 3 : in the Create Database Audit Specification window, enter the following parameters.

L Enter Databaseauditspecification-object-create/alter/drop in "Name".

L The Audit object "Audit-adventureworks2012-create/alter/drop" that was created before "Audit" is selected.

L in the "Actions" area, under "Audit Action Type", select "Schema_object_change_group" in the first row.

650) this.width=650; "title=" clip_image004 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M02/57/84/wKiom1ScuxqzMCgFAAP09cOOs2A510.jpg "border=" 0 "height=" 679 "/>

"Schema_object_change_group" is a database-level audit Action group that is raised when the Create, alter, and drop operations are performed on the schema.

Step 4 : Click the "OK" button to complete the setup.

Step 5 : Right-click the database audit Specification "Databaseauditspecification-object-create/alter/drop" that you just created and select "Enable database Audit Specification "option.

650) this.width=650; "title=" clip_image005 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image005 "src=" http://s3.51cto.com/wyfs02/M00/57/84/wKiom1Scux6yYYXsAACzuTSshGA481.jpg "border=" 0 "height=" 253 "/>

Task 3 : Test auditing features – Create, modify, and delete database objects.

Step 1 : open SSMs, click New Query to execute the following code to create, modify, and delete objects in the database AdventureWorks2012.

USE ADVENTUREWORKS2012GO--CREATE DATABASE OBJECTCREATE TABLE T1 (Col1 int  Not null primary key) gocreate view v1 as select * from  T1GOCREATE SYNONYM SYN1 FOR T1GOCREATE FUNCTION FUN1 (@InParm  int)   returns int as begin return  @InParm  endgocreate procedure proc1 (@ Inparm int)  AS BEGIN SET  @InParm  = 0 ENDGOCREATE TRIGGER  Trig1 on t1 after insert as raiserror (' this is a dummy table ', 16,10) Go--alter database objectalter table t1 add col2 varchar (+) GOALTER  PROC PROC1 AS SELECT 1GO--Drop Database ObjectDROP TRIGGER  trig1godrop procedure proc1godrop function fun1godrop synonym syn1godrop  View v1godrop table t1GO 

Task 4 : Use the log file Viewer to read the audit log.

Step 1 : Expand "Security", "audits" node.

Step 2 : on the "Audit-adventureworks2012-create/alter/drop" object you just created, right-select "View Audit Logs".

Step 3 : in the "Select Logs" area in the upper-left corner of the "Log File Viewer" window, verify that "Audit Collection" is checked, "audit-adventureworks2012-create/alter/ Drop ". Use the following methods to read the recorded audit data.

650) this.width=650; "title=" clip_image006 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M02/57/82/wKioL1Scu86Bsh8SAAccZDsMlHA393.jpg "border=" 0 "height=" 681 "/>

In the "Log file Summary" area on the right, in the "Action ID" area, click "CREATE", "ALTER", "DROP" and other operation types.

The "Class Type" displays database objects such as "TABLE", "VIEW", "STORED PROCEDURE" for each type of object recorded.

In the "Statement" area below, you can see the program code that is logged.

L Gets the date and time that the event occurred, possibly using the newly supplied datetime function sysutcdatetime () after SQL Server 2008. The postback data type is datetime2, and the content value contains the date and event of the server that is executing the instance of SQL Server. This date and time is returned in the time format of the International Standard Time (coordinated Universal TIME:UTC), this function will retrieve the GMT (Greenwich Mean TIME:GMT) on the server, which is 8 hours behind the GMT standard.

Note:datetime2 is a new data type after SQL Server 2008, contains date data, and combines the time based on the 24-hour system. Datetime2 can be considered an extension of the existing datetime data type, which has a large date range, a more precise fractional number of significant digits, and a selective user-specified number of significant digits. Date range can be from January 1, 1 A.D. to December 31, 9999, the time range can be from 00:00:00 to 23:59:59.9999999, the accuracy is 100ns (1ns=10 negative nine seconds).

Using Greenwich Mean Time (GMT) to record datetime data, it is a practice to standardize the time of the log for DBAs who have database servers in different time zones. But for users in non-GMT (GMT) time zones, such as Beijing's time zone (gmt+08:00), it can be a problem. In the next practice, you will use the T-SQL function Fn_get_audit_file to analyze and view the data for the audit log, and you can convert Greenwich Mean Time (GMT) to the appropriate timezone information.

Task 5 : Use the Windows application event log file instead to log audit information.

Step 1 : on the "Security", "Audit-adventureworks2012-create/alter/drop" object on the "audits" node, right-click to disable auditing.

650) this.width=650; "title=" clip_image007 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image007 "src=" http://s3.51cto.com/wyfs02/M02/57/82/wKioL1Scu8-z3OlXAAC3CPdpzPk315.jpg "border=" 0 "height=" 252 "/>

To adjust the properties of the "audits" object, you must disable this "audits" before you can adjust it, otherwise an error message will be generated and the property cannot be adjusted.

Step 2 : Click the Approved object that is disabled, right-click to select the "Properties" option.

Step 3 : in the Audit Properties window, in the Audit destination box, drop down to select Application Log.

650) this.width=650; "title=" clip_image008 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image008 "src=" http://s3.51cto.com/wyfs02/M00/57/82/wKioL1Scu9CClDtiAAIY9nj8W88070.jpg "border=" 0 "height=" 677 "/>

Step 4 : Click the "OK" button to complete the adjustment of the property.

Step 5 : Click the Approved object that is disabled, and then right-enable auditing.

Step 6 : T-SQL code to create, modify, and delete database objects AdventureWorks2012 the database before executing again.

Task 6: Review the audit information that is stored in the Windows application event log file.

Step 1 : Expand "Security", "audits" node.

Step 2 : on the "Audit-adventureworks2012-create/alter/drop" object you just created, right-select "View Audit Logs".

Step 3 : in the "Select Logs" area in the upper left corner of the "Log File Viewer" window, verify that "Windows NT" and "Application" are checked.

Step 4 : in the "Log file summary" area on the right, you can view the recorded data.

650) this.width=650; "title=" clip_image009 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image009 "src=" http://s3.51cto.com/wyfs02/M00/57/84/wKiom1ScuyOjFB6zAAY5aRJDSdE103.jpg "border=" 0 "height=" 685 "/>

Use the Windows Application event log file method to hold the audit log with the following characteristics.

L Audit the date and time portion of the internal record, which is still recorded in GMT.

The log time that is logged in the Application event log file has been converted to the date time of the database server's time zone.

The audit record to the data is stored in the message area, its "statement" section, there is a record of the executed T-SQL program code. But this is bound to add to the puzzle in reading and subsequent analysis. If compared with the audit target stored in binary files, the audit log stored in binary files is classified in each data row, so that it can not only increase readability, but also be easy to filter and analyze.

If you use the Windows Event Viewer to read the audit data that you have logged, you can view the information that is logged on the application page.

650) this.width=650; "title=" clip_image010 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image010 "src=" http://s3.51cto.com/wyfs02/M00/57/82/wKioL1Scu9KCjEL6AAP7yqi-VmY603.jpg "border=" 0 "height=" 618 "/>

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1596119

SQL Server Audit (Audit)--Create a database-level audit