任务1：创建审核
步骤1：打开SSMS，执行以下脚本。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
--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，执行以下脚本。
|
1
2
3
4
5
6
7
8
9
10
|
--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：执行以下代码，查看服务器审核规范对象。
|
1
2
3
4
5
6
7
8
|
--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：执行以下代码，查看可用的审核操作、审核操作组的项目。
|
1
2
3
4
5
|
--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，执行以下脚本
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
--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：执行以下脚本，查看可用于设置数据库审核规范对象的相关信息。
|
1
2
3
4
5
6
7
8
|
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并赋予适当的权限。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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，执行以下代码。
|
1
2
3
4
5
|
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：执行以下代码。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
--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
|
|
1
2
3
|
--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来取得所需的数据。在调用此数据表值函数时，请输入要分析的审核文件的完整路径，可以搭配使用通配符“*”。
本文转自UltraSQL51CTO博客，原文链接： http://blog.51cto.com/ultrasql/1597725，如需转载请自行联系原作者
