Mssql - 대칭키를 활용한 데이터 암복호화 처리하기

Mssql에서 암호화, 복호화 처리하기

암호화, 복호화를 하기위해서 먼저 인증서와 인증서를 통한 대칭키를 생성해야합니다.

 

 

인증서와 대칭키 생성하기

--인증서 생성
CREATE CERTIFICATE testCert   --인증서명
WITH SUBJECT = 'TestCert';    --인증서 제목 생성
GO

--인증서를 통해 대칭키 생성하기
CREATE SYMMETRIC KEY testKey    --대칭키명
    WITH ALGORITHM = AES_256    --사용알고리즘 AES256
    ENCRYPTION BY CERTIFICATE testCert;  --사용할 인증서
GO

쿼리문을 동작시킵니다.

해당 데이터베이스의 보안-인증서, 보안-대칭키에 생성된 것을 확인 할 수 있습니다.

 

다음은 대칭키를 사용하여 데이터를 삽입해보고 삭제해보겠습니다.

 

 

 

대칭키를 사용하여 암호화하기

-임시 테이블 생성

테스트할 임시 테이블 user_info라는 테이블을 생성합니다.

--임시테이블 생성
CREATE TABLE dbo.user_info
(
	seq int IDENTITY(1,1) PRIMARY KEY,
	name nvarchar(10) NOT NULL,
	phone varbinary(128)
)

seq : PRIMARY KEY, Auto Increment 옵션을 주었습니다.

name : 이름을 입력할 컬럼입니다.

phone : 암호화된 핸드폰 번호가 입력될 컬럼입니다.(varbinary)

 

테이블을 생성했으니 암호화된 데이터를 삽입해보겠습니다.

 

-암호화 데이터 삽입하기

--암호화를 할때는 인증서를 통해 대칭키를 열어줍니다.
OPEN SYMMETRIC KEY testKey
   DECRYPTION BY CERTIFICATE testCert;

--임시데이터삽입
INSERT INTO user_info (name, phone) VALUES ('김민수', EncryptByKey(Key_GUID('testKey'), '010-1234-5678'));
INSERT INTO user_info (name, phone) VALUES ('이유나', EncryptByKey(Key_GUID('testKey'), '010-7777-1234'));
INSERT INTO user_info (name, phone) VALUES ('박민지', EncryptByKey(Key_GUID('testKey'), '010-4321-4321'));
INSERT INTO user_info (name, phone) VALUES ('최철수', EncryptByKey(Key_GUID('testKey'), '010-1111-1111'));
INSERT INTO user_info (name, phone) VALUES ('홍길동', EncryptByKey(Key_GUID('testKey'), '010-2222-2222'));
INSERT INTO user_info (name, phone) VALUES ('홍길순', EncryptByKey(Key_GUID('testKey'), '010-3333-3333'));

SELECT * FROM user_info;

암호화된 데이터로 잘 삽입되었다.

 

암호화를 할때는 항상 인증서를 통해 대칭키를 열어주고 사용합니다.

seq컬럼은 자동증가이므로 제외하고, name, phone 컬럼에만 해당하는 데이터를 삽입하였습니다.

EncryptByKey(Key_GUID('대칭키 이름'), '암호화 할 값')

함수 EncryptByKey를 통해 암호화를 진행합니다.

첫번째 파라미터에는 Key_GUID 함수를 사용하고 파라미터로 대칭키 이름을 문자열 형태로 입력합니다.

두번째 파라미터에는 암호화할 문자열 값을 입력합니다.

 

 

 

대칭키를 사용하여 복호화하기

-암호화 데이터 복호화하기

OPEN SYMMETRIC KEY testKey
   DECRYPTION BY CERTIFICATE testCert;

SELECT
	seq,
	name, 
	phone,
	CONVERT(varchar, DecryptByKey(phone)) as 'decryptPhone'
FROM user_info;

 

DecryptByKey 함수를 통해 데이터를 복호화하고 자료형을 알맞게 캐스팅합니다.

 

 

 

아래 글을 통해 암복호화 커스텀 함수 만들기로 이어집니다.

myhappyman.tistory.com/230