반응형
반응형

 

한대성

Microsoft Premier Field Engineer

 

외부 사이트에 유용한 글이 올라와 있어서 간략히 정리합니다.

원본 : http://www.sqlservercentral.com/articles/Security/66151/

 

 

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

GO

 

반응형
저작자표시

'연구개발 > DBA' 카테고리의 다른 글

Web Application Stress Tool  (0) 2009.07.28
Statment 타입별 실행 빈도 및 리소스 사용량 확인  (0) 2009.07.27
SERVICE MASTER KEY (SMK)  (0) 2009.07.26
Cannot find folder \Maintenance plans 에러시 대처법  (0) 2009.07.26
SSMS에서 특정 사용자의 DB만 보이게 하는 방법  (0) 2009.07.26
반응형

한대성

SQL Server MVP

에이디컨설팅 | SQLLeader.com

 

 

Service Pack 2 이후에 몇 가지 새로운 기능들 중에서 유용한 기능을 하나 소개 드립니다.

 

SQL Server 2005에 추가된 DDL 트리거 중, 로그온과 관련된 이벤트가 추가되었습니다.

이를 이용하여, SQL Server에 로그인한 정보를 테이블이나 다른 형태에 별도로 남길 수 있습니다.

 

Logon 이벤트 정보를 기록할 테이블 생성

USE master

GO

 

IF EXISTS (SELECT * FROM sys.tables WHERE type = 'U' AND Name = N' Login_Info_Table ')

        DROP TABLE Login_Info_Table

GO

 

CREATE TABLE Login_Info_Table (

        PostTime datetime,            -- 로그온 한 시간

        SID varbinary(85),            -- 로그인에 대한 보안 아이디(Security ID)

        ClientHost nvarchar(100),     -- 연결을 한 Client의 Host ID.

                                      -- 서버에서 연결할 경우 ‘’으로 저장됨

        LoginName nvarchar(100),      -- 로그온 명

        LoginType nvarchar(30),       -- 로그온 유형

        IsPooled tinyint              -- 연결 풀링 여부

)

GO

 

 

LOGON 이벤트에 대한 DDL 트리거 생성

IF EXISTS (SELECT * FROM sys.server_triggers WHERE NAME = 'Trg_Logon_Info')

        DROP TRIGGER Trg_Logon_Info on ALL SERVER

GO

 

CREATE TRIGGER Trg_Logon_Info

ON ALL SERVER FOR LOGON

AS

BEGIN

        DECLARE @data XML

        SET @data = EVENTDATA()

 

        INSERT INTO Login_Info_Table(PostTime, SID, ClientHost,

                        LoginName, LoginType, IsPooled)

        SELECT GETDATE(),

               @data.value('(/EVENT_INSTANCE/SID)[1]', 'varbinary(85)'),

               @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(100)'),

               @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),

               @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(30)'),

               @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'tinyint')

END

GO

 

 

트리거 생성 확인

SELECT * FROM sys.server_triggers

GO

 


트리거 삭제

DROP TRIGGER Trg_Logon_Info on ALL SERVER

GO

 

 

 

 

결과 확인

 

 

이를 이용하면 Profiler나 PSSDiag, 또는 다른 Audit 툴을 이용하지 않고도 SQL Server에 로그인 한 정보를 기록을 남길 수 있습니다.

단, SQL Server 2005 Service Pack 2 이상이 설치되어야 LOGON에 대한 DDL 트리거 생성이 가능합니다.

 

 

 

본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 글의 링크를 밝혀주셔야 합니다. 

 

반응형
저작자표시

'연구개발 > DBA' 카테고리의 다른 글

Cannot find folder \Maintenance plans 에러시 대처법  (0) 2009.07.26
SSMS에서 특정 사용자의 DB만 보이게 하는 방법  (0) 2009.07.26
데이터베이스 유지 관리 계획 백업 및 복원  (0) 2009.07.25
SQL 서버를 활용한 성능 카운터 수집  (0) 2009.07.25
파일 그룹별 개체 리스트 및 Size 관리  (0) 2009.07.25
반응형

IP 주소에서 해당되는 아이피 숫자를 가지고 올 때에는
charindex, right, trim 등등 막 써가며 가져왔었는데
그럴필요가 없었다..-_-;
무식하면 용감하다고 ㅋㅋ

아이피주소에서 어느 부분이든 구하려면
PARSENAME을 쓰면 간단하다.

- 아이피주소중 마지막 아이피 찾을 때의 이전 방법
SELECT SUBSTRING(right(rtirm(ip주소),3),charindex('.',right(rtrim(ip주소),3))+1,3)

- PARSENAME을 쓰는 방법
SELECT PARSENAME(ip주소, 구하고자하는 번호)


하지만 parsename은 엄연히 써야할 곳이 있다는 것을 명심..

아래 부분 참고

개체 이름에서 지정된 부분을 반환합니다. 검색 가능한 개체의 부분은 개체 이름, 소유자 이름, 데이터베이스 이름 및 서버 이름입니다.

ms188006.note(ko-kr,SQL.100).gif참고:
PARSENAME 함수는 지정된 이름의 개체가 있는지 여부를 나타내지 않으며 지정된 개체 이름에서 지정된 부분만 반환합니다.

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

PARSENAME ( 'object_name' , object_piece )
' object_name '

지정된 개체 부분을 검색할 개체의 이름입니다. object_name은 sysname입니다. 이 매개 변수는 선택적으로 한정된 개체 이름입니다. 개체 이름의 모든 부분이 한정되면 이 이름은 서버 이름, 데이터베이스 이름, 소유자 이름, 개체 이름 등 네 부분으로 구성됩니다.

object_piece

반환할 개체 부분입니다. object_piece는 int 형식이며 다음과 같은 값을 가질 수 있습니다.

1 = 개체 이름

2 = 스키마 이름

3 = 데이터베이스 이름

4 = 서버 이름

nchar

다음 조건 중 하나가 만족되면 PARSENAME이 NULL을 반환합니다.

  • object_name 또는 object_piece가 NULL입니다.
  • 구문 오류가 발생합니다.

요청한 개체 부분의 길이가 0이고 잘못된 Microsoft SQL Server 식별자입니다. 길이가 0인 개체 이름은 한정된 이름 전체를 올바르지 않은 이름으로 렌더링합니다. 자세한 내용은 식별자를 개체 이름으로 사용을 참조하십시오.

다음 예에서는 PARSENAME을 사용하여 AdventureWorks 데이터베이스의 Contact 테이블에 대한 정보를 반환합니다.

코드 복사
USE AdventureWorks;
SELECT PARSENAME('AdventureWorks..Contact', 1) AS 'Object Name';
SELECT PARSENAME('AdventureWorks..Contact', 2) AS 'Schema Name';
SELECT PARSENAME('AdventureWorks..Contact', 3) AS 'Database Name';
SELECT PARSENAME('AdventureWorks..Contact', 4) AS 'Server Name';
GO

결과 집합은 다음과 같습니다.

코드 복사
Object Name                    
------------------------------ 
Contact                        

(1 row(s) affected)

Schema Name                     
------------------------------ 
(null)                         

(1 row(s) affected)

Database Name                  
------------------------------ 
AdventureWorks                           

(1 row(s) affected)

Server Name                    
------------------------------ 
(null)                         

(1 row(s) affected)
반응형
저작자표시

'연구개발 > DBA' 카테고리의 다른 글

SQL Server 2005 인스턴스 간에 로그인 및 암호 전송  (0) 2009.07.25
sys.extended_properties  (0) 2009.07.21
xp_crypt  (0) 2009.07.19
Backup...  (0) 2009.07.02
"파일을 액세스할 수 없거나 메모리 또는 디스크 공간이 부족하여 '거시기' 데이터베이스를 열 수 없습니다" 란 메시지 나올 시  (0) 2009.07.01

+ Recent posts

반응형

티스토리툴바