一,概述
在上一篇文章中已经介绍了审计的概念;本篇文章主要介绍如何创建审计,以及该收集哪些审核规范.
二,常用的审核对象
2.1,服务器审核对象
1.FAILED_LOGIN_GROUP(Audit Login Failed Event Class)
指示主体尝试登录到 SQL Server,等效于 Audit Login Failed Event Class,
比如:登入失败的操作
2.SERVER_OBJECT_CHANGE_GROUP(Audit Server Object Management)
针对服务器对象执行 CREATE,ALTER 或 DROP 操作时将引发此事件.等效于 Audit Server Object Management 事件类.
比如:删除链接服务器对象等凌驾于数据库级别以上的对象 (权限除外).
3.SERVER_ROLE_MEMBER_CHANGE_GROUP
向固定服务器角色添加登录名或从中删除登录名时将引发此事件, 此事件由 sp_addsrvrolemember 和 sp_dropsrvrolemember 存储过程引发. 等效于 Audit Add Login to Server Role Event Class.
比如:授予,撤销服务器角色等
4.SERVER_PRINCIPAL_CHANGE_GROUP
创建,更改或删除服务器主体时将引发此事件
比如:创建删除登入名等
5.SUCCESSFUL_LOGIN_GROUP
指示主体已成功登录到 SQL Server. 此类中的事件由新连接引发或由连接池中重用的连接引发. 等效于 Audit Login Event Class.
注意: 每一个连接会话都会记录,开启 SUCCESSFUL_LOGIN_GROUP 会产生很多的记录
2.2,审核自身审核对象
AUDIT_CHANGE_GROUP
2.3,数据库审核对象
DDL 相关
1.DATABASE_PRINCIPAL_CHANGE_GROUP
在数据库中创建,更改或删除主体(如用户)时,将引发此事件. 等效于 Audit Database Principal Management Event Class.
比如:创建删除登入名等
2.DATABASE_ROLE_MEMBER_CHANGE_GROUP
向数据库角色添加登录名或从中删除登录名时将引发此事件. 此事件类与 sp_addrolemember,sp_changegroup 和 sp_droprolemember 存储过程一起使用.等效于 Audit Add Member to DB Role 事件类
比如:授予,撤销服务器角色等
3.DATABASE_CHANGE_GROUP(Audit Database Management 事件)
创建,更改或删除数据库时将引发此事件.创建,更改或删除任何数据库时均将引发此事件.等效于 Audit Database Management 事件类.
比如:删除创建数据库,修改数据库属性等数据库本身的修改操作
4.DATABASE_OBJECT_CHANGE_GROUP(Audit Database Object Management 事件)
针对数据库对象(如架构)执行 CREATE,ALTER 或 DROP 语句时将引发此事件.创建,更改或删除任何数据库对象时均将引发此事件.等效于 Audit Database Object Management 事件类.
比如:Serive Broker 相关对象,存储,安全等凌驾于用户创建的对象以上的对象 (权限除外).
5.SCHEMA_OBJECT_CHANGE_GROUP(Audit Schema Object Management Event Class)
针对架构执行 CREATE,ALTER 或 DROP 操作时将引发此事件.等效于 Audit Schema Object Management 事件类.此事件针对架构对象引发.等效于 Audit Object Derived Permission 事件类.任何数据库的任何架构发生更改时,均将引发此事件.等效于 Audit Statement Permission 事件类.
比如:表,存储过程,视图,函数,架构等对象.
DML 相关
SELECT,DELETE,INSERT,UPDATE
三,创建服务审核
1. 创建审核
USE [master]
GO
----创建审核,命名规范AuditServer_描述( FILEPATH = N'D:\Audit\AuditServer_All' ----文件路径
CREATE SERVER AUDIT [AuditServer_All]
TO FILE
,MAXSIZE = 2 GB ----文件最大大小,单位可以是MB,GB,TB
,MAX_FILES = 2147483647 ----最大文件数,最大2147483647也就是无限制
-----,MAX_ROLLOVER_FILES = 2147483647 ----最大滚动更新文件数,最大2147483647也就是无限制,不能和MAX_FILES一起配置
,RESERVE_DISK_SPACE = ON ----保留磁盘空间
)
WITH
( QUEUE_DELAY = 1000 ----队列延时,默认1S,ON_FAILURE = CONTINUE ----审核失败继续,还可以指定SHUTDOWN关闭数据库服务器,但是必须有相关权限
)
GO
注意:默认创建审核是禁用的,必须手动启用,同样修改审核之前也必须先禁用然后再修改.路径'D:\Audit\AuditServer_All'必须事先创建好.2. 启用和禁用审核
---启用
---禁用
USE [master]
GO
ALTER SERVER AUDIT AuditServer_All WITH(STATE=ON);
3. 创建审核规范
USE [master]
GO
ALTER SERVER AUDIT AuditServer_All WITH(STATE=OFF);
----创建服务器审核规范,命名规范:AuditSpecification_描述(和审核描述保持一致)
审核的事件包括:登入失败的操作,服务器级别对象的操作,创建删除登入用户,服务器角色的授予和撤销,对审核配置的操作.还有一些其它的审核类型暂时不做审计.其实这里也可以将将数据库级别的审核操作创建到服务器级别下,这样的话
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION AuditSpecification_All
FOR SERVER AUDIT AuditServer_All
ADD (FAILED_LOGIN_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP)
GO
4. 启用和禁用审核规范
--启用
---禁用,注意作业会影响禁用
GO
ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=ON);
GO
ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=OFF);
5. 测试
----创建登入名,并授予sysadmin角色
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [test]
GO
四,创建数据库审核
1. 创建数据库审核
USE [master]
GO
----创建审核,审核只能创建在服务器级别,命名规范AuditDatabase_数据库名_描述( FILEPATH = N'D:\Audit\AuditDatabase_chenmh_AllObjectChange' ----文件路径
CREATE SERVER AUDIT [AuditDatabase_chenmh_AllObjectChange]
TO FILE
,MAXSIZE = 2 GB ----文件最大大小,单位可以是MB,GB,TB
,MAX_FILES = 2147483647 ----最大文件数,最大2147483647也就是无限制
-----,MAX_ROLLOVER_FILES = 2147483647 ----最大滚动更新文件数,最大2147483647也就是无限制,不能和MAX_FILES一起配置
,RESERVE_DISK_SPACE = ON ----保留磁盘空间
)
WITH
( QUEUE_DELAY = 1000 ----队列延时,默认1S,ON_FAILURE = CONTINUE ----审核失败继续,还可以指定SHUTDOWN关闭数据库服务器,但是必须有相关权限
)
GO
---启用---禁用
USE [master]
GO
ALTER SERVER AUDIT AuditDatabase_chenmh_AllObjectChange WITH(STATE=ON);
注意:数据库审核也是创建在服务器级别,数据库审核规范是创建在具体的数据库下.
USE [master]
GO
--ALTER SERVER AUDIT AuditDatabase_chenmh_AllObjectChange WITH(STATE=OFF);
2. 创建数据库审核规范
----创建服务器审核规范,命名规范:AuditSpecification_描述(和审核描述保持一致)
CREATE DATABASE Audit;
GO
---DDL相关操作
USE [Audit]
GO
CREATE DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange
FOR SERVER AUDIT AuditDatabase_Audit_AllObjectChange
---DML相关操作,PUBLIC代表所有用户
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
--启用
-----ADD(SELECT ON SCHEMA: :[dbo] BY PUBLIC),
ADD(DELETE ON SCHEMA: :[dbo] BY PUBLIC),
ADD(INSERT ON SCHEMA: :[dbo] BY PUBLIC),
ADD(UPDATE ON SCHEMA: :[dbo] BY PUBLIC);
GO
---禁用,注意作业会影响禁用
USE [Audit]
GO
ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=ON);
3. 测试
USE [Audit]
GO
--ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=OFF);
USE [Audit]
GO
CREATE TABLE a(id int not null);
GO
ALTER TABLE a ADD name varchar(10);
GO
INSERT INTO a values(1,'a')
GO
DELETE FROM a WHERE id=1;
GO
DROP TABLE a;
备注:审计记录的日期和使用 GETDATE() 查询的时间 8 个小时
五,查询
----查询审核日志
select * from sys.fn_get_audit_file('D:\Audit\AuditServerFile_66D8F97A-B495-4CB4-83EA-564D1ECF9988_0_131604628132230000.sqlaudit',DEFAULT, DEFAULT) AS a
--服务器审核相关视图
--数据库审核规范视图
SELECT * FROM SYS.server_audits
SELECT * FROM sys.server_audit_specifications
SELECT * FROM sys.server_audit_specification_details
SELECT sa.name as AuditName,
sa.type_desc AS StoreType,
sas.name AS AuditSpecificationsName,
sasd.audit_action_id,
sasd.audit_action_name,
sasd.class_desc as AuditGrade
FROM SYS.server_audits sa INNER JOIN sys.server_audit_specifications sas ON sa.audit_guid=sas.audit_guid
INNER JOIN sys.server_audit_specification_details sasd ON sas.server_specification_id=sasd.server_specification_id
LEFT JOIN sys.dm_audit_actions daa ON sasd.audit_action_id=daa.action_id AND sasd.class_desc=daa.class_desc
六,删除
SELECT * FROM chenmh.sys.database_audit_specifications
SELECT * FROM chenmh.sys.database_audit_specification_details
SELECT sa.name as AuditName,
sa.type_desc AS StoreType,
sas.name AS AuditSpecificationsName,
sasd.audit_action_id,
sasd.audit_action_name,
sasd.class_desc as AuditGrade
FROM SYS.server_audits sa INNER JOIN chenmh.sys.database_audit_specifications sas ON sa.audit_guid=sas.audit_guid
INNER JOIN chenmh.sys.database_audit_specification_details sasd ON sas.database_specification_id=sasd.database_specification_id
LEFT JOIN sys.dm_audit_actions daa ON sasd.audit_action_id=daa.action_id AND sasd.class_desc=daa.class_desc
----
SELECT * FROM sys.dm_audit_actions WHERE action_id='DL'
--删除服务器审核规范,先禁用才能删除
--删除服务器审核对象,先禁用才能删除
USE [master]
GO
ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH (STATE=OFF)
GO
DROP SERVER AUDIT SPECIFICATION AuditSpecification_All
GO
七,总结
USE [master]
ALTER SERVER AUDIT AuditServer_All WITH (STATE=OFF)
GO
DROP SERVER AUDIT AuditServer_All;
审核是数据库规范的一部分,在安全审计方面也非常的重要,建议生产系统都必须创建审核.
参考:https://docs.microsoft.com/zh-cn/sql/relational-databases/system-functions/sys-fn-get-audit-file-transact-sql
备注:
作者: pursuer.chen
博客: http://www.cnblogs.com/chenmh
本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利.
《欢迎交流讨论》
来源: https://www.cnblogs.com/chenmh/p/8296446.html