최소한의 권한으로 LOGON TRIGGER 구현

실제 LOGON 트리거를 적용하려고 보니

로그가 남는 테이블에 대한 Write의 권한이 있어야 했다

번거롭게 테이블에 Write 권한을 주는거보다

프로시저를 작성하여 해당 프로시저에게만 실행권한을 주어 접근권한을 최소화 하였다.

 

아래는 작성한 예제입니다.

 

/****** 개체:  DdlTrigger [TRG_LOGON_DBLOGIN]    스크립트날짜: 05/20/2013 18:56:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER TRIGGER [TRG_LOGON_DBLOGIN]

ON ALL SERVER FOR LOGON

AS

 

BEGIN

   

      DECLARE @EVENTLOG XML

      SET @EVENTLOG = EVENTDATA()

 

      EXEC TEST_DB.DBO.sp_insert_logon_dblogin @EVENTLOG

     

     

--      DECLARE @EVENTLOG XML

--      SET @EVENTLOG = EVENTDATA()

--      

--

--      

--       INSERT TEST_DB.DBO.DBLOGIN_LOG_TBL

--

--       SELECT

--       @EVENTLOG.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(60)') AS LOGIN_NAME,

--       @EVENTLOG.value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(20)') AS LOGIN_IP_ADDRESS ,

--       @EVENTLOG.value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(40)') AS LOGIN_AUTH,

--       CONVERT(NVARCHAR(10),GETDATE(),126) AS LOGIN_DATE,

--       CONVERT(NVARCHAR(8),GETDATE(),108) AS LOGIN_TIME

--

 

 

 

     

END

 

 

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---프로시저부분

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

CREATE PROCEDURE DBO.sp_insert_logon_dblogin

(

@EVENTLOG XML

)

AS

 

   

      

 

      

       INSERT TEST_DB.DBO.DBLOGIN_LOG_TBL

 

       SELECT

       @EVENTLOG.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(60)') AS LOGIN_NAME,

       @EVENTLOG.value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(20)') AS LOGIN_IP_ADDRESS ,

       @EVENTLOG.value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(40)') AS LOGIN_AUTH,

       CONVERT(NVARCHAR(10),GETDATE(),126) AS LOGIN_DATE,

       CONVERT(NVARCHAR(8),GETDATE(),108) AS LOGIN_TIME

     

     

  

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---실행권한

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

GRANT EXECUTE ON sp_insert_logon_dblogin TO TEST_ID