SQL Server Auditing (Audit)--Auditing components

SQL Server Auditing (Audit)--Auditing components

The architecture for auditing is as follows.

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/77/wKioL1SbbfvRCDKeAAK11W3uhUg032.jpg "border=" 0 "height=" 718 "/>

SQL Server Auditing

Auditing is a combination of actions such as Server action groups, database action groups, and so on, which can be used to collect and monitor server-level or database-level operations. Internal auditing is created using the Extended events (Extended event). The audit component is at the SQL Server instance level. Each instance of SQL Server can have multiple audits. When you define auditing, the output location of the results is specified. This is the target location for auditing. Auditing is created in a disabled state, so no actions are automatically audited. When auditing is enabled, the audit target receives data from the audit.

Server Audit Specification

The server audit specification object belongs to auditing. You can create a server audit specification for each audit, because they are created within the scope of the SQL Server instance.

The server audit specification collects many server-level action groups that are raised by the Extended events feature. You can include the Audit Action group in the server audit specification. Audit action groups are predefined action groups, which are atomic events that occur in the database engine. These actions are sent to the audit, and the audit logs them to the target.

--Creates a server audit called "Hippa_audit" with a binary file as the target and no options. CREATE Server AUDIT hipaa_auditto FILE (FILEPATH = ' \\SQLPROD_1\Audit\ ');/*creates a server AUDIT specification called "H Ippa_audit_specification "That audits failed logins for the SQL Server Audit" Hippa_audit "created Above.*/create Server A Udit specification Hippa_audit_specificationfor SERVER Audit hippa_auditadd (failed_login_group); go--enables the audit. ALTER SERVER AUDIT hipaa_auditwith (state = ON); GO

Database Audit Specification

The database audit Specification object is also part of SQL Server auditing. For each audit, you can create a database audit specification for each SQL Server database.

The database Audit specification collects database-level audit actions that are raised by the Extended events feature. You can add audit action groups or audit events to the database audit specification. An "audit event" is an atomic operation that can be audited by the SQL Server engine. The Audit Action Group is a predefined group of actions. They are all located in the SQL Server database scope. These actions are sent to the audit, and the audit logs them to the target. Do not include server-scoped objects (such as system views) in the User Database audit specification.

Use master; go--Create the server audit. CREATE SERVER AUDIT payrole_security_auditto FILE (FILEPATH = ' C:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\data '); go--Enable the server audit. ALTER SERVER AUDIT payrole_security_auditwith (state = ON); Use AdventureWorks2012; go--Create the database audit specification. CREATE DATABASE AUDIT Specification audit_pay_tablesfor SERVER AUDIT payrole_security_auditadd (SELECT, Inserton humanre Sources. EmployeePayHistory by dbo) with (state = on); GO

Target

The audit results are sent to the target, either the file, the Windows Security event log, or the Windows Application event log. These logs must be reviewed and archived periodically to ensure that the target has enough space to write more records.

From a security point of view, any authenticated user can read and write to the Windows application event log. The application event log requires less permissions than the Windows Security event log and is less secure than the Windows Security event log.

The SQL Server service account must be added to the build security audit policy to write to the Windows security log. By default, local systems, local services, and network services are part of this policy. This setting can be configured by using the Security Policy snap-in (secpol.msc). In addition, the Audit object access security policy must be enabled for both success and failure. This setting can be configured by using the Security Policy snap-in (secpol.msc). In Windows Server 2008, you can set a more detailed "application-generated" policy from the command line by using the Audit Policy program (AuditPol.exe). The Windows Event log is global to the Windows operating system. If you need more precise permissions on auditing, use the binary file destination.

action groups and actions

Using the SQL Server Audit feature, you can audit server-level and database-level event groups and individual events.

SQL Server auditing consists of 0 or more audit action items. These audit action items can be a set of actions, such as Server_object_change_group, or a single action, such as a SELECT operation on a table.

Auditing can have the following categories of actions:

L Server level. These actions include server operations, such as administrative changes and logon and logoff operations.

L Database level. These operations include data manipulation language (DML) and data definition language (DDL) operations.

L Audit level. These actions include actions during the audit process.

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

SQL Server Auditing (Audit)--Auditing components