sqlserver 2008 R2 审计

晨曦之光 发布于 2012/04/19 16:34
阅读 193
收藏 0

/*

审计web账户对jr数据库的rate表操作
*/


--步骤1:创建审核对象

USE master
GO
CREATE SERVER AUDIT rate
TO FILE (FILEPATH='s:\audit\table\',MAXSIZE=100 MB)
WITH (QUEUE_DELAY = 3000)
go

 


--步骤2:创建数据库审核规范,并将其映射到审核对象。

USE jr
GO
create DATABASE AUDIT SPECIFICATION audit_table_rate
FOR SERVER AUDIT rate
ADD (UPDATE
ON jr.dbo.rate BY test@kk.com)
WITH (STATE=off)
go  --审计的是jr库rate表

 

 

--步骤3:查看状态
SELECT is_state_enabled,*
FROM master.sys.server_file_audits


SELECT is_state_enabled,*
FROM jinri.sys.database_audit_specifications

 

 

--步骤4:开启
use master
ALTER SERVER AUDIT rate WITH (STATE=On)
go


USE jr
ALTER DATABASE AUDIT SPECIFICATION audit_table_rate WITH (STATE=on)
go

 

 

--步骤5:查看审计结果

SELECT session_server_principal_name, statement, event_time,action_id
FROM fn_get_audit_file ('s:\audit\table\rate*',NULL, NULL)
order by event_time desc
go

 

--步骤5:查看审计结果

SELECT * FROM (
SELECT session_server_principal_name, statement, dateadd (hh,8,event_time) as event_time,action_id
FROM fn_get_audit_file ('s:\audit\table\rate*',NULL, NULL)
--order by event_time DESC
) c
WHERE c.statement LIKE '%set lock=1%'
order by event_time DESC
go

 

SELECT session_server_principal_name, statement, event_time,action_id
FROM fn_get_audit_file ('s:\audit\table\rate*',NULL, NULL)
order by event_time DESC

 

 

 


原文链接:http://blog.csdn.net/yangzhawen/article/details/7431880
加载中
返回顶部
顶部