로그온 트리거

아티클
04/11/2024

적용 대상: SQL Server Azure SQL Managed Instance

로그온 트리거는 LOGON 이벤트에 대한 응답으로 저장 프로시저를 시작합니다. 이 이벤트는 SQL Server 인스턴스에 사용자 세션이 설정된 경우 발생합니다. 로그온 트리거는 로그인의 인증 단계가 완료되었지만 사용자 세션이 설정되기 전에 발생합니다. 따라서 오류 메시지 및 PRINT 문의 메시지와 같이 일반적으로 사용자에게 전달되는 모든 트리거 내 발생 메시지는 PRINT 오류 로그로 전달됩니다. 인증에 실패할 경우 로그온 트리거는 실행되지 않습니다.

로그온 트리거를 사용하여 로그인 활동을 추적하거나, SQL Server로 로그인을 제한하거나, 특정 로그인에 대한 세션 수를 제한하는 등 서버 세션을 감사하고 제어할 수 있습니다. 예를 들어 다음 코드에서 로그온 트리거는 해당 로그인에서 만든 사용자 세션이 이미 3개인 경우 로그인 login_test(으)로 시작된 SQL Server에 대한 로그인 시도를 거부합니다.

USE master;
GO

CREATE LOGIN login_test
WITH PASSWORD = N'3KHJ6dhx(0xVYsdf' MUST_CHANGE,
CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger ON ALL SERVER
WITH EXECUTE AS N'login_test'
FOR LOGON AS BEGIN
    IF ORIGINAL_LOGIN() = N'login_test'
    AND (
        SELECT COUNT(*)
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1
            AND original_login_name = N'login_test') > 3
    ROLLBACK;
END;

LOGON 이벤트는 이벤트 알림에서 사용할 수 있는 AUDIT_LOGIN SQL 추적 이벤트에 해당합니다. 트리거와 이벤트 알림의 주요 차이점은 이벤트 알림이 비동기인 반면 트리거는 이벤트와 동기적으로 발생한다는 것입니다. 예를 들어 세션이 설정되지 않도록 하려면 로그온 트리거를 사용해야 합니다. 이런 경우에는 AUDIT_LOGIN 이벤트의 이벤트 알림을 사용할 수 없습니다.

첫 번째 및 마지막 트리거 지정

LOGON 이벤트에 여러 트리거를 정의할 수 있습니다. 이러한 트리거 중 하나는 sp_settriggerorder 시스템 저장 프로시저를 사용하여 이벤트에서 발생할 첫 번째 또는 마지막 트리거를 지정할 수 있습니다. SQL Server는 다시 기본 트리거의 실행 순서를 보장하지 않습니다.

트랜잭션 관리

SQL Server이 로그온 트리거를 실행하기 전, SQL Server에서는 어느 사용자 트랜잭션에도 종속되지 않은 암시적 트랜잭션을 만듭니다. 따라서 첫 번째 로그온 트리거가 시작되었을 때 트랜잭션 수는 1입니다. 모든 로그온 트리거 실행이 완료되면 트랜잭션이 커밋됩니다. 다른 유형의 트리거와 마찬가지로 로그온 트리거가 트랜잭션 수가 0인 실행을 완료하면 SQL Server에서 오류를 반환합니다. ROLLBACK TRANSACTION 문은 중첩된 트랜잭션 내에서 해당 문이 실행되더라도 트랜잭션 수를 0으로 다시 설정합니다. COMMIT TRANSACTION은 트랜잭션 개수를 0으로 감소시킬 수 있습니다. 따라서 로그온 트리거 내에서 COMMIT TRANSACTION 문을 발행하지 않는 것이 좋습니다.

로그온 트리거 내에서 ROLLBACK TRANSACTION 문을 사용하는 경우 다음을 고려하세요.

ROLLBACK TRANSACTION에 대한 모든 데이터 수정 사항은 롤백됩니다. 이러한 수정에는 현재 트리거 및 동일한 이벤트에서 실행된 이전 트리거의 변경 내용이 포함됩니다. 특정 이벤트에 대한 현행 트리거는 실행되지 않습니다.
현재 트리거는 ROLLBACK 문 다음에 표시되는 남아 있는 모든 문을 계속 실행합니다. 이 문 중에서 데이터를 수정한 경우 수정 사항은 롤백되지 않습니다.

LOGON 이벤트에서 트리거를 실행하는 동안 다음 조건이 발생하는 경우 사용자 세션은 설정되지 않습니다.

원래 암시적 트랜잭션이 롤백되거나 실패합니다.
심각도가 20 이상인 오류가 트리거 본문 내에 발생합니다.