SQL 2005에 있는 DDL 트리거를 이용하여 데이터베이스에 접근하는 특정 IP를 막거나, 또는 특정 IP만을 허용하도록 하는 기능을 구현하는 예입니다.
IP 차단이나 허용 등은 OS 수준에서 제어하는 것이 바람직하지만, 경우에 따라서는 데이터베이스 수준에서 이를 제어해야 할 경우도 있습니다.
다음과 같은 경우에 유용합니다.
|
? 웹 서버의 접속만 허용하고 다른 서버 또는 사용자 등의 접속을 차단하고자 할 때 ? 부하를 많이 발생시키는 사용자의 접근을 차단하고자 할 때 ? 지정된 PC에서만 접근을 허용하고자 할 때 |
아래 설명할 방식은 특정 IP에 대해서만 접근을 허용하는 방식입니다. 이를 이용해서 특정 IP의 접근을 막는 것도 간단히 활용하실 수 있을 것입니다.
|
--관리용 데이터베이스 생성 CREATE DATABASE DBAWork GO
USE DBAWork GO
--접근 허용 목록 테이블 생성 CREATE TABLE dbo.ValidIP ( IP NVARCHAR(15), CONSTRAINT PK_ValidIP PRIMARY KEY CLUSTERED (IP) ); GO
INSERT INTO dbo.ValidIP (IP) VALUES ('100.20.2.22'); GO
|
|
USE MASTER GO --DDL Trigger 생성 CREATE TRIGGER tr_logon_CheckIP ON ALL SERVER FOR LOGON AS BEGIN IF IS_SRVROLEMEMBER('sysadmin') = 1 BEGIN DECLARE @IP NVARCHAR(15); SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)')); IF NOT EXISTS(SELECT IP FROM DBAWork.dbo.ValidIP WHERE IP = @IP) ROLLBACK; END; END; GO |
만약 허용되지 않은 곳에서 해당 DB 서버를 연결하면 다음과 같이 연결이 제한됩니다.
DDL 트리거를 비활성화 하거나 제거하려면 다음 명령을 이용하면 됩니다.
|
USE MASTER GO
--Disable DISABLE TRIGGER tr_logon_CheckIP ON ALL SERVER GO
--Drop Trigger DROP TRIGGER tr_logon_CheckIP ON ALL SERVER |
