SQL SERVER 审计(核)案例

 
---创建审计及审计规范
USE master
CREATE SERVER AUDIT my_first_audit TO FILE(FILEPATH='D:\SQL\TEST\',MAXSIZE=10MB)
GO
---创建和配置可以用于审计的 SQL Server 审计对象
USE Test
CREATE DATABASE AUDIT SPECIFICATION my_aud_spec FOR SERVER AUDIT my_first_audit ADD (SELECT,UPDATE,INSERT,DELETE,EXEC,REFERENCES ON dbo.T_A BY Public) ---本次使用的数据库级别的审核操作(审核级别操作有;服务器级别、数据库级别、审核级别)
go
----查看审计是否存在，并存在的路径
USE master
SELECT * FROM sys.server_file_audits
USE TEST
--审计规范是否存在及其状态
SELECT * FROM sys.database_audit_specifications
-- 启用审计和审计规范（即 是否允许或禁止审核收集此审核规范的记录。）
USE master
ALTER SERVER AUDIT my_first_audit WITH (STATE=ON)
GO
USE Test
ALTER DATABASE AUDIT SPECIFICATION my_aud_spec WITH (STATE=ON)
go
------------执行相关的SQL语句
SELECT * FROM t_a 
DELETE FROM t_a WHERE ID=3
update t_a set name='审计' where id=2

---查看审计收集的记录数据
SELECT * FROM fn_get_audit_file ('D:\SQL\TEST\*',NULL, NULL)
go


一个完整的审核案例

---创建审核对象
USE [master]

GO

CREATE SERVER AUDIT [my_first_audit]
TO FILE 
( FILEPATH = N'D:\test_audit'
 ,MAXSIZE = 0 MB  ---文件大小无限制
 ,MAX_ROLLOVER_FILES = 2147483647 --滚动文件大小无限制
 ,RESERVE_DISK_SPACE = OFF--关闭保留硬盘空间
)
WITH
( QUEUE_DELAY = 1000  ---队列延迟时间 MS为单位
 ,ON_FAILURE = CONTINUE ---审核日志故障时不关闭服务器（continue|shutdown）
)
---审核目标：FILE\SECURITY LOG\APPLICATION LOG
GO
---服务器审核规范
USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [Server_AuditSpecification]
FOR SERVER AUDIT [my_first_audit]
ADD (BACKUP_RESTORE_GROUP), ---审核操作类型
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (TRACE_CHANGE_GROUP)

---具体审核操作类型：见联机丛书
GO

---启用服务器审核规范
ALTER SERVER AUDIT SPECIFICATION [Server_AuditSpecification]
WITH (STATE = ON)
----关闭或禁用服务器审核规范
ALTER SERVER AUDIT SPECIFICATION [Server_AuditSpecification]
WITH (STATE = OFF)

---启用服务器审核对象
ALTER SERVER AUDIT [my_first_audit] WITH (STATE = ON)

---关闭或禁用服务器审核对象
ALTER SERVER AUDIT [my_first_audit] WITH (STATE = OFF)

/*创建数据库审核规范*/
USE [db_test]

GO

CREATE DATABASE AUDIT SPECIFICATION [Database_AuditSpecification]
FOR SERVER AUDIT [my_first_audit]
ADD (SELECT ON OBJECT::[dbo].[a] BY [audit])---制定审核操作类型、对象类（object、schema、database）、对象架构（DBO)）对象名称（a）、 主体名称（登录名或用户名）
---具体审核操作类型：见联机丛书
GO
---启用数据库审核规范
ALTER DATABASE AUDIT SPECIFICATION [Database_AuditSpecification]
WITH (STATE = ON)
---关闭或禁用数据库审核规范
ALTER DATABASE AUDIT SPECIFICATION [Database_AuditSpecification]
WITH (STATE = OFF)