SQL Server Audit (Audit)--Audit access to database objects

SQL Server Audit (Audit )-- audit access to database objects

Task 1 : Create a login account and grant the appropriate permissions

Step 1 : Open SSMs, enter the following statement, create a login account Ultrasql, grant access to Person.person and Person.password permissions to the ADVENTUREWORKS2012 database.

Use mastergo--create login ultrasqlcreate login ultrasqlwith password=n ' Mpdfzh7 ', default_database=adventureworks2012 , Check_expiration=off, Check_policy=offgo--create User ultrasql in Adventureworks2012use AdventureWorks2012GOCREATE USER ultrasql for LOGIN ultrasqlgo--grant select Privilege to Person.person & Person.passwordgrant Select on person.pe Rson to Ultrasqlgrant SELECT on Person.password to Ultrasqlgo

Task 2 : 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.

L Enter audit-adventureworks2012-accesstable in "Audit name".

L Select "File" in "Audit destination"

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

L Uncheck "Unlimited" in the "Maximum Rollover files" box.

L Enter 100 in "Number of files".

L Uncheck "Unlimited" located under the "Maximum File Size" box.

L in "Maximum File Size" Enter 10, select the "MB" box.

Set "Maximum file Size" to 10MB, you can keep the size of each audit file within the specified size, when the space for this audit file is exhausted, the setting of the Maximum rollover files option will be referenced, depending on the default value, The maximum amount of audit file data that can be retained is up to 2,147,483,647 files. Here, each audit file can be up to 10MB, and the maximum number of files that can be retained is 100.

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

Step 4 : Click OK to complete the setup.

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

Task 3 : Create an audit to access the data table

Step 1 : Expand "Database", "AdventureWorks2012", "Security", "Database Audit Specifications" node.

650) this.width=650; "title=" clip_image002 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "border=" 0 "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M00/57/9D/ Wkiom1sgslgcwzjfaadwuwf0ply694.jpg "height=" 325 "/>

Step 2 : in the "Database Audit Specifications" node, right-click to select the "New Database Audit specification ..." option.

Step 3 : Enter the following parameters in the "Create Database Audit Specification" window.

L Enter Databaseauditspecification-ultrasqlaccessperson.password in "Name".

L in the "Audit" drop-down box, select the Audit object audit-adventureworks2012-accesstable that you created earlier.

L Choose Select in the Audit Action Type.

L Select the "Object" option in "Object Class".

L Click the chart in "Object Name" and select the "person.password" option.

L Click the chart in "Principal Name" and select the "ultrasql" option.

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

Step 4 : in step 3, in the second row of the action area, add a watch that says "Audit Action Type" is "UPDATE". Person.password for the user ultrasql query or update data table.

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

Step 5 : Right click on the database Audit specification "Databaseauditspecification-ultrasqlaccessperson.password" that you just created and select "Enable database Audit Specification "option.

Task 4 : Test the audit feature

Step 1: connect to AdventureWorks2012 via SSMs as Ultrasql.

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

Step 2 : use ultrasql account to perform query and update on data table Person.password.

Use Adventureworks2012goselect * from Person.passwordselect * from Person.personselect * from Person.passwordupdate Perso N.password SET modifieddate=getdate () WHERE businessentityid=1/*msg 229, Level A, State 5, line 1The UPDATE permission wa S denied on the object ' Password ', database ' AdventureWorks2012 ', schema ' person '. */select * from Person.password WHERE Bu Sinessentityid=1select A.passwordhash,b.firstname,b.lastnamefrom Person.password a INNER JOIN Person.Person BON A. BusinessEntityID = B.businessentityidselect TOP 1 * from Person.password WHERE businessentityid=1

Task 5 : Use "log File Viewer" to read the audit log

Step 1 : log in as sysadmin, expand "Object Explorer", "Security", "audits" node.

Step 2 : Right-click on audit "Audit-adventureworks2012-accesstable" and select "View Audit Logs".

Step 3 : on the left side of the Log File Viewer window, select "Audit Collection", "audit-adventureworks2012-accesstable", and use the following methods to read the recorded audit information.

L in the "Log file summary" area on the right, under the Action ID box, click the data row log for the "select" Action type.

L in the "Database Principal Name" area below, you can see the logged user. You can see the recorded program code in the "Statement" area.

650) this.width=650; "title=" clip_image006 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "border=" 0 "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M01/57/9D/ Wkiom1sgslfigpjsaaydb6gjmra588.jpg "height=" 686 "/>

There is a special phenomenon, organized as follows.

To execute the UPDATE statement, for example, two "action ID" behaviors such as select and update will be raised, and two audit logs will be generated, with the data recorded in addition to the "Action ID" area (one is select, one is update) , the rest are the same. In addition, even if the user Ultrasql does not have permission to modify the data table, these behaviors will also be logged by the Audit object, but in the "succeeded" section the log is false.

650) this.width=650; "title=" clip_image007 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "border=" 0 "alt=" clip_image007 "src=" http://s3.51cto.com/wyfs02/M02/57/9D/ Wkiom1sgslijy87jaazt-vb2y4s797.jpg "height=" 682 "/>

Task 6 : Use T-SQL functions to parse and filter the audited log data

Step 1 : using the sysadmin identity, execute SSMs, enter the following query statement, and use the Sys.dm_server_audit_status dynamic management view to view the current status of each Audit object.

Use Mastergoselect audit_id n ' audit id ', name n ' Audit name ', Status_desc N ' Server audit status ', Status_time n ' last status Changed Timestamp ', audit_file_size n ' Audit file size ', Audit_file_path N ' audit file full path ' from Sys.dm_server_audit_ Status

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

L data row Status_desc (server audit status): Check that each Audit object is enabled.

L data row Audit_file_size (audit file size (KB)): View the audit file that is the target of the binary file, the space used, in kilobytes, or the audit log that is not a binary file, such as the Windows Application event log file. Null is displayed.

L Data row Audit_file_path (the full path name of the audit file destination): View the full path and file name of the audit file that is the target of the binary file.

Step 2 : Click "New Query" to execute the following code to analyze the audit file using the function Fn_get_audit_file

--ex1 query the audit file/*fn_get_audit_file  (Transact-SQL) Fn_get_audit_file (file_ Pattern, {default | initial_file_name | null },{default | audit_file_ Offset | null}) Param:file_patternset the audit file full path.*/select  * from sys.fn_get_audit_file (N '  d:\mssql\data\audit_logs\audit-login-create%9alter%9drop_ Af3aaecb-30cc-4476-9395-8754e60e356c_0_130639659785910000.sqlaudit ', default,default);--EX2 Using *  Query some Audit Files created by the same audit  Objectselect * from sys.fn_get_audit_file (N '   '  d:\mssql\data\audit_logs\ Audit-login-create%9alter%9drop_*.sqlaudit ', default,default);--ex3 attention: event_time type  is datetime2,stored gmtselect event_time n ' audit caused date &  time (GMT) ',  server_principal_name&nbsP N ' Login ', database_principal_name n ' User ',  database_name n ' Database ', object_name  N ' object name ',  statement n ' TSQL ' from sys.fn_get_audit_file (n '   '  d:\mssql\data\ Audit_logs\audit-login-create%9alter%9drop_*.sqlaudit ', Default,default);--ex4 convert to  Timezone bejing (gmt+08:00)  using data type datetimeoffset & function  switchoffset) Select switchoffset (CAST (event_time as datetimeoffset), ' +08:00 ')   N ' Audit action caused date & time (timezone bejing gmt+08:00) ', Server_ Principal_name n ' Login ',  database_principal_name n ' User ', database_name n ' database ',  Object_name n ' Object name ',  statement n ' TSQL ' from sys.fn_get_audit_file (N '   '  d:\mssql\data\audit_logs\audit-login-create%9alter%9drop_*.sqlaudit ', default,default);--EX5 Import  all audit files  In the directory into systemselect *from sys.fn_get_audit_file (N '   '  D : \mssql\data\audit_logs\* ', Default,default);--ex6 convert to timezone bejing (GMT+08:00)  using data type datetimeoffset & function switchoffset) SELECT  Switchoffset (CAST (event_time as datetimeoffset), ' +08:00 ')  n ' audit action caused  Date & time (timezone bejing gmt+08:00) ', server_principal_name n ' Login ',  Database_principal_name n ' User ', database_name n ' Database ',  object_name n ' object name ' ,  statement n ' TSQL ' from sys.fn_get_audit_file (N '   '  d:\mssql\data\audit_logs\* ', Default,default);

Description

L in the EX1 section of the first variable of the function Fn_get_audit_file, after the change to execute sys.dm_server_audit_status, get the value portion of the field Audit_file_path, or fill in the file name of the full audit file. In the second and third variables, fill in default, and take the defaults.

To load multiple audit files generated by the same audit object into the system for analysis, you can use the wildcard character * in the file name to refer to the EX2 code.

The date time recorded by the audit is GMT (GMT), the data type is datatime2, can be converted to the data type DateTimeOffset, and the function Switchoffset, the data is changed to the Beijing Time Zone (gmt+08:00) format to display, Refer to EX3 and EX4.

If you need to load each audit file within the specified folder into the system for analysis, you can use the wildcard characters *, refer to EX5 and EX6.

Task 7 : Archive storage of audit files

Step 1 : Create a new folder Archive_audit within D:\MSSQL\DATA\Audit_logs.

Step 2 : open Folder D:\MSSQL\DATA\Audit_logs, copy the audit files (*.sqlaudit) within this folder into the folder D:\MSSQL\DATA\Audit_logs, this is the way to easily backup audit files.

Step 3 : To delete an audit file that has been backed up, confirm that the Audit object is disabled. Otherwise, the audit file being used will not be deleted.

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

SQL Server Audit (Audit)--Audit access to database objects