MS-SQL 암호화 해보자. - 2

/*
    MS-SQL 2005 이상 대칭키 사용 방법
    대칭키(Symetric)와 인증키(Certificate)를 OPEN하고 패스워드(Password)를 입력한다.
    ※옵션(Option) : 패스워드는 인증키 생성시 입력 했으면 입력 해야함
    KEY_GUID의 값을 넣기 위한 변수 선언 및 Symetric Key의 name 값으로 KEY_GUID를 가져와야한다.
*/
-- 1. Start Session Open
/*
    이거 세트로 해야되고 GUID 값 받는거 선언해야하는거 이해하느라 맨땅에 하루 반나절 해딩했다...
    해딩은아니고 이곳 저곳 찾아보다가 막 해보다가 알게되었어...
*/
USE [DB_NAME];
OPEN SYMMETRIC KEY [SYMMETRIC_KEY_NAME]
DECRYPTION BY CERTIFICATE [CERTIFICATE_KEY_NAME]
WITH PASSWORD = '[CERTIFICATE_PASSWORD]'
DECLARE @GUID UNIQUEIDENTIFIER
SET @GUID = (SELECT KEY_GUID FROM SYS.symmetric_keys WHERE name='[SYMMETRIC_KEY_NAME]')
-- 1. End Session Close 여기 까지가 암호화/복호화 준비 과정 [이 구문은 세트(SET) 라고 보면 된다.]

-- 2. Start ENCRYPTION
/*
    수정(UPDATE)를 한다고 치자. 물론 그전에 암호화 해서 넣을 컬럼(COLUMN)을 만들어 줘야겠지?
    이놈은 암호화기 때문에 문자가 엄청 길어질꺼잖아 그래서 형식(TYPE)을 VARBINARY를 해서 만들어 줘야해
    여기서 잘봐야 할 건 저기 ENCRYPTBYKEY 구문이야, 저걸 해줘야 당근 암호화가 되겠지?
    삽입/입력(INSERT) 도 마찬가지로 ENCRYPTBYKEY를 해줘야겠지 대칭키는 ENCRYPTBYKEY 를 써줘야하고 다른 암호화 방식은
    ENCRYPTBYPHASE(?) 맞나 암튼 그런걸로 해줘야 해.. 일단 대칭키 방법이 가장 빠르다니깐 뭐 이걸 많이 쓰는 것 같어
 
*/
UPDATE  TEST_TABLE  SET ENCRYP_COLUMN = ENCRYPTBYKEY(@GUID, NORMAL_COLUMN)
--또는
UPDATE  TEST_TABLE  SET ENCRYP_COLUMN = ENCRYPTBYKEY(@GUID, '멍청이')
-- 2. End ENCRYPTION (여기 까지가 암호화)

-- 3. Start DECRYPTION
/*
    복호시 CAST나 CONVERT 를 사용해서 TYPE 변환을 해줘야 해. WHY? 그렇게 하지 않으면 이상한 문자로 깨져 나와..
    그리고 복호시에는 데이터 타입이 문자(String)이면 NVARCHAR, NCHAR, 숫자면 VARCHAR, CHAR로 해야되.
*/
SELECT col1, CONVERT(VARCHAR(MAX),DECRYPTBYKEY(col2)) FROM TEST_TABLE
SELECT col1, CAST(DECRYPTBYKEY(col2) AS VARCHAR(MAX)) FROM TEST_TABLE
-- 3. End SECRYPTION (여기 가지가 복호화)

-- 4. Start Session Close
/*
    그리고 다했으면 세션(SESSION)을 받아줘야 해. 무슨 세션이냐고? 당근 암호화 세션이지..
*/
CLOSE SYMMETRIC KEY [SYMMETRIC_KEY_NAME]

-- 4. End Seeion Close