SQL Server 审核（Audit）-- 使用T-SQL创建审核，以审核数据库内的查询操作-阿里云开发者社区

任务1：创建审核

步骤1：打开SSMS，执行以下脚本。

--01 Create SQL Server Audit
USE master
GO

CREATE SERVER AUDIT [Audit-AdventureWorks2012-SELECT]

TO FILE

( FILEPATH = N'D:\MSSQL\DATA\Audit_logs'
,MAXSIZE=10MB
,MAX_ROLLOVER_FILES=100

,RESERVE_DISK_SPACE=OFF)
WITH

( QUEUE_DELAY=1000,ON_FAILURE=CONTINUE)
GO
--02 Enable the Audit

ALTER SERVER AUDIT [Audit-AdventureWorks2012-SELECT]

WITH (STATE=ON)
GO
--03 Use sys.server_audits to check current status

SELECT name N'Audit', is_state_enabled N'Enabled',type_desc N'Audit Type',queue_delay N'Wait Time',create_date N'Create Time',modify_date N'Modify Time'

FROM sys.server_audits

利用CREATE SERVER AUDIT语法，创建审核对象，说明如下。

l 审核名称：Audit-AdventureWorks2012-SELECT。

l 队列延迟：1000（秒）。

l 审核目标：File。

l 文件路径：D:\MSSQL\DATA\Audit_logs。

l 最大滚动更新文件：100。

l 最大文件大小：10MB。

l 此服务器审核对象已经启用。

任务2：创建服务器审核规范对象

步骤1：打开SSMS，执行以下脚本。

--01 Create Server Audit Specification
USE master
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Successful]

FOR SERVER AUDIT [Audit-AdventureWorks2012-SELECT]

ADD (SUCCESSFUL_LOGIN_GROUP)
GO
--02 Enable the Server Audit Specification

ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Successful]

WITH (STATE=ON)

利用CREATE SERVER AUDIT SPECIFICATION语法，创建Server Audit Specification，具备以下的特性。

l 服务器审核规范的名称：ServerAuditSpecification-Login-Successful。

l 使用此审核规范的审核名称：Audit-AdventureWorks2012-SELECT。

l 所要审核的操作组：SUCCESSFUL_LOGIN_GROUP。

步骤2：执行以下代码，查看服务器审核规范对象。

--01 Query Server Audit Specification

SELECT name N'Server Audit Specification',is_state_enabled N'Enabled',

Create_date N'Create Time',modify_date N'Modify Time'

FROM sys.server_audit_specifications
--02 Query Server Audit Specification more information

SELECT audit_action_id N'Audit Action ID',audit_action_name N'Audit Action Or Audit Action Group',

Class_desc N'Object Class',is_group N'Action Group'

FROM sys.server_audit_specification_details

步骤3：执行以下代码，查看可用的审核操作、审核操作组的项目。

--Audit Action or Audit Action Group

SELECT * FROM sys.dm_audit_actions

SELECT * FROM sys.dm_audit_actions WHERE action_id='LGSD'
--Audit class type

SELECT * FROM sys.dm_audit_class_type_map ORDER BY securable_class_desc

任务3：创建数据库审核规范对象

步骤1：打开SSMS，执行以下脚本

--01 Create Database Audit Specification
USE AdventureWorks2012
GO

CREATE DATABASE AUDIT SPECIFICATION [AuditDatabaseSpecification-Object-SELECT]

FOR SERVER AUDIT [Audit-AdventureWorks2012-SELECT]

ADD (SELECT ON SCHEMA::[dbo] BY [public])
GO
/*
{

Action [ ,…n] ON [ class :: ] securable BY principal [ ,…n]
}
*/
--02 Enable Database Audit Specification

ALTER DATABASE AUDIT SPECIFICATION [AuditDatabaseSpecification-Object-SELECT]

WITH (STATE=ON)

利用CREATE DATABASE AUDIT SPECIFICATION语法，创建数据库审核规范对象，具备以下的特性。

l 数据库审核对象的名称：[AuditDatabaseSpecification-Object-SELECT]

使用此审核规范的审核名称：[Audit-AdventureWorks2012-SELECT]

l 所要审核的操作：SELECT。

l Class是安全性实体上的类名。在此，特别用SCHEMA关键词，这是指数据库内的架构。目前可选用的审核类型，请查询目录视图sys.dm_audit_class_type_map。

l Securable使用dbo关键词，这表示包含dbo架构下的所有对象。可以依据审核的要求，填入适合的架构名称。

l 在principal部分，使用public关键词，代表固定数据库级别角色public。因为每位数据库用户都属于public数据库角色，借此可以包含数据库的每一位用户账户。

步骤2：执行以下脚本，查看可用于设置数据库审核规范对象的相关信息。

SELECT * FROM sys.database_audit_specifications

SELECT name N'Database Audit Specification',is_state_enabled N'Enabled',

Create_date N'Create Time',modify_date N'Modify Time'

FROM sys.database_audit_specifications

SELECT * FROM sys.database_audit_specification_details

SELECT audit_action_id N'Audit Action ID',audit_action_name N'Audit Action Or Audit Action Group',

Class_desc N'Class Desc',is_group N'Action Group'

FROM sys.database_audit_specification_details

任务4：测试审核功能 – 登录目标数据库，查询数据表

步骤1：打开SSMS，执行以下脚本，创建账户superpippo，登录到数据库AdventureWorks2012并赋予适当的权限。

USE master
GO

CREATE LOGIN [superpippo]

WITH PASSWORD=N'Mpdfzh7',DEFAULT_DATABASE=AdventureWorks2012,

CHECK_EXPIRATION=OFF,CHECK_POLICY=OFF
GO
USE AdventureWorks2012
GO

CREATE USER [superpippo]

FOR LOGIN [superpippo]
GO

EXEC sp_addrolemember N'db_datareader',N'superpippo'

步骤2：利用登录账户superpippo登录SQL Server，执行以下代码。

USE AdventureWorks2012
GO

SELECT * FROM dbo.DatabaseLog

SELECT * FROM dbo.ErrorLog

SELECT * FROM Person.Password

任务5：使用日志文件查看器阅读审核日志

步骤1：展开“Object Explorer”，“Security”，“Audits”节点。

步骤2：在“Audits”节点“Audit-AdventureWorks2012-SELECT”对象上右击，选择“View Audit Logs”选项。

步骤3：在“Log File Viewer”左上角的“Select logs”区域，确认有勾选“Audit Collection”，“Audit-AdventureWorks2012-SELECT”，利用以下的方式来阅读所记录的审核信息。

需要将“Action ID”为LOGIN SUCCEEDED与SELECT两者的日志信息结合后，才能筛选处所需要的审核信息。

任务6：创建T-SQL函数进一步分析审核的日志

步骤1：执行以下代码。

--Create Function ufn_AuditReport
USE master
GO

IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[ufn_AuditReport]') AND type in(N'FN',N'IF',N'TF',N'FS',N'FT'))

DROP FUNCTION [dbo].[ufn_AuditReport]
GO

CREATE FUNCTION dbo.ufn_AuditReport

(@filepath varchar(1000))

RETURNS @retAuditReport TABLE
(

[session_id] int NULL,

[server_principal_name] sysname NOT NULL,

[ip] nvarchar(100) NULL,

[login_time] datetimeoffset(7) NULL,

[database_name] sysname NOT NULL,

[action_time] datetimeoffset(7) NULL,

[tsql] nvarchar(4000) NULL
)
AS
BEGIN

DECLARE @tsl TABLE

(RN int, session_id int, event_time datetime2, server_principal_name sysname,

Server_principal_id int, database_name sysname, statement nvarchar(4000))

DECLARE @tlgls TABLE

(RN int IDENTITY(1,1), event_time datetime2, additional_information nvarchar(4000))

INSERT @tsl

SELECT ROW_NUMBER() OVER(ORDER BY event_time) 'RN',
Session_id,event_time,server_principal_name,server_principal_id,database_name,statement

FROM sys.fn_get_audit_file(@filepath,default,default)

WHERE action_id IN('SL')

DECLARE @rid INT=1

DECLARE @tslRN INT=(SELECT MAX(RN) FROM @tsl)
WHILE @tslRN>=@rid
BEGIN

DECLARE @session_id int=(SELECT session_id FROM @tsl WHERE RN=@rid)

DECLARE @event_time datetime2=(SELECT event_time FROM @tsl WHERE RN=@rid)

DECLARE @server_principal_id int=(SELECT server_principal_id FROM @tsl WHERE RN=@rid)

INSERT @tlgls(event_time,additional_information)

SELECT TOP (1) event_time,additional_information

FROM sys.fn_get_audit_file(@filepath,default,default)

WHERE action_id='LGIS' AND session_id=@session_id AND server_principal_id=@server_principal_id AND event_time<@event_time

ORDER BY event_time DESC

SET @rid+=1
END

INSERT @retAuditReport

SELECT s.session_id, s.server_principal_name, (CAST(g.additional_information AS XML)).value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data";(action_info/address)[1]','nvarchar(100)') N'ip',

SWITCHOFFSET(CAST(g.event_time AS datetimeoffset),'+08:00') N'login_time',
s.database_name,

SWITCHOFFSET(CAST(s.event_time AS datetimeoffset),'+08:00') N'action_time',

s.statement N'tsql'

FROM @tsl s INNER JOIN @tlgls g

ON s.RN=g.RN
RETURN

END;
GO

--02 Using ufn_AuditReport to analysis audit log file

SELECT session_id N'Session ID', server_principal_name N'Login', ip N'Client Host Address', database_name N'Database', action_time N'Audit Time(Timezone Beijing GMT+08:00)', tsql N'T-SQL'

FROM dbo.ufn_AuditReport(N' D:\MSSQL\DATA\Audit_logs\Audit-AdventureWorks2012-SELECT_*')

创建数据表值函数ufn_AuditReport，来分析审核所记录的数据，在数据列additional_information存放客户端主机的IP地址，以XML结构方式显示，所以，需要利用XQuery来取得所需的数据。在调用此数据表值函数时，请输入要分析的审核文件的完整路径，可以搭配使用通配符“*”。