대칭키를 사용하여 SQL Server 암호화(열 수준) 하기

대칭키를 사용하여 SQL Server 암호화(열 수준) 하기

 

• Version : SQL Server 2008, 2008R2, 2012

 

개인 정보에 대한 중요성이 강화 되면서 개인을 식별 할 수 있는 데이터에 대해서는 모두 암호화를 하도록 법적으로 강제 되고 있다.

테이블에 저장된 데이터를 암호화하는 여러 방법이 있지만 SQL Server의 대칭키를 이용하여 열 수준(컬럼단위) 암호화 하는 방법을 알아 보자.

 

테스트를 위하여 샘플 데이터를 생성 한다. 여기에는 중요한 개인정보인 전화번호를 예를 들었다.

CREATE TABLE MEMBER( NAME NVARCHAR(50), PHONE NVARCHAR(50) ) GO   INSERT INTO MEMBER VALUES ('강성욱' , '010-3786-0318') GO   SELECT * FROM MEMBER

 

 

 

서버 마스터 키는 SQL Server 암호화 계층의 루트 이다. 이는 인스턴스 생성시 생성된다. 아래 쿼리를 사용하여 마스터키 확인이 되지 않는 다면 수동으로 생성 해야 한다.

select * from master.sys.symmetric_keys where name = '##MS_ServiceMasterKey##'

 

 

 

암호화에 사용할 마스터키를 생성해야 한다. 이 키는 마스터키는 데이터를 암호화 할 때 사용되는 암호를 정의 한다. 마스터키는 백업을 하여 잘 보관 할 수 있도록 하자. 마스터키의 암호 설정 시 윈도우의 보안등급에 따라야 한다.

create master key encryption by password = 'sqlmvp.kr:010-3786-0318'; go

 

[보안등급을 불만족]

 

[보안등급 만족]

 

 

마스터키 생성이 완료 되었으면 인증서를 생성 한다. 인증서는 SQL Server의 공개키를 포함한 디지털 서명 보안 개체 이다.

CREATE Certificate Cerificate1 WITH Subject = 'Project Data' GO

 

 

 

대칭키를 생성 한다. 대칭키는 암호화와 복호화에 모두 사용되는 하나의 열쇠(키) 이다. 대칭키를 사용하여 암호화 및 암호화 해독을 진행 한다.

CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Cerificate1; GO

 

 

 

암호화 테스트를 위하여 암호화된 데이터를 저장할 컬럼을 추가하고 암호화를 진행 한다.

ALTER TABLE MEMBER ADD PHONE_ENCRYPT VARBINARY(MAX) GO   OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Cerificate1; GO   UPDATE member SET PHONE_ENCRYPT = ENCRYPTBYKEY (key_guid('SymmetricKey1'), phone) FROM member GO   CLOSE SYMMETRIC KEY SymmetricKey1 GO

 

 

기존의 전화번호 컬럼(nvarchar)의 값이 암호화 되어 저장된 것을 확인 하였다.

 

이번에는 암호화된 컬럼의 값을 대칭키를 이용하여 복호화 하는 방법을 알아 보자. 암호화와 동일한 방법으로 진행 되며 다만 복호화 함수 명령어 쿼리만 변경되었다.

OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Cerificate1; GO   SELECT Name, Phone, PHONE_ENCRYPT, CONVERT(nvarchar(50), DECRYPTBYKEY(PHONE_ENCRYPT)) AS PHONE_DECRYPT FROM member   CLOSE SYMMETRIC KEY SymmetricKey1 GO

 

 

 

만약 허가 받지 않은 사용자(권한이 없는 사용자)가 DESCRYPTION 함수를 사용하여 암호화된 데이터를 복호화 하려고 접근할 때 어떻게 될까? 아래 그림처럼 에러는 발생 하지 않으며 복호화된 값이 NULL로 표시 된다.

SELECT Name, Phone, PHONE_ENCRYPT, CONVERT(nvarchar(50), decryptbykey(PHONE_ENCRYPT)) AS PHONE_DECRYPT FROM member

 

 

 

암호 해독에 대한 권한 추가는 아래 명령어를 통하여 부여하며 읽기에 대한 기능을 부여 한다.

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO test; GO   GRANT VIEW DEFINITION ON CERTIFICATE::Cerificate1 TO test; GO

 

 

처음에도 언급 하였듯이 개인정보는 매우 중요한 정보이며 암호화 방법은 여러 가지가 있다.

이번 실습의 예는 SQL Server에서 지원하는 암호화 중 하나를 설명한 것으로 다른 암호화도 학습하여 안전한 데이터를 지향 하도록 해야 한다.

 

참고 자료

키 생성 : http://msdn.microsoft.com/ko-kr/library/ms188357.aspx

암호화 계층 : http://msdn.microsoft.com/ko-kr/library/ms189586.aspx

암호화 함수 : http://msdn.microsoft.com/ko-kr/library/ms173744.aspx

 

 

2013-01-25 / 강성욱 / http://sqlmvp.kr