Mysql 테이블 데이터 암호화 비법들  

 

 해킹사건들이 대형 인터넷 업체에서 발생됨으로 고객의 정보들이 쉽게 유출 가능한 부분으로

 사용자 Data의 암호화 는 인터넷 업체에서 중요한 필요사항으로 되었다.

 

 다음 사항들을 참고하여 암호화 하기전에 다음 사항을 보고 어떤 암호화를 사용 할 지 점검을 하시면 도움이 되실것입니다.

 

 암호화 하면 테이블수정이 되어야하는 부분이 있으니 수행 시간의 단축과 장애복구, 롤백 등의 작업을 위한

 대비도 필요합니다.

 alter table 'table_name' rename to 'new_table_name'; 명령을 이용하는 법을 이용 바랍니다

 

가. 데이터 암호화 방법

    1) 단방향 암호화

      - MD5, SHA1 같은 방법으로 암호화(Encrypt)후 원래대로 복호화(Decrypt)가 필요없는 경우

       - EX) 패스워드, 주민번호(복호화 불필요시) 등

    2) 쌍방향 암호화

       - DES, DES3, ENC, COMPRESS 같은 방법으로 암호화(Encrypt)후

         원래대로 복호화(Decrypt)가 필요한 경우

      - EX) 이름, 아이디, 주민번호 (나이계산, 생일), 메일주소, 주소,닉네임, 나이,생일 등

 

나. MYSQL에서 지원하는 암호화 함수

    1) 쌍방향 암호화 (암호화,복호화) 지원

        - AES_ENCRYPT ,AES_DECRYPT 

        - DES_ENCRYPT ,DES_DECRYPT

        - DECODE, ENCODE

        - COMPRESS, UNCOMPRESS

      - AES 나 DES 정도가 무난할 것입니다.

    2)  단방향 암호화만 지원

       - MD5  

       - PASSWORD, OLD_PASSWORD

       - SHA1, SHA

     - MD5 나 password나 같은 방법이니 아무거나 선택 바랍니다

  

 

다. Table DATA 암호화 하는 과정 과 비법

    1) 테스트 준비 및 테스트

        - 암호화 방법 결정 및 테스트가 되어야함

    2) Temp Data를 통한 검증  

       -  데이터의 길이 및 암호화의 특성과 암호화 키관리

    3) 실데이터 반영하기 및 소스체크 

         - 전반적인 예행 연습 및 테스트가 되어야함

            (별도의 테스트서버가 있음좋고 )

        - 예상 문제는 ?

    4) 테이블 복제 후 암호화 및 테이블 적용

         - 서비스의 다운타임을 어떻게 줄일것인가 ?

         - 예상치 못한 문제와 대비는 ?

         - 빠르고 정확한 방법은 ?  테이블 복제와 rename을 이용할까 말까 ?

    5) 실서버 적용

 

    테이블 반영 절차 요약

    0. 암호화 할 데이터를 선별하고 암호화 방법을 결정한다.

    1. 암호화 테스트

       1.1. create table n_member ;   테이블 복제

       1.2. insert into n_member from member;   데이터 복제 및 암호화 병행

    3. Data 암화화 확인 / 테스트 완료

        - 소요시간 및 장애 요소, 백업, 배치

    4. 실서버 반영

       4.1.실 DB 적용 : 테이블 스위칭

         alter table 'member' rename to 'o_member'; 

          alter table 'n_member' rename to 'member'; 

      4.2. 동시에 소스도 수정 및 반영

 

     *) 생성하는데는 소요시간을 감안하고,  암호화는 insert하면서 암호화를 같이 하면된다.

         테이블 rename을 이용하여 소요시간을 줄일수 있는 방안도 필요하다. (1초 이내 수행)  

 

라. 암호화 실습 TEST

    - 다음 테이블을 만들어 충분한 연습을 하길 권고 합니다.

   1.Create  Temp Table

   ---------------------

    CREATE TABLE `user_jumin` (
          `uid` int(10) unsigned NOT NULL,
           `user_id` int(10) unsigned NOT NULL,
           `user_name` varchar(20) NOT NULL,
          `password` varchar(20) NOT NULL,
          `jumin_no` varchar(18) NOT NULL,
          `n_password` varchar(100) NOT NULL,
          `n_jumin_no` varchar(100) NOT NULL,
            PRIMARY KEY (`uid`)

    ) ENGINE=InnoDB DEFAULT CHARSET=euckr

 

   *) 암호화를 하면 데이터의 길이가 늘어나므로 Data 길이 예측이 힘듭니다.

      잘모르는 경우는 255 정도 까지 늘려두고 테스트후에 길이를 확인 해보셔야합니다.

 

   2. 회원 테이블에서 데이터를 일부 혹은 전체를  복사한다.

   ---------------------------------------------

    insert into user_jumin ( uid, user_id,user_name,

password, jumin_no )
              select uid, user_id,user_name, password, jumin_no,
                from users_member;

 

    *) 또는 아래와 같이 암호화를 같이 할수도 있다.

  

  insert into user_jumin ( uid, user_id,user_name,

password, jumin_no, 

n_password, n_jumin_no)
    select uid, user_id,user_name,

               password,  jumin_no, 

              DES_ENCRYPT(password, 'enc-key' ) ,DES_ENCRYPT( jumin_no,'enc-key' ) 
      from users_member; 

 

  *) 암호화를 하는데는 CPU의 사용량혹은 데이터의 량이 있어 시간이 걸리거나 DB의 락이 걸릴수도 있다

     따라 모의 시험을 해서  데이터의 변경 하는데 소요되는 시간을 판단하고

     실수를 줄이기 위한 모의 테스트를 해야한다.

 

  3.  암호화 TEST ( AES , DES 로 비교해 보았습니다.)

      - 아래 SQL을 이용하여 암호화와 복호화를 해보며 Data의 길이와 차이점을 비교한다.

     select a2.pass,
       a2.aes_pass,
       a2.des_pass,
       length(a2.pass),
       length(a2.aes_pass),
       length(a2.des_pass),
       AES_DECRYPT(a2.aes_pass,'master-key'),
       DES_DECRYPT(a2.des_pass,'master-key')
 from (
      select a1.pass,
             AES_ENCRYPT(a1.pass,'master-key') aes_pass,
             DES_ENCRYPT(a1.pass,'master-key') des_pass
        from ( select 'password-manager-text-test-with-too-large-length'  pass
               union
               select 'second-password-manager-text-test-with-too-large-length'  pass
               union
               select 'Third-TESt-Password-text-test-with-too-large-length'  pass
             ) a1
      ) a2;

      

17631553_1237385453.png

 

  참고) DES는 위그림의 파란색 부분으로 첫글자가 "y"  같이 모두 같다는 것이 특징이다.

           반면 AES는 모두 다르며 AES는 64자 이며 DES 는 57자 이다.

          ( 물론 길이는 암호화 키나 내용에 따라 차이가 있다.)

 

   4. 수행 속도 및 데이터 체크 , 반영방법결정

        위의 예제를 이용하여 user_jumin 테이블의 주민 번호및 passwd를 암호화 하여

        수행 시간을 체크하고 ,

       - 실제 DB에 업데이트를 Temp Table을 만들어 암호화 할부분을 암호화 하고 실DB에 반영할지,

       - 아니면 전체를 실시간 update 할지

       또아니면

       - alter table을 이용하여 rename 할지 고민을 해야한다.

 

마. 실테이블 직접 UPDAT vs  테이블 복제후 RENAME 으로 교체

     - 암호화를 하기위해 서비스의 다운 타임과 장애를 줄이기 위한 방안으로

        회원 테이블(member)을 복제하여 암호화를 하여두고

       (N_member : 복제+암호화     member: 암호화전의 원본)

       원래 회원테이블을 O_member로 rename 하고

       암호화한 테이블을 member로 rename 하는 방법은 여러가지 장애의 대비책이 될것으로 보임

    - 원래테이블을 남겨두어 시스템의 롤백을 염두해 두어야 함.

 

 

참고) DES 와 AES가 쌍방향 암호화 기능이 있어 비교를 해보았습니다.

    AES 는 암호키 (위) master-key) 를 이용하여

    AES_ENC  new_len = 16 × (trunc(string_length / 16) + 1)

    DES_ENC new_len = orig_len + (8 - (orig_len % 8)) + 1

    의 길이가 된다고 합니다.

    암호화 되는 데이터의 가변 길이로 인해서 테이블의 Data 사이즈가 변화 되는것을

    예측후에 테이블을 수정하여 튜닝을 해야 합니다.

 

아래 mysql 싸이트에서 자세한 것을 보세요

 

참고자료;

   mysql4.0 : http://dev.mysql.com/doc/refman/4.1/en/encryption-functions.html

   mysql5.0 : http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html


출처 : http://blog.naver.com/nsjung74?Redirect=Log&logNo=110052521469