LOGON 트리거

LOGON 트리거의 자세한 내용은 MSDN Logon 트리거를 참조 바랍니다.

SQL Server에는 크게 3가지 트리거가 있습니다.
DML 트리거
DDL 트리거
LOGON 트리거

DML 트리거는 트리거에 정의된 테이블이나 뷰에 영향을 주는 DML(데이터 조작 언어) 이벤트에 대한 응답으로 실행됩니다.
DDL 트리거는 DDL(데이터 정의 언어) 이벤트에 대한 응답으로 실행됩니다.
LOGON 트리거는 LOGON 이벤트에 대한 응답으로 실행됩니다.

LOGON 트리거를 사용하면 로그인 작업 추적, 로그인 제한, 특정 로그인에 대한 세션 수 제한 등의 작업을 수행하는 감사 정책을 만들 수 있습니다.

DML 트리거가 inserted, deleted라는 두 개의 특수 테이블을 사용하여 정의된다면
LOGON 트리거는 EVENTDATA()라는 함수가 반환하는 XML형식의 데이터를 사용하여 정의됩니다.
XML 데이터의 내용은 다음과 같습니다.

    LOGON
    2014-04-09T12:56:56.750
    53
    MyComputer\SQL2008_INST1
    DBUser
    SQL Login
    30BIfakj40mclRLYeKZv9A==
    127.0.0.1
    0

이 데이터에 value()메서드를 사용하여 원하는 데이터를 뽑을 수 있습니다.

다음은 특정 로그인에 대한 로그인을 거부하는 LOGON 트리거의 소스입니다.
CREATE TRIGGER TRG_LOGON ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON

AS

BEGIN
        SET NOCOUNT ON

        DECLARE @DATA xml = EVENTDATA()

        DECLARE @ClientHost VARCHAR(15)
        DECLARE @LoginName VARCHAR(128)
        DECLARE @LoginType VARCHAR(128)
        DECLARE @PostTime DATETIME
        DECLARE @SPID SMALLINT

        DECLARE @HostName NVARCHAR(128)
        DECLARE @ProgramName NVARCHAR(128)

        SET @ClientHost = @DATA.value('(/EVENT_INSTANCE/ClientHost)[1]', 'VARCHAR(15)')
        SET @LoginName = @DATA.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)')
        SET @LoginType = @DATA.value('(/EVENT_INSTANCE/LoginType)[1]', 'VARCHAR(128)')
        SET @PostTime = @DATA.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
        SET @SPID = @DATA.value('(/EVENT_INSTANCE/SPID)[1]', 'SMALLINT')

        SELECT @HostName = CONVERT(NVARCHAR(128), host_name)
                , @ProgramName = CONVERT(NVARCHAR(128), program_name)
        FROM sys.dm_exec_sessions
        WHERE session_id = @SPID

        IF @LoginName = 'DBUser' BEGIN
                ROLLBACK
        END
END
이 트리거를 만들어 놓고 DBUser라는 로그인 이름으로 로그인 하면 다음과 같은 팝업이 뜨면서 로그인이 거부됩니다.

트리거의 내용을 살펴보면
EVENTDATA()함수를 통해 받은 XML데이타에 value 메서드로 접근하여 해당 세션에 대한 정보를 확인 하는것을 볼 수 있습니다.
이렇게 확인한 데이터를 특정 테이블에 쌓을 수도 있습니다.
그러면 로그인에 대한 log데이터가 쌓이게 되겠죠.
하지만 권한이 없어서 log테이블에 쓰기를 못하는 경우를 대비하여 WITH EXECUTE AS 'sa' 를 추가하였습니다.
sa말고 다른 LOGIN을 사용해도 되겠습니다. (sa는 disable이 진리!!)

한가지 주의할 점은!!!
LOGON 트리거를 잘 못 짜면 어떻게 될까요?
만약 LOGON 트리거를 만들어 놨는데 LOGON 트리거에 문제가 생겨서 에러가 발생한다면 어떻게 될까요?
로그인이 안되겠죠.
누구나 로그인시 에러 대화상자를 만나게 될 것입니다. sa든, sa권한을 가진 누구든...
저는 결코 저런 경우를 당해본 적이 없습니다.!!!
트리거를 삭제하려면 로그인을 해야 하는데 로그인이 안된다라... 하...

그래서 만들기 전에 철저한 테스트가 필요합니다.
테스트 머신에서 확인 후에 적용하시기 바랍니다.
한가지 팁을 드리자면...
트리거를 만들었던 그 창을 닫지 마세요. 그 창은 이미 로그인된 창입니다. ^^


PS.
서버에 정의된 트리거의 목록을 보려면 sys.server_triggers 카다로그 뷰를 사용합니다.
서버에 정의된 트리거가 실행되는 이벤트를 확인하려면 sys.server_trigger_events 카다로그 뷰를 사용합니다.
서버에 정의된 트리거의 소스를 보려면 sys.server_sql_modules 카다로그 뷰를 사용합니다.