MSSQL Column Level(열 단위) 암호화

msdn 웹 문서에서 제공하는 예제를 통해 컬럼 레벨 암호화 테스트.

   

Column Level 암호화는 SQL Server 2005 이상의 버전에서 에디션에 상관없이 구현할 수 있다.(암호화 함수를 사용하는 것이기 때문에.)

Cell Level 암호화와 구분해서 사용할 것.

   

테스트 버전

SQL Server 2008 R2 Ent.

   

--- 간단한 대칭 암호화 Example-----------------------------------

   

USE AdventureWorks2008R2;

GO

   

--마스터 키 생성 (마스터키가 없으면)

IF NOT EXISTS

(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = 'P@ssw0rd'

GO

   

-- 인증서 생성

CREATE CERTIFICATE HumanResources037

WITH SUBJECT = 'Employee Social Security Numbers';

GO

   

-- 대칭키를 인증서로 암호화 (AES_256)

CREATE SYMMETRIC KEY SSN_Key_01

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE HumanResources037;

GO

   

   

-- 암호화된 국가번호 컬럼 추가

ALTER TABLE HumanResources.Employee

ADD EncryptedNationalIDNumber varbinary(128);

GO

   

-- 대칭키 오픈

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

   

-- 추가된 컬럼을 대칭키로 암호화(컬럼을 통째로..)

-- 암호화를 위해 EncryptByKey 함수를 사용

UPDATE HumanResources.Employee

SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);

GO

   

   

-- 암호화가 잘 됬는지?

SELECT NationalIDNumber AS '원본'

, EncryptedNationalIDNumber AS '암호화 후..'

FROM HumanResources.Employee;

GO

   

   

   

-- 암호화된 데이터를 해독하기 위해 대칭키 오픈

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

GO

   

-- 원래 데이터, 암호화 된 데이터, 복호화 된 데이터 비교

-- 복호화를 위해서 DecryptByKey 함수를 사용

SELECT NationalIDNumber, EncryptedNationalIDNumber

AS '암호화 된 데이터',

CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS '복호화 된 데이터'

FROM HumanResources.Employee;

GO

   

   

참고

How to: Encrypt a Column of Data (http://msdn.microsoft.com/en-us/library/ms179331(v=sql.105).aspx)

ENCRYPTBYKEY (http://technet.microsoft.com/ko-kr/library/ms174361(v=sql.105).aspx)

DECRYPTBYKEY (http://technet.microsoft.com/ko-kr/library/ms181860(v=sql.105).aspx)