데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

대칭키를 사용하여 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

 


강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3126
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1469
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1230
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1088
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 938
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 912
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 945
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1883
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1255
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1223
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2689
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1687
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1750
» 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5673
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1343
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3807
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1787
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1244
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1261





XE Login