대칭키를 이용한 DB 암호화 처리 작업 시
아래와 같이 대칭키와 인증서 OPEN 후 암복호화를 해야하는 것으로 알고 있었음.
OPEN SYMMETRIC 대칭키이름 DECRYPTION BY CERTIFICATE 인증서이름;
그런데 문제는 일반 프로시저나 웹 소스에서는 문제가 없으나
외부에 뷰테이블 형태로 제공하는 데이터가 있었는데 뷰테이블에서는
그때까지만 해도.. OPEN SYMMETRIC 을 할수 없는게..아닌가..하고 난감했지만..
https://docs.microsoft.com/ko-kr/sql/t-sql/functions/decryptbykeyautocert-transact-sql?view=sql-server-2017
DecryptByKeyAutoCert 내장 함수를 사용하면 가능해진다는 것..
--Create the keys and certificate.
USE AdventureWorks2012;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Sammamish HR',
EXPIRY_DATE = '10/31/2009';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
----Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037 ;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber
= EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO
--
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key
--2. Decrypt the data
--3. Close the symmetric key
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS 'Decrypted ID Number'
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--OPTION TWO, using DecryptByKeyAutoCert()
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS 'Encrypted ID Number',
CONVERT(nvarchar, DecryptByKeyAutoCert ( cert_ID('HumanResources037') , NULL ,EncryptedNationalIDNumber))
AS 'Decrypted ID Number'
FROM HumanResources.Employee;
