SQL Server 创建服务器和数据库级别审计

2018-01-24 20:06:44来源:cnblogs.com作者:pursuer.chen人点击

分享

一、概述

在上一篇文章中已经介绍了审计的概念;本篇文章主要介绍如何创建审计,以及该收集哪些审核规范。

二、常用的审核对象

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_描述CREATE SERVER AUDIT [AuditServer_All]TO FILE (    FILEPATH = N'D:/Audit/AuditServer_All'  ----文件路径    ,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]GOALTER SERVER AUDIT AuditServer_All WITH(STATE=ON);---禁用USE [master]GOALTER SERVER AUDIT AuditServer_All WITH(STATE=OFF);

3.创建审核规范

----创建服务器审核规范,命名规范:AuditSpecification_描述(和审核描述保持一致)USE [master]GOCREATE SERVER AUDIT SPECIFICATION AuditSpecification_AllFOR 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.启用和禁用审核规范

--启用GOALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=ON);GO---禁用,注意作业会影响禁用ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=OFF);

5.测试

----创建登入名,并授予sysadmin角色USE [master]GOCREATE LOGIN [test] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOALTER SERVER ROLE [sysadmin] ADD MEMBER [test]GO

四、创建数据库审核

1.创建数据库审核 

USE [master]GO----创建审核,审核只能创建在服务器级别,命名规范AuditDatabase_数据库名_描述CREATE SERVER AUDIT [AuditDatabase_chenmh_AllObjectChange]TO FILE (    FILEPATH = N'D:/Audit/AuditDatabase_chenmh_AllObjectChange'    ----文件路径    ,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]GOALTER SERVER AUDIT AuditDatabase_chenmh_AllObjectChange WITH(STATE=ON);---禁用USE [master]GO--ALTER SERVER AUDIT AuditDatabase_chenmh_AllObjectChange WITH(STATE=OFF);

注意:数据库审核也是创建在服务器级别,数据库审核规范是创建在具体的数据库下。

2.创建数据库审核规范

CREATE DATABASE Audit;
GO----创建数据库审核规范,命名规范:AuditSpecification_描述(和审核描述保持一致)USE [Audit]GOCREATE DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChangeFOR SERVER AUDIT AuditDatabase_Audit_AllObjectChange ---DDL相关操作 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), ---DML相关操作,PUBLIC代表所有用户 -----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]GOALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=ON);---禁用,注意作业会影响禁用USE [Audit]GO--ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=OFF);

3.测试

USE [Audit]GOCREATE TABLE a(id int not null);GOALTER TABLE a ADD name varchar(10);GOINSERT INTO a values(1,'a')GODELETE FROM a WHERE id=1;GODROP 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_auditsSELECT * FROM sys.server_audit_specificationsSELECT * FROM sys.server_audit_specification_detailsSELECT 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 AuditGradeFROM SYS.server_audits sa INNER JOIN sys.server_audit_specifications sas ON sa.audit_guid=sas.audit_guidINNER JOIN  sys.server_audit_specification_details sasd ON sas.server_specification_id=sasd.server_specification_idLEFT 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_specificationsSELECT * FROM chenmh.sys.database_audit_specification_detailsSELECT 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 AuditGradeFROM SYS.server_audits sa INNER JOIN chenmh.sys.database_audit_specifications sas ON sa.audit_guid=sas.audit_guidINNER JOIN  chenmh.sys.database_audit_specification_details sasd ON sas.database_specification_id=sasd.database_specification_idLEFT 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]GOALTER SERVER  AUDIT SPECIFICATION AuditSpecification_All WITH (STATE=OFF)GODROP SERVER AUDIT SPECIFICATION AuditSpecification_AllGO--删除服务器审核对象,先禁用才能删除USE [master]ALTER SERVER AUDIT AuditServer_All WITH (STATE=OFF)GODROP SERVER AUDIT AuditServer_All;

七、在AlwaysOn中创建审核

1.创建服务器级别审核

和在单实例上创建审核一样,主副本和辅助副本都需要创建。

2.创建数据库级别审核

先在主副本中执行

USE [master]GO----创建审核,审核只能创建在服务器级别,命名规范AuditDatabase_数据库名_描述CREATE SERVER AUDIT [AuditDatabase_Audit_AllObjectChange]TO FILE (    FILEPATH = N'C:/Audit/AuditDatabase_Audit_AllObjectChange'    ----文件路径    ,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]GOALTER SERVER AUDIT AuditDatabase_Audit_AllObjectChange WITH(STATE=ON);---禁用USE [master]GO--ALTER SERVER AUDIT AuditDatabase_Audit_AllObjectChange WITH(STATE=OFF);CREATE DATABASE Audit;GO----创建数据库审核规范,命名规范:AuditSpecification_描述(和审核描述保持一致)USE [test]GOCREATE DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChangeFOR SERVER AUDIT AuditDatabase_Audit_AllObjectChange    ---DDL相关操作    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),    ---DML相关操作,PUBLIC代表所有用户    -----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 [test]GOALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=ON);

在辅助副本执行

-------========================创建服务器审核=======================USE [master]GO----创建审核,命名规范AuditServer_描述CREATE SERVER AUDIT [AuditServer_All]TO FILE (    FILEPATH = N'C:/Audit/AuditServer_All/'  ----文件路径    ,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]GOALTER SERVER AUDIT AuditServer_All WITH(STATE=ON);---------================创建数据库审核规范====================---创建数据库审核规范,命名规范:AuditSpecification_描述(和审核描述保持一致)USE [master]GOCREATE SERVER AUDIT SPECIFICATION AuditSpecification_AllFOR 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--启用GOALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=ON);GOUSE [master]GO----创建审核,审核只能创建在服务器级别,命名规范AuditDatabase_数据库名_描述CREATE SERVER AUDIT [AuditDatabase_Audit_AllObjectChange]TO FILE (    FILEPATH = N'C:/Audit/AuditDatabase_Audit_AllObjectChange'    ----文件路径    ,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]GOALTER SERVER AUDIT AuditDatabase_Audit_AllObjectChange WITH(STATE=ON);-----===============关联数据库审核规范与服务器审核====================USE [TEST]GO----禁用数据库审核规范ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=OFF);GO---将数据库审核规范关联服务器审核ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChangeFOR SERVER AUDIT AuditDatabase_Audit_AllObjectChangeWITH (STATE =ON)GO----启用数据库审核规范ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=ON);

八、总结

审核是数据库规范的一部分,在安全审计方面也非常的重要,建议生产系统都必须创建审核。

参考: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

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。

《欢迎交流讨论》

相关文章

    无相关信息

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台