SQLServer审计功能配置

一. SQL Server审计功能介绍

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

Audit都需要创建一个实例级的“SQL Server审核”，然后可以创建从属于它“服务器审核规范”和“数据库审核规范”。可以理解“SQL Server审核”是审核的顶级容器，这两个“规范”是定义要审核的具体内容。

创建和使用审核的一般过程：

1. 创建审核并启用。

2. 创建映射到审核的服务器审核规范或数据库审核规范，并启用审核规范。

3. 通过使用 Windows“事件查看器”、“日志文件查看器”或 fn_get_audit_file 函数来读取审核事件。

官方手册参考：https://learn.microsoft.com/zh-cn/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver16

二. 启用审核功能

下面以SQL Server2019为例，启用审核功能，具体配置过程如下：

1.创建审核并启用

“SQL Server 审核” 对象收集单个服务器实例或数据库级操作和操作组以进行监视。 这种审核处于 SQL Server 实例级别。 每个 SQL Server 实例可以具有多个审核。

定义审核时，将指定结果的输出位置，这是审核的目标位置。目标位置支持三种，分别是文件、windows安全事件日志、windows应用程序事件日志。 审核是在 禁用 状态下创建的，因此不会自动审核任何操作。 启用审核后，审核目标将从审核接收数据。

创建审核可以通过SSMS界面化配置，也可通过执行SQL语句实现。

（1）创建一个审核，名为 Audit-logfile，目标位置为文件（审核结果文件存放在c:\dbaudit路径中），通过SSMS界面化配置方法如下：
用管理员账号登录SQL Server管理工具（SQL Server Management Studio），在“安全性”-“审核”菜单上，右键-“新建审核”，如下图：

新建审核界面如下图：

点击“确定”即可创建一个审核，创建后在“安全性”-“审核”下面多了一个名为“Audit-logfile”的审核配置，默认是未启用状态（红色的叉号表示未启用，若想启用审核，可右键点击，选择启用审核即可），如下图：

对应的通过SQL语句方式创建审核的语句如下：

USE [master]
GO

CREATE SERVER AUDIT [Audit-logfile]
TO FILE 
(	FILEPATH = N'C:\dbaudit\'
	,MAXSIZE = 1024 MB
	,MAX_FILES = 100
	,RESERVE_DISK_SPACE = ON
) 
WITH 
(	QUEUE_DELAY = 1000, 
	ON_FAILURE = CONTINUE
)

ALTER SERVER AUDIT [Audit-logfile] WITH (STATE = OFF)
GO

如下图，执行sql语句后，会在左侧“安全性”-“审核”下面生成一个名为“Audit-logfile”的配置：

SQL语句中相关参数说明如下：

• TO FILE:指定输出到审核文件，也可以指定为SECURITY_LOG和APPLICATION_LOG。

• FILEPATH：审核文件的目录地址。

• MAXSIZE：单个审核文件的大容量。

• MAX_FILES：类似于Trace,指定Rollover允许多文件数。

• RESERVE_DISK_SPACE：预先分配审核文件到MAXSIZE，推荐启用。

• QUEUE_DELAY：指定事件发生到被强制审核的毫秒间隔。指定为0则为同步审核。

• ON_FAILURE ：当审核向上档写入数据失败时，接下来会采取的行为：CONTINUE | SHUTDOWN | FAIL_OPERATION。

• ALTER SERVER AUDIT [Audit-logfile] WITH (STATE = OFF) # 该语句是是否启用审核，默认是创建时未启用（STATE = OFF），也可以创建的时候直接启用（STATE = ON）

启用审核后，在服务器 C:\dbaudit 路径下会生成一个 审计日志文件（Audit-logfile），如下图：

文件为sqlaudit类型（不可读，后面需要通过sql语句进行查询：select * from sys.fn_get_audit_file(‘C:\dbaudit*’,default,default)）

（2）创建一个审核，名为 Audit-log，目标位置为windows应用程序日志（审核结果写在windows应用程序事件中），通过SSMS界面化配置方法如下：

通过sql语句创建如下：

USE [master]
GO

CREATE SERVER AUDIT [Audit-log]
TO APPLICATION_LOG 
WITH (
QUEUE_DELAY = 1000, 
ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit-log] WITH (STATE = ON)  --创建时启用
GO

上面创建了两个审核，Audit-logfile 和 Audit-log（审核目标位置不同），介绍了目标位置为不同方式的两种创建方法，实际运用中可选择其中一种即可。

2. 创建审核规范并启用

审核规范可以是服务器级别的审核规范，也可以是数据库级别的审核规范，二者创建一种即可。

如果是服务器级别的审核规范，则审核规范对所有数据库都生效，如果是数据库级别的审核规范，则需要对每一个具体的数据库定义审核规范。

下面分别介绍两种审核规范的创建方式。

2.1 创建服务器审核规范

“服务器审核规范” 对象属于审核。 您可以为每个审核创建一个服务器审核规范，因为它们都是在 SQL Server 实例范围内创建的。

服务器审核规范可收集许多由扩展事件功能引发的服务器级操作组。 您可以在服务器审核规范中包括“审核操作组” 。 审核操作组是预定义的操作组，它们是 数据库引擎中发生的原子事件。 这些操作将发送到审核，审核将它们记录到目标中。

如为上一步创建的审核（Audit-logfile） 创建一个服务器审核规范，下面分别介绍两种方式：通过SSMS界面配置和通过SQL语句创建。

（1）通过SSMS界面创建服务器审核规范
右键“安全性”-“服务器审核规范”：

点击“新建服务器审核规范”：

如上图：名称自定义，如Server-audit，审核对象选择 Audit-logfile,审核操作类型可选择需要审计的类型：

部分审计类型含义如下：

• AUDIT_CHANGE_GROUP：当Audit被创建、修改和删除时，触发该事件
• BATCH_COMPLETED_GROUP：当任何batch、sp或事务完成执行时，触发该事件，并记录下执行的命令的文本。
• BATCH_STARTED_GROUP：当任何batch、sp或事务开始执行时，触发该事件，并记录下执行的命令的文本。
• DATABASE_OBJECT_CHANGE_GROUP：当任何数据库中的对象执行create、alter或drop命令时，触发该事件，可能会产生巨大的审计日志
• FAILED_LOGIN_GROUP：当一个principal尝试登录SQL Server，并失败时，触发该事件
• FAILED_DATABASE_AUTHENTICATION_GROUP：当一个Principal尝试登录数据库，并失败时，触发该事件
• SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP：当一个principal成功登录SQL Server中的数据库时，触发该事件
• SUCCESSFUL_LOGIN_GROUP：当一个principal成功登录SQL Server时，触发该事件

（2）通过SQL语句创建，执行sql语句如下：

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Server-audit]
FOR SERVER AUDIT [Audit-logfile]
ADD (FAILED_LOGIN_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON)
GO

执行成功后，会在左侧“安全性”-“服务器审核规范”下面生成一条配置，名为 Server-audit，如下图

2.2 创建数据库审核规范

“数据库审核规范” 对象也属于 SQL Server 审核。 针对每个审核，您可以为每个 SQL Server 数据库创建一个数据库审核规范。

数据库审核规范可收集由扩展事件功能引发的数据库级审核操作。 你可以向数据库审核规范添加审核操作组或审核事件。 审核事件 是可以由 SQL Server 引擎审核的原子操作。 “审核操作组”是预定义的操作组。 它们都位于 SQL Server 数据库作用域。 这些操作将发送到审核，审核将它们记录到目标中。

下面分别介绍两种方式：通过SSMS界面创建和通过执行SQL语句创建数据库审核规范，针对测试环境数据库mydb创建数据库审核规范。

（1）通过SSMS界面创建数据库审核规范
选择“数据库”-mydb-“安全性”-“数据库审核规范”，右键如下图：

点击“新建数据库审核规范”，如下图：

如上图：名称自定义，如Db-Audit，审核对象选择 Audit-logfile,审核操作类型可选择需要审计的事件操作类型等：

（2）通过执行SQL语句创建数据库审核规范

use [mydb]
GO

CREATE DATABASE AUDIT SPECIFICATION [Db-Audit]
FOR SERVER AUDIT [Audit-logfile]
ADD (DELETE,UPDATE,SELECT,INSERT ON DATABASE::[mydb] BY [public])
WITH (STATE = ON)
GO

执行如下图：

执行成功后，会在左侧 “数据库”-mydb-“安全性”-“数据库审核规范” 下面生成一个名为 Db-Audit 的配置项。

数据库级别的审核规范，审计动作组部分含义如下：

• BATCH_COMPLETED_GROUP
• BATCH_STARTED_GROUP
• DATABASE_OBJECT_CHANGE_GROUP：当对数据库对象执行CREATE、ALTER、 或 DROP命令时，触发该事件
• DATABASE_OBJECT_PERMISSION_CHANGE_GROUP：当数据库对象的权限(执行GRANT, REVOKE, or DENY)发生变化时，触发该事件
• DATABASE_PRINCIPAL_CHANGE_GROUP：当数据库中的user，role等发生变化时，触发该事件
• SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP：当一个Schema中的对象的权限(执行GRANT, REVOKE, or DENY)发生变化时，触发该事件
• SCHEMA_OBJECT_CHANGE_GROUP：当对Schema中的对象执行CREATE、ALTER、 或 DROP命令时，触发该事件
• SCHEMA_OBJECT_ACCESS_GROUP：当schema中的对象的权限发生变化时，触发该事件

数据库级别的审核规范，单个审计动作如下：
数据库级别的审计动作(Audit Action)，分别在执行查询、修改、插入、删除、执行、等命令时触发

• SELECT ：发出 SELECT 语句时将引发此事件。
• UPDATE： 发出 UPDATE 语句时将引发此事件。
• INSERT： 发出 INSERT 语句时将引发此事件。
• DELETE： 发出 DELETE 语句时将引发此事件。
• EXECUTE： 发出 EXECUTE 语句时将引发此事件。
• RECEIVE： 发出 RECEIVE 语句时将引发此事件。
• REFERENCES： 检查 REFERENCES 权限时将引发此事件。

3. 查看审核事件日志

3.1 通过SSMS日志文件查看器查看

右键 “安全性”-“审核”-“Audit-logfile”, 选择 “查看审核日志”：

日志如下图所示：

3.2 针对目标位置为文件的审核日志

通过SQL查询：

select * from 
sys.fn_get_audit_file('C:\dbaudit\*',default,default)

如下图：