[SQL Server] 암호화 된 View 다루기

 

 

 

뷰는 쿼리 결과 집합을 논리적으로 나타낸 결과물으로 가상 테이블처럼 사용하는 경우가 많다. 

 

자주 사용되는 복잡한 쿼리가 있을 때 뷰로 추상화해서 간단한 SELECT 구문으로 복잡한 쿼리를 쉽게 사용하거나, 특정 테이블에서 민감한 정보를 제외하고 뷰를 만들어 특정 컬럼만 노출시킬 때 사용하는데..

 

예전에 경험한 프로젝트에서는 개발자가 이메일, 전화번호, 카드번호 등 고객의 민감한 정보가 담긴 테이블에 직접 접근하는 대신 민감한 정보가 암호화된 뷰를 통해 우회적으로 접근하는 경우도 있었다.

 

exec sp_helptext 'VIEW_NAME';

 

 

SQL Server에서는 해당 프로시저를 사용해 뷰가 어떤 쿼리로 정의되어있는지 확인할 수 있는데..

 

CREATE VIEW VIEW_NAME
WITH ENCRYPTION
AS
SELECT * FROM TABLE_NAME;

 

 

 

 

뷰를 정의할 때 WITH ENCRYPTION 옵션을 사용한 경우 프로시저로 쿼리를 확인할 수 없다. 

 

원본 쿼리를 외부에 백업해두지 않으면 뷰를 정의한 관리자도 쿼리를 확인할 수 없고, 뷰를 수정할 때 해당 객체를 DROP 후 다시 CREATE 해야 한다.

 

EXEC sp_refreshview 'VIEW_NAME';

 

 

뷰를 구성하는 원본 테이블의 column 타입, 길이 등 스키마가 변경된 경우, 뷰에도 변경된 스키마를 반영해 줘야 한다.

 

이런 경우 위의 프로시저를 사용하는데, 뷰를 구성하는 쿼리 자체를 수정하는게 아니고 원본 테이블의 스키마 변경 사항을 뷰 정의에 반영하는 역할을 수행한다.

 

 

뷰에서의 인덱스 사용은 크게 일반 뷰와 인덱싱된 뷰로 나눌 수 있다.

 

일반 뷰는 자체 인덱스를 가질 수 없고, 원본 테이블의 인덱스를 그대로 사용해 뷰를 정의한 쿼리 실행 시 원본 테이블의 인덱스를 참조해서 성능을 최적화한다. 

 

인덱싱된 뷰는 일반 뷰와 다르게 뷰 자체에 CLUSTERED INDEX를 생성해 성능을 최적화 할 수 있어, 원본 테이블 대신 뷰의 인덱스를 참조해 성능을 최적화한다. 

이 때 생성되는 CLUSTERED INDEX는 디스크에 저장되니 원본 테이블에 DML 발생 시 뷰의 인덱스도 함께 업데이트된다.

 

 

SELECT         
    DAMO.DBO.DEC_VARCHAR('TARGET_SCHEME','DBO.VIEW_NAME','EMAIL_ADRS', EMAIL_ADRS) AS emailAdrs,            
FROM 
    scheme.dbo.TALBE_NAME_DAMO TND
WHERE 
    TIIA.sec1_EMAIL_ADRS = DAMO.DBO.PRED_META_PLAIN_V('${email}','TARGET_SCHEME','DBO.VIEW_NAME','EMAIL_ADRS')

 

 

뷰에는 복호화된 원문 이메일 데이터가 저장되어있고, DAMO 테이블에는 암호화된 이메일 데이터가 저장된 구조이다.

 

원본 DB에는 민감 정보를 암호화된 형태로 저장하고, 값을 비교해야 할 때는 DAMO에서 제공하는 암호화 및 복호화 관련 함수를 사용해서 처리한다.