[MariaDB] 컬럼 암호화(Encrypt)

* 들어가기에 앞서

먼저 제가 이 포스팅을 작성하게 된 이유는 MariaDB와 MySQL에서 컬럼 암호화에 들어가는 HEX가 왜 필요한지에 대해서 였습니다.

왜 사용해야하고 어떤 문제점이 있는가에 대한 내용은 다음 포스팅때 다루겠습니다.

이전 옥션 해킹 사태부터 여럿 해킹 사례가 있었지만 이번 컬럼 암호화는 회원 정보를 담을 Table 을 생성과 SQL Injection 을 당하더라도 단방향 암호화를 통해 개인정보가 쉽게 풀리지 않도록 해보겠습니다. 여기서 다룰 내용은 회원가입을 통해 암호를 암호화(Encrypt) 하는 구문과 설명을 포함하도록 하겠습니다. 더불어 레인보우 테이블에도 조금은 안전하게 한번 짜보도록 하겠습니다.

* 더불어 저도 아직 취준생에 초급입니다. 혹여나 잘못된 부분이나 개선사항이 있다면 덧글로 달아주시면 감사하겠습니다.

* 암호화에 대해 조금 고급지게 생각해보실 분은 2번 항목부터 보시면 됩니다.

* 사용된 버전과 툴

MariaDB 10.1

Toad for MySQL 8.0

StarUML

* Table 생성시 고려해야할 점

테이블 생성시 고려사항으로는 암호화 할 곳에 BLOB형 입니다. MariaDB 문서에는 찾아볼 수 없었지만 MySQL 문서에는 문자집합의 문제와 공백 등등을 위해 암호화를 할 컬럼에는 BLOB형을 권장하고있습니다. 이유에 대해서는 다음 포스팅때 적도록 하겠습니다.

* Table 생성

* DB 생성과 User 생성은 제외하도록 하겠습니다.

CREATE TABLE user_tb (
  idx MEDIUMINT UNSIGNED AUTO_INCREMENT COMMENT'회원 인덱스',
  id VARCHAR(50) UNIQUE COMMENT'회원 ID - EMAIL',
  passwd BLOB COMMENT'회원 암호',
  name VARCHAR(10) NOT NULL COMMENT'회원 이름',
  rnn_foward BLOB COMMENT'주민번호 앞자리',
  rnn_backward BLOB COMMENT'주민번호 뒷자리',
  date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'회원가입일',
  status ENUM('Y', 'N') NOT NULL DEFAULT 'N' COMMENT'탈퇴 여부',
  PRIMARY KEY (idx)
) ENGINE = InnoDB AUTO_INCREMENT = 1 ROW_FORMAT = DEFAULT CHARACTER SET utf8;

테이블에 대한 설명은 위 테이블의 COMMENT 로 대체합니다.

* 테이블에서 암호화 할 대상

개인정보보호법에서는 거의 다인듯하게 적혀있습니다만 여기서는 테스트를 위해 부분적으로 암호화를 하도록 하겠습니다.

passwd, rnn_backward 를 대상으로 삼겠습니다.

1.1 암호화에 쓰이는 함수

AES_ENCRYPT(암화화할 값,암호키)
AES_DECRYPT(복호화할 값,암호키)

이 두가지가 쓰입니다. 쓰는법은 간단합니다. 암호화 할 위치에 저 항목을 적어주시면 됩니다.

1.2 암호화 할 대상에 Insert

INSERT INTO user_tb(id, passwd, name, rnn_foward, rnn_backward, `date`, status)
VALUES("abc@testmyhomepage.pe.kr", AES_ENCRYPT("abc", SHA2("enc_key",512)),"홍길동", 100000, AES_ENCRYPT("0000000", SHA2("enc_key",512)), CURRENT_TIMESTAMP, "N"); 

그리고선 한번 그냥 SELECT 로 확인해 보겠습니다.

1.3 암호화가 잘 되었는지 확인

SELECT idx, id, passwd, name, rnn_foward, rnn_backward, `date`, status
FROM user_tb;

BLOB 이라 그런지 BLOB 형태로 출력이 됩니다 암호화가 잘 되었는지 확인을 하면 아래 이미지처럼 잘 되어 있습니다.

1.4 복호화로 데이터 확인

이번에는 DECRYPT 로 복호화 한 뒤에 데이터를 확인해 보도록 하겠습니다.

SELECT idx, id, AES_DECRYPT(passwd, SHA2("enc_key",512)), name, rnn_foward, AES_DECRYPT(rnn_backward, SHA2("enc_key",512)), `date`, status
FROM user_tb;

위 이미지를 확인하면 복호화가 잘 된것을 확인했습니다.

1.5 중간 점검

이제까지 한 내용중에는 암호화 할 대상에는 BLOB 을 써야하고 기본적인 AES로 된 암호화 복호화 함수를 사용했습니다. 다음에는 조금 안전하게 사용하는 법을 배우도록 하겠습니다.

2.1 고급 암호화

위에 1번 항목까지는 기본적인 암호화 복호화 정도만을 다뤘습니다. 위에서 가장 문제가 되는 점은 암호화 복호화에 들어갈 키 값이 노출이 되어있다는 점일것입니다. 더불어 요새 홈페이지들이 시도때도없이 암호를 계속 바꾸라는 메세지가 귀찮으실지도 모를것입니다. 아마 저는 그게 이 부분에 있지 않나 싶습니다.

2.2 AES KEY값을 어떻게 하면 좋을까?

2.1 에서 나왔듯이 암호화 복호화에 들어갈 키값 노출이 가장 큰 문제입니다. 저는 현업도 아니고 취준생이라 예측이지만 아마 KEY값으로 사용자 암호로 사용하는게 가장 베스트였습니다.

하지만

암호를 Brute force 또는 사용자 실수로 노출이 된다면 키값도 같이 노출되는 형태라 회원정보 1에 대해서는 무조건 암호해제가 되는 형태가 되는 꼴이 되어져 버립니다. 더불어 일반적인 사용자 암호의 길이는 해독이 금방 가능한 자리수입니다.

다음으로 생각한것이 사용자 암호 + 개발자가 생성한 임의의 암호 가 가장 이상적이고 베스트였습니다. 이것을 솔팅이라고 합니다. 하지만 솔팅을 했다 하더라도 임의의 문자열이기에 레인보우 테이블에 포함될 가능성이 상당히 높습니다. 이것에 대해서는 다음 포스팅에서 다룰 예정입니다. 지금은 이게 가장 베스트라고 생각하고 진행 하시면 됩니다.

2.3 중간 가로채기

DB와 어플리케이션을 통신하는데 소규모이다 하면 포트를 열지 않은체 진행하겠지만 혹여나 트로이목마나 기타 바이러스로 인해 탈취할 가능성이 높습니다. 심지어 그게 솔팅을 했더라도 직접적으로 키값이 그대로 전송되는게 보여질 수도 있습니다. 심지어 개인 개발을 하더라도 와이어샤크로 확인해 보았을때 그대로 키값이 노출됩니다. 그러기 위해서는 적어도 암호화가 필요한 table 에 대해서는 SSL 연결을 해주시는게 가장 이상적입니다.

2.4 ID 찾기와 암호 찾기

2.1~2.3 까지 읽어보셨다면 아마 사용자가 쓴 암호로 어떻게 암호를 찾고 변경을 하지? 라는 생각이 자연스레 들수도있고 해결법이 바로 생각나신 분들이 있을지도 모릅니다. 일반적으로 핸드폰 인증을 제외한다고 했을때는 email 인증을 이용한 암호 해제법입니다. 특정 주소에 접속을 했다 하면 DB에서 password 를 초기화 해서 제공을 하던가 아니면 기본 암호를 생성하여 이메일로 보내는 방식을 생각해 볼 수 있습니다.

* 마치며...

근래들어 보안과 개인정보가 중요해짐에 따라 컬럼 암호화는 선택이 아닌 필수가 되었습니다. SSL 연결도 중요하지만 만일의 사태를 대비해 데이터에 대한 보안을 꼭 하셨으면 하는 바램에 SSL 암호화 연결 보다는 컬럼 암호화 연결을 먼저 포스팅 합니다.