[MS-SQL] 데이터 암호화

안녕하세요.

이번포스팅은 MS-SQL에서 데이터 암호화하는 방식을 설명드리겠습니다. 

---

1. 암호화

암호화란 사용자가 입력한 데이터를 알아볼 수 없는 데이터로 변경하는 과정입니다. 암호문의 형태로 정보를 기억 장치에 저장하거나 통신 회선을 통해 전송함으로써 정보를 보호할 수 있습니다.

---

2. 복호화

암호화된 데이터를 다시 사용자가 입력한 데이터로 변환하는 과정입니다. 암호화된 데이터를 사용자가 보고 판단 못하기 때문에 사용자가 사용할 수 있게 하는 행위입니다.

---

이번 글에서 설명드릴 암호화는 두 가지입니다. AES와 SHA입니다. 암호화에는 대칭키 암호화와 단방향 암호화 두 가지가 있습니다. AES는 유명한 대칭키 암호화방법이고, SHA는 단방향 암호화 대표적인 예입니다. 대칭키와 단방향 두 가지의 차이는 복호화 가능여부입니다. 

우리가 암호화를 사용해야 할 가장 큰 예제는 비밀번호입니다. 비밀번호는 관리자도 알면 안되는 정보입니다.  그렇기 때문에 비밀번호는 단방향 암호화인 SHA로 설정하는 것이 표준입니다. 시스템 감리를 받을 때 비밀번호가 SHA암호화가 되어있지 않다면 지적 사항 중 하나입니다. 그리고 AES 같은 경우는 개인정보 등에 사용할 수 있습니다.

※SHA

구문은 HASHBYTES(@ALGORITHM, @DATA) 로 구성이 되어있습니다.

ALGORITHM에 들어갈 변수는 MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512 사용 가능합니다.  저희는 SHA2_256 알고리즘을 사용합니다.

Return 값은 varbinary(최대 8000바이트)로 구성되어있습니다. 예제는 아래를 참조 부탁드립니다.

DECLARE @PASSWORD1 VARCHAR(MAX) = 'password'
DECLARE @PASSWORD2 NVARCHAR(MAX) = 'password'
SELECT HASHBYTES('SHA2_256', @PASSWORD1);
-- 결과 : 0x5E884898DA28047151D0E56F8DC6292773603D0D6AABBDD62A11EF721D1542D8
SELECT HASHBYTES('SHA2_256', @PASSWORD2);
-- 결과 : 0xE201065D0554652615C320C00A1D5BC8EDCA469D72C2790E24152D0C1E2B6189

사용 시 주의점은 데이터 형에 따라 결과 값이 다릅니다. 하나는 VARCHAR 그리고 다른 하나는 NVARCHAR로 구현시 다른 결과 값을 확인할 수 있습니다. 사용 시 참고 부탁드립니다.

※AES

구문은 EncryptByKey ( key_GUID , { 'cleartext' | @cleartext } [, { add_authenticator | @add_authenticator } , { authenticator | @authenticator } ] )로 구성되어 있습니다. 

key_GUID
일반 텍스트 암호화에 사용할 키의 GUID입니다. uniqueidentifier

'cleartext'
키로 암호화할 데이터입니다.

@cleartext
키로 암호화될 데이터가 들어 있는 navarchar, char, varchar, 이진, varbinary 또는 nchar 형식의 변수입니다.

add_authenticator
인증자가 일반 텍스트와 함께 암호화될지 여부를 나타냅니다. 인증자를 사용하는 경우 1이어야 합니다. int

@add_authenticator
인증자가 일반 텍스트와 함께 암호화될지 여부를 나타냅니다. 인증자를 사용하는 경우 1이어야 합니다. int

authenticator
인증자가 파생될 데이터입니다. sysname.

@authenticator
인증자가 파생될 데이터를 포함하는 변수입니다.

Return 값은 varbinary(최대 8000바이트)로 구성되어있습니다.

AES 같은 경우는 초기 구성단계가 있습니다. 

1. 자격증명 생성하기

--마스터키 생성
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TESTPassw0rd!!@#$';  

--자격증명 생성
CREATE CERTIFICATE Certi  -- Certi : 이름
WITH SUBJECT = 'NameAES';   -- SUBJECT : 내용(주제)
GO

2. 자격증명을 사용해서 대칭키 생성하기

CREATE SYMMETRIC KEY myKey  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE Certi;  
GO

3. 암호화/복호화하기

-- 암호화
--암호화를 위한 대칭키 활성화하기
OPEN SYMMETRIC KEY myKey  
   DECRYPTION BY CERTIFICATE Certi;  
 
--데이터 암호화하기  
UPDATE dbo.Employee
SET Encrypted_PhoneNumber = EncryptByKey(Key_GUID('myKey'), phoneNumber);  
GO  

-- 복호화
--복호화를 위한 대칭키 활성화하기
OPEN SYMMETRIC KEY myKey  
DECRYPTION BY CERTIFICATE Certi;  
GO  
 
--데이터 복호화하기 
SELECT Id, name, phoneNumber, Encrypted_PhoneNumber   
    AS 'Encrypted Phone Number',  
    CONVERT(nvarchar, DecryptByKey(Encrypted_PhoneNumber))   
    AS 'Decrypted Phone Number'  
    FROM dbo.Employee;  
GO

이번 포스팅은 MSSQL 암호화였습니다. 궁금하신 내용이나 잘못된 내용은 댓글이나 메일로 부탁드리겠습니다. 감사합니다.