DML 변경 내용 추적하기

기능

데이타베이스 감사를 이용한 특정 데이타베이스 DML 구문 추적 로그 생성

가) 감사 기능 설정

USE [master]

GO

 

-- 감사 비활성화

ALTER SERVER AUDIT [AWDMLAudit] WITH (STATE = OFF)

GO

 

-- 감사 삭제

DROP SERVER AUDIT [AWDMLAudit]

GO

 

-- 데이타베이스 감사 사양에서 사용할 서버 감사 생성

CREATE SERVER AUDIT [AWDMLAudit] 

TO FILE  

(  FILEPATH = 'D:\TEST' 

   ,MAXSIZE = 200 MB 

   ,MAX_ROLLOVER_FILES = 0 

   ,RESERVE_DISK_SPACE = OFF 

) 

WITH 

(  QUEUE_DELAY = 2000 

   ,ON_FAILURE = CONTINUE 

)  

GO

 

-- 감사 활성화

ALTER SERVER AUDIT [AWDMLAudit] WITH (STATE = ON)

GO

 

 

-----------------------------------------------------------------------

-- DML 활동을 감사할 데이타베이스 감사 사양 생성

-----------------------------------------------------------------------

CREATE DATABASE AuditTestDB

GO

 

use AuditTestDB

go

 

drop table dbo.tbltest

go

 

create table dbo.tbltest

(

             aa int

             , bb int

)

go

 

-- 기존에 존재하면 삭제

ALTER DATABASE AUDIT SPECIFICATION audittest1

WITH (STATE = OFF)

GO

 

DROP DATABASE AUDIT SPECIFICATION audittest1

GO

            

-- audit all updates in the Sales schema

CREATE DATABASE AUDIT SPECIFICATION audittest1

FOR SERVER AUDIT AWDMLAudit

ADD (INSERT , UPDATE, DELETE 

     ON SCHEMA::dbo

     BY PUBLIC)    

WITH (STATE = ON)

GO

나) 테스트

insert into dbo.tbltest values(1,1)

go

 

update dbo.tbltest

set

bb=2

where aa =1

go

 

 

-- 조회 쿼리

USE master

GO

-- get the audit file

DECLARE @filepattern VARCHAR(300)

DECLARE @folder VARCHAR(255)

DECLARE @auditguid VARCHAR(36)

SELECT @auditguid = audit_guid,@folder = log_file_path

FROM sys.server_file_audits WHERE name = 'AWDMLAudit'

 

SELECT @filepattern = @folder + '*_' + @auditguid + '*'

 

-- view the results

SELECT

             a.name AS Action

             ,event_time

             ,c.class_type_desc AS ObjectType,

             f.server_principal_name

             ,f.schema_name,f.OBJECT_NAME,f.statement

FROM fn_get_audit_file(@filepattern,NULL,NULL) AS f

JOIN sys.dm_audit_class_type_map c ON f.class_type = c.class_type

JOIN sys.dm_audit_actions a ON f.action_id = a.action_id

AND c.securable_class_desc = a.class_desc

WHERE f.action_id <> 'AUSC'

ORDER BY event_time DESC,sequence_number   

 

 
위에 보시는 것과 같이 어느 테이블에 언제, 누가 수정한 부분이 보입니다^^.

수고하세요^^