[2k8] 마스터키, 인증서키, 대칭키를 이용한 암호화/복호화

/********************************************************************************************
-- Title : [2k8] 마스터키, 인증서키, 대칭키를 이용한 암호화/복호화
-- Key word : master key certificate key symmetric key encryption decryption 암호화 복호화
********************************************************************************************/

DROP DATABASE Certdb;
GO

CREATE DATABASE Certdb;
GO

USE Certdb;
GO

-- 1. 마스터 키
-- DROP MASTER KEY;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password12#$';

SELECT * FROM sys.symmetric_keys;
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password12#$';
GO

-- 2. 인증서 키
-- DROP CERTIFICATE CertKey;
CREATE CERTIFICATE CertKey WITH SUBJECT = 'Certificate Key'
    , START_DATE = '2011/05/01'
    , EXPIRY_DATE = '2099/12/31';

SELECT * FROM sys.certificates;
GO

-- 3. 테스트 테이블 생성
-- DROP TABLE ttt;
CREATE TABLE ttt
(id INT NOT NULL, org NCHAR(20), cert VARBINARY(512) NULL, symm VARBINARY(512));

INSERT INTO ttt SELECT 1, N'꼴통18호',NULL,NULL;
INSERT INTO ttt SELECT 2, N'하나하면$%123';
GO

-- 4. 인증서 키 암호화 입력
UPDATE ttt
SET cert = EncryptByCert (Cert_ID('CertKey'), org);
GO

-- 5. 인증서 키 복호화 출력
SELECT id, org, cert
     , CONVERT( NVARCHAR(50), DecryptByCert( Cert_ID('CertKey'), cert)) "pwd"
FROM ttt;
GO

-- 6. 대칭 키
-- DROP SYMMETRIC KEY SymKey
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;

SELECT * FROM sys.symmetric_keys;
GO

-- 7. 대칭 키 오픈
OPEN SYMMETRIC KEY SymKey
   DECRYPTION BY CERTIFICATE CertKey;
GO

-- 8. 대칭 키 암호화 입력
UPDATE ttt
SET symm = EncryptByKey(Key_GUID('SymKey'), org);

SELECT * FROM ttt;
GO

-- 9. 대칭 키 복호화 출력
SELECT id, org, symm
     , CONVERT(NVARCHAR,DecryptByKey(symm))
FROM ttt;
GO

-- 10. 사용 후 닫기
-- 세션별로 OPEN/CLOSE 필수
CLOSE SYMMETRIC KEY SymKey
GO
 
-- 11. 마스터키/인증서 백업
BACKUP MASTER KEY
    TO FILE = 'D:\backup\wdms3db\master_key_backup_20111026'
    ENCRYPTION BY PASSWORD = 'password12#$';
   
BACKUP CERTIFICATE CertKey TO FILE = 'D:\backup\wdms3db\CertKey_backup_20111026';

-- 12. 마스터키 복원/인증서 복원(가져오기?)는 모르겠다!!
RESTORE MASTER KEY
    FROM FILE = 'D:\backup\wdms3db\master_key_backup_20111026'
    DECRYPTION BY PASSWORD = 'password12#$'
    ENCRYPTION BY PASSWORD = 'password12#$';