SQL Server Audit feature-records all operational records

SQL Server Audit-record all operational records

When it comes to auditing, it is believed that as an Enterprise Manager, as an ad administrator, it is generally possible to turn on auditing through policy and record some custom transaction logs. Auditing is the same for SQL Server, where the SQL Server audit object collects actions and behavior monitoring groups for a single instance of the server or database level. Auditing is at the SQL Server instance level. There can be multiple audit/SQL Server instances. When you define an audit, you specify the location of the output results. This is the audit target. Auditing is a disabled state that is created and does not automatically audit any actions. After auditing is enabled, data is received from the audit audit target. Auditing an instance of SQL Server database engine or a single database includes trace and logging events that occur in the database engine. SQL Server audit Server allows you to create audits that can contain audit specifications for server-level events, and database audit specifications for database-level events. Audit events can be written to an event log or an audit file. The introduction of the audit is not much introduced here, we can search the official web site to check. Start our topic today: We record the user insert, UPDATE, delete operations to the database by turning on SQL Server audits.

We first created a test database DB1 and then created a table info

Insert some test data

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/ED/wKioL1jPZNTQczzdAAFn_Nvlxuo450.png" height= "484"/>

Next we create an audit to monitor all operations on the database

Under DB instance-security-audit-new audit

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/ED/wKioL1jPZNaDAPPIAAGi9nEUXqk788.png" height= "446"/>

Define the audit name and the path and size configuration of the audit file.

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/EE/wKioL1jPZNqA4gY3AAJBQwNTMrM237.png" height= "484"/>

We can also use scripts

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.

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/F0/wKiom1jPZNzQn6weAAJOXE9q1Ms688.png" height= "484"/>

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.

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/EE/wKioL1jPZN6BcTk5AAG_D8ZTiZs004.png" height= "384"/>

Disabled after the audit creation is complete

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/F0/wKiom1jPZODT6oLRAAG1Eaew7ss706.png" height= "484"/>

We right-click--Enable auditing

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/EE/wKioL1jPZOGAtFchAAHjbxOoBUg176.png" height= "464"/>

Enable success

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/F0/wKiom1jPZOLgwrn7AAGT1LdUCbw488.png" height= "406"/>

We want to define which database table to audit operations, and which database we need to create a new--server audit specification

Define the server audit specification name-and the previously created audit service

We need to create an audit specification under the current database

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/F0/wKiom1jPZOSzE-fuAAF0NJkqDN4566.png" height= "484"/>

We select actions such as INSERT, delete, update by defining the type of audit action---

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/F0/wKiom1jPZOWCe5k_AAECiDcOjA0190.png" height= "331"/>

We can use scripts to create

Use [db1]gocreate DATABASE AUDIT specification [serverauditspecification-db1]for SERVER AUDIT [Audit-db1]add] (DELETE on D ATABASE::[DB1], add (INSERT on DATABASE::[DB1] by [public]), add (UPDATE on DATABASE::[DB1] by [public]) with (S TATE = ON) GO

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/F0/wKiom1jPZOaz0XStAAIvVY4ebXs530.png" height= "402"/>

This will be a database DB1 under the database audit specification

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/EE/wKioL1jPZOjTSbTUAAHue3NtFq0352.png" height= "473"/>

We'll work on the database next

We insert a piece of data and then view the audit

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/F0/wKiom1jPZOqC5geCAAFKoGGKbIk591.png" height= "407"/>

Right-click-View audit log

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M02/8E/EE/wKioL1jPZOzg2ZWxAAHRzB0ZwwM012.png" height= "484"/>

We found that the operation type information can also be seen in the following ways, we can configure the filter to display the custom content

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/EE/wKioL1jPZO3RaqcWAACwWi--xCA522.png" height= "336"/>

Once again, we need to review the audit log for production, and we can view it through the command line by using the following information:

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M00/8E/F0/wKiom1jPZO7h4MOhAABkN4Q2c_A352.png" height= "196"/>

https://msdn.microsoft.com/en-us/library/cc280765 (v=sql.110). aspx

SELECT * from Sys.fn_get_audit_file (' \\serverName\Audit\HIPPA_AUDIT.sqlaudit ', default,default); Goselect * from Sys.fn_get_audit_file (' D:\Audit_Log\Audit-DB1_29F2408B-C8F5-4747-A3F9-CCFD66D0D8C1_0_ 131343921494640000.sqlaudit ', default,default); GO

After we query, found not very friendly, so we need to jump out of the key fields to find

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/EE/wKioL1jPZO_y0epHAAEiPvriNi4094.png" height= "267"/>

We need to list the key fields separately, just show the key names.

SELECT Event_time, action_id, succeeded, session_id, Session_server_principal_name, object_name, statement, file_name, Audit_file_offsetfrom sys.fn_get_audit_file (' D:\Audit_LogFiles\Audit-DB_7CB2AD0B-C97D-4478-8360-E3C3D11B216C_0_ 131343949988730000.sqlaudit ', Default,default)

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/8E/F0/wKiom1jPZPDwrzTPAAE4Fvr5L0o922.png" height= "285"/>

This article from "Gao Wenrong" blog, declined reprint!

SQL Server Audit feature-records all operational records