SQL Server에서 Trigger 활성 / 비활성 감시
- Version : SQL Server 2005, 2008, 2008R2, 2012
SQL Server는 기본적으로 트리거의 활성/비활성화를 추적하지 않는다. 트리거를 생성하면 기본적으로는 활성화 되어 있다. 하지만 권한을 가진 다른 사용자가 트리거를 비활성 시킬 수 있다.
트리거의 활성/비활성을 감시하는데에는 많은 비용이 소모 될 수 있다. 실습을 통하여 트리거의 활성/비활성을 캡처하자.
다음과 같이 서버 감사를 생성하고 실행 한다.
CREATE SERVER AUDIT ServerAudit TO FILE (FILEPATH = 'D:\', MAXSIZE = 1GB) WITH (ON_FAILURE = CONTINUE); GO
ALTER SERVER AUDIT ServerAudit WITH (STATE = ON); |
서버 감사를 실행 하면 다음과 같이 지정된 경로에 감사 파일이 생성되는 것을 확인 할 수 있다.
데이터베이스 수준에서 감사 사양을 생성해야 한다.
캡처의 범위는 SCHEMA_OBJECT_CHANGE_GROUP이다.
CREATE DATABASE AUDIT SPECIFICATION schema_change FOR SERVER AUDIT ServerAudit ADD (SCHEMA_OBJECT_CHANGE_GROUP) WITH (STATE = ON); GO |
간단한 트리거가 있는 테이블을 생성한다.
CREATE TABLE SW_Trigger( ID INT); GO
CREATE TRIGGER T_SW_Trigger ON SW_Trigger FOR INSERT AS BEGIN SELECT 1 END GO |
트리거 생성이 완료 되었으면 간단한 테이블을 만들어 본다. 그리고 다음의 코드를 실행하면 다음과 같이 ServerAudit 파일에서 트리거된 내용을 확인 할 수 있다.
SELECT EVENT_TIME, SUCCEEDED, SERVER_PRINCIPAL_NAME, [OBJECT_NAME], [STATEMENT] FROM SYS.FN_GET_AUDIT_FILE ('D:\SERVERAUDIT*' , NULL, NULL) WHERE DATABASE_NAME = 'SW_TEST' |
트리거를 활성 비활성화 해보자.
DISABLE TRIGGER T_SW_Trigger ON SW_Trigger GO
ENABLE TRIGGER T_SW_Trigger ON SW_Trigger GO |
트리거의 활성/비활성 내용이 캡처 되고 있는지 확인 할 수 있다.
SELECT EVENT_TIME, SUCCEEDED, SERVER_PRINCIPAL_NAME, [OBJECT_NAME], [STATEMENT] FROM SYS.FN_GET_AUDIT_FILE ('D:\SERVERAUDIT*' , NULL, NULL) WHERE DATABASE_NAME = 'SW_TEST' |
비활성의 내용만 확인하려면 다음과 같이 where에 스크립을 추가 한다.
AND [statement] LIKE '%Disable%TRIGGER%' |
특정 조건에 따라 필터를 적절히 활용 할 수 있도록 하자.
SQL Server에서 특정 동작을 캡처하는 방법에는 여러가지가 있다. 버전에 따라 약간의 제약사항은 있지만 데이터베이스 감사는 매우 중요하다. 많은 오버헤드를 발생하지 않는 적정선에서 잘 활용 할 수 있도록 하자.
[참고자료]
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
