（4.18）数据库审计，sql server audit

概念

审计（Audit）用于追踪和记录SQL Server实例或数据库中发生的事件，审计主要包括审计对象（Audit）和审计规范（Audit Specification）;

　　创建审计首先需要创建一个SQL Server 实例级的审计对象，然后，创建从属于它的“服务器审计规范”或“数据库审计规范”。审计数据可以输出到审计文件（File）、安全日志（Security Log）和应用程序日志（Application Log）。

启用审计的目的一般是为了监控SQL Server执行的操作;

　　例如，记录什么人在什么时候查询数据、修改数据，登陆SQL Server实例等，由于审计记录的数据有可能很丰富，因此，启用审计可能产生大量的日志数据，占用磁盘的大量空间。

　　审计使用一句话来概括就是：记录谁在什么时候做了什么事，审计对象（Audit）定义：配置数据存在何处，而审计规范（Audit Specification）定义：记录什么事，一旦特定的事件触发，SQL Server引擎就使用审计记录事件发生的现场信息。

创建和使用审计的一般步骤是：

step1：创建服务器级别的审计对象，并启用审计对象；
step2：创建审计规范，并映射到审计对象审核，启用审计规范，审计对象开始追踪和记录数据；
step3：查看审计数据，可以通过使用SSMS的”Log Files Viewer“或函数sys.fn_get_audit_file 查看记录的日志数据。

一，创建审计（存储对象）

首先创建服务器级别的审计对象，展开"Security"，右击Audits，通过“New Audit”，打开“Create Audit”窗体开始创建审计对象，审计输出的数据保存到“Audit destination”中，本文选择File，把数据存储到审计文件中，其他类型是：Security Log和Application Log。Queue delay是指数据写入到审计文件的延迟，默认是1s。审计对象的作用是指定审计数据保存的路径，以及写入数据的延迟和数据文件的大小，审计对象主要是存储审计规范的数据。

根据硬盘空间的限制，设置审计对象的属性

　　Audit File maximum Limit、Maximum File size、以及Reserve disk space，控制审计文件的大小，管理硬盘空间的使用，避免硬盘爆掉。

改一下，改成最多100个文件滚动更新，如果已经有100个，要到101个的时候，会覆盖重写第1个文件，这就是滚动更新；

且设置了一个文件最大为1GB

创建的审计对象默认是禁用（Disable）的，在使用审计对象之前，必须启用，选中新建的审计对象，右击，选中“Enable Audit”。

二，创建服务器级别的审计规范（实际的实例层面审计项）

展开Security，选中“Server Audit Specifications”，右击弹出快捷菜单，选择“New Server Audit Specifications”，打开“Create Server Audit Specifications”的窗体，通过UI创建审计规范：

审计规范指定审计对象记录的事件类型，在审计规范中指定的事件类型，SQL Server 一旦检测到事件发生，就会把跟该事件相关的信息写入到审计对象指定的文件中，保存起来，以便于后续的检查（review）。

1，添加审计操作组

创建审计规范，选择审计对象，从Audit Action Type列表中选择审计操作组，创建的审计规范默认是禁用的，选中新建的审计规范，右击弹出快捷菜单，选中”Enable Server Audit Specifications “启用：

从审核操作类型列表中选择审计操作组，审计操作组是审计记录的事件操作类型，常用的审计操作组是：

DATABASE_OBJECT_ACCESS_GROUP：访问数据库对象时将引发此事件；
DATABASE_OBJECT_CHANGE_GROUP：针对数据库对象（如架构）执行 CREATE、ALTER 或 DROP 语句时将引发此事件。创建、更改或删除任何数据库对象时均将引发此事件。
DATABASE_OPERATION_GROUP：数据库中发生操作（如检查点或订阅查询通知）时将引发此事件。对于任何数据库的任何操作都将引发此事件。
FAILED_DATABASE_AUTHENTICATION_GROUP：指示某个主体尝试登录到数据库并且失败。
FAILED_LOGIN_GROUP：指示主体尝试登录到 SQL Server ，但是失败。
SUCCESSFUL_LOGIN_GROUP：指示主体已成功登录到 SQL Server。

2，查看审计数据

点击审计对象，右击弹出快捷菜单，点击”View Audit Logs“查看审计对象记录的数据：

比如一个创建表的记录，信息如下；谁操作的，什么库下，等等相关信息都会记录

或者通过TSQL 函数查看审计数据，

sys.fn_get_audit_file ( file_pattern,   
    { default | initial_file_name | NULL },   
    { default | audit_record_offset | NULL } )

审计文件名由四部分组成：AuditName_GUID_n_m.sqlaudit，第一个参数是file_pattern，包括路径和文件名，对于文件名，可以通过特殊的匹配符指定：

*：表示所有的字符；
{}：指定GUID；
如果文件名以扩展名( .sqlaudit）结尾，表示查看特定的文件；

例如，查看所有的审计文件的数据：

SELECT 
    DATEADD(hour,8, event_time) as '执行时间',
    CASE 
        WHEN action_id = 'IN' THEN 'INSERT'
        WHEN action_id = 'AL' THEN 'ALTER'
        WHEN action_id = 'DL' THEN 'DELETE'
        WHEN action_id = 'SL' THEN 'SELECT'
        WHEN action_id = 'UP' THEN 'UPDATE'
        WHEN action_id = 'DR' THEN 'DROP'
        WHEN action_id = 'CR' THEN 'CREATE'
        ELSE action_id END as '操作类型',
    CASE
        WHEN succeeded = 1 THEN '成功'
        WHEN succeeded = 0 THEN '失败'
        ELSE ' ' END as '是否执行成功',
    session_id as '会话ID',
    session_server_principal_name as '执行人',
    object_name as '操作对象名称',
    statement as '执行操作',
    file_name ,
    audit_file_offset
from sys.fn_get_audit_file('D:\dba_tools\eventlog\audit*',default,default)

三，创建数据库级别的审计规范（审计规则只应用到该数据库）

在数据库的Security中右击“Database Audit Specifications”，数据库级别的审计操作组，大部分和服务器级别的审计操作组很相似；

除了数据库级别的审计动作（Database-Level Audit Actions），在数据库对象上发生以下操作（Action）时，记录事件的信息：

SELECT
UPDATE
INSERT
DELETE
EXECUTE
REFERENCES

数据库级别的审计操作追踪和记录的是数据库对象（schema，objects）上发生的事件，因此必须配置Object Class、Object Schema，Object Name 和 Principal Name字段：

查看审计数据，选中Server级别的审计对象，通过”View Audit Logs“查看记录的日志数据。

四，维护审计

创建审计很简单，DBA需要更多关注的是审计数据的维护

略，后续补充

参考文档：

SQLSERVER2008新增的审核/审计功能

SQL Server Audit (Database Engine)

SQL Server Audit Action Groups and Actions

sys.fn_get_audit_file (Transact-SQL)

作者：悦光阴

出处：http://www.cnblogs.com/ljhdo/

本文版权归作者和博客园所有，欢迎转载，但未经作者同意，必须保留此段声明，且在文章页面醒目位置显示原文连接，否则保留追究法律责任的权利。

本文只是简单介绍，详情参考：https://www.cnblogs.com/chenmh/p/8296446.html

【最佳实践】

（1）构建实例与数据库审计代码

---------------------------------------------------------------------------------------------------------
--create Server Audit

USE [master]
GO
CREATE SERVER AUDIT [RAPDB_CD_PRC_Audit_7]
TO FILE
( FILEPATH = N'D:\RAPDB_AUDIT\RAPDB_CD_PRC'
 ,MAXSIZE = 50 MB
 ,MAX_ROLLOVER_FILES = 10
 ,RESERVE_DISK_SPACE = ON
)
WITH
( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
 --AUDIT_GUID=uniqueidentifier 
)
--WHERE object_name='tb'

GO
alter server audit [RAPDB_CD_PRC_Audit_7] with (state=on)        --Enable Server Audit
GO
-- 查询需要添加审计的表
USE [RAPDB_CD_PRC]
GO
select * from sys.objects where  type = 'U' order by name


--Create and enable Database Audit Specification

USE [RAPDB_CD_PRC]
GO
CREATE DATABASE AUDIT SPECIFICATION [RAPDB_CD_PRC_Audit]
FOR SERVER AUDIT [RAPDB_CD_PRC_Audit_157]
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
--ADD (DELETE, INSERT, UPDATE ON OBJECT::[dbo].[a] BY [public])
ADD (DELETE, INSERT, UPDATE ON OBJECT::[dbo].[e_time] BY [public]),
·······
--    设置需要审计的表
WITH (STATE=ON)
GO

（2）针对整个库

在SSMS上无法选择所有库；选库就会自动变成object，不写又说必须要填对象名称，恶心；

但可以用SQL来

use test;
CREATE DATABASE AUDIT SPECIFICATION [Audit_dongxin]
FOR SERVER AUDIT [audit-20211222-154058] --审计文件名
ADD (SELECT ON DATABASE::[test] By public)  --【test】为数据库名，public 为主体（也可以是账户）
WITH (STATE = ON)
GO

（3）查看审计文件

-- 审核文件保存路径：   I:\AUDIT\
-- select audit record
SELECT 
    DATEADD(hour,8, event_time) as '执行时间',
    CASE 
        WHEN action_id = 'IN' THEN 'INSERT'
        WHEN action_id = 'AL' THEN 'ALTER'
        WHEN action_id = 'DL' THEN 'DELETE'
        WHEN action_id = 'SL' THEN 'SELECT'
        WHEN action_id = 'UP' THEN 'UPDATE'
        WHEN action_id = 'DR' THEN 'DROP'
        WHEN action_id = 'CR' THEN 'CREATE'
        ELSE action_id END as '操作类型',
    CASE
        WHEN succeeded = 1 THEN '成功'
        WHEN succeeded = 0 THEN '失败'
        ELSE ' ' END as '是否执行成功',
    session_id as '会话ID',
    session_server_principal_name as '执行人',
    object_name as '操作对象名称',
    statement as '执行操作',
    file_name ,
    audit_file_offset
from sys.fn_get_audit_file('I:\AUDIT\*',default,default)

这是查询审计结果

delete审计结果展示：

posted @ 2018-09-26 16:27 郭大侠1 阅读(1215) 评论(0) 编辑收藏举报

刷新页面返回顶部