대칭키 암호화 데이터 뷰테이블에서 복호화 하는 방법 / DecryptByKeyAutoCert
대칭키를 이용한 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;