Contents

1 예제1

2 예제2 - 데이터베이스 수준의 트리거

3 예제3 - 서버 수준의 트리거

4 참고자료

--http://databaser.net
--참고: http://www.sqlservercentral.com/articles/SQLServerCentral.com/sqlserver2005logontriggers/2366/
USE AdeventureWorks
GO
 
--서비스 브로커 활성화
ALTER DATABASE AdeventureWorks SET ENABLE_BROKER
GO
 
--DDL트리거에 의해 데이터가 담길 테이블
CREATE TABLE dbo.T_DDLLog
(
	LoggingID int IDENTITY(1,1)
,	EventTime DATETIME
,	EventType NVARCHAR(255)
,	LoginName NVARCHAR(255)
,	HostName NVARCHAR(255)
,	NTUserName NVARCHAR(255)
,	NTDomainName NVARCHAR(255)
,	Success INT
,	FullLog XML
,	Script nvarchar(max)
)
GO

--Try~Catch
CREATE TABLE dbo.T_SP_ErrorLog(
	ErrorNumber int NULL
,	ErrorSeverity int NULL
,	ErrorState int NULL
,	ErrorProcedure nvarchar(126) NULL
,	ErrorLine int NULL
,	ErrorMessage nvarchar(2048) NULL
,	ErrorDatetime datetime NULL
,	Parameters nvarchar(max) NULL
) 
 
CREATE CLUSTERED INDEX CIX_EventTime
ON dbo.T_DDLLog(EventTime DESC);
 
CREATE INDEX NIX_LoggingID
ON dbo.T_DDLLog(LoggingID);
GO
 
--서비스를 위한 설정들(큐, 서비스)
--DROP QUEUE LoggingQueue
CREATE QUEUE LoggingQueue
GO
 
--DROP SERVICE LoggingService
CREATE SERVICE LoggingService
ON QUEUE LoggingQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
 
--이벤트 노티 생성
--DROP EVENT NOTIFICATION Logging_Event_Notification ON SERVER
--ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/wmi9/html/e2916cd7-a3ed-41e6-97b4-2ee060754cbe.htm
CREATE EVENT NOTIFICATION Logging_Event_Notification
ON SERVER
FOR DDL_EVENTS
--,	TRC_CLR
--,	TRC_DEPRECATION
--,	TRC_ERRORS_AND_WARNINGS
--,	TRC_FULL_TEXT
--,	TRC_LOCKS
--,	TRC_OBJECTS
--,	TRC_OLEDB
--,	TRC_PERFORMANCE
--,	TRC_QUERY_NOTIFICATIONS
--,	TRC_SECURITY_AUDIT
--,	AUDIT_LOGIN
--,	AUDIT_LOGOUT
--,	AUDIT_LOGIN_FAILED
--,	AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
--,	TRC_SERVER
--,	TRC_STORED_PROCEDURES
--,	TRC_TSQL
--,	TRC_USER_CONFIGURABLE
TO SERVICE 'LoggingService', 'current database'
GO
 
--서비스 브로커의 큐에서 활성화될 프로시저
CREATE PROCEDURE Up_DDL_Log_Create
AS
SET XACT_ABORT ON 
SET NOCOUNT ON 
 
DECLARE     @message_body XML,
            @message_type_name NVARCHAR(256),
            @dialog UNIQUEIDENTIFIER ;
 
--Endless loop
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;
 
    -- Receive the next available message
	WAITFOR 
	(
		RECEIVE TOP(1)
			@message_type_name=message_type_name
		,	@message_body=message_body
		,	@dialog = conversation_handle
		FROM LoggingQueue
	), TIMEOUT 2000
 
	--Rollback and exit if no messages were found
	IF (@@ROWCOUNT = 0)
	BEGIN
		ROLLBACK TRANSACTION ;
		BREAK ;
	END;
 
   --End conversation of end dialog message
    IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    BEGIN
		PRINT 'End Dialog received for dialog # ' + cast(@dialog AS nvarchar(40)) ;
		END CONVERSATION @dialog ;
    END ELSE IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
	BEGIN
		PRINT 'End Dialog received for dialog # ' + cast(@dialog AS nvarchar(40)) ;
		END CONVERSATION @dialog ;
	END;
	ELSE
    BEGIN
		BEGIN TRY			
			INSERT INTO dbo.T_DDLLog 
			(
				EventTime
			,	EventType
			,	LoginName
			,	HostName
			,	NTUserName
			,	NTDomainName
			,	Success
			,	FullLog	
			,   Script
			)
			SELECT
				CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME)
			,	CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100))
			,	CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER)
			,	@message_body
			,   @message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
		END TRY
 
		BEGIN CATCH
			IF		(XACT_STATE())	= -1 ROLLBACK; 
			ELSE IF (XACT_STATE())	=  1 COMMIT;
 
			INSERT dbo.T_SP_ErrorLog
			(
				ErrorNumber
			,	ErrorSeverity
			,	ErrorState
			,	ErrorProcedure
			,	ErrorLine
			,	ErrorMessage
			,	ErrorDatetime
			,	Parameters
			)
			SELECT
				ERROR_NUMBER()
			,	ERROR_SEVERITY()
			,	ERROR_STATE()
			,	ERROR_PROCEDURE()
			,	ERROR_LINE()
			,	ERROR_MESSAGE()
			,	GETDATE()
			,	CAST(@message_body AS nvarchar(max));	-- nvarchar(max) (파라미터를 넣는다.)
 
			RETURN ERROR_NUMBER();
		END CATCH;
	END;
COMMIT TRANSACTION
END
GO
 
--큐 변경
--DROP QUEUE LoggingQueue
ALTER QUEUE LoggingQueue
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = dbo.Up_DDL_Log_Create ,
MAX_QUEUE_READERS = 2, EXECUTE AS SELF ), STATUS = ON
GO
 
--ALTER QUEUE dbo.LoggingQueue WITH STATUS = ON 
--ALTER QUEUE dbo.LoggingQueue WITH STATUS = OFF
 
/*
--Test
drop table test
CREATE TABLE test(id int)
GO
 
SELECT 
	EventTime
,	FullLog.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(255)') LoginName
,	FullLog.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(255)') EventType
,	FullLog.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') CommandText
FROM dbo.T_DDLLog
WHERE LoggingID = 2
*/
 
CREATE PROC dbo.Up_DDL_Log_Read
	@BeginDT datetime
,	@EndDT datetime
,	@LoggingID int = 0
,	@EventType nvarchar(255) = ''
,	@LoginName nvarchar(255) = ''
,	@HostName nvarchar(255) = ''
,	@NTUserName nvarchar(255) = ''
,	@NTDomainName nvarchar(1000) = ''
,	@SQLViewYN bit = 0
AS
SELECT 
	LoggingID
,	EventTime
,	EventType
,	LoginName
,	HostName
,	NTUserName
,	NTDomainName
,	Success
,	FullLog
--,	CASE WHEN @SQLViewYN = 0 THEN '' ELSE FullLog.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') END SQLSource
FROM dbo.T_DDLLog
WHERE 1=1
AND EventTime BETWEEN @BeginDT AND @EndDT
GO
 
/*
 
SELECT * FROM dbo.T_DDLLog
where EventType like '%LOGIN%'
 
select top 100 * from LoggingQueue
 
 
truncate table T_DDLLog
SELECT top 100 * FROM LoggingQueue
*/

use TestDB
go

-- 트리거 관련 데이터를 저장할 테이블
CREATE TABLE DDL_Log (
	ID      int IDENTITY(1, 1)
		PRIMARY KEY
,	TrData	xml
)
GO

-- 테스트용으로 DROP, ALTER를 수행 할 테이블
CREATE TABLE TEST01 ( id int )
GO

-- DDL 트리거
CREATE TRIGGER tr_Test1
	ON DATABASE 
	FOR DROP_TABLE, ALTER_TABLE 
AS
	DECLARE @trData XML
	   -- EVENTDATA() 함수 호출 결과를 XML로 저장하거나, XQuery로 검색
	SET @trData = EVENTDATA()

	   -- DDL 트리거는 ROLLBACK 처리가 가능하다
	ROLLBACK

	   -- Log 기록
	SET NOCOUNT ON
	INSERT INTO DDL_Log (TrData) VALUES (@trData)
GO

/*
   DDL 트리거 호출 테스트
*/

-- 1) DROP TABLE 이벤트는 직접 수행
DROP TABLE TEST01
SELECT * FROM DDL_Log
GO

-- 2) ALTER TABLE 이벤트는 TRY ... CATCH 내에서 수행해 봄
BEGIN TRY
	ALTER TABLE TEST01 ADD b int
END TRY
BEGIN CATCH
	SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
SELECT * FROM DDL_Log

-- SQL 로그인 생성을 하지 못하도록 하는 트리거 예.

-- CREATE LOGIN 에 대한 DDL 트리거
CREATE TRIGGER tr_Test2
ON ALL SERVER
FOR CREATE_LOGIN
AS
SET NOCOUNT ON

   -- XQuery 의 value() 메서드를 사용, 특정 요소 값을 검색하고 비교
IF (EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(100)') 
			= 'SQL Login')
BEGIN
ROLLBACK
PRINT 'SQL 표준 사용자는 생성할 수 없습니다!'
END
ELSE
BEGIN
PRINT 'Windows 사용자는 생성 가능합니다!'
END
GO

-- SQL 표준 사용자 생성 테스트 => 실패
CREATE LOGIN SqlUser WITH PASSWORD = 'p@ssw0rd'
GO

-- Windows 표준 사용자 생성 테스트
CREATE LOGIN [SQL2005PC\Guest] FROM WINDOWS
GO
DROP LOGIN [SQL2005PC\Guest]
GO

• IP로 클라이언트 제한(http://www.sqlservercentral.com/articles/Security/66151/)
  
• SQL 2008 & 2005 DDL Auditing - Full Server Self Maintaining Solution(http://www.sqlservercentral.com/articles/DDL+Event/70657/)