[2k5] 인증서, 대칭키, 비대칭키를 이용한 암호화 사용하기

/**********************************************************************************************
-- Title : [2k5] 인증서, 대칭키, 비대칭키를 이용한 암호화 사용하기
-- Reference : feelanet.com
-- Key word : EncryptByAsymKey AsymKey_ID EncryptByKey Key_GUID EncryptByCert Cert_ID
**********************************************************************************************/
USE master;
GO

DROP DATABASE Certdb;
GO

CREATE DATABASE Certdb;
GO

USE Certdb;
GO

--adventureWorks.Person.Contact를 certdb..contact로 생성
SELECT
 ContactID, NameStyle, Title, FirstName, MiddleName, --nvarchar
 LastName, CAST ('' AS VARBINARY(512)) AS LastNameEncrypt,
 Suffix,
 EmailAddress,
 CAST ('' AS VARBINARY(512)) AS EmailCert,
 CAST ('' AS VARBINARY(512)) AS EmailSym,
 CAST ('' AS VARBINARY(512)) AS EmailAsym,
 CAST ('' AS VARBINARY(512)) AS EmailPassPh,
 EmailPromotion, Phone, PasswordHash, PasswordSalt, rowguid, ModifiedDate
INTO contact
FROM adventureWorks.Person.Contact;
GO

-- 1. 마스터 키
-- 데이터베이스에 있는 인증서의 개인 키와 비대칭 키를 보호하는 데 사용되는 대칭 키입니다.
-- DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

SELECT * FROM sys.symmetric_keys;
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssw0rd'
GO

-- 2. 인증 키
CREATE CERTIFICATE CertKey WITH SUBJECT = 'test Col';

SELECT * FROM sys.certificates;
GO

--DROP CERTIFICATE CertKey
SELECT EncryptByCert (Cert_ID('CertKey'), N'ab@c.com');
SELECT EncryptByCert (Cert_ID('CertKey'), N'010-7123-XXXX');

SELECT page_count
FROM sys.dm_db_index_physical_stats(db_id(), object_id('contact'), NULL, NULL, DEFAULT)
--583
GO

UPDATE contact
SET emailCert = EncryptByCert (Cert_ID('CertKey'), EmailAddress );
--7초,648 >> 997 페이지
GO

SELECT page_count
FROM sys.dm_db_index_physical_stats(db_id(), object_id('contact'), NULL, NULL, DEFAULT)
--931
GO

--잘못된 방법
SELECT TOP 100 EmailAddress, emailCert, * FROM contact;

SELECT EncryptByCert (Cert_ID('CertKey'), N'gustavo0@adventure-works.com');

SELECT * FROM contact
WHERE emailCert = EncryptByCert (Cert_ID('CertKey'), N'gustavo0@adventure-works.com');
      -- 이 방식으로 검색 안된다.
GO

--옳은 방법
SELECT TOP  1000 EmailAddress, CONVERT( NVARCHAR(50), DecryptByCert( Cert_ID('CertKey'), EmailCert)), *
FROM contact;
--13초

CREATE INDEX ix2 ON contact (emailCert) INCLUDE (EmailAddress)
GO
SELECT EmailAddress,  EmailCert FROM contact
WHERE CONVERT( NVARCHAR(50), DecryptByCert( Cert_ID('CertKey'), EmailCert)) = N'gustavo0@adventure-works.com'
GO -- Index Scan 해버린다. 또한 무자게 오래 걸리네..ㅡㅡ;; 써글..
 
-- 3. 대칭 키
CREATE SYMMETRIC KEY symKey
    WITH ALGORITHM = DES
                   --DES, 3DES(?!), RC2, RC4, RC4_128,
                   --DESX, AES_128, AES_192, AES_256
                   --AES : Windows XP, Windows Server 2000 지원 안함
    ENCRYPTION BY CERTIFICATE CertKey;
GO
--DROP SYMMETRIC KEY symKey
GO

-- 4. 대칭 키 사용 입력
OPEN SYMMETRIC KEY symKey
   DECRYPTION BY CERTIFICATE CertKey;
GO

-- 5. 대칭 키 사용 조회
SELECT EmailAddress, emailSym,
 EncryptByKey(Key_GUID('symKey'), EmailAddress)
FROM contact;
GO

SELECT page_count, avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('contact'), NULL, NULL, DEFAULT);

UPDATE contact
SET emailSym = EncryptByKey(Key_GUID('symKey'), EmailAddress);

ALTER INDEX ALL ON contact REBUILD;

SELECT page_count, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(db_id(), object_id('contact'), NULL, NULL, DEFAULT);
--1초, 592 >> 856
GO

CREATE INDEX ix_sym ON contact (emailSym) include (emailAddress);

SELECT EmailAddress, emailSym
FROM contact
WHERE EncryptByKey(Key_GUID('symKey'), emailSym) = N'gustavo0@adventure-works.com';
      -- Index Scan하고 욜라 오래 걸린다..ㅡㅡ;; 흐미..
      -- 뭐 잘못했나? 결과가 왜 안 나오지???

-- 6. 사용 후 닫기
CLOSE SYMMETRIC KEY symKey
GO
 
-- 7. 비대칭 키
CREATE ASYMMETRIC KEY asymKey
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = '123456';
GO
--DROP ASYMMETRIC KEY asymKey

-- 8. 비대칭 키 사용 입력
SELECT EmailAddress, emailAsym,
 EncryptByAsymKey(AsymKey_ID('asymKey'), EmailAddress)
FROM contact;
GO

SELECT page_count, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(db_id(), object_id('contact'), NULL, NULL, DEFAULT);

UPDATE contact
SET emailAsym = EncryptByAsymKey(AsymKey_ID('asymKey'), EmailAddress);

ALTER INDEX ALL ON contact REBUILD;

SELECT page_count, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(db_id(), object_id('contact'), NULL, NULL, DEFAULT)
GO
--18초, 856 >> 1665

SELECT TOP  1000 EmailAddress, emailAsym
FROM contact;

-- 9. 비대칭 키 사용 조회
CREATE INDEX ix_asym ON contact (emailAsym) include (emailAddress);

SELECT EmailAddress, emailAsym,
 EncryptByAsymKey(AsymKey_ID('asymKey'), EmailAddress)
FROM contact
WHERE EncryptByAsymKey(AsymKey_ID('asymKey'), emailAsym) = N'gustavo0@adventure-works.com'
GO --Index Scan한다. 이것도 또 왜 안나온데..ㅡㅡ;; 에휴..