SQL Server Audit（审核\审计）

什么是审核？

SQL Server 2016开始支持Audit审核功能，且所有版本都支持。Audit收集单个服务器实例或数据库级操作和操作组以进行监视。 SQL Server Audit分为服务器实例级别和数据库级别，使用扩展事件来帮助创建审核，审核的事件写入事件日志或审核文件。

服务器审核对象

创建服务器审核和数据库审核的第一步都先创建服务器审核对象（Server Audit），Server Audit是审核的数据保存定义，例如保存到文件\Windows日志，FILEPATH 审核文件保存位置，MAX_ROLLOVER_FILES 最大文件多少个（超出了就会删除旧的文件），MAX_SIZE单个审核文件的大小，WHERE对审核的对象进行限制（例如OBJECT='XXX'代表只对表名是XXX进行审核）,event_field_name可以对sys.fn_get_audit_file的字段进行筛选

服务器审核规范（Server Audit Specification）

要创建服务器审核，必须创建一个服务器审核规范。服务器审核规范收集由扩展事件功能引发的服务器级操作组（事件）。如果说审核本身是一个数据库功能引擎，那审核规范就是创建要审核的事件的定义，决定哪些事件需要被审核。审核引擎会按照定义收集事件记录然后发送到指定的输出保存位置。

数据库审核规范（Database Audit Specification）

类同上面的服务器规范，只是审核事件变成是数据库级别的操作。

审核记录输出目标

目标可以是文件、Windows 安全事件日志或 Windows 应用程序事件日志。必须定期查看和归档这些日志。输出目标是磁盘文件时，必须确保SQL Server 服务帐户对磁盘文件目录有读写权限。

审核记录

一般审核记录都是保存到文件中，再通过调用SQL函数sys.fn_get_audit_file读取数据。由于SQL Audit可能生成多个目标文件（取决于启用SQL Audit时的MAX_ROLLOVER_FILES 参数），每次要实现增量数据读取，就需要通过以下几个步骤：1、初始化读取全部数据，sys.fn_get_audit_file(目录路径及文件pattern,default,default); 2、从初始化数据中获取audit_file_offset最大值及该记录的file_name字段值作为下一次增量读取使用；3、sys.fn_get_audit_file(目录路径及文件pattern,上一步获取的filename,上一步获取的audit_file_offset);

如何创建审核？

创建服务器审核和数据库审核的第一步都先创建服务器审核对象（Server Audit），然后才是创建服务器审核规范。

例子：创建服务器审核规范，并且审核成功和失败登录事件

USE master
GO

-- 检查下有没有存在的服务器审核规范，先禁用
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE NAME = 'SVR_Audit_Specification_LOGON')
    ALTER SERVER AUDIT SPECIFICATION SVR_Audit_Specification_LOGON WITH (STATE = OFF) 
GO

IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE NAME = 'SVR_Audit_Specification_LOGON')
    DROP SERVER AUDIT SPECIFICATION SVR_Audit_Specification_LOGON
GO

IF EXISTS (SELECT * FROM sys.server_audits WHERE NAME = 'Logon_Security_Audit')
    ALTER SERVER AUDIT Logon_Security_Audit WITH (STATE = OFF) 
GO

IF EXISTS (SELECT * FROM sys.server_audits WHERE NAME = 'Logon_Security_Audit')
    DROP SERVER AUDIT Logon_Security_Audit  
GO

-- 创建服务器审核对象先，指定3个文件，每个10MB
CREATE SERVER AUDIT Logon_Security_Audit  
TO FILE ( FILEPATH =   
'D:\临时工作目录\SQL Server实验\SQL Server Audit审核\服务器审核规范\' 
, MAXSIZE = 10MB
, MAX_ROLLOVER_FILES = 3
, RESERVE_DISK_SPACE = OFF )
WITH( ON_FAILURE = CONTINUE )
WHERE server_principal_name <> 'NT AUTHORITY\NETWORK SERVICE';
GO  

-- 启用服务器审核对象
ALTER SERVER AUDIT Logon_Security_Audit   
WITH (STATE = ON) ;  
GO  

-- 创建服务器审核规范，审核成功登录和失败登录事件
CREATE SERVER AUDIT SPECIFICATION SVR_Audit_Specification_LOGON
FOR SERVER AUDIT Logon_Security_Audit  
    ADD (SUCCESSFUL_LOGIN_GROUP),
    ADD (FAILED_LOGIN_GROUP)  
    WITH (STATE=ON);  
GO  



SELECT *
FROM sys.fn_get_audit_file('D:\临时工作目录\SQL Server实验\SQL Server Audit审核\服务器审核规范\*',default,default)

例子：创建数据库审核规范，并且审核由数据库用户jerry发起对数据库表dbo.db_audit_test的insert操作，包括成功和失败的insert

USE AdventureWorks2019
GO

-- 检查下有没有存在的服务器审核规范，先禁用
IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE NAME = 'DB_Audit_Specification_tbl_test_insert_by_jerry')
    ALTER DATABASE AUDIT SPECIFICATION DB_Audit_Specification_tbl_test_insert_by_jerry WITH (STATE = OFF) 
GO

IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE NAME = 'DB_Audit_Specification_tbl_test_insert_by_jerry')
    DROP DATABASE AUDIT SPECIFICATION DB_Audit_Specification_tbl_test_insert_by_jerry
GO

USE master
GO

IF EXISTS (SELECT * FROM sys.server_audits WHERE NAME = 'SVR_Audit_DB_tbl_test_insert_by_jerry')
    ALTER SERVER AUDIT SVR_Audit_DB_tbl_test_insert_by_jerry WITH (STATE = OFF) 
GO

IF EXISTS (SELECT * FROM sys.server_audits WHERE NAME = 'SVR_Audit_DB_tbl_test_insert_by_jerry')
    DROP SERVER AUDIT SVR_Audit_DB_tbl_test_insert_by_jerry  
GO

-- 创建服务器审核对象先，指定3个文件，每个10MB
CREATE SERVER AUDIT SVR_Audit_DB_tbl_test_insert_by_jerry  
TO FILE ( FILEPATH =   
'D:\临时工作目录\SQL Server实验\SQL Server Audit审核\数据库审核规范\' 
, MAXSIZE = 10MB
, MAX_ROLLOVER_FILES = 3
, RESERVE_DISK_SPACE = OFF )
WITH( ON_FAILURE = CONTINUE )
WHERE server_principal_name <> 'NT AUTHORITY\NETWORK SERVICE';
GO  

-- 启用数据库审核对象
ALTER SERVER AUDIT SVR_Audit_DB_tbl_test_insert_by_jerry   
WITH (STATE = ON) ;  
GO  

USE AdventureWorks2019
GO

-- 创建数据库审核规范，审核成功登录和失败登录事件
CREATE DATABASE AUDIT SPECIFICATION DB_Audit_Specification_tbl_test_insert_by_jerry
FOR SERVER AUDIT SVR_Audit_DB_tbl_test_insert_by_jerry  
    ADD (INSERT ON dbo.db_audit_test BY JERRY )    
    WITH (STATE=ON);  
GO  

USE master
GO

SELECT *
FROM sys.fn_get_audit_file('D:\临时工作目录\SQL Server实验\SQL Server Audit审核\数据库审核规范\*',default,default)