SQL Server 审计 第二篇: 创建审计
SQL Server 审计系列:
启用审计的目的是为了监控SQL Server执行的操作,例如,记录什么人在什么时候查询数据、修改数据、登陆SQL Server实例等,由于审计记录的数据有可能很丰富,因此,启用审计可能产生大量的日志数据,占用大量的硬盘的间。使用一句话来概括审计,那就是:记录谁在什么时候做了什么事。
审计对象(Audit)定义:配置数据存在何处,而审计规范(Audit Specification)定义:记录什么事,一旦特定的事件触发,SQL Server引擎就使用审计记录事件发生的现场信息。
创建和使用审计的一般步骤是:
- step1:创建服务器级别的审计对象,并启用审计对象;审计对象用于指定存储审计数据的路径。
- step2:创建审计规范,并映射到审计对象审核,启用审计规范,审计对象开始追踪和记录数据;
- step3:查看审计数据,可以通过使用SSMS的”Log Files Viewer“或函数sys.fn_get_audit_file 查看记录的日志数据。
一,创建审计对象
审计对象的作用是指定审计数据保存的路径,以及写入数据的延迟和数据文件的大小,审计对象主要是存储审计规范的数据。可以使用SSMS通过UI方式来创建服务器级别的审计对象,展开"Security",右击Audits,通过“New Audit”,打开“Create Audit”窗体开始创建审计对象,为审计对象设置属性:
- Queue delay:是指数据写入到审计文件的延迟,默认是1s。
- On Audit Log Failure:当Target写入审计日志出错时,SQL Server实例是继续、关机还是失败。
- Audit Destination:审计输出的数据保存到“Audit destination”中,本文选择File,把数据存储到审计文件中,其他Audit Destination类型是:Security Log和Application Log。
根据硬盘空间的限制,设置审计对象的属性 Audit File的数量,每个文件的最大size(Maximum File size)、以及Reserve disk space,控制审计文件的大小,管理硬盘空间的使用,避免硬盘爆掉。
每当审计重新启动时(在重新启动SQL Server实例时,或者重启审计时),或者达到一个文件的最大size,审计都会新建一个Audit File。
- 如果文件数量超过MAX ROLLOVER FILES ,那么删除最早的文件。如果把MAX_ROLLOVER_FILES的设置为0时,每当审计重启时,都会创建一个新文件。
- 如果设置为Max files时,审计不会rollover到下一个文件,达到Max files的限制后,新的审计数据将无法记录,并会导致失败。
创建的审计对象默认是禁用(Disable)的,在使用审计对象之前,必须启用,选中新建的审计对象,右击,选中“Enable Audit”即可。
用户也可以使用TSQL脚本创建审计对象:
use [master] go create server audit [AuditMonitorQuery] to file ( filepath = N'G:\AuditFiles\MonitorQuery\' ,maxsize = 1 GB ,max_rollover_files = 128 ,reserve_disk_space = off ) with ( queue_delay = 1000 --1000ms ,on_failure = continue ) go alter server audit [AuditMonitorQuery] with (state = on) go
三,创建服务器级别的审计规范
展开服务器级别的Security,选中“Server Audit Specifications”,右击弹出快捷菜单,选择“New Server Audit Specifications”,打开“Create Server Audit Specifications”的窗体,通过UI创建审计规范:
审计规范指定审计对象记录的事件类型,在审计规范中指定的事件类型,SQL Server 一旦检测到事件发生,就会把跟该事件相关的信息写入到审计对象指定的文件中,保存起来,以便于后续的检查(review)。
使用向导来创建服务器级别的审计规范,从列表中选择审计对象,从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。
四,创建数据库级别的审计规范
在数据库的Security中右击“Database Audit Specifications”,数据库级别的审计操作组,大部分和服务器级别的审计操作组很相似,除了数据库级别的审计动作(Database-Level Audit Actions),在数据库对象上发生以下操作(Action)时,记录事件的信息:
- SELECT
- UPDATE
- INSERT
- DELETE
- EXECUTE
- REFERENCES
- RECEIVE
1,使用Wizard创建数据库审计规范
数据库级别的审计操作追踪和记录的是数据库对象上发生的事件,因此必须配置Object Class、Object Schema,Object Name 和 Principal Name字段:
注意:如果要记录所有user的行为,那么Principal Name请选择 Public。
2,使用TSQL命令创建数据库级别的审计规范
在审计规范中,如何设置Principal为public,表示对所有database principals进行审计。
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20191118-091731] FOR SERVER AUDIT [Server_Audit_name] ADD (DELETE ON Database::PartnerBI BY public) WITH (STATE = ON) ;
五,查看审计数据
用户可以通过两种方式来查看审计数据,第一种是使用UI来查看,第二种是使用系统视图来查看。
1,使用UI查看审计数据
点击审计对象,右击弹出快捷菜单,点击”View Audit Logs“查看审计对象记录的数据:
2,通过TSQL 函数查看审计数据
查看审计数据通过sys.fn_get_audit_file()来查看:
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 f.event_time ,f.sequence_number ,f.action_id ,a.name as action_name ,f.succeeded ,f.session_server_principal_name ,f.server_principal_name ,f.database_name ,f.object_id ,f.schema_name ,f.object_name ,f.class_type ,m.class_type_desc ,f.statement ,f.application_name from sys.fn_get_audit_file('G:\AuditFiles\Monitor\*',default,default) f inner join sys.dm_audit_actions a on f.action_id=a.action_id inner join sys.dm_audit_class_type_map m on f.class_type=m.class_type
六,修改数据库级别的审计规范
使用TSQL命令修改数据库级别的审计规范:
Step1:禁用Server Audit
ALTER SERVER AUDIT [AuditMonitorQuery] WITH (STATE = OFF) GO
Step2:修改数据库级别的审计规范
alter DATABASE AUDIT SPECIFICATION [db_Audit_MonitorQuery] WITH (STATE = OFF) go alter DATABASE AUDIT SPECIFICATION [db_Audit_MonitorQuery] FOR SERVER AUDIT [AuditMonitorQuery] ADD (DELETE ON DATABASE::[PartnerBI] BY [public]), ADD (EXECUTE ON DATABASE::[PartnerBI] BY [public]), ADD (INSERT ON DATABASE::[PartnerBI] BY [public]), ADD (RECEIVE ON DATABASE::[PartnerBI] BY [public]), ADD (REFERENCES ON DATABASE::[PartnerBI] BY [public]), ADD (SELECT ON DATABASE::[PartnerBI] BY [public]), ADD (UPDATE ON DATABASE::[PartnerBI] BY [public]) WITH (STATE = ON) GO
Step3:修改并启用Server Audit
alter SERVER AUDIT [AuditMonitorQuery] TO FILE ( FILEPATH = N'G:\AuditFiles\MonitorQuery\' ,MAXSIZE = 1GB ,MAX_ROLLOVER_FILES = 128 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ) ALTER SERVER AUDIT [AuditMonitorQuery] WITH (STATE = ON) GO
参考文档:
SQL Server Audit (Database Engine)