MS SQL 암호화 복호화 DBMS




DB 암호화 개요 

  

현재 시장에 나와 있는 DB 보안 제품은 크게 

-네트워크 채널에서 필터링, 모니터링 및 접근제어를 수행하는 방식과 

-DB 내에 저장되어 있는 데이터 자체를 암호화 하는 

두 가지 방식이 있다. 


네트워크 채널 접근제어 방식의 가장 큰 장점은 기존 시스템 운영환경에 영향을 전혀 주지 않고 네트워크 단에 설치하는 수준에서 적용할 수 있다는 점이다. 

반면에 상대적인 단점은 필터링의 성능 구조상 모든 접근채널에 대해 적용할 수가 없어, 원천적인 접근제어가 어렵다는 점이다. 


DB 상의 데이터를 암호화하는 방식은 데이터자체를 암호화하기 때문에 암호화 키가 노출되지 않는 한 DB가 통째로 노출된다 하더라도 키 관리 시스템에 의한 적절한 권한을 가진 사용자가 아니면 그 내용을 절대 풀어볼 수 없다는 것이 가장 큰 장점이다. 

반면 적용절차가 상대적으로 번거로우며, 크고 작은 성능저하를 수반한다는 단점이 있다. 


조직의 상황이나 필요에 따라 두 가지 제품 중 하나만 선택할 수도 있지만, 요즘은 두 가지 방식을 모두 도입해 상호 보완적인 제품으로 운영하는 사례도 늘어나고 있다. 

  

  




암호화 방식 

  

예전 방식의 암호화는 평문을 암호알고리즘에 입력하여 암호화된 문장을 얻어내는 방식이었습니다. 

이런 방식은 아주 심각한 문제를 안고 있습니다. 

암호화할 때 사용한 암호알고리즘이 노출되는 경우 암호화된 문장을 누구나 해독할 수 있다는 거죠. 

  

그래서 현대의 암호화알고리즘은  평문 + KEY값을 입력값으로 하여 암호화된 문장을 얻어냅니다. 

물론 복호화 역시 암호문+KEY 값을 입력값으로 하여 이루어집니다. 


이런 방식은 암호화 알고리즘이 노출되었다 하더라도 KEY 값을 알지 못하면 복호화 할 수 없기 때문에 훨씬 안전하다는 장점을 가지고 있습니다. 

암호화에서 말하는 키값은 임의의 문자열이며  암호알고리즘이 노출되어도 암호문을 맘대로 해독할 수 없도록 해주는 일종의 보안장치 역할을 합니다 





[암호화] 

  

 평문 

  +                =====>    암호알고리즘  =========>  암호문 

 KEY 

  



[복호화] 

  

암호문 

  +              ========>  암호알고리즘 =========>  평문 

 KEY 




현대 암호알고리즘들은 크게 대칭형 암호알고리즘과  비대칭형 암호알고리즘으로 구분됩니다 

  

  

  

  

  
  대칭형 
  비대칭형 
 정의  
-암호화할 때 사용되는 키값과 복호화할 때 사용되는 키값이 동일한 알고리즘 

-관용암호(conventional encryption, 비밀키 암호(secret-key encryption), 

 단일키 암호(single-key encryption)  라고도 불림 

-1970년대 후반에, 공개키 암호화 방식이 알려지기 전에 흔히 사용되던 방법 


-주로 통신을 통해 주고 받는 데이터를 암호화 할 때 사용됨 
  
-암호화할때 사용하는 키값과 복호화할 때 사용하는 키값이 서로 다른 알고리즘 

-공개키 알고리즘이라고도 불림 


-대칭형 암호알고리즘의 KEY를 암호화하는데 사용하거나, 주로 인증에 많이 사용됨 

 (인터넷 뱅킹) 
  
 장점  
-암호화/복호화 속도가 비대칭형 암호알고리즘보다 빠르다 
  . 최소10~최대1000배.암호화/복호화 속도는 통신속도에도 많은 영향을 

    끼치므로 당연히 암/복호화 속도가 빠른 알고리즘이 통신에 유리 

-암호문의 크기가 평문보다 크지 않다. 

  . 암호화시 사이즈 증가가 없다.사이즈가 증가하지 않는다는 것은 암호화를 

    거쳐도 데이터량의 증가가 없다는 것이고 네트워크 대역폭을 추가적으로 

    필요로 하지 않는다는 것이므로 당연히 통신에 적합 

-안전성 

 . 128bit AES 같은 경우 비밀키를 선정할시 가이드 라인을 따랐을때 

  제3자가 해킹을 시도했을시 암호화된 메세지를 가로채도 키를 절대 알아낼 

  수 없음 

-확장성 

  기존의 DES를 같은 하드웨어로 3DES 구현가능 

  AES 같은 경우 128bit, 256bit, 512bit을 지원하기 때문에 더 많은 보안을 

  필요시 쉽게 구현 
  
-  키교환이 편하다 
- 중간에 해커에게 키를 가로채여도 일반인에게 알려진 공개키 이기 때문에 
  해커는 그 키로 암호화나 복호화할수 없다. 

- 사전에 키를 교환하지 않아도 됨 

- 수학적으로 어려운 문제들을 기반으로 하기때문에 해킹이  불가능함 
  

  

  
 단점  
- 암호화와 복호화시 사용되는 키가 유출되면 더이상 그 키를 사용할수 없음 

- 사전에 키를 교환해야 됨 
  
-암호화와 복호화 시간이 많이 걸린다 

- Authenticity 문제 (MD-5, SHA-1을 대체할 해쉬함수의 부재) 

  -> 전자서명의 문제지만 공개키랑 관련이 많음 
  
 종류  
SEED, DES, DES3, AES 

어느것이 더 많이 쓰이는 지는 알지 못하나 개발 순서는  

DES -> 3중 DES -> AES이며  DES는 키의 길이가 짧아 3중 DES의 사용이 권고되고 있고 차세대 미국 표준으로 AES가 선정되었기 때문에 앞으로는 AES의 사용량이 늘어날 것으로 보임 
  
RSA 
  



그래서!! 비밀키의 경우 맨처음 키를 교환할때만 공개키를 이용해서 

서로 키를 교환하고 그 다음부터 비밀키를 사용하기도 합니다. 

  

  

  


DES를 포함한 대부분의 대칭 블록 암호 알고리즘은 1973년 Horst Feistel이 최초로 소개한 구조를 기반으로 만들어 졌습니다 































  




  

왼쪽이 Feistel을 이용한 암호화, 오른쪽이 Feistel을 이용한 복호화 과정입니다 

  

보시면 칸이 세곳으로 나뉘어 있는데 이 각각을 라운드라 부릅니다 

  

라운드는 임의의 횟수만큼 반복 가능하며, 라운드 수를 늘일 수록 보안은 강화됩니다 

(보통은 16 라운드 수행) 

  

  


암호화 진행 과정 


  

1. 평문(암호화 되지 않은 원래 텍스트)을 두조각 냅니다 

2. 반으로 나뉜 평문중 왼쪽 부분에 대체(substitution)을 수행합니다 

3. 반으로 나뉜 평문중 오른쪽 부분에 라운드 함수(그림의 F)를 적용합니다 

4. 3의 결과문과 2의 내용에 베타적 논리합(XOR)을 적용합니다 

5. 마지막으로 두개의 반쪽짜리 데이터를 교환합니다 

6. 1~5를 매 라운드 반복합니다 

이 때 각 라운드는 이전 라운드 과정에서 나온 두개의 반쪽짜리 데이터와 K로부터 생성된 서브키들을 사용합니다 

(서브키는 별도의 생성 알고리즘에 의해 만들어집니다) 

  

  

DES는 이 Feistel네트워크를 변형한 형태입니다 

  


3중 DES는 3개의 키를 사용하고, DES 알고리즘을 3회 시행합니다. 

알고리즘은 암호-복호-암호(EDE)순서를 따릅니다 

  

  

  

DES들과는 달리 AES의 경우 Feistel에 기반을 두고 있지 않습니다 

  

AES는 각 라운드에서 대체와 치환을 사용해서 데이터 블록 전체를 병렬로 처리합니다 

  

작업은 라운드 단위로 구분되며, 암호화의 경우 시작전에 라운드 키를 더하는 단계에서 시작합니다 

  

이후 각 라운드를 진행하게 되는데 라운드는 다음과 같은 스테이지로 구성됩니다 



  

1. 바이트 대체 : S-box라는 표를 이용해서 바이트 단위 형태로 블록교환 

2. 행 이동 : 단순히 행과 행을 치환 

3. 열 섞기 : 열에 속한 모든 바이트들의 함수로. 열에 잇는 각 바이트를 변화시킴 

4. 라운드 키 더하기 : 확장된 키의 일부와 현재 블록에 비트별 XOR시행 

  



이 라운드는 총 10회 수행되며. 이중 마지막 라운드는 열 섞기 단계가 존재하지 않습니다 

  

  



암복호화시 중점적으로 체크 한  부분은  다음 3가지이다. 

- 암복호화에 따른 시간 소요를 최소화 할 수 있느냐 
- 암호화한 데이터들의 검색속도가 제 퍼포먼스를 발휘 할 수 있느냐 
- DB서버에 직접 적용 됨으로 인해, 생기는 DB서버 퍼포먼스의 저하를 무시할 수 있느냐 


위의 3가지 상황을 고려하여 DB 자체에서  보완이 필요한  주요 필드에 대해서만  암/복호화 를 수행하는 방법을 택함 


암복호화  데모 

1.DBAToolkit  인스톨 (Third Party Vender의  무료  솔루션 ) 
2.Install 시 키는 변경이 가능함 (디폴트는 Master Key 1, Local Key 1  로 되어 있음) 
3.UnInstall 시  Master and Local Key Vault tables  의 데이터를 삭제 해줘야 함 
4.사용한 암호화 알고리즘 :  3DES 
5.테스트 테이블 : 온라인 회원 마스터(TB_USER_CRYPT) 
      - 레코드 건수 : 1133715건 

      - 암호화 필드 : 비번,주민,전화,핸드폰 

      - 암호화 소요시간 : 약 1시간 30분 정도 걸림 (내 PC 서  쿼리 실행시, 서버에서 직접 하면 더 단축 되겠지.) 

6.    암호화 후 인덱스는 다시 잡아야 함 


[암호화] 

UPDATE  TB_USER_CRYPT  SET 

  US_PWD_ENCRYPT  = MASTER.dbo.fn_encrypt_3des(CAST(us_PWD AS VARBINARY(16)), 'Local Key 1',  NULL) 

,  US_JUMIN_ENCRYPT  = MASTER.dbo.fn_encrypt_3des (CAST(us_jumin AS VARBINARY(28)),  'Local Key 1',  NULL) 

,  US_HOMETEL_ENCRYPT  = MASTER.dbo.fn_encrypt_3desCAST(us_HOMETEL AS VARBINARY(28)), 'Local Key 1',  NULL) 

,  US_HANDTEL_ENCRYPT  = MASTER.dbo.fn_encrypt_3des (CAST(us_HANDTEL AS VARBINARY(28)), 'Local Key 1',  NULL) 


[복호화] 

SELECT  CAST(MASTER.dbo.fn_decrypt_3des(us_jumin_encrypt,  'Local Key 1‘,  NULL) AS VARCHAR(14)) 

FROM TB_USER_CRYPT 

  

  

  


[암호화 필드 검색 및 조인시 속도 테스트] 


-암호화된 필드를 LIKE 로 검색해야 하는경우 

SELECT * FROM TB_USER_CRYPT WHERE US_JUMIN LIKE '74%' 

SELECT * FROM TB_USER_CRYPT WHERE CAST(MASTER.dbo.fn_decrypt_3des(US_JUMIN_ENCRYPT,'Local Key 1',NULL) AS VARCHAR(14)) LIKE '74%‘ 


à 암호화된 필드를 복호화 한 후  LIKE 로 검색해야 해서 속도 느림 

    그래서 암호화된 필드 검색은 LIKE 가 아닌  = 로 검색 한다. 


SELECT * FROM TB_USER_CRYPT WITH (NOLOCK)  WHERE  US_JUMIN =‘주민번호' 

SELECT * FROM TB_USER_CRYPT WHERE  US_JUMIN_ENCRYPT = MASTER.dbo.fn_encrypt_3des (CAST(‘주민번호’ AS VARBINARY(28)),  'Local Key 1',  NULL) 



-암호화된 필드 조인하는경우 속도 비교 

SELECT * FROM TB_USER_CRYPT A JOIN TB_USER_CRYPT B ON(A.US_JUMIN=B.US_JUMIN) 

SELECT * FROM TB_USER_CRYPT A JOIN TB_USER_CRYPT B ON(A.US_JUMIN_ENCRYPT=B.US_JUMIN_ENCRYPT) 



-테스트  후  의견 

회원 마스터를 기준으로 3DES, AES  두가지 암호화 모듈을 사용해서 테스트 해  본 결과 암호화 및 복호화시  걸리는 시간이 3DES 가 조금  빨랐음 

암호화 필드를 조건 검색시 속도 현저히 떨어짐 -> 암호화 필드 검색은 지양해야 할듯 

암호화 된 필드 조인은  속도차가 거의 없었음 


만약 특정 필드 암호화 작업을 한다면  

1.기존 디비 암호화 

2.SP 수정: INSERT, UPDATE 시는 암호화,  SELECT 시는 복호화 하는 쿼리로 수정 

3.혹시 회원마스터를 사용하는 사이트 중 SP 가 아닌  ASP 페이지 내 하드코딩 한경우  페이지 내 쿼리 수정 필요 

  

  


MS-SQL 2005에서의 암호화 지원 


이전 버전(SQL Server 2000)에서는 데이터를 암호화(Encrypt) 하고 암호를 해독(Decrypt)하는데 있어서, 

자체 개발이나 Third Party Vender의 솔루션을 이용해야 했습니다. 

이런 문제점을 해결하기 위해 2005에서는 SQL Server 자체적으로 지원하고 있습니다. 





-테스트 디비를 만들고  테스트 한 후 디비 삭제 

create database data_encryption 

go 



use data_encryption 

go 



create table dbo.dataSecure 



 id int identity(1,1), 

 data_secure varchar(20), 

 data_encryption varchar(1000) 



go 


  

--데이터베이스마스터키만들기 

create master key encryption by password = 'pass' 

go 



--마스터키로부터 비대칭키만들기 

create asymmetric key asym_Encryption_Test with algorithm = rsa_1024 

go 



--데이터암호화에사용되는대칭키만들기 

create symmetric key sym_Encryption_Test with algorithm = des 

encryption by asymmetric key asym_Encryption_Test 

go 

  

  



--대칭키열기 

open symmetric key sym_Encryption_Test decryption by asymmetric key asym_Encryption_Test  



--대칭키의GUID 를열고데이터를입력 

declare @guid uniqueidentifier 

set @guid = (select key_guid from sys.symmetric_keys where name = 'sym_Encryption_Test') 


select '중요한기밀내용', encryptbykey(@guid, '중요한기밀내용') 


insert into dbo.dataSecure (data_secure, data_encryption) values ('중요한기밀내용', encryptbykey(@guid, '중요한기밀내용')) 

insert into dbo.dataSecure (data_secure, data_encryption) values ('02-6911-0327', encryptbykey(@guid, '02-6911-0327')) 

insert into dbo.dataSecure (data_secure, data_encryption) values ('1111', encryptbykey(@guid, '1111')) 


go 



--암호화된데이터보기 

select id, data_secure, data_encryption from dbo.dataSecure 



--복호화테스트 

select id, data_secure,data_encryption, cast(decryptbykey(data_encryption) as varchar(1000)) from dataSecure 



--테스트개체삭제 

drop symmetric key sym_Encryption_Test 

drop asymmetric key asym_Encryption_Test 

go 



use master 

go 



drop database data_encryption 

go 

  

  

  

  

  

MSSQL 디비 암복호화  참고 자료 


http://www.dbguide.net/prod/prod131001.jsp?mode=view&divcateno=315&divcateno_=315&pg=1&idx=32 

  

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=106&eid=AdOPVbvsAexkMu6xPwpac5ToYX/a3Mx6&qb=ZGIgvs+6ucijyK0=&pid=fTde6loi5Usssa7iNXCsss--392172&sid=SWQGUqDiY0kAABpG0yU 

  

http://kin.naver.com/detail/detail.php?d1id=13&dir_id=1310&eid=cvt4AGwh18RIIRcC2MqRMdWVCOG3izPH&qb=vs/Io8itILjwteIgM2RlcyDA5bTcwaE=&pid=fT2rIsoi5U4ssZCi5dNsss--374655&sid=SW06GjAdbUkAAD70U5k 

  


http://guardium.tistory.com/21 


http://blog.naver.com/bt1102?Redirect=Log&logNo=110008909549 


http://blog.naver.com/kkb2689?Redirect=Log&logNo=70038034589 


  

  

암호알고리즘 

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=106&eid=bSfAamqCzQB9hEOzlTY1cz8Ou5z+q98p&qb=VHdvZmlzaA==&pid=fUta9doi5ThssZozI1Gsss--496701&sid=SW-hZQnAb0kAACTfYo8 

  

ms sql 2005 

  

http://blog.taks.pe.kr/20 

http://hopangbear.tistory.com/127 

 *** http://blog.naver.com/acetious?Redirect=Log&logNo=80040844189

덧글

댓글 입력 영역


접속정보

통계 위젯 (화이트)

76
67
50548

이 이글루를 링크한 사람 (화이트)

3