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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

 

任务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,如需转载请自行联系原作者



相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2天前
|
XML Java 数据库连接
【MyBatis】MyBatis操作数据库(一)
【MyBatis】MyBatis操作数据库(一)
9 1
|
5天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
5天前
|
存储 关系型数据库 MySQL
MySQL数据库的数据类型、语法和高级查询
MySQL数据库的数据类型、语法和高级查询
22 0
|
6天前
|
SQL 存储 关系型数据库
关系型数据库中的SQL Server
【6月更文挑战第11天】
43 3
|
6天前
|
SQL 弹性计算 API
云服务器 ECS产品使用问题之如何通过API调用阿里云服务器上SQL Server数据库中的数据
云服务器ECS(Elastic Compute Service)是各大云服务商阿里云提供的一种基础云计算服务,它允许用户租用云端计算资源来部署和运行各种应用程序。以下是一个关于如何使用ECS产品的综合指南。
|
13天前
|
SQL 关系型数据库 MySQL
Python 操作 MySQL 数据库
Python 操作 MySQL 数据库
|
12天前
|
SQL 存储 安全
SQL入门与进阶:数据库查询与管理的实用指南
一、引言 在数字化时代,数据库已经成为各行各业存储、管理和分析数据的关键基础设施
|
2天前
|
SQL 前端开发 数据可视化
数据库开发关键之与DQL查询语句有关的两个案例
数据库开发关键之与DQL查询语句有关的两个案例
5 0
|
2天前
|
SQL 前端开发 关系型数据库
零基础学习数据库SQL语句之查询表中数据的DQL语句
零基础学习数据库SQL语句之查询表中数据的DQL语句
4 0
|
2天前
|
SQL Java 数据库连接
【MyBatis】深入解析MyBatis:高效操作数据库技术详解
【MyBatis】深入解析MyBatis:高效操作数据库技术详解
17 0

热门文章

最新文章