SQL Server 审计（Audit）

审计（Audit）用于追踪和记录SQL Server实例，或者单个数据库中发生的事件（Event），审计运作的机制是通过捕获事件（Event），把事件包含的信息写入到事件日志（Event Log）或审计文件（Audit File）中，为review提供最真实详细的数据。

审计主要包含服务器审计对象（Server Audit，简称审计对象）、服务器级别的审计规范（Server Audit Specification）、数据库级别的审计规范（Database Audit Specification）和目标（Target）。

审计对象是在服务器级别上创建的对象，必须启用，用于指定审计数据的存储方式和存储路径，并提供工具查看审计数据。
服务器级别的审计规范用于记录服务器级别的事件（Event），
数据库级别的审计规范用于记录数据库级别的事件，
Target是指用于存储Audit数据的媒介，可以是File、Windows Security Event Log 或 Windows Application Event Log，常用的Target是File。

SQL Server使用 Extended Events来帮助创建审计，也就是说，审计是在扩展事件的基础上设计的功能，专门用于审核数据库的安全。为了启用审计，首先需要创建一个SQL Server 实例级的审计对象，然后创建从属于它的“服务器审计规范”或“数据库审计规范”，审计输出的结果数据可以存储到审计文件（File）、安全日志（Security Log）和应用程序日志（Application Log）中。

一，审计对象的构成

构成审计的成分主要有4大类：审计对象、服务器级别的审计规范、数据库级别的审计规范和目标（Target）。

审计对象可以包含一个或多个审计规范，用于指定输出结果存储的路径。审计对象创建时默认的状态是disable。
服务器级别的审计规范，从属于审计对象，它包含服务器级别的审计动作组（Audit Action Group）或单个Audit Action，这些动作组或动作由Extended Events触发。
数据库级别的审计规范，也从属于审计对象，它包含数据库级别的审计动作组（Audit Action Group）或单个Audit Action，这些动作组或动作由Extended Events触发。
Target（也称作Audit Destination）用于存储审计的结果，Target可以是一个file，Windows Security Event log 或者Windows application event log.

Action Group 是预定义的一组Action，每一个Action（也称作Action Event）都是一个原子事件，因此，Action Group也是由Action Event构成的组合。当Event（也称作Action）发生时，Event被发送到审计对象（audit）中，SQL Server把数据记录到Target中。关于审计的动作组和动作的详细信息，请阅读官方文档：SQL Server Audit Action Groups and Actions

二，审计对象

审计监控SQL Server实例，从服务器级别或数据库级别的Action或Action Group中收集这些Action发生时产生的数据。

当定义一个审计对象时，需要指定保存审计输出结果的路径，即audit的Target（审计的目的，audit destination）。新建的Audit默认处于disable状态，不能自动追踪和记录（审计）任何audit action。当Audit启用后，audit destination会接收从审计输出的数据。

创建审计对象的语法：

CREATE SERVER AUDIT audit_name
{
    TO { [ FILE (<file_options> [ , ...n ] ) ] | APPLICATION_LOG | SECURITY_LOG | URL | EXTERNAL_MONITOR }
    [ WITH ( <audit_options> [ , ...n ] ) ]
    [ WHERE <predicate_expression> ]
}  [ ; ]

<file_options>::=
{
        FILEPATH = 'os_file_path'
    [ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]
    [ , { MAX_ROLLOVER_FILES = { integer | UNLIMITED } } | { MAX_FILES = integer } ]
    [ , RESERVE_DISK_SPACE = { ON | OFF } ]
}

<audit_options>::=
{
    [   QUEUE_DELAY = integer ]
    [ , ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION } ]
    [ , AUDIT_GUID = uniqueidentifier ]
}

主要参数注释：

FILEPATH ='os_file_path'：指定审计日志存储的目录，目录中包含的文件是基于审计名称和审计GUID产生的。
MAXSIZE = { max_size }：指定一个审计文件的最大容量，
MAX_ROLLOVER_FILES ={ integer | UNLIMITED }：指定审计包含的审计文件的最大数量，当文件数量达到限制的数量时，SQL Server会自动删除创建时间最早的审计文件。
MAX_FILES =integer：是指审计只能最多包含的审计文件的数量，当文件数量达到限制的数量时，不会删除最老的审计文件，当产生更多的审计数据时，SQL Server会抛出错误，并失败。
RESERVE_DISK_SPACE = { ON | OFF }：在Disk上为审计预先分配MAXSIZE的空间
QUEUE_DELAY =integer：单位是毫秒（千分之一秒），用于指定一个Audit Action从发生到被强制处理可以经过的时间间隔，默认值是1000，即1秒，这也是可以甚至的最小查询延迟。值为0表示同步传送，
ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION }：当Target不能继续写入审计日志时，审计对象的动作：
- 　　CONTINUE，是指不保留审计记录，审计继续尝试记录事件，如果解决了故障情况，则继续进行审计。
- 　　SHUTDOWN：如果SQL Server由于任何原因不能写入审计记录，那么强制关闭SQL Server实例
- 　　FAIL_OPERATION：如果数据库操作导致审计事件的发生，那么执行的数据库操作将失败；不会导致审计事件发生的操作，将继续执行。

注意：审计对象（Server Audit）的create、alter、或 drop，需要ALTER ANY SERVER AUDIT或CONTROL SERVER的权限。当保存Audit Log时，为了保证审计数据的安全，需要限制无关人员的权限，禁止无关人员访问审计文件的存储目录。

举个例子，创建审计对象：

CREATE SERVER AUDIT [AuditMonitorQuery]
TO FILE
(    FILEPATH = N'G:\AuditFiles\MonitorQuery\'
    ,MAXSIZE = 1 GB
    ,MAX_ROLLOVER_FILES = 128
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'xxx'
)
ALTER SERVER AUDIT [AuditMonitorQuery] WITH (STATE = ON)
GO

三，服务器级别的审计规范

服务器级别的审计规范属于Server Audit，服务器界别的审计规范从服务器级别的action group中收集数据。

CREATE SERVER AUDIT SPECIFICATION audit_specification_name
FOR SERVER AUDIT audit_name
{
    { ADD ( { audit_action_group_name } )
    } [, ...n]
    [ WITH ( STATE = { ON | OFF } ) ]
}

注意：拥有ALTER ANY SERVER AUDIT权限的用户可以创建服务器级别的审计规范，拥有CONTROL SERVER权限或sysadmin可以查看审计数据。

举个例子：创建服务器级别的审计规范

CREATE SERVER AUDIT SPECIFICATION HIPAA_Audit_Specification
FOR SERVER AUDIT HIPAA_Audit
    ADD (FAILED_LOGIN_GROUP)
    WITH (STATE=ON);

四，数据库级别的审计规范

数据库级别的审计规范属于一个Server Audit，数据库级别的审计规范从数据库级别的action group，或者audit event中收集数据。action group是预先定义的一组audit event，这些action产生的信息都会被发送到Audit，被存储到Target中。

CREATE DATABASE AUDIT SPECIFICATION audit_specification_name
{
    FOR SERVER AUDIT audit_name
        [ { ADD ( { <audit_action_specification> | audit_action_group_name } ) } [, ...n] ]
    [ WITH ( STATE = { ON | OFF } ) ]
} [ ; ]  

<audit_action_specification>::= action [ ,...n ] ON [ class :: ] securable BY principal [ ,...n ]

重要参数注释：

action：数据库级别的action的名，
class：securable的class name
securable：数据库中审计作用（应用audit action 或audit action group）的table、view 等安全对象（Securable Object）。
principal：数据库中审计作用（应用audit action 或audit action group）的principal，如果审计所有的principal，请使用public。

注意：拥有 ALTER ANY DATABASE AUDIT 权限的用户可以创建数据库级别的审计规范，当数据库级别的审计规范被创建之后，可以被CONTROL SERVER和sysadmin产看。

举个例子：

CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT HIPAA_Audit
ADD (SELECT , INSERT ON HumanResources.EmployeePayHistory BY public )
WITH (STATE = ON) ;

五，Target

审计输出的结果会被发送到Target，实际上，Target的作用是存储审计数据，可以是一个文件，Windows Security event log或Windows Application event log。

任何授权的用户都可以读写Windows Application event log，它的安全级别比Windows Security event log要低。通常情况下，审计数据都会存储到单独的文件中。

当把审计信息存储到一个file，为了保证审计数据的安全，可以通过以下方式限制对文件位置的访问：

SQL Server Service Account必须同时具有读写权限
Audit 管理员需要有审计文件的读写权限
Audit Reader：具有读取审计文件的权限

因为数据库引擎可以访问审计文件，拥有CONTROL SERVER权限的SQL Server Login都可以使用数据库引擎来访问审计文件。为了记录正在读取审计文件的用户，需要定义一个审计，追踪和记录用户对函数master.sys.fn_get_audit_file的调用记录，这个审计记录了拥有CONTROL SERVER权限的Login通过SQL Server访问审计文件的记录。

如果审计管理员（Audit Admin）把文件复制到其他位置（处于存档等目的），那么需要将新位置上的权限降级为：

Audit Administrator - Read / Write
Audit Reader - Read

注意：为了保护审计文件，应该对审计文件进行加密，对存储审计文件的目录使用Windows BitLocker Drive Encryption或Windows Encrypting File System，从而防止未经授权的访问。

参考文档：

SQL Server Audit (Database Engine)

悦光阴