SqlServer 审核(脚本示例)
此文章主要是脚本示例,更多说明看官方文档:审核(数据库引擎)
-- 必须在 master 数据库中创建审核 USE master; GO -- 创建服务器审核对象 -- https://msdn.microsoft.com/zh-cn/library/cc280448(v=sql.100).aspx CREATE SERVER AUDIT [Audit_ToFile] TO FILE ( --目标类型:FILE(文件)/APPLICATION_LOG(应用程序日志)/SECURITY(安全日志) FILEPATH = N'E:\' --审核日志的路径 , MAXSIZE = 100MB --审核文件最大大小(MB、GB、TB 或 UNLIMITED) , MAX_ROLLOVER_FILES = 5 --最大文件数(或者UNLIMITED) , RESERVE_DISK_SPACE = ON --预先分配MAXSIZE大小(MAXSIZE<>UNLIMITED 时适用。默认:OFF) ) WITH ( QUEUE_DELAY = 1000 --强制审核前时间,默认1000(毫秒),值 0 指示同步传递 , ON_FAILURE = CONTINUE --无法写入目标文件时:CONTINUE(默认) | SHUTDOWN(需要权限) --, AUDIT_GUID = <GUID> --数据库镜像使用 ) GO -- 更改服务器审核对象(与创建格式一样) -- https://msdn.microsoft.com/zh-cn/library/cc280563%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396 ALTER SERVER AUDIT [Audit_ToFile] MODIFY NAME = [Audit_ToFile] --更改审核名称 GO ALTER SERVER AUDIT [Audit_ToFile] WITH (STATE = ON ) --启用审核收集记录 GO -- 删除审核(必须禁用审核收集记录) -- https://msdn.microsoft.com/zh-cn/library/cc280899(v=sql.100).aspx ALTER SERVER AUDIT [Audit_ToFile] WITH (STATE = OFF ) GO IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'Audit_ToFile') DROP SERVER AUDIT [Audit_ToFile] GO
-- 创建服务器审核规范对象 -- https://msdn.microsoft.com/zh-cn/library/cc280767%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396 CREATE SERVER AUDIT SPECIFICATION [Audit_Specification_Server] FOR SERVER AUDIT [Audit_ToFile] --应用此规范的审核名称 ADD ( FAILED_LOGIN_GROUP ) --服务器级别可审核操作组的名称 (如 登录失败审核) WITH ( STATE = ON ) --允许或禁止审核收集此审核规范的记录 GO -- SQL Server 审核操作组和操作 -- https://msdn.microsoft.com/zh-cn/library/cc280663%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396 -- 更改服务器审核规范对象(必须将审核规范的状态设置为 OFF 选项) -- https://msdn.microsoft.com/zh-cn/library/cc280682(v=sql.100).aspx ALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server] WITH ( STATE = OFF ) GO ALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server] FOR SERVER AUDIT [Audit_ToFile] ADD ( SUCCESSFUL_LOGIN_GROUP ) , ADD ( LOGOUT_GROUP ), DROP ( LOGIN_CHANGE_PASSWORD_GROUP ) WITH ( STATE = ON ) GO /* 必须将审核规范的状态设置为 OFF 选项,以便更改审核规范,否则出现错误: 消息 33229,级别 16,状态 1,第 1 行 禁用审核规范时,对审核规范的更改必须已完成。 */ -- 删除服务器审核规范对象(必须将审核规范的状态设置为 OFF 选项) -- https://msdn.microsoft.com/zh-cn/library/cc280603%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396 ALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server] WITH ( STATE = OFF ) GO IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'Audit_Specification_Server') DROP SERVER AUDIT SPECIFICATION [Audit_Specification_Server] GO
-- 创建数据库审核规范对象 (只对当前数据库创建审核) -- https://msdn.microsoft.com/zh-cn/library/cc280404%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396 USE AdventureWorks2008R2; GO CREATE DATABASE AUDIT SPECIFICATION [Audit_Specification_Database] FOR SERVER AUDIT [Audit_ToFile] ADD (SCHEMA_OBJECT_CHANGE_GROUP) WITH ( STATE = ON ) GO CREATE DATABASE AUDIT SPECIFICATION [Audit_Specification_Database] FOR SERVER AUDIT [Audit_ToFile] ADD ( SELECT ON dbo.ErrorLog BY [dbo]) WITH ( STATE = ON ) GO -- 更改数据库审核规范对象 (必须禁止审核收集此审核规范的记录) -- https://msdn.microsoft.com/zh-cn/library/cc280645%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396 ALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database] WITH ( STATE = OFF ) GO ALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database] FOR SERVER AUDIT [Audit_ToFile] ADD ( UPDATE,INSERT,DELETE ON dbo.ErrorLog BY [dbo]), DROP ( SELECT ON dbo.ErrorLog BY [dbo]) WITH ( STATE = ON ) GO -- 删除数据库审核规范对象 (必须禁止审核收集此审核规范的记录) -- https://msdn.microsoft.com/zh-cn/library/cc280479%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396 ALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database] WITH ( STATE = OFF ) GO IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N'Audit_Specification_Database') DROP DATABASE AUDIT SPECIFICATION [Audit_Specification_Database] GO
-- 当删除数据库AdventureWorks2008R2的表ErrorLog时 delete from dbo.ErrorLog -- 审核日志记录如下
-- 相关视图查询 select * from sys.server_audits --审核对象 select * from sys.dm_server_audit_status --审核的当前状态 select * from sys.server_audit_specifications --服务器审核规范对象 select * from sys.server_audit_specification_details --服务器审核规范对象的审核操作 select * from sys.database_audit_specifications --数据库审核规范对象 select * from sys.database_audit_specification_details --数据库审核规范对象的审核操作 select * from sys.dm_audit_actions --所有可用的操作组 select * from sys.dm_audit_class_type_map --审核日志中的字段映射 select * from sys.server_file_audits --审核文件信息 select * from sys.fn_get_audit_file('E:\Audit_ToFile_*.sqlaudit',default,default);
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。