任务1：创建登录账户，授予适当的权限
步骤1：打开SSMS，输入如下语句，创建登录账户UltraSQL，授予访问AdventureWorks2012数据库的Person.Person和Person.Password权限。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
USE master
GO
--Create Login UltraSQL
CREATE
LOGIN UltraSQL
WITH
PASSWORD
=N’Mpdfzh7’,
DEFAULT_DATABASE=AdventureWorks2012, CHECK_EXPIRATION=
OFF
, CHECK_POLICY=
OFF
GO
--Create User UltraSQL In AdventureWorks2012
USE AdventureWorks2012
GO
CREATE
USER
UltraSQL
FOR
LOGIN UltraSQL
GO
--Grant Select Privilege to Person.Person & Person.Password
GRANT
SELECT
ON
Person.Person
TO
UltraSQL
GRANT
SELECT
ON
Person.
Password
TO
UltraSQL
GO
|
任务2：创建审核
步骤1：打开SSMS，登录到指定的实例，展开“Security”，“Audits”节点。
步骤2：在“Audits”节点上，右键选择“New Audit…”选项。
步骤3：在“Create Audit”窗口，输入以下的参数。
l 在“Audit name”输入Audit-AdventureWorks2012-AccessTable。
l 在“Audit destination”选择“File”
l 在“File path”输入D:\MSSQL\DATA\Audit_logs。
l 取消勾选位于“Maximum rollover files”框下的“Unlimited”。
l 在“Number of files”输入100。
l 取消勾选位于“Maximum file size”框下的“Unlimited”。
l 在“Maximum file size”输入10，选择“MB”框。
设置“Maximum file size”为10MB，可以让每个审核文件的大小，维持在指定的大小之内，当此审核文件的空间耗用完之后，将参照“Maximum rollover files”选项的设置，依据默认值，最多可保留的审核文件数据量，可达2147483647个文件。这里，每个审核文件最大可达10MB，最多可保留的文件数量是100个。
步骤4：单击“OK”完成设置。
步骤5：右键点击刚刚创建的审核“Audit-AdventureWorks2012-AccessTable”，选择“Enable Audit”选项。
任务3：创建访问数据表的审核
步骤1：展开“Database”，“AdventureWorks2012”，“Security”，“Database Audit Specifications”节点。
步骤2：在“Database Audit Specifications”节点，单击鼠标右键选择“New Database Audit Specification…”选项。
步骤3：在“Create Database Audit Specification”窗口输入以下的参数。
l 在“Name”输入DatabaseAuditSpecification-UltraSQLAccessPerson.Password。
l 在“Audit”下拉框选择之前创建的审核对象Audit-AdventureWorks2012-AccessTable。
l 在“Audit Action Type”中选择“SELECT”。
l 在“Object Class”中选择“OBJECT”选项。
l 在“Object Name”中单击图表，选择“Person.Password”选项。
l 在“Principal Name”中单击图表，选择“UltraSQL”选项。
步骤4：参照步骤3，在“Action”区域的第二行部分，添加对“Audit Action Type”是“UPDATE”的监视。。以针对用户UltraSQL查询或是更新数据表Person.Password进行审核。
步骤5：右键点击刚刚创建的数据库审核规范“DatabaseAuditSpecification-UltraSQLAccessPerson.Password”，选择“Enable Database Audit Specification”选项。
任务4：测试审核功能
步骤1：以UltraSQL的身份，通过SSMS连接到AdventureWorks2012。
步骤2：使用UltraSQL账号，对数据表Person.Password执行查询与更新等操作。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
USE AdventureWorks2012
GO
SELECT
*
FROM
Person.
Password
SELECT
*
FROM
Person.Person
SELECT
*
FROM
Person.
Password
UPDATE
Person.
Password
SET
ModifiedDate=GETDATE()
WHERE
BusinessEntityID=1
/*
Msg 229,
Level
14, State 5, Line 1
The
UPDATE
permission was denied
on
the object
'Password'
,
database
'AdventureWorks2012'
,
schema
'Person'
.
*/
SELECT
*
FROM
Person.
Password
WHERE
BusinessEntityID=1
SELECT
a.PasswordHash,b.FirstName,b.LastName
FROM
Person.
Password
a
INNER
JOIN
Person.Person b
ON
a.BusinessEntityID = b.BusinessEntityID
SELECT
TOP
1 *
FROM
Person.
Password
WHERE
BusinessEntityID=1
|
任务5：使用“Log File Viewer”，阅读审核日志
步骤1：以sysadmin身份登录实例，展开“Object Explorer”，“Security”，“Audits”节点。
步骤2：在审核“Audit-AdventureWorks2012-AccessTable”上单击右键，选择“View Audit Logs”。
步骤3：在“Log File Viewer”窗口的左侧，选择“Audit Collection”，“Audit-AdventureWorks2012-AccessTable”，并利用以下的方式来阅读所记录的审核信息。
l 在右边的“Log file summary”区域内，在“Action ID”框下，单击“SELECT”操作类型的数据行日志。
l 在下方的“Database Principal Name”区域，可以看到所记录的数据库用户。在“Statement”区域可以看到所记录的程序代码。
有个特别的现象，整理如下。
l 以执行UPDATE语句为例，将会引发SELECT与UPDATE等两个“Action ID”行为，也将会产生两笔审核日志，所记录下来的数据除了在“Action ID”区域是不同的之外（一个是SELECT，一个是UPDATE），其余部分都一样。此外，即便是用户UltraSQL没有权限可以修改数据表，这些行为也将被审核对象所记录，但在“Succeeded”区域部分日志为False。
任务6：使用T-SQL函数来分析、筛选审核的日志数据
步骤1：利用sysadmin身份，执行SSMS，输入如下查询语句，使用sys.dm_server_audit_status动态管理视图来查看各个审核对象的当前状态。
|
1
2
3
4
5
6
|
USE master
GO
SELECT
audit_id N
'Audit ID'
,
name
N
'Audit Name'
, status_desc N
'Server Audit Status'
,
Status_time N
'Last Status Changed Timestamp'
, audit_file_size N
'Audit File Size'
,
Audit_file_path N
'Audit File Full Path'
FROM
sys.dm_server_audit_status;
|
l 数据行status_desc（服务器审核状态）：查看各个审核对象是否已经启用。
l 数据行audit_file_size（审核文件大小（KB））：查看以二进制文件为目标的审核文件，其所使用的空间，以KB为单位；若非二进制文件，例如：以“Windows应用程序事件日志文件”来存放的审核日志，则会显示NULL。
l 数据行audit_file_path（审核文件目标的完整路径名称）：查看以二进制文件为目标的审核文件，其所在的完整路径与文件名。
步骤2：单击“New Query”，执行如下代码，使用函数fn_get_audit_file分析审核文件
|
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
|
--EX1 Query the Audit File
/*
Fn_get_audit_file (Transact-SQL)
fn_get_audit_file(file_pattern, {
default
| initial_file_name |
NULL
},{
default
| audit_file_offset |
NULL
})
Param:file_pattern
Set
the Audit File
Full
Path.
*/
SELECT
*
FROM
sys.fn_get_audit_file(N’ D:\MSSQL\DATA\Audit_logs\Audit-Login-
Create
%9Alter%9Drop_AF3AAECB-30CC-4476-9395-8754E60E356C_0_130639659785910000.sqlaudit’,
default
,
default
);
--EX2 Using * Query some Audit Files created by the same audit object
SELECT
*
FROM
sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\Audit-Login-
Create
%9Alter%9Drop_*.sqlaudit’,
default
,
default
);
--EX3 attention: event_time type is datetime2,stored GMT
SELECT
event_time N’Audit caused
Date
&
Time
(GMT)’, server_principal_name N’Login’,
Database_principal_name N’
User
’, database_name N’
Database
’, object_name N’Object
Name
’, statement N’TSQL’
FROM
sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\Audit-Login-
Create
%9Alter%9Drop_*.sqlaudit’,
default
,
default
);
--EX4 Convert to Timezone Bejing(GMT+08:00) using data type datetimeoffset & Function SWITCHOFFSET)
SELECT
SWITCHOFFSET(
CAST
(event_time
AS
datetimeoffset),’+08:00’) N’Audit
Action
caused
date
&
time
(Timezone Bejing GMT+08:00)’,
Server_principal_name N’Login’, database_principal_name N’
User
’,
Database_name N’
Database
’, object_name N’Object
Name
’, statement N’TSQL’
FROM
sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\Audit-Login-
Create
%9Alter%9Drop_*.sqlaudit’,
default
,
default
);
--EX5 Import all audit files in the directory into system
SELECT
*
FROM
sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\*’,
default
,
default
);
--EX6 Convert to Timezone Bejing(GMT+08:00) using data type datetimeoffset & Function SWITCHOFFSET)
SELECT
SWITCHOFFSET(
CAST
(event_time
AS
datetimeoffset),’+08:00’) N’Audit
Action
caused
date
&
time
(Timezone Bejing GMT+08:00)’,
Server_principal_name N’Login’, database_principal_name N’
User
’,
Database_name N’
Database
’, object_name N’Object
Name
’, statement N’TSQL’
FROM
sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\*’,
default
,
default
);
|
说明：
l 在EX1部分中函数fn_get_audit_file的第一个变量，更换为执行sys.dm_server_audit_status后，取得字段audit_file_path的值部分，或是填入完整的审核文件的文件名。在第二个和第三个变量，填入default，采取默认值即可。
l 若要将同一个审核对象所产生的多个审核文件，都加载到系统内进行分析，可以在文件名上，搭配使用通配符*，参考EX2代码。
l 审核所记录的日期时间是格林威治时间（GMT），数据类型为datatime2，可以转换成数据类型datetimeoffset，并利用函数SWITCHOFFSET，将此数据改以北京时区（GMT+08:00）的格式来显示，参考EX3和EX4。
l 若需要将指定文件夹内的各个审核文件，都加载到系统内进行分析，可以搭配通配符*，参考EX5和EX6。
任务7：审核文件的归档存放
步骤1：在D:\MSSQL\DATA\Audit_logs内创建新的文件夹Archive_Audit。
步骤2：打开文件夹D:\MSSQL\DATA\Audit_logs，复制此文件夹内的审核文件（*.sqlaudit）到文件夹D:\MSSQL\DATA\Audit_logs内，此为简易备份审核文件的方式。
步骤3：若要删除已经备份过的审核文件，请先确认此审核对象已经禁用。否则，正在使用的审核文件将将无法删除。
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1597086 ，如需转载请自行联系原作者
