开发者社区> 技术小甜> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server 审核(Audit)-- 使用T-SQL创建审核,以审核数据库内的查询操作

简介:
+关注继续查看

 

任务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 此服务器审核对象已经启用。

 

clip_image001

 

 

任务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

 

clip_image002

 

步骤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

 

clip_image003

clip_image004

clip_image005

 

 

任务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

 

clip_image006

clip_image007

 

 

任务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”,利用以下的方式来阅读所记录的审核信息。

 

clip_image008

需要将“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来取得所需的数据。在调用此数据表值函数时,请输入要分析的审核文件的完整路径,可以搭配使用通配符“*”。

 

clip_image009


















本文转自UltraSQL51CTO博客,原文链接: http://blog.51cto.com/ultrasql/1597725,如需转载请自行联系原作者



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
NV21数据的旋转
NV21数据的旋转
47 0
Java数据库连接--JDBC基础知识(操作数据库:增删改查)
一、JDBC简介   JDBC是连接java应用程序和数据库之间的桥梁。   什么是JDBC?   Java语言访问数据库的一种规范,是一套API。   JDBC (Java Database Connectivity) API,即Java数据库编程接口,是一组标准的Java语言中的接口和类,使用这些接口和类,Java客户端程序可以访问各种不同类型的数据库。
1033 0
jquery清空表单数据
转载的文章,要明显的标志出转载地址: http://reymont.iteye.com/blog/756756 惯例: 我是温浩然: 如果同一张页面,可能需要编辑的数据不一样,我的如图下: 按钮不同,页面相同,还需要显示的数据不同,这里会由于页面的缓存问题,导致,每次点开这个页面显示的数据相同。 这不是我们想要的。这就需要清楚表单数据了。 方法如下: 注意!!!!!
1279 0
用vc创建新的SQL SERVER数据库
   _variant_t strSQL;    CString strConnect;//===================使用系统数据库========================...
681 0
清空数据库中的所有用户表(删除数据库中的表)
想找一个命令能清空数据库中所有用户表的方法没有找到,只能用一个比较烦琐的方法,不知道有没有更简单的方法? declare @strSqlTmp varchar(8000)declare @strSql varchar(8000)set @strSqlTmp = ''declare online_cu...
657 0
+关注
10137
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载