SQL Server Data 대칭키 암호화

Data를 암호화 하는 방법은 여러가지가 있다.

보통, 지금까지 수행한 암호화 방법은 Application Layer에서 암호가 필요한 속성을 암호화 해서, 

DB에 저장했지만, 지금 소개할 방법은 Database Layer에서 암호화 하는 방식이다.

(SQL Server 전문 강사 정원혁님의 강의를 바탕으로 실습한 내용을 포스팅하였습니다.)

첫 번째 방법은 대칭키를 이용한 암호화 방법이다.

먼저 AdventureWorks2012(AdventureWorks2012설치) Database를 선택한 다음, 아래와 같이 테스트 테이블을 만든다.

Use AdventureWorks2012
go

if OBJECT_ID('dbo.EmpTest') IS NOT NULL
	drop table dbo.EmpTest
go

--테이블 만들기
select BusinessEntityID, LoginID, JobTitle, HireDate 
	into dbo.EmpTest
from 
HumanResources.Employee
go

테스트 테이블을 만든 후, 아래와 같이 대칭 키를 만든다.

이 때, encryption by password = 'P@ssw0rd' 의 비밀번호는 절대로 잃어버리면 안된다.

또한, 어떤 암호화 알고리즘을 사용했는지 공개해선 안되기 때문에 아래 구문도 공개되어선 안된다. 

참고로 지원하는 암호화 알고리즘은 

DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128 | DESX | AES_128 | AES_192 | AES_256

이다.

--대칭 키 만들기
Create Symmetric key mykey01
	with algorithm = aes_256
	encryption by password = 'P@ssw0rd' -- 복잡한 암호 지정
go	

키를 만들고 나면, 아래 그림과 같이 대칭 키가 생긴다.

그리고, 대칭키를 연 다음, 암호화할 속성을 저장할 컬럼을 만든다.

그 다음, 해당 컬럼에 암호화할 속성을 암호화 해서 저장한다.

--복호화하기 위해, 반드시 open을 해야 아래 키를 이용한 복호화가 가능하다.
open symmetric key mykey01
	DECRYPTION by password = 'P@ssw0rd'
go	

alter table dbo.EmpTest
	Add encLoginId varbinary(256)
go	

-- 새로운 열에 암호화 된 데이터 저장
update dbo.EmpTest
	set encLoginId = ENCRYPTBYKEY(Key_GUID('mykey01'), LoginID) 
GO	

암호화가 잘 되었는지 확인하고, 기존  암호화 안된 속성을 삭제한다.

아래는 복호화해서 확인 하는 쿼리다.

--복호화해서 확인
select businessentityId, CONVERT(nvarchar, DecryptByKey(encLoginId)) as 'LoginId', jobTitle 
, hireDate
from dbo.EmpTest
go

--사용 후에는 반드시 닫아야 한다.
close symmetric key mykey01
go

이상의 방법은 대칭키를 이용한 방법이다.

다만, 검색을 할 때, 아래와 같이 쿼리를 수행해야 하기 때문에,  인덱스 스캔을 사용할 수 없는 단점이 있다.

소량의 데이터를 암호화하기에는 충분히 고려할 만하다.

select businessentityId, CONVERT(nvarchar, DecryptByKey(encLoginId)) as 'LoginId', jobTitle 
, hireDate
from dbo.EmpTest
where CONVERT(nvarchar, DecryptByKey(encLoginId)) = 'adventure-works\ovidiu0'
go