MSSQL LOGIN Trigger 사용법 및 특징

LOGON Trigger는 SQL Server 인스턴스에 사용자 세션이 설정되었을 때 발생하는 이벤트로 사용자의 로그인을 통제할 때 사용합니다.

 

LOGIN Trigger 특징.

• LOGON Trigger는 LOGON 이벤트에 대한 응답으로 저장 프로시저의 다른 유형 중 하나로 생각하면 됩니다.
• LOGON Trigger는 로그인의 인증 단계가 완료되었지만 사용자 세션이 설정되기 전에 발생합니다. 
• 즉, 사용자 아이디, 패스워드, 권한에 대한 인증 완료 후 세션을 생성할 때 Trigger 실행됩니다.
• 사용자 아이디, 패스워드, 권한에 대한 인증에 실패할 경우는 LOGON Trigger는 실행되지 않습니다.
• LOGON Trigger는 로그인 사용자의 활동을 추적하는 경우 사용합니다.
• 또한 SQL Server로 로그인을 제한할 수도 있으며, 특정 로그인에 대한 세션 수를 제한하거나, 제어할 수 있습니다. 
• Trigger 특징 및 유형에 게시글의 그림 설명을 참조해 보세요.

LOGIN Trigger 사용법.

LOGIN Trigger 생성.

• LOGIN Trigger 세션으로 사용자의 접속제한 방법.
• 소스 코드입니다.

USE master;
GO
CREATE LOGIN AurumGuideLoginTrigger
WITH PASSWORD = N'aurumguide' MUST_CHANGE,CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO AurumGuideLoginTrigger;
GO

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

• LOGIN Trigger 사용자의 접속 이력 설정 방법.

USE master;
DROP TABLE IF EXISTS AurumGuide_Login_Trigger;
-- Create Sample Table 
CREATE TABLE AurumGuide_Login_Trigger(
    AurumId           varchar(100),
    loginTime         datetime 
); 
GO 
-- CREATE TRIGGER;
CREATE TRIGGER tr_AurumGuide_Login_Log ON ALL SERVER
WITH EXECUTE AS N'AurumGuideLoginTrigger'
FOR LOGON AS BEGIN
	IF ORIGINAL_LOGIN() = N'AurumGuideLoginTrigger'
	BEGIN 
		insert into AurumGuide_Login_Trigger
		values('AurumGuideLoginTrigger',GETDATE())
		;
	END;
END;
go
SELECT *
 FROM AurumGuide_Login_Trigger;

 

• AurumGuideLoginTrigger에게 테이블에 insert 할 수 있는 권한이 필요합니다.
• 에러 메시지.
• Logon failed for login 'AurumGuideLoginTrigger' due to trigger execution. Changed database context to 'master'. Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
• 이용자 생성 및 권한은  https://aurumguide.tistory.com/4   참조해 보세요.

소스 코드 설명.

접속 이력(감사 기능) 만들기.

LOGIN Trigger 삭제.

LOGIN Trigger 삭제 방법은? 

DROP TRIGGER tr_AurumGuide_Login_Log ON ALL SERVER;

 

LOGIN Trigger 주의 사항.

• LOGIN Trigger 생성할 때 특정 LOGIN ID에 대해 특정해서 작업을 했으면 합니다.
• 예를 들면, 모든 LOGIN ID에 대한 접속 이력을 테이블에 기록하는데 실수로 인해 테이블을 삭제했다면,
• LOGIN Trigger 에러로 인해서 누구도 서버에 접속할 수 없는 상황이 발생합니다.
• Trigger를 삭제해 줘야 SQL Server에 접속할 수 있습니다.
• 해결 불가능할 때 답글로 알려주세요.