백도어란 애플리케이션이 원래는 지원하려고 의도하지 않았던 것들을 가능하게 하는 문서화되지 않은 기능들을 말한다. 이 기사에서 SQL 서버 뷰에 있어서의 세가지 백도어에 대해 설명하면서 필자의 T-SQL 백도어 연재기사를 마무리하고자 한다. 이번 기사에서 설명할 세가지 뷰의 백도어는 사용자가 INFORMATION_SCHEMA 뷰를 생성하는 방법, 업데이트 가능한 정렬된 뷰를 생성하는 방법, 그리고 뷰에 있어서의 업데이트 제약사항을 피해가는 방법이다. 세 가지 백도어 외에, 문서화되지 않은 기능들 중에서 SQL 서버가 지원한다면 T-SQL 개발자들이 유용하게 사용할 수 있었을 법한 몇 가지 기능들에 대해서도 다룰 것이다.

INFORMATION_SCHEMA 뷰

INFORMATION_SCHEMA 뷰는 데이터베이스와 데이터베이스 내의 사용자 오브젝트들에 대한 메타데이터 정보를 반환하는 뷰이다. INFORMATION_SCHEMA 뷰는 ANSI에서 정의한 표준 포맷으로 정보를 반환하며 ANSI 포맷으로 결과를 반환하는 시스템 테이블 쿼리를 포함한다. INFORMATION_SCHEMA 뷰는 특수 프로시저가 동작하는 것과 유사하게 동작한다. Master 데이터베이스에서만 만들어 두면 모든 다른 데이터베이스에서 사용이 가능하며 수행 시점에 참조하고 있는 데이터베이스의 컨텍스트 내의 시스템 테이블 정보를 반환한다. 예를 들어 Northwind 데이터베이스에서 INFORMATION_SCHEMA.TABLES에 대하여 SELECT 문을 수행하면 Northwind 데이터베이스에 있는 테이블과 뷰의 목록이 반환된다. 이런 기능이 유용함에도 불구하고 불행히도 SQL 서버는 사용자가 INFORMATION_SHCEMA 뷰와 유사하게 동작하는 뷰를 생성하는 것을 지원하지 않는다. SQL 서버를 사용하다 보면 시스템 테이블을 조회하여 SQL 서버가 제공하는 ANSI INFORMATION_SHCEMA 뷰로는 얻을 수 없는 메타데이터 정보를 반환하는 자신만의 뷰를 직접 생성할 필요가 있는 경우가 발생한다. 예를 들어 인덱스에 관한 정보를 반환하는 뷰를 생성하기를 원할 수 있을 것이다. 공식적으로 지원하지는 않지만 T-SQL 백도어를 활용하면 사용자가 자신이 원하는 정보를 반환하는 INFORMATION_SHCEMA 뷰를 생성하는 것이 가능하다.

"allow updates" SQL Server Configuration 옵션을 활성화하면 사용자가 master 데이터베이스에서 INFORMATION_SHCEMA 뷰를 생성할 수 있다.

예를 들어 INFORMATION_SHCEMA.USERTABLES 라는 이름으로 뷰를 생성해 보자. 이 뷰는 데이터베이스에 있는 모든 테이블의 이름과 소유자를 반환(뷰는 제외)하는 뷰이며 [리스트 1]의 코드를 실행하면 이 뷰가 만들어진다.

생성한 뷰를 테스트하려면 Northwind와 Pubs에서 각각 한 번씩 뷰를 쿼리하는 다음 코드를 실행해 보면 된다.

USE Northwind
SELECT * FROM INFORMATION_SCHEMA.USERTABLES
USE pubs
SELECT * FROM INFORMATION_SCHEMA.USERTABLES

첫 번째 쿼리는 Northwind의 사용자 테이블 목록을 반환하고 두 번째 쿼리는 Pubs의 사용자 테이블 목록을 반환한다.

keycol		datacol
------		-------
1		b
2		c	
3		a

정렬된 뷰

ANSI에 의하면 뷰를 정의할 때 SELECT 문에 ORDER BY 절을 지정하는 것이 허용되지 않는다. 이러한 제약 사항은 뷰는 테이블을 대표하는 것이고 테이블에 있는 행들에 대해서는 사전에 정의된 정렬 순서가 없다는 사상에 기초한다. ORDER BY 절이 있는 SELECT 쿼리는 ORDER BY 절이 없는 SELECT 쿼리가 반환하는 것처럼 테이블을 결과로 반환하지 않고 커서를 반환한다. ANSI 규정에 의하면 뷰의 행을 정렬하기를 원한다면 뷰의 정의를 수정하지 말고 뷰를 SELECT 하는 문장에 ORDER BY 절을 명시하도록 되어 있다.

그러나 필자는 종종 Public SQL 서버 포럼 (news://msnews.microsoft.com/microsoft.public.sqlserver.programming 와 같은)에서 정렬된 뷰를 생성하는 방법에 대한 질문을 받는다. 필자가 비록 이런 정렬에 있어서의 제약사항을 피할 수 있는 백도어를 설명하고 있기는 하지만 백도어의 사용은 가급적 피할 것을 권고한다. ORDER BY 절을 지정한 뷰에서 ORDER BY 절을 제거하고 뷰를 액세스하는 쿼리문에 ORDER BY 절을 사용할 것을 권고한다.

정렬된 뷰를 생성할 수 있도록 해 주는 첫 번째 백도어는 SELECT 절에 TOP 100 PERCENT 를 사용하는 것이다. 이 백도어는 SQL 서버에서는 TOP 절을 명시하면 뷰에서도 ORDER BY 절을 지정할 수 있다는 점을 활용한 것이다. ORDER BY 절은 TOP에 대하여 논리적인 의미를 가지기 때문이다. 그러나 TOP 100 PERCENT를 명시하면 요청한 순서대로 정렬된 모든 행들을 반환하기 때문에 TOP 절은 아무런 의미가 없다.

[리스트 2]의 스크립트를 실행하면 이 백도어를 테스트할 수 있다. [리스트 2]의 스크립트는 keycol 컬럼과 datacol 칼럼을 가지는 T1 테이블을 생성하고, 3개 행을 Insert 한 후, VSortedT1TOP 뷰를 생성한다. VSortedT1TOP 뷰는 T1 의 행들을 keycol 순으로 정렬하여 반환한다. 이제, VSortedT1TOP 뷰에 대하여 SELECT * 쿼리를 실행해 보자. [그림 1]에 keycol 순으로 정렬된 세 개의 행이 있다.

이 기법은 ANSI 호환이 아니라는 점 외에도 부정적인 면들을 가진다. 만일 뷰에 대한 쿼리에서 ORDER BY 절을 사용한다면 SQL 서버는 데이터를 두 번 정렬하는 것이 되며 이는 심각한 성능 저하를 유발할 수 있다. 이런 동작 원리를 확인하려면 [리스트 3] 의 코드를 실행해 보면 된다. SHOWPLAN_TEXT 옵션을 설정하고 T1 과 VSortedT1TOP을 쿼리(keycol 컬럼에 대하여 역순으로 정렬)한 다음에 SHOWPLAN_TEXT 옵션을 비활성화한다.

[그림 2]에 T1 테이블에 대한 쿼리의 실행 계획과 VSortedT1TOP 뷰에 대한 쿼리의 실행 계획이 있다. T1 테이블을 직접 쿼리하면 정렬된 결과를 반환하기 위하여 옵티마이저가 keycol 칼럼의 클러스터드 인덱스를 사용하여 정렬된 스캔을 수행한다. 뷰에서 ORDER BY 절을 사용하지 않고, 뷰를 참조하는 쿼리에서 ORDER BY 절을 사용했다면 동일한 실행 계획을 얻을 것이다. 이와 반대로 VSortedT1TOP 뷰를 쿼리하면 우선 T1 에 대해 클러스터드 인덱스 스캔을 수행하는 실행 계획이 생성되고 그 다음에 뷰에 대한 쿼리에서 명시된 정렬 요청에 따라 결과를 재정렬한다. 다시 말하면 SQL 서버는 뷰 안에서 요청된 ORDER BY와 뷰를 참조하는 쿼리에서 요청된 ORDER BY를 별개로 처리하기 때문에 별도의 정렬비용이 발생하게 된다.

A
StmtText	
--------------------------------------------------------
  |--Clustered Index Scan(OBJECT:
  ([tempdb].[dbo].[T1].[PK_T1_
  12731CD]), ORDERED BACKWARD)


B
StmtText
--------------------------------------------------------
  |--Sort(ORDER BY:([T1].[keycol] DESC))
     |--Clustered Index Scan(OBJECT:
     ([tempdb].[dbo].[T1].[PK_T1_
     1273C1CD]), ORDERED FORWARD)

이 백도어를 사용하는 경우에 발생할 수 있는 또 다른 단점은 TOP 을 사용하여 뷰를 생성하면 뷰에 대하여 업데이트를 수행할 수 없다는 점이다. 다음의 UPDATE 쿼리를 실행해 보면 UPDATE를 실행할 수 없다는 오류 메시지가 반환된다.

UPDATE VSortedT1TOP
SET datacol = 'bb'
WHERE keycol = 2

Microsoft Certified Trainer (MCT)인 Zoltan Kovacs가 업데이트 가능한 정렬된 뷰를 생성할 수있도록 해 주는 백도어를 발견하였다. 이 기법은 OPENQUERY() 함수내의 SELECT 쿼리에 ORDER BY 절을 지정하는 것이다. OPENQUERY() 를 호출하여 자기 자신 서버에 대하여 쿼리를 실행하기 위해서는 우선 data access 서버 옵션을 설정하는 것이 필요하다.

EXEC sp_serveroption
  [], 
  'data access', true

그 다음에 다음 코드를 실행하여 뷰를 생성한다.

CREATE VIEW VSortedT1OPENQUERY AS
SELECT * FROM 
   OPENQUERY([], 
   N'SELECT * FROM tempdb..T1 
   ORDER BY keycol') 
   AS T

이 뷰를 쿼리하면 정렬된 결과를 얻으면서 업데이트도 가능하게 된다. 다음의 UPDATE 문을 실행하여 keycol 컬럼의 값이 2 인 행에 대하여 datacol 컬럼의 값을 'bb' 로 변경해 보자.

UPDATE VSortedT1OPENQUERY
SET datacol = 'bb'
WHERE keycol = 2

이 쿼리는 성공적으로 실행된다. 이 기법의 단점은 ANSI 호환이 아니라는 점 외에 로컬 쿼리에 비해 더 많은 비용이 드는 분산 쿼리를 사용한다는 점이다.

VIEW_METADATA

필자가 마지막으로 설명하는 백도어를 사용하면 그래픽 툴에서 뷰를 조작하는 경우에 뷰가 예상치 못한 방식으로 동작한다. 먼저 겉으로 보기에는 관련이 없어 보이는 뷰의 세 가지 특징들에 대하여 설명한 다음에 그 특징들의 연관성을 살펴 보도록 하겠다.

첫 번째 특징은 보안 매카니즘으로서 뷰의 활용이다. 뷰의 소유자와 테이블의 소유자가 동일한 경우에는 뷰의 베이스 테이블에 대한 액세스 권한이 없는 사용자에게 뷰에 대하여 select, insert, update, delete를 수행할 수 있는 권한을 부여할 수 있다.

exec sp_executesql N'UPDATE
  "tempdb".."T1" SET "datacol"=@P1
  WHERE "keycol"=@P2 AND
  "datacol"=@P3', N'@P1
  varchar(10),@P2 int,@P3
  varchar(10)', 'e', 1, 'a'

두 번째 특징은 다중 테이블을 액세스하는 뷰의 수정에 관한 부분이다. 뷰는 테이블처럼 동작하고 테이블과 유사하게 보이는 부분이 많지만 ANSI에서는 몇 가지 차이점들을 규정하고 있고 SQL 서버에서도 그와 같이 구현되어 있다. 여러 테이블을 조인한 뷰의 경우에는 뷰에 대한 하나의 UPDATE 문이나 하나의 INSERT 문에서는 오직 하나의 베이스 테이블만 수정할 수 있다.

세 번째 특징은 뷰를 통하여(베이스 테이블을 직접 수정하지 않고) 뷰의 쿼리 필터링 조건과 상충되는 수정을 하는 부분이다. 그런 수정 작업이 실행되면 입력되거나 수정된 행이 뷰에서 사라진 것처럼 보인다. datacol LIKE 'a%'라는 조건을 사용하여 앞에서 사용했던 T1 테이블의 모든 행을 조회하는 뷰를 생성했다고 가정하자. 뷰를 통하여 datacol 에 'd' 라는 값을 입력하면 정상적으로 수행된다. 그러나 뷰를 쿼리하면 뷰의 쿼리 필터링 조건을 충족시키지 않기 때문에 새로 입력된 행은 반환되지 않는다. 뷰의 쿼리 필터링 조건과 상충되는 수정이 가능하지 않게 하려면 뷰를 생성할 때 WITH CHECK OPTION 을 추가하면 된다. 다음 코드를 실행하면 CHECK 옵션을 명시한 VT1OnlyA 뷰가 만들어진다.

CREATE VIEW VT1OnlyA AS
SELECT * FROM T1
WHERE datacol LIKE 'a%'
WITH CHECK OPTION

이 뷰는 datacol 칼럼의 값이 'a' 로 시작하는 모든 행들을 조회하고 있다. 다음의 INSERT 문은 뷰의 필터링 조건과 상충되지 않는 행을 추가하기 때문에 성공적으로 실행된다.

INSERT INTO VT1OnlyA    VALUES(4, 'aa')

뷰를 쿼리하면 keycol이 1이고 datacol이 'a' 인 1개 행과 keycol이 4이고 datacol 'aa' 인 1개 행이 반환된다. 이제 뷰의 쿼리 필터링 조건과 상충되는 INSERT, UPDATE 문을 실행해 보자.

INSERT INTO VT1OnlyA 
  VALUES (5, 'd')
UPDATE VT1OnlyA
SET datacol = 'e'
WHERE keycol = 1

두 쿼리 모두 쿼리문이 명시된 CHECK 옵션과 상충한다는 오류를 반환한다.

이제 백도어를 활용해 보자. 엔터프라이즈 관리자와 같은 그래픽 인터페이스를 통하여 수정작업을 실행하면 뷰는 앞에서 언급한 세가지 특징에 관하여 의도한 것과 다르게 동작한다. 베이스 테이블이 아닌 뷰를 액세스하는 경우에 엔터프라이즈 관리자를 통해 데이터를 수정하려고 하면 보안 오류가 발생한다. 엔터프라이즈 관리자에서는 뷰를 통하여 한번에 여러 개의 테이블을 변경하는 작업이 성공적으로 수행된다. 뷰의 CHECK 옵션과 상충되는 수정 작업일지라도 엔터프라이즈 관리자에서 뷰를 통해 데이터를 입력 또는 수정하는 경우에는 성공적으로 수행된다.

뷰가 이렇게 다르게 동작하는 이유는 엔터프라이즈 관리자는 클라이언트 데이터베이스 인터페이스를 통하여 SQL 서버로부터 브라우즈 모드 메타데이터 정보를 요청하고 베이스 테이블의 메타데이터 정보를 얻는다. 그런 다음에 엔터프라이즈 관리자가 뷰 대신 베이스 테이블에 대한 쿼리를 작성하기 때문에 이런 차이가 발생하는 것이다.

SQL 서버 프로필러 추적을 활용하면 엔터프라이즈 관리자가 수행하는 쿼리를 캡쳐할 수 있다. 예를 들어 추적을 시작한 상태에서 엔터프라이즈 관리자를 통하여 VT1OnlyA 뷰를 수정하면 [그림 3]과 같은 실행문이 캡쳐된다. 엔터프라이즈 관리자는 뷰 대신 T1 테이블에 대하여 직접 UPDATE 문을 실행한다는 점에 유의하기 바란다. 다행스럽게도 이 문제는 단순히 VIEW_METADATA 옵션을 사용하여 뷰를 수정하면 손쉽게 예방할 수 있다.

ALTER VIEW VT1OnlyA   WITH VIEW_METADATA AS
SELECT * FROM T1   WHERE datacol LIKE 'a%' WITH CHECK OPTION

이 옵션을 사용하면 SQL 서버가 베이스 테이블의 메타데이터 대신 뷰의 메타데이터 정보를 전송한다. 이 방법을 사용하면 백도어를 막을 수 있다.

백도어를 닫아라

백도어를 사용하는 것이 위험하기는 하지만 운영 데이터베이스에서 백도어들에 직면하게 될 때 그런 백도어들을 인지하고 대안을 제시할 수 있는 능력이 필요하다. 필자는 SQL 서버를 사용하는 개발자들이 어떤 SQL 서버 백도어들은 공식적으로 지원되는 기능들만큼 T-SQL 개발자들에게 매우 가치 있는 기능일 수도 있다는 것을 깨닫기를 바란다.

[리스트 1] INFORMATION_SCHEMA.USERTABLES 뷰 생성 스크립트

USE master
GO

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

IF OBJECT_ID('INFORMATION_SCHEMA.USERTABLES') IS NOT NULL
  DROP VIEW INFORMATION_SCHEMA.USERTABLES
GO

CREATE VIEW INFORMATION_SCHEMA.USERTABLES
AS
SELECT USER_NAME(uid) AS owner, name FROM sysobjects
WHERE type = 'u'
GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

[리스트 2] T1 테이블과 VSortedT1TOP 뷰 생성 스크립트

USE tempdb

CREATE TABLE T1
(  keycol  int NOT NULL PRIMARY KEY,
   datacol varchar(10) NOT NULL )

SET NOCOUNT ON
INSERT INTO T1 VALUES(1, 'b')
INSERT INTO T1 VALUES(3, 'a')
INSERT INTO T1 VALUES(2, 'c')
GO

CREATE VIEW VSortedT1TOP
AS
SELECT TOP 100 PERCENT *
FROM T1 ORDER BY keycol
GO

[리스트 3] 베이스 테이블로부터 정렬된 데이터를 요청하는 쿼리와 정렬된 뷰로부터 정렬된 데이터를 요청하는 쿼리

SET SHOWPLAN_TEXT ON
GO
SELECT * FROM T1
ORDER BY keycol DESC
SELECT * FROM VSortedT1TOP
ORDER BY keycol DESC
GO
SET SHOWPLAN_TEXT OFF