Open Source Developer

사전 배경

고객 정보 저장시 개인정보 컬럼(이름, 생년월일, 전화번호, 계좌번호... 등등) 암호화 저장이 필요함

암/복호화가 가능한 방식으로 최소 권고 사항을 봤을 때 AES128 이상 적용해야 함

어플리케이션에서 암호화 복호화를 수행해서 저장하여도 되지만... 어플리케이션 로그등에 평문 개인정보가 남는 문제가 생겨서 DB 펑션으로 해결 하기로 함.

적용 암호 알고리즘

AES (AES128과 AES256은 암복호화 키의 길이에 따라 16bit 면 AES128, 32bit면 AES256 으로 분리되는 것 같다)

우리는 AES256으로 할 예정이므로 32bit의 암호화 키를 미리 준비 하자.

1. 암호화

CREATE FUNCTION AES256_ENCRYPT(plainText VARCHAR(1000)) RETURNS varchar(1000)
    DETERMINISTIC
BEGIN
	DECLARE returnVal VARCHAR(1000);
	SELECT HEX(AES_ENCRYPT(plainText, '32bit 암호화키'))
	INTO returnVal;
	RETURN returnVal;
END

2. 복호화

CREATE FUNCTION AES256_DECRYPT(encryptedText VARCHAR(1000)) RETURNS varchar(1000)
    DETERMINISTIC
BEGIN
	DECLARE returnVal VARCHAR(1000);
	SELECT CAST(AES_DECRYPT(UNHEX(encryptedText), '32bit 암호화키') AS CHAR)
	INTO returnVal;
	RETURN returnVal;
END

(암호화키는 당연히 암호화 할 때 사용한 암호화 키를 넣어 줘야 함)

3. 사용법

3-1. 고객정보 테이블(예시) 생성

CREATE TABLE CUSTOMER_INFO (
	SEQ INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '고객 순번',
    PHONE VARCHAR(255) NOT NULL COMMENT '전화번호',
    NAME VARCHAR(255) NOT NULL COMMENT '이름',
    BIRTH_DT VARCHAR(255) NOT NULL COMMENT '생년월일',
    GENDER VARCHAR(255) NOT NULL COMMENT '성별',
    EMAIL VARCHAR(255) NOT NULL COMMENT '이메일',
    PRIMARY KEY (SEQ),
    INDEX (PHONE)
) ENGINE=InnoDB COMMENT="고객 정보";

3-2. 고객 정보 입력

INSERT INTO CUSTOMER_INFO (
	PHONE, NAME, BIRTH_DT, GENDER, EMAIL
) VALUES (
	AES256_ENCRYPT('01012345678'), AES256_ENCRYPT('홍길동'), AES256_ENCRYPT('19901001'), AES256_ENCRYPT('M'), AES256_ENCRYPT('asdf@google.com')	
);

3-3. 고객정보 조회

SELECT
	SEQ,
	AES256_DECRYPT(PHONE) AS PHONE,
	AES256_DECRYPT(NAME) AS NAME,
	AES256_DECRYPT(BIRTH_DT) AS BIRTH_DT,
	AES256_DECRYPT(GENDER) AS GENDER,
	AES256_DECRYPT(EMAIL) AS EMAIL
FROM CUSTOMER_INFO
WHERE PHONE = AES256_ENCRYPT('01012345678')

4. 참고1 ( DETERMINISTIC )

펑션 선언에 쓰인 DETERMINISTIC 구문은 생략할 경우 기본 적으로 NOT DETERMINISTIC 로 정의 된다, 해당 구문은 동일한 입력값에 대해 동일한 결과값을 반환 하는 경우에만 유효 하며 성능에 지대한 영향을 주니 참고 하자.

5. 참고2 (암호화키 보안 문제)

위와 같이 함수를 선언하는 경우 사용자 생성시 모든 권한을 부여 하게 되면 사용자가 마음만 먹으면 언제든지 암호회 키를 조회 할 수 있다.

이것을 회피 하는 방법으로는

함수를 생성한 definer 계정 외에 일반 사용자 계정을 분리 하고 일반 사용자는 해당 함수의 실행 권한만 부여 하는 방법으로 가능하다.

아래와 같이 계정 생성시 모든 권한이 부여된 사용자.

MySQL [mysql]> create user 'user01'@'192.168.1.101' identified by '비밀번호';
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]> grant all privileges on mydatabase.* to 'user01'@'192.168.1.101';
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show grants for  'user01'@'192.168.1.101';
+--------------------------------------------------------------------+
| Grants for user01@192.168.1.101                                      |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`192.168.1.101` IDENTIFIED '비밀번호' |
| GRANT ALL PRIVILEGES ON `mydatabase`.* TO `user01`@`192.168.1.101`   |
+--------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [mysql]>

해당 사용자에게 펑션의 권한을 삭제 해보자

MySQL [mysql]> REVOKE EXECUTE ON FUNCTION mydatabase.AES256_DECRYPT FROM 'user01'@'192.168.1.101';
ERROR 1403 (42000): There is no such grant defined for user 'user01' on host '192.168.1.101' on routine 'AES256_DECRYPT'

MySQL [mysql]>

모든 권한을 가진 사용자에게서 특정 권한만 제거 할 수 없는 듯

이미 부여된 권한 제거

MySQL [mysql]> REVOKE ALL ON mydatabase.* FROM 'user01'@'192.168.1.101';
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]>

사용자의 실제 권한에 따라 차등 부여 (여기서는 조회, 입력, 수정, 삭제만 부여)

MySQL [mysql]> GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'user01'@'192.168.1.101';
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]>

위 사용자에게 보안이 필요한 펑션의 실행 권한만 부여

MySQL [mysql]> GRANT EXECUTE ON FUNCTION mydatabase.AES256_ENCRYPT TO 'user01'@'192.168.1.101';
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> show grants for 'user01'@'192.168.1.101';
+-------------------------------------------------------------------------------------+
| Grants for user01@192.168.1.101                                                     |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`192.168.1.101`                                      |
| GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase`.* TO `user01`@`192.168.1.101`   |
| GRANT EXECUTE ON FUNCTION `mydatabase`.`aes128_encrypt` TO `user01`@`192.168.1.101` |
+-------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MySQL [mysql]>

사용자가 많은 경우 매우 귀찮은 작업이 될 수 있다.

아래 글 참고 하여 그룹 기반의 권한을 부여하는 것도 좋은 방법.

https://opensrc.tistory.com/236

참고 3 (동일한 결과를 리턴하는 Java 코드)

( apache-common 패키지 필요)

import java.io.UnsupportedEncodingException;
import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
import org.apache.commons.codec.CharEncoding;
import org.apache.commons.codec.binary.Hex;
/**
 * MariaDB 암/복호화 Java 테스트
 * @author kyuyoung.lee
 *
 */
public class MariaDBAESTest {
	private static final String key = "위에서 사용한 암호화키 동일하게";
	private static SecretKeySpec genAesKey(final String key, final String encoding) {
		try {
			final byte[] finalKey = new byte[16];
			int i = 0;
			for(byte b : key.getBytes(encoding)) {
				finalKey[i++ % 16] ^= b;
			}
			return new SecretKeySpec (finalKey, "AES");
		} catch (UnsupportedEncodingException e) {
			throw new RuntimeException(e);
		}
	}
	public static String encrypt(String plainText) throws Exception {
		// Encrypt
		final Cipher encryptCiper = Cipher.getInstance("AES");
		encryptCiper.init(Cipher.ENCRYPT_MODE, genAesKey(key, "UTF-8"));
		String encStr = new String(Hex.encodeHex(encryptCiper.doFinal(plainText.getBytes("UTF-8")))).toUpperCase();
		return encStr;
	}
	public static String decrypt(String encryptedText) throws Exception {
		// Decrypt
		final Cipher decryptCiper = Cipher.getInstance("AES");
		decryptCiper.init(Cipher.DECRYPT_MODE, genAesKey(key, "UTF-8"));
		String decStr = new String(decryptCiper.doFinal(Hex.decodeHex(encryptedText.toCharArray())), CharEncoding.UTF_8);
		return decStr;
	}
	public static void main (String [] args) throws Exception {
		String plainText = "123456ABCdef";
		System.out.println("Plain Text : " + plainText);
		String encryptedText = MariaDBAESTest.encrypt(plainText);
		System.out.println("Encrypted Text : " + encryptedText);
		String decryptedText = MariaDBAESTest.decrypt(encryptedText);
		System.out.println("Decrypted Text : " + decryptedText);
	}
}

Good Luck!