/*

审计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 ) 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