2018-01-16 16:11 by pursuer.chen, … 阅读, … 评论, 收藏, 编辑

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

 

 

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会产生很多的记录

AUDIT_CHANGE_GROUP

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 事件类。
比如：表、存储过程、视图、函数、架构等对象。

SELECT、DELETE、INSERT、UPDATE

  1. USE [master]
  2. GO
  3. ----创建审核,命名规范AuditServer_描述
  4. CREATE SERVER AUDIT [AuditServer_All]
  5. TO FILE 
  6. (    FILEPATH = N'D:\Audit\AuditServer_All'  ----文件路径
  7.     ,MAXSIZE = 2 GB                     ----文件最大大小，单位可以是MB、GB、TB
  8.     ,MAX_FILES = 2147483647             ----最大文件数，最大2147483647也就是无限制
  9. -----,MAX_ROLLOVER_FILES = 2147483647   ----最大滚动更新文件数，最大2147483647也就是无限制，不能和MAX_FILES一起配置
  10.     ,RESERVE_DISK_SPACE = ON            ----保留磁盘空间
  11. )
  12. WITH
  13. (    QUEUE_DELAY = 1000                  ----队列延时，默认1S
  14.     ,ON_FAILURE = CONTINUE              ----审核失败继续，还可以指定SHUTDOWN关闭数据库服务器，但是必须有相关权限
  15. )
  17. GO

注意：默认创建审核是禁用的，必须手动启用，同样修改审核之前也必须先禁用然后再修改。路径‘D:\Audit\AuditServer_All’必须事先创建好。

 

2.启用和禁用审核

  1. ---启用
  2. USE [master]
  3. GO
  4. ALTER SERVER AUDIT AuditServer_All WITH(STATE=ON);
  5. ---禁用
  6. USE [master]
  7. GO
  8. ALTER SERVER AUDIT AuditServer_All WITH(STATE=OFF);
  1. ----创建服务器审核规范,命名规范:AuditSpecification_描述（和审核描述保持一致）
  2. USE [master]
  3. GO
  4. CREATE SERVER AUDIT SPECIFICATION AuditSpecification_All
  5. FOR SERVER AUDIT AuditServer_All
  6.     ADD (FAILED_LOGIN_GROUP),
  7.     ADD (SERVER_OBJECT_CHANGE_GROUP),
  8.     ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
  9.     ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
  10.     ADD (AUDIT_CHANGE_GROUP)
  11. GO

审核的事件包括：登入失败的操作、服务器级别对象的操作、创建删除登入用户、服务器角色的授予和撤销、对审核配置的操作。还有一些其它的审核类型暂时不做审计。其实这里也可以将将数据库级别的审核操作创建到服务器级别下，这样的话

  1. --启用
  2. GO
  3. ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=ON);
  4. GO
  5. ---禁用,注意作业会影响禁用
  6. ALTER SERVER AUDIT SPECIFICATION AuditSpecification_All WITH(STATE=OFF);
  1. ----创建登入名,并授予sysadmin角色
  2. USE [master]
  3. GO
  4. CREATE LOGIN [test] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  5. GO
  6. ALTER SERVER ROLE [sysadmin] ADD MEMBER [test]
  7. GO

  1. USE [master]
  2. GO
  3. ----创建审核,审核只能创建在服务器级别，命名规范AuditDatabase_数据库名_描述
  4. CREATE SERVER AUDIT [AuditDatabase_chenmh_AllObjectChange]
  5. TO FILE 
  6. (    FILEPATH = N'D:\Audit\AuditDatabase_chenmh_AllObjectChange'    ----文件路径
  7.     ,MAXSIZE = 2 GB                     ----文件最大大小，单位可以是MB、GB、TB
  8.     ,MAX_FILES = 2147483647             ----最大文件数，最大2147483647也就是无限制
  9. -----,MAX_ROLLOVER_FILES = 2147483647   ----最大滚动更新文件数，最大2147483647也就是无限制，不能和MAX_FILES一起配置
  10.     ,RESERVE_DISK_SPACE = ON            ----保留磁盘空间
  11. )
  12. WITH
  13. (    QUEUE_DELAY = 1000                  ----队列延时，默认1S
  14.     ,ON_FAILURE = CONTINUE              ----审核失败继续，还可以指定SHUTDOWN关闭数据库服务器，但是必须有相关权限
  15. )
  17. GO
  18. ---启用
  19. USE [master]
  20. GO
  21. ALTER SERVER AUDIT AuditDatabase_chenmh_AllObjectChange WITH(STATE=ON);
  22. ---禁用
  23. USE [master]
  24. GO
  25. --ALTER SERVER AUDIT AuditDatabase_chenmh_AllObjectChange WITH(STATE=OFF);

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

  1. CREATE DATABASE Audit;
    GO
  2. ----创建服务器审核规范,命名规范：AuditSpecification_描述（和审核描述保持一致）
  3. USE [Audit]
  4. GO
  5. CREATE DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange
  6. FOR SERVER AUDIT AuditDatabase_Audit_AllObjectChange
  7.     ---DDL相关操作
  8.     ADD (DATABASE_CHANGE_GROUP),
  9.     ADD (DATABASE_OBJECT_CHANGE_GROUP),
  10.     ADD (SCHEMA_OBJECT_CHANGE_GROUP),
  11.     ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
  12.     ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
  13.     ---DML相关操作,PUBLIC代表所有用户
  14.     -----ADD (SELECT ON SCHEMA::[dbo] BY PUBLIC),
  15.     ADD (DELETE ON SCHEMA::[dbo] BY PUBLIC),
  16.     ADD (INSERT ON SCHEMA::[dbo] BY PUBLIC),
  17.     ADD (UPDATE ON SCHEMA::[dbo] BY PUBLIC);
  18. GO
  19.  
  20. --启用
  21. USE [Audit]
  22. GO
  23. ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=ON);
  24. ---禁用,注意作业会影响禁用
  25. USE [Audit]
  26. GO
  27. --ALTER DATABASE AUDIT SPECIFICATION AuditSpecification_AllObjectChange WITH(STATE=OFF);
  1. USE [Audit]
  2. GO
  3. CREATE TABLE a(id int not null);
  4. GO
  5. ALTER TABLE a ADD name varchar(10);
  6. GO
  7. INSERT INTO a values(1,'a')
  8. GO
  9. DELETE FROM a WHERE id=1;
  10. GO
  11. DROP TABLE a;

备注：审计记录的日期和使用GETDATE()查询的时间8个小时

  1. ----查询审核日志
  2. select * from  sys.fn_get_audit_file('D:\Audit\AuditServerFile_66D8F97A-B495-4CB4-83EA-564D1ECF9988_0_131604628132230000.sqlaudit',DEFAULT, DEFAULT) AS a
  5. --服务器审核相关视图
  6. SELECT * FROM SYS.server_audits
  7. SELECT * FROM sys.server_audit_specifications
  8. SELECT * FROM sys.server_audit_specification_details
  10. SELECT sa.name as AuditName,
  11. sa.type_desc AS StoreType,
  12. sas.name AS AuditSpecificationsName,
  13. sasd.audit_action_id,
  14. sasd.audit_action_name,
  15. sasd.class_desc as AuditGrade
  16. FROM SYS.server_audits sa INNER JOIN sys.server_audit_specifications sas ON sa.audit_guid=sas.audit_guid
  17. INNER JOIN  sys.server_audit_specification_details sasd ON sas.server_specification_id=sasd.server_specification_id
  18. LEFT JOIN sys.dm_audit_actions daa ON sasd.audit_action_id=daa.action_id AND sasd.class_desc=daa.class_desc
  21. --数据库审核规范视图
  22. SELECT * FROM chenmh.sys.database_audit_specifications
  23. SELECT * FROM chenmh.sys.database_audit_specification_details
  25. SELECT sa.name as AuditName,
  26. sa.type_desc AS StoreType,
  27. sas.name AS AuditSpecificationsName,
  28. sasd.audit_action_id,
  29. sasd.audit_action_name,
  30. sasd.class_desc as AuditGrade
  31. FROM SYS.server_audits sa INNER JOIN chenmh.sys.database_audit_specifications sas ON sa.audit_guid=sas.audit_guid
  32. INNER JOIN  chenmh.sys.database_audit_specification_details sasd ON sas.database_specification_id=sasd.database_specification_id
  33. LEFT JOIN sys.dm_audit_actions daa ON sasd.audit_action_id=daa.action_id AND sasd.class_desc=daa.class_desc
  35. ----
  36. SELECT * FROM  sys.dm_audit_actions  WHERE action_id='DL'

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

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

《欢迎交流讨论》

 

版权声明：本文为chenmh原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/chenmh/p/8296446.html