SQL Server审核功能–发现和体系结构

介绍 (Introduction)

Intended audience

目标听众

This document is intended for database administrators who plan to develop, deploy, assess or implement auditing solutions in Microsoft SQL Server on Microsoft Windows platform.

本文档适用于计划在Microsoft Windows平台上的Microsoft SQL Server中开发，部署，评估或实施审核解决方案的数据库管理员。

Context

语境

Security has become a very critical mission for almost every IT professional. In Europe, for instance, the European Commission has published a regulation called « General Data Protection Regulation » a.k.a. GDPR that allows personal data circulation (in Europe) but under certain conditions: every company must guarantee that its data are safe or at least it did its best to make it secure.

对于几乎所有IT专业人员而言，安全性已成为一项非常关键的任务。 例如，在欧洲，欧洲委员会发布了一项名为《通用数据保护条例》的法规，又称GDPR，该法规允许（在欧洲）流通个人数据，但在某些情况下：每家公司必须保证其数据安全或至少做到了最好使其安全。

This regulation makes auditing a requirement as we should be able to detect security breaches and prove at any time that you made the best effort to guarantee confidentiality, integrity and availability of the system and log any activities of interest. For instance, we need to know that someone (who?) has modified the structure of a table at 1 AM even if it’s planned maintenance. This regulation not only applies to European-based company, but also any company around the world that processes data related European citizens.

该规定使审计成为一项要求，因为我们应该能够检测到安全漏洞，并随时证明您已尽最大努力保证系统的机密性，完整性和可用性并记录任何感兴趣的活动。 例如，我们需要知道有人（谁？）在凌晨1点修改了表的结构，即使该表已计划维护。 该法规不仅适用于总部位于欧洲的公司，而且适用于处理与数据相关的欧洲公民的全球任何公司。

There are multiple ways to audit activity in SQL Server. They use different features and techniques and they all have their advantages and disadvantages. Based on those particularities and what we need to audit, we can come to think that it could be “better” to choose one from another. Among them, there are SQL Server Audits which are built on top of Extended Events.

有多种方法可以审计SQL Server中的活动。 它们使用不同的功能和技术，各有优缺点。 基于这些特殊性以及我们需要审核的内容，我们可以认为，从另一个中选择是“更好”的选择。 其中包括基于扩展事件构建的SQL Server审核 。

But we won’t insist on each of those features as they are already very well introduced by Minette Steynberg in her article entitled “Creating a successful auditing strategy for your SQL Server databases“.

但是，我们不会坚持每个功能，因为Minette Steynberg在其标题为“ 为SQL Server数据库创建成功的审核策略 ”的文章中已经很好地介绍了这些功能。

Actually, in the following sections, we will first take a look at the SQL Server Audit using SQL Server Management Studio (SSMS). This discovery will lead us to notice important components in audit architecture. Finally, we will see what can be done with audits and what built-in tools (dmv, dmf) are at our disposal.

实际上，在以下各节中，我们将首先了解使用SQL Server Management Studio（SSMS）进行SQL Server审核。 这一发现将使我们注意到审计体系结构中的重要组成部分。 最后，我们将看到审计可以做什么以及哪些内置工具（dmv，dmf）可供我们使用。

SQL Server审核的发现 (A discovery of SQL Server Audit)

To provide a good overview of SQL Server Audit, let’s assume that we’ve never read anything about this feature and we are going to use SQL Server Management Studio (SSMS) to get an insight on them. In the following subsections, we will try to set up an audit for DBCC commands.

为了提供对SQL Server审核的良好概述，假定我们从未阅读过有关此功能的任何内容，并且将使用SQL Server Management Studio（SSMS）来对它们进行深入了解。 在以下小节中，我们将尝试为DBCC命令设置审核。

Creating audits using SQL Server Management Studio (SSMS)

使用SQL Server Management Studio（SSMS）创建审核

Creating a SQL Server Audit is pretty simple using SSMS. To do so, we must connect to a SQL Server instance (using SSMS) and go down the tree view to “Security/Audits”.

使用SSMS创建SQL Server审核非常简单。 为此，我们必须连接到SQL Server实例（使用SSMS），并将树形视图下移至“安全性/审计”。

Then right-click on “Audits” and choose “New Audit…”

然后右键单击“审计”，然后选择“新审计...”

And you can fill the form, then click “OK”.

您可以填写表格，然后单击“确定”。

Note
The File path must exist otherwise you get the following error message

注意
文件路径必须存在，否则会出现以下错误消息

As you can see in the figure below, the audit is actually created in a disabled state.

如下图所示，审核实际上是在禁用状态下创建的。

Here are the actions we can do on this audit when we right-click on it:

右键单击审核时，可以采取以下措施：

There are basically two actions that can be useful here: “Enable Audit” and “View Audit Logs”, which is only valuable if the audit is enabled.

基本上有两个可用的操作：“启用审核”和“查看审核日志”，这仅在启用审核后才有用。

But, so far, we haven’t told the audit what user action or feature events we want to audit… Actually, if we enable the audit, we will only get a list of events related to this audit. Here is what is stored in audit log if we enable then disable the audit at this stage: (the audit log is actually truncated because it’s too large to display on a single page)

但是，到目前为止，我们还没有告诉审计我们要审计哪些用户操作或功能事件……实际上，如果启用审计，我们将仅获得与此审计有关的事件列表。 如果启用此功能，则在此阶段禁用审核，这是存储在审核日志中的内容：（审核日志实际上被截断，因为它太大而无法在单个页面上显示）

So, we can say that an audit, audits events on itself.

因此，可以说审核是对自身事件的审核。

Creating Server Audit Specifications

创建服务器审核规范

To tell SQL Server the audit “Audit-Demo-SSMS” has to store for instance, all calls to DBCC, we must create an additional SQL Server object called “Server Audit Specification”:

为了告诉SQL Server审核“ Audit-Demo-SSMS”必须存储例如对DBCC的所有调用，我们必须创建一个名为“ Server Audit Specification”的附加SQL Server对象：

We can right-click on this “folder” and add a new Server Audit Specification. We must link it to the “Audit-Demo-SSMS” audit object and add “DBCC_GROUP” in the “Audit Action Type” column then click “Ok”.

我们可以右键单击该“文件夹”，然后添加新的服务器审核规范。 我们必须将其链接到“ Audit-Demo-SSMS”审核对象，并在“ Audit Action Type”列中添加“ DBCC_GROUP”，然后单击“ Ok”。

Note

注意

1. The Server Audit Specification is created in a disabled state
   服务器审核规范在禁用状态下创建
2. We can’t choose a name for Server Audit Specification
   我们无法为服务器审核规范选择名称

So, in order to keep track of DBCC command execution, we need to enable this server specification as well as the server audit object itself. If we do not enable the server audit specification, these commands won’t be tracked.

因此，为了跟踪DBCC命令的执行，我们需要启用此服务器规范以及服务器审核对象本身。 如果我们未启用服务器审核规范，则不会跟踪这些命令。

Then we can run a DBCC command like DBCC CHECKDB and see what happens…

然后，我们可以运行DBCC命令，例如DBCC CHECKDB ，看看会发生什么……

Note
Log records only appeared once DBCC CHECKDB complete.

注意
只有DBCC CHECKDB完成后，日志记录才会出现。

Database Audit Specifications

数据库审核规范

Well, for curious ones who have the chance to run Enterprise Edition of SQL Server, you will be able to notice that there is also an audit specification for databases:

好吧，对于那些有机会运行SQL Server企业版的好奇者，您将可以注意到，还有一个针对数据库的审核规范：

Summary

摘要

Based on our observations, we can conclude that:

根据我们的观察，我们可以得出以下结论：

• • Server Audit Specifications
    服务器审核规范
  • Database Audit Specifications
    数据库审核规范
• These specifications respond to events that occur on SQL Server instance
  这些规范响应SQL Server实例上发生的事件
• Amongst these events, there are all changes on the audit itself
  在这些事件中，审计本身发生了所有变化
• The audit has an output configuration
  审核具有输出配置

As we could imagine, the audit will output to a given target also known as « Audit Log ». We will see it in further details in following section. Let’s now dive into the architecture of SQL Server Audits…

可以想象，审计将输出到给定的目标，也称为“审计日志”。 我们将在下一部分中更详细地介绍它。 现在让我们深入了解SQL Server审核的体系结构…

SQL Server审核的体系结构 (The architecture of SQL Server Audits)

Audits takes advantage of an Event-based architecture

审核利用基于事件的架构

SQL Server Audit is actually part of an event-based architecture built inside SQL Server that involves several elements that we will be talking about in this section. These elements are combined into a single “package” and specialized to respond to a group of server or database actions.

SQL Server审核实际上是SQL Server内部建立的基于事件的体系结构的一部分，该体系结构涉及我们将在本节中讨论的几个元素。 这些元素组合成一个“包”，专门用于响应一组服务器或数据库操作。

Events may be raised because of user activity like CREATE DATABASE or DBCC CHECKDB statements or because of a change on a feature like Database Mirroring or on the server itself.

可能由于诸如CREATE DATABASE或DBCC CHECKDB语句之类的用户活动，或者由于诸如数据库镜像之类的功能或服务器本身的变化而引发事件。

We can summarize how SQL Server Audit works as follows. First of all, a DBA must create a new SQL Server Audit object and specify the events that should be part of this audit. It will also create mandatory elements to trap those events and get it as input. So, once the audit object is created, every time an event subject to this audit occurs, this event is trapped, eventually transformed, and pushed as an input to the SQL Server Audit object previously created. This object will finally take care of the event and output its data as per its “configuration”. We sometimes refer to that output as the audit.

我们可以总结一下SQL Server审核的工作方式如下。 首先，DBA必须创建一个新SQL Server审核对象，并指定应作为该审核一部分的事件。 它还将创建强制性元素以捕获那些事件并将其作为输入。 因此，一旦创建了审核对象，则每次发生要进行此审核的事件时，都会捕获，最终转换该事件并将其作为先前创建SQL Server Audit对象的输入进行推送。 该对象最终将处理事件，并根据其“配置”输出其数据。 有时我们将该输出称为审核。

The figure sums up the workflow of the way an event is handled by SQL Server Audit in a very high level:

该图在很高的层次上总结了SQL Server Audit处理事件的方式的工作流程：

To provide a suitable comparison, defining SQL Server Audits reminds me the way to define an event handler when implementing a graphical user interface.

为了提供适当的比较，定义SQL Server审核使我想起了在实现图形用户界面时定义事件处理程序的方法。

In that context, there could be a “ButtonClicked” event that is raised by user interface to an event manager and we should define an “OnButtonClicked” procedure that should run whenever the “ButtonClicked” event occurs. To make it happen, we need to link this event and the procedure we wrote. In some language, we will use a SIGNAL.. SLOT call, in some other we would call an AddEventHandler function. With SQL Server Audit, we will use an Audit Specification object as the link between events and SQL Server Audit.

在这种情况下，用户界面可能会向事件管理器引发一个“ ButtonClicked”事件，我们应该定义一个“ OnButtonClicked”过程，该过程应在“ ButtonClicked”事件发生时运行。 为了实现它，我们需要将该事件与编写的过程链接起来。 在某些语言中，我们将使用SIGNAL .. SLOT调用，在其他语言中，我们将调用AddEventHandler函数。 使用SQL Server Audit，我们将使用Audit Specification对象作为事件和SQL Server Audit之间的链接。

The input: Extended Events

输入：扩展事件

The input or the source of information of a SQL Server Audit is always an instance of an Extended Event. For those who are not familiar with them, we will talk a little bit about them. First of all, here is how Microsoft defines Extended Events:

SQL Server审核的信息输入或来源始终是扩展事件的实例。 对于那些不熟悉它们的人，我们将对它们进行一些讨论。 首先，这是Microsoft如何定义扩展事件 ：

SQL Server Extended Events (Extended Events) is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications.

SQL Server扩展事件（Extended Events）是服务器系统的常规事件处理系统。 扩展事件基础结构支持来自SQL Server的数据关联，并且在某些情况下还支持来自操作系统和数据库应用程序的数据关联。

Concretely, when we define/create an Extended Event, we define:

具体来说，当我们定义/创建扩展事件时，我们定义：

• A set of SQL Events (like those ones we can choose in SQL Trace) that might be collected.
• 可能会收集的一组SQL事件 （如我们可以在SQL Trace中选择的事件 ）。
• A set of additional session data that should be collected when an event occurs. These data can be the hostname of the end-user that raised the event or the application this client used. They are referred to as Extended Event Actions.
  事件发生时应收集的一组其他会话数据。 这些数据可以是引发事件的最终用户的主机名，也可以是此客户端使用的应用程序。 它们被称为扩展事件操作。
• target“. 目标 ”。

  There can be several kinds of targets like:

  可以有几种目标，例如：

  1. They are used to obtain information about workload characteristics without adding the overhead of full event collection.

     它们用于获取有关工作负载特征的信息，而不会增加完整事件收集的开销。

  2. They are used to write event session output from complete memory buffers to disk. That’s generally the preferred target for most DBAs.

     它们用于将事件会话输出从完整的内存缓冲区写入磁盘。 通常，这是大多数DBA的首选目标。

  3. They are used to count the number of times that a specified event occurs.

     它们用于计算指定事件发生的次数。

  4. They are used to hold the event data in memory on a first-in first-out (FIFO) basis, or on a per-event FIFO basis. We can imagine that Ring Buffer output is used in SQL Server Audit implementation.

     它们用于以先进先出（FIFO）或每个事件FIFO的形式将事件数据保存在内存中。 我们可以想象在SQL Server审核实现中使用了环形缓冲区输出。

     (Definition from SQL Server Extended Events Targets)

     （ SQL Server扩展事件目标的定义）

• A set of parameters to provide to internal extended event handler like its state (ON/OFF) or the way event retention should be made.
  提供给内部扩展事件处理程序的一组参数，例如其状态（ON / OFF）或应保留事件的方式。

As an example, you will find below the creation statement of default extended event called system_health that is defined by default starting SQL Server 2012.

作为示例，您将在默认情况下启动SQL Server 2012的默认扩展事件的创建语句system_health的创建语句下找到。

We will notice that several events are defined, some with actions. We will also see that events can be filtered (using a WHERE clause). Finally, we will notice that two targets are set, one to a file and one to a ring buffer.

我们将注意到定义了几个事件，其中一些带有动作。 我们还将看到可以过滤事件（使用WHERE子句）。 最后，我们将注意到设置了两个目标，一个目标到一个文件，一个目标到一个环形缓冲区。

 
CREATE EVENT SESSION [system_health] ON SERVER 
	ADD EVENT sqlclr.clr_allocation_failure(
		ACTION(package0.callstack,sqlserver.session_id)),
	ADD EVENT sqlclr.clr_virtual_alloc_failure(
		ACTION(package0.callstack,sqlserver.session_id)),
	ADD EVENT sqlos.memory_broker_ring_buffer_recorded,
	ADD EVENT sqlos.memory_node_oom_ring_buffer_recorded(
		ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)),
	ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,
	ADD EVENT sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,
	ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,
	ADD EVENT sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded,
	ADD EVENT sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,
	ADD EVENT sqlos.scheduler_monitor_system_health_ring_buffer_recorded,
	ADD EVENT sqlos.wait_info(
		ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)
		WHERE ([duration]>(15000) AND ([wait_type]>(31) AND ([wait_type]>(47) AND [wait_type]<(54) OR [wait_type]<(38) OR [wait_type]>(63) AND [wait_type]<(70) OR [wait_type]>(96) AND [wait_type]<(100) OR [wait_type]=(111) OR [wait_type]=(117) OR [wait_type]>(178) AND [wait_type]<(183) OR [wait_type]=(190) OR [wait_type]=(214) OR [wait_type]=(276)) OR [duration]>(30000) AND [wait_type]<(22)))),
	ADD EVENT sqlos.wait_info_external(
		ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)
		WHERE ([duration]>(5000) AND ([wait_type]>(410) AND [wait_type]<(419) OR [wait_type]>(419) AND [wait_type]<(423) OR [wait_type]>(425) AND [wait_type]<(431) OR [wait_type]>(468) AND [wait_type]<(472) OR [wait_type]>(474) AND [wait_type]<(480) OR [wait_type]>(480) AND [wait_type]<(483) OR [wait_type]=(424) OR [duration]>(45000) AND ([wait_type]>(430) AND [wait_type]<(434) OR [wait_type]>(471) AND [wait_type]<(475) OR [wait_type]>(482) AND [wait_type]<(485) OR [wait_type]>(490) AND [wait_type]<(498) OR [wait_type]>(499) AND [wait_type]<(521) OR [wait_type]>(532) AND [wait_type]<(547) OR [wait_type]=(412) OR [wait_type]=(419) OR [wait_type]=(425) OR [wait_type]=(435) OR [wait_type]=(480) OR [wait_type]=(550))))),
	ADD EVENT sqlserver.connectivity_ring_buffer_recorded(SET collect_call_stack=(1)),
	ADD EVENT sqlserver.error_reported(
		ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)
		WHERE ([severity]>=(20) OR ([error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902)))),
	ADD EVENT sqlserver.security_error_ring_buffer_recorded(SET collect_call_stack=(1)),
ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1)
		WHERE ([sqlserver].[is_system]=(1) AND [component]<>(4))),
	ADD EVENT sqlserver.xml_deadlock_report 
	-- Targets:
	ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)),
	ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
 

If you are interested in the subject of extended events, please, refer to their dedicated documentation on msdn.

如果您对扩展事件的主题感兴趣，请参阅其有关msdn的专用文档 。

The notion of Audit Action Type (or Groups)

审核操作类型（或组）的概念

As Extended events can take care of multiple SQL Events at the same time, the SQL Server Audit feature comes with the definition of groups of events. These groups can be divided into three primary levels:

由于扩展事件可以同时处理多个SQL事件，因此SQL Server审核功能附带事件组的定义。 这些组可以分为三个主要级别：

• Server-Level to audit server operations like login/logoff, create server trigger and so on
  服务器级别审核服务器操作，如登录/注销，创建服务器触发器等
• Database-Level to audit database change operations using either DML or DDL
  数据库级别，以使用DML或DDL审核数据库更改操作
• Audit-Level to audit actions performed on the audit itself
  审核级别，以审核对审核本身执行的操作

Each of these levels has a set of event groups. Here are some example events:

每个级别都有一组事件组。 以下是一些示例事件：

Event Level	Event Name	Description
Server	BACKUP_RESTORE_GROUP	This event is raised whenever a backup or a restore command is issued
Server Database	DATABASE_CHANGE_GROUP	This event is raised whenever any database is created, altered or dropped
Audit	AUDIT_CHANGE_GROUP	This event is raised whenever a management command on a SQL Server audit (or one of its components) are fired. /td>

活动等级	活动名称	描述
服务器	BACKUP_RESTORE_GROUP	每当发出备份或还原命令时，都会引发此事件
服务器数据库	DATABASE_CHANGE_GROUP	每当创建，更改或删除任何数据库时，都会引发此事件
审计	AUDIT_CHANGE_GROUP	每当触发SQL Server审核（或其组件之一）上的管理命令时，都会引发此事件。 / td>

Notice the second event is this list can be defined at both server and database levels. To get the entire list of those event groups, please, refer to SQL Server Audit Action Groups and Actions.

请注意，第二个事件是可以在服务器和数据库级别定义此列表。 要获取这些事件组的完整列表，请参阅SQL Server审核操作组和操作 。

Audit specifications or the link between Extended events and SQL Server Audit

审核规范或扩展事件与SQL Server审核之间的链接

So, in the previous section, we saw that SQL Server Audits takes care of auditing groups of SQL events. How do we actually tell a SQL Server Audit to respond to such a group? We create an audit specification object.

因此，在上一节中，我们看到SQL Server审核负责审核SQL事件组。 我们实际上如何告诉SQL Server审核来响应这样的组？ 我们创建一个审核规范对象。

As we could expect, there are two kinds of audit specifications:

如我们所料，审计规范有两种：

1. Server Audit Specification for server-level events
2. 服务器级别事件的服务器审核规范
3. Database Audit Specification for database-level events
4. 数据库级事件的数据库审核规范

The output: Audit Target

输出：审计目标

As you can read in Minette Steynberg’s article entitled “Understanding the SQL Server Audit” or in the CREATE SERVER AUDIT technet page, the audit target also referred to as audit destination is of one of the three following types:

正如你在米内特Steynberg的文章，题目改为“ 了解SQL Server审核 ”或在CREATE SERVER AUDIT 的TechNet页面，审计目标也被称为审计目标是以下三种类型之一：

• Files in a directory on host’s filesystem or on a network share. This is the most common and recommended way to perform an audit. The name of the file is generated by SQL Server using different factors that make this name unique. While this is the simplest way to implement server audits, it’s not the most secure way to store this information as a file can be deleted, altered, lost. 文件在主机上的文件系统或网络共享。 这是执行审核的最常见和推荐的方法。 该文件的名称由SQL Server使用各种使该名称唯一的因素生成。 尽管这是实施服务器审核的最简单方法，但是由于可以删除，更改，丢失文件，因此并不是存储此信息的最安全方法。
• Windows Security Log. This is the least used way for implementing server audits because it requires additional permissions for SQL Server service account to be able to write into this log. In fact, these restrictions make it a very good place to store audit information. Windows安全日志 。 这是实施服务器审核的最少使用的方法，因为它需要SQL Server服务帐户具有其他权限才能写入此日志。 实际上，这些限制使其成为存储审核信息的好地方。
• Windows Application Log. SQL Server has, by default, access to the Application Log and so, can write server audit information to the Application Log. If you don’t have a sort of “application log fetcher” which takes every new application log entry and transmits it to a safe place, don’t use this mode. Why? Because the Application Log is designed as a rollover log which means that persistence of server audit data is not guaranteed. Windows应用程序日志 。 SQL Server默认具有对应用程序日志的访问权限，因此可以将服务器审核信息写入应用程序日志。 如果您没有某种“应用程序日志提取器”来接收每个新的应用程序日志条目并将其传输到安全的地方，请不要使用此模式。 为什么？ 因为应用程序日志被设计为过渡日志，这意味着不能保证服务器审核数据的持久性。

Each destination has its own set of configuration parameters and I recommend you to have a look at the CREATE SERVER AUDIT technet page.

每个目标都有其自己的一组配置参数，我建议您查看CREATE SERVER AUDIT technet页面。

Summary

摘要

You will find below a diagram that summarizes the architecture of SQL Server Audits. SQL Server Audit makes use of Extended Events feature by grouping events into Audit Action groups. Those Audit action groups refer to a given level: either server, or database or audit. Audit action groups are mapped to a server audit specification or a database audit specification, primarily based on our audit policy.

您将在下面的图表中总结SQL Server审核的体系结构。 SQL Server审核通过将事件分组到“审核操作”组中来利用扩展事件功能。 这些审核操作组是指给定级别：服务器，数据库或审核。 审核操作组主要根据我们的审核策略映射到服务器审核规范或数据库审核规范。

All these components together form what we call a server audit. This server audit outputs to an audit target which can be either a file on the server host, the application log or the security log.

所有这些组件共同构成了我们所谓的服务器审核。 该服务器审核将输出到审核目标，该审核目标可以是服务器主机上的文件，应用程序日志或安全日志。

读取SQL Server审核的输出 (Reading the output of a SQL Server Audit)

Reading the output of a SQL Server Audit is pretty easy in T-SQL while the destination is set to File. Actually, Microsoft provides us a function called fn_get_audit_file. You will find an example usage taken from Microsoft documentation:

在目标设置为“文件”的情况下，在T-SQL中读取SQL Server审核的输出非常容易。 实际上，Microsoft为我们提供了一个名为fn_get_audit_file的函数。 您可以从Microsoft文档中找到用法示例：

 
-- This example reads from a file that is named 
-- \\serverName\Audit\HIPPA_AUDIT.sqlaudit
SELECT * FROM sys.fn_get_audit_file ('\\serverName\Audit\HIPPA_AUDIT.sqlaudit',default,default);  
GO  
 

安全注意事项 (Security Considerations)

Unfortunately, due to its nature, SQL Server Audit has not an extremely customizable permission assignment model. We could expect to grant permissions on a particular audit object, but it’s actually not the case. After all, it’s a database administration feature and in essence a DBA has already need advanced permissions on the instances he manages.

不幸的是，由于其性质，SQL Server审核还没有一个非常可定制的权限分配模型。 我们可以期望授予对特定审核对象的权限，但实际上并非如此。 毕竟，这是数据库管理功能，从本质上讲，DBA已经对其管理的实例具有高级权限。

So, let’s list and review different permissions that can be assigned to a user in order to administrate SQL Server Audits. For each permission, there is a little description and eventually a link to a documentation page on Microsoft’s website.

因此，让我们列出并查看可以分配给用户的不同权限，以便管理SQL Server审核。 对于每个许可，都有一点描述，并最终提供了指向Microsoft网站上的文档页面的链接。

Permission	Description	Resource Link
ALTER ANY SERVER AUDIT	CREATE, DROP, ALTER server audit and audit specifications	ALTER SERVER AUDIT
CONTROL SERVER	Alternative to previous permission. This server permission allows a lot more than just managing audits!	Permission Graph
ALTER ANY DATABASE AUDIT + CONNECT on the database	CREATE, DROP, ALTER database audit specifications. If a server principal is already assigned permissions to manage server-level audits, it can also manage database-level audits.	ALTER DATABASE AUDIT SPECIFICATION
VIEW ANY DEFINITION	For server-level readability, a principal with this permission will be able to query views related to server-level audits
VIEW DEFINITION + CONNECT On database	For database-level readability, a database principal with this permission will be able to query views related to database-level audits.	VIEW DEFINITION

允许	描述	资源链接
更改任何服务器审核	创建，删除，更改服务器审核和审核规范	更改服务器审核
控制服务器	替代先前的许可。 该服务器权限不仅可以管理审核，还可以提供更多功能！	权限图
更改任何数据库审核 + 在数据库上连接	CREATE，DROP，ALTER数据库审核规范。 如果已经为服务器主体分配了管理服务器级审核的权限，则它也可以管理数据库级审核。	更改数据库审核规范
查看任何定义	为了提高服务器级别的可读性，具有此权限的主体将能够查询与服务器级别的审核有关的视图
查看定义 +连接 在数据库上	为了提高数据库级别的可读性，具有此权限的数据库主体将能够查询与数据库级别审核有关的视图。	查看定义

By the way, if you have situations where you are obliged to live with the developers having full control permissions (I mean sysadmin and/or db_owner fixed roles) over a production server (this should never happen), be aware that they are almighty and can tamper any component you put in place…

顺便说一句，如果您不得不与对生产服务器具有完全控制权限（我的意思是sysadmin和/或db_owner固定角色）的开发人员同住（这永远不会发生），请注意他们是万能的，可以篡改您放置的任何组件…

管理对象 (Management objects)

Now we are able to create a complete audit solution, let’s dive into the objects that Microsoft provides us to manage this solution.

现在我们可以创建一个完整的审核解决方案，让我们深入研究Microsoft提供给我们的对象来管理该解决方案。

So far, we’ve already seen one of these objects in section 4, the sys.fn_get_audit_file function that allowed us to read the content of an audit file.

到目前为止，我们已经在第4节中看到了这些对象之一，即sys.fn_get_audit_file函数，该函数使我们能够读取审核文件的内容。

But there are other management objects, which are:

但是还有其他管理对象，它们是：

• sys.server_audit, a view that contains one row for each SQL Server audit in a server instance sys.server_audit ，一个视图，其中包含服务器实例中每个SQL Server审核的一行
• sys.server_file_audits, a view for audits targeting to a file sys.server_file_audits ，用于针对文件的审核的视图
• sys.server_audit_specifications, a view for audit specifications sys.server_audit_specifications ，审计规范视图
• sys.server_audit_specification_details, another view like the previous one, but with more details sys.server_audit_specification_details ，与上一个视图类似，但具有更多详细信息
• sys.database_audit_specifications, same as sys.database_audit_specifications ，同sys.server_audit_specifications, but for database-level audit specifications sys.server_audit_specifications ，但对于数据库级别审核规范
• sys.database_audit_specification_details, same as sys.database_audit_specification_details ，同sys.server_audit_specification_details but at database-level. sys.server_audit_specification_details ，但在数据库级。
• sys.dm_server_audit_status is a particularly useful view to monitor audit status sys.dm_server_audit_status是用于监视审核状态的特别有用的视图
• sys.dm_audit_actions, a view that sys.dm_audit_actions ，该视图returns a row for every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit 为可以在审核日志中报告的每个审核操作以及可以配置为SQL Server审核的每个审核操作组返回一行

结论 (Conclusion)

Today, we’ve acquired sufficient knowledge to understand SQL Server Audit architecture so that we should be able to build and manage a security audit based on SQL Server Audit feature. I would understand those practical guys who are willing to get into a concrete example.

今天，我们已经掌握了足够的知识来理解SQL Server审核体系结构，因此我们应该能够基于SQL Server审核功能来构建和管理安全审核。 我会理解那些愿意举一个具体例子的实践者。

This is actually the subject of the article entitle « SQL Server Audit feature: DDL events auditing examples ». In this article, we will take a look at some solutions to implement DDL event auditing and pinpoint advantages and disadvantages of each method.

这实际上是标题为“ SQL Server审核功能：DDL事件审核示例”的主题。 在本文中，我们将介绍实现DDL事件审核的一些解决方案，并查明每种方法的优缺点。

Next article in this series:

本系列的下一篇文章：

• SQL Server Audit feature – DDL event auditing examples SQL Server审核功能– DDL事件审核示例

资源资源 (Resources)

• SQL Server Audit (Database Engine) SQL Server审核（数据库引擎）
• SQL Server Audit Action Groups and Actions SQL Server审核操作组和操作
• EVENTDATA (Transact-SQL) EVENTDATA（Transact-SQL）
• SQL Server Extended Events Targets SQL Server扩展事件目标

翻译自: https://www.sqlshack.com/sql-server-audit-feature-discovery-architecture/