서류가방 상세정보

제 목 : SQL Server에서 암호화 사용을 위한 기본 내용
잡필방 서류가방 전산과 컴퓨터 데이타베이스
작성자: 문시형 작성일: 2017-01-06 조회수: 149

먼저 BOL 에서 설명하는 SQL Server의 암호화 계층에 대한 간단한 이해가 필요할 것 같습니다.

이 그림은 암호화된 데이터를 처리하기 위해 거쳐야 하는 단계를 표시합니다.

예를들어 [SMK ->DMK -> 인증서 -> 대칭키] 를 통해서 암호화된 데이터를 처리할 수 있습니다.

물론 짧은 경로로 [암호 -> 대칭키] 를 통해서 암호화된 데이터를 처리할 수도 있습니다.

 

테스트 해본 바로는 암호화 계층 그림에 표시된 순서로 암/복호화는 되지만 이때 암호화된 데이터를 다른 DB로 가져가서 계속 동일한 암/복호화를 사용할 수는 없다는 것입니다.

또, 정확히 아래 그림대로 암호화 단계를 거치지 않는 경우도 있으며, 정확히 아래 그림대로 구분지어 지지 않는 경우도 있습니다.

예를 들어 EncryptByCert 함수를 이용하면 인증서를 통해 비대칭키를 사용해 암호화를 진행하게 됩니다. 그림에 대입하기에는 애매한 시나리오가 되는 것이죠.

 

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

 

 

 

 

그렇다면 실제로 암호화를 사용하는 몇가지 시나리오별로 테스트해 보겠습니다.

 

 

 

1. [SMK – DMK – 인증서 – 대칭키]로 암호화

 

- DMK 생성시 암호를 사용하더라도 SMK를 통해 암호화가 함께 진행되며, 이것은 DMK 생성 후 ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY 명령을 이용하여 SMK를 사용하지 않도록 변경할 수 있습니다.

 

- SMK로 DMK를 암호화하지 않을 경우 DMK 를 사용할 때 마다 OPEN MASTER KEY를 사용해 키를 열어 주어야 합니다.

 

 

 

== 테스트 스크립트

 

-- DB 생성

CREATE DATABASE SDB1

GO

USE SDB1

GO

 

-- 암호로 마스터 키 생성

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

GO

-- 자동으로 SMK로 DMK가 암호화 되었다는 것을 확인

-- SMK로 암호화 한것을 제거하면 DMK사용할 때 마다 키를 열어줘야 해서 귀찮으니 그냥 이대로 사용합니다.

SELECT NAME, IS_MASTER_KEY_ENCRYPTED_BY_SERVER FROM SYS.DATABASES WHERE NAME LIKE 'SDB%'

-- SDB1 1

GO

 

-- 인증서 생성

-- 아래와 같이 암호를 입력하지 않는 경우 DMK를 통해 보호하게 됩니다.

CREATE CERTIFICATE TESTCert WITH SUBJECT = 'name cert'

GO

 

-- 대칭키 생성

-- 이전 단계에서 생성한 인증서를 통해 대칭키를 보호합니다

CREATE SYMMETRIC KEY TESTSymKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE TESTCert

GO

 

-- 암호화 테스트를 위한 대칭키 사용

OPEN SYMMETRIC KEY TESTSymKey DECRYPTION BY CERTIFICATE TESTCert

GO

 

-- 테이블 생성 및 데이터 추가

CREATE TABLE T1 (NAME VARCHAR(50), EncryptedName VARBINARY(128))

GO

INSERT INTO T1 VALUES

        ('MS', ENCRYPTBYKEY(KEY_GUID('TESTSymKey'), 'MS'))

        , ('HP', ENCRYPTBYKEY(KEY_GUID('TESTSymKey'), 'HP'))

        , ('IBM', ENCRYPTBYKEY(KEY_GUID('TESTSymKey'), 'IBM'))

        , ('SONY', ENCRYPTBYKEY(KEY_GUID('TESTSymKey'), 'SONY'))

GO

 

-- 데이터 확인

SELECT CONVERT(VARCHAR(50), DECRYPTBYKEY(EncryptedName)) FROM T1

 

CLOSE SYMMETRIC KEY TESTSymKey

 

 

 

== 테스트 시나리오

 

  1. 사용한 DB를 백업 후 다른 장비에서 복원하는 경우
    사용했던 DB를 잘 백업해 두었고, DMK의 암호를 알고 있다면 다른 장비에서도 문제없이 잘 사용할 수 있습니다.
    물론 기존에 SMK를 이용해 DMK를 암호화해 사용했다면 DMK의 암호를 입력해 복원하는 대신 기존 장비의 SMK를 대상 장비에 복원해서 바로 대칭키를 사용할 수 있게 됩니다.

 

== SMK를 백업/복원하지 않는 경우

-- DB 복원

RESTORE DATABASE SDB1 FROM DISK = 'C:\WORK\SDB1.BAK'

GO

USE SDB1

GO

 

-- DB를 복원하면 SMK를 통해 DMK를 암호화한 부분이 빠져 있는것을 확인할 수 있습니다.

SELECT NAME, IS_MASTER_KEY_ENCRYPTED_BY_SERVER FROM SYS.DATABASES WHERE NAME LIKE 'SDB%'

-- SDB1 0

GO

 

-- 그냥 사용하려면 매번 DMK를 열어야 하므로 SMK로 DMK를 암호화 하도록 합니다

-- 이때 DMK의 암호가 필요합니다.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

CLOSE MASTER KEY

GO

 

-- 대칭키를 열어 데이터를 확인해 보면 데이터가 잘 복호화 되는것을 확인할 수 있습니다.

OPEN SYMMETRIC KEY TESTSymKey DECRYPTION BY CERTIFICATE TESTCert

GO

SELECT CONVERT(VARCHAR(50), DECRYPTBYKEY(EncryptedName)) FROM T1

CLOSE SYMMETRIC KEY TESTSymKey

== SMK를 백업/복원 하는 경우

-- DB 복원

RESTORE DATABASE SDB1 FROM DISK = 'C:\WORK\SDB1.BAK'

GO

USE SDB1

GO

 

-- SMK 복원

RESTORE SERVICE MASTER KEY FROM FILE = 'c:\work\smk' DECRYPTION BY PASSWORD = 'P@ssw0rd' FORCE

 

-- SMK 복원 후 SMK로 DMK가 암호화 된것을 확인할 수 있으며, 즉시 대칭키를 사용할 수 있게 됩니다.

SELECT NAME, IS_MASTER_KEY_ENCRYPTED_BY_SERVER FROM SYS.DATABASES WHERE NAME LIKE 'SDB%'

-- SDB1 1

GO

 

-- 대칭키를 열어 데이터를 확인해 보면 데이터가 잘 복호화 되는것을 확인할 수 있습니다.

OPEN SYMMETRIC KEY TESTSymKey DECRYPTION BY CERTIFICATE TESTCert

GO

SELECT CONVERT(VARCHAR(50), DECRYPTBYKEY(EncryptedName)) FROM T1

CLOSE SYMMETRIC KEY TESTSymKey

 

 

 

  1.  
  2. 같은 장비 또는 다른 장비의 다른 DB에서 암호화된 데이터를 사용하는 경우
    대칭키를 이용하여 암호화한 데이터의 경우 DB 백업/복원을 거치지 않는 경우 복호화가 불가능한 것 같습니다.
    제가 생각하기에 SQL Server에서는 대칭키를 백업/복원하는 구문이 없기 때문에 동일한 대칭키를 생성해 낼 수 없고, 결국 암호화된 데이터는 다른 DB에서 복호화 할 수 없는 것 같습니다.
    따라서 특정 비즈니스에 의해 암호화된 데이터를 복호화 할 수 있는 DB가 필요하다면 최초 암호화를 진행할 때 대칭키를 만든 상태의 초기 DB를 백업해 두었다가 나중에 복원하여 암호화된 데이터를 복호화 하는 용도로 이용할 수 있을것 같습니다.

 

 

 

 

 

2. [SMK – DMK – 인증서]로 암호화

 

인증서를 사용하는 방식은 비대칭 키를 사용해 암호화를 하기 때문에 많은 리소스를 사용하게 됩니다. 라고 BOL에서 설명하고 있습니다.

 

하지만 SMK, DMK, 인증서는 백업/복원이 가능하기 때문에 암호 문자열과 필요한 SMK, DMK, 인증서의 백업이 있는 경우 암호 문자열을 복호화 할 수 있게 됩니다.

 

== 테스트 스크립트

 

-- DB 생성

CREATE DATABASE SDB1

GO

USE SDB1

GO

 

-- 암호로 마스터 키 생성

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

GO

 

-- 인증서 생성

CREATE CERTIFICATE TESTCert WITH SUBJECT = 'name cert'

GO

 

-- 테이블 생성 및 데이터 추가

CREATE TABLE T1 (NAME VARCHAR(50), EncryptedName VARBINARY(128))

GO

INSERT INTO T1 VALUES

        ('MS', ENCRYPTBYCERT(Cert_ID('TESTCert'), 'MS'))

        , ('HP', ENCRYPTBYCERT(Cert_ID('TESTCert'), 'HP'))

        , ('IBM', ENCRYPTBYCERT(Cert_ID('TESTCert'), 'IBM'))

        , ('SONY', ENCRYPTBYCERT(Cert_ID('TESTCert'), 'SONY'))

GO

 

-- 데이터 확인

SELECT CONVERT(VARCHAR(50), DECRYPTBYCERT (Cert_ID('TESTCert'),EncryptedName)) FROM T1

 

 

 

== SMK, DMK, 인증서의 백업과 복원

 

-- SMK 백업

BACKUP SERVICE MASTER KEY TO FILE = 'c:\work\smk' ENCRYPTION BY PASSWORD = 'P@ssw0rd'

 

-- DMK 백업

BACKUP MASTER KEY TO FILE = 'c:\work\dbmasterkey' ENCRYPTION BY PASSWORD = 'P@ssw0rd'

 

-- 인증서 백업

BACKUP CERTIFICATE TESTCert TO FILE = 'C:\work\TESTCert.cer'

              WITH PRIVATE KEY (FILE = 'C:\work\TESTCertKEY.pvk',

              ENCRYPTION BY PASSWORD = 'Pwd@certkey');

 

 

-- SMK 복원

RESTORE SERVICE MASTER KEY FROM FILE = 'c:\work\smk' DECRYPTION BY PASSWORD = 'P@ssw0rd' FORCE

 

-- DMK 복원

RESTORE MASTER KEY FROM FILE = 'c:\work\dbmasterkey'

DECRYPTION BY PASSWORD = 'P@ssw0rd'

ENCRYPTION BY PASSWORD = 'P@ssw0rd'

 

-- 인증서 복원

CREATE CERTIFICATE TESTCert from FILE = 'C:\work\TESTCert.cer'

              WITH PRIVATE KEY (FILE = 'C:\work\TESTCertKEY.pvk',

              DECRYPTION BY PASSWORD = 'Pwd@certkey');

 

 

 

 

 

3. [암호 – 대칭키] 로 암호화

 

SMK, DMK 없이 대칭키를 바로 암호를 이용해서 생성하게 됩니다.

 

이 경우 대칭키를 열때마다 코드에 암호가 노출되기 때문에 보안상 좋지 않을것 같습니다.

 

하지만 대칭키를 백업하는 구문이 없기 때문에 전체 DB 백업 없이 암호화된 문자열만 가지고는 평문을 복원을 해낼 수 없습니다.

 

물론 DB백업을 복원할 경우 대칭키가 함께 백업되므로 이때 대칭키를 암호화한 암호를 안다면 모든 데이터를 복호화 해낼 수 있게 됩니다.

 

 

 

== 테스트 스크립트

 

-- DB 생성

CREATE DATABASE SDB1

GO

USE SDB1

GO

 

-- 암호로 대칭키 생성 (SMK, DMK 상관없음)

CREATE SYMMETRIC KEY TESTSymKey WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'P@ssw0rd'

GO

 

-- 암호화 테스트를 위한 대칭키 사용

OPEN SYMMETRIC KEY TESTSymKey DECRYPTION BY PASSWORD = 'P@ssw0rd'

 

-- 테이블 생성 및 데이터 추가

CREATE TABLE T1 (NAME VARCHAR(50), EncryptedName VARBINARY(128))

GO

INSERT INTO T1 VALUES

        ('MS', ENCRYPTBYKEY(KEY_GUID('TESTSymKey'), 'MS'))

        , ('HP', ENCRYPTBYKEY(KEY_GUID('TESTSymKey'), 'HP'))

        , ('IBM', ENCRYPTBYKEY(KEY_GUID('TESTSymKey'), 'IBM'))

        , ('SONY', ENCRYPTBYKEY(KEY_GUID('TESTSymKey'), 'SONY'))

GO

 

-- 복호화 확인

SELECT CONVERT(VARCHAR(50), DECRYPTBYKEY(EncryptedName)) FROM T1

 

CLOSE SYMMETRIC KEY TESTSymKey

 

 

 

 

 

결론

 

  1. DMK, 인증서, 대칭키, 비대칭키는 DB 단위로 저장됩니다.
  2. SMK, DMK, 인증서는 백업/복원이 되지만 대칭키, 비대칭키는 백업/복원이 되지 않습니다.
  3. 암호화에 사용한 DB를 백업/복원하는 경우 암호화에 사용한 요소의 암호를 알면 암호화된 데이터를 복호화 할 수 있습니다.
    예를들어 DMK를 암호로 암호화한 경우 암호화된 DB백업본과 DMK의 암호를 알면 암호화된 데이터를 복호화 할 수 있습니다.
  4. 만일을 위해 SMK로 암호화를 사용하면 DMK 의 암호를 잃어버려도 SMK를 통해 복호화 해낼 수 있습니다.
    또 DMK를 열 필요도 없으므로 코드도 간소화 되므로 이런 여러 장정 때문에 BOL에서 기본적으로 이 방식으로 가이드 하는것 같습니다.
  5. 대칭키는 백업/복원되지 않기 때문에 DB 백업/복원을 통해서만 동일한 대칭키를 생성해 낼 수 있습니다.
    만일 여러 장비에서 동일한 대칭키를 만들어야 한다면 대칭키를 생성할 때 IDENTITY_VALUE, KEY_SOURCE 값을 동일하게 주어서 만들 수 있습니다.
    하지만 이 경우 IDENTITY_VALUE, KEY_SOURCE 값이 유출될 경우 암호화된 문자열을 복호화 할 수 있게 되므로 보안상 위험할 수 있을것 같습니다.
  6. 비대칭키는 성능 문제로 인해 운영환경에서 사용하기 힘들므로 일반적인 환경에서는 대칭키를 사용하게 됩니다.
        글 삭 제
  댓글 달기
공동주택관리 홈페이지  |  : 관리자 : 문시형  |   : wedew@naver.com
: CopyRight(c) 2002년 우리와 이슬 그리고 도담 All Reght Reserved
: 경기도 의왕시 갈미 1로     : 010-6238-****