SQL Server data HASHBYTES 를 이용한 암호화

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

이번에는 HASHBYTES 함수를 이용한 data 암호화 방식을 알아보자.

HASHBYTES로 암호화를 진행하면 다음과 같은 특징이 있다.

• 복호화가 불가능하다.
• 인덱스 사용이 가능하다.

먼저 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

-- 암호화 데이터를 저장할 컬럼을 추가
alter table dbo.EmpTest
	add HashedLoginId varbinary(256)
go

--HASHBYTES를 이용한 data 암호화
update dbo.EmpTest
	set HashedLoginId = HASHBYTES('SHA1', LoginId)
go

테이블을 조회 해보면 아래와 같이 암호화 된 것을 확인 할 수 있다.

참고로 HASHBYTES에서 지원하는 암호화 알고리즘은  MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512 이다.

대칭키를 이용한 가장 큰 단점은 검색시 where 조건의 좌변을 변경해야 하기 때문에 인덱스 사용이 불가능하다는 점이다.

하지만, HASHBYTES를 이용하면, 인덱스 사용이 가능하다.

-- 먼저 조회할 컬럼으로 index를 생성
create index ix1 on dbo.EMPtest(HashedLoginId)
go

-- 검색 조건에서 HASHBYTES를 이용해서, 좌변 변형 없이 조회
select BusinessEntityID, JobTitle, HireDate , loginId, HashedLoginId from dbo.EmpTest
where HashedLoginId = HASHBYTES('SHA1', N'adventure-works\ken0')
go

데이터를 조회해보면, 인덱스 스캔을 수행한 것을 알 수 있다.