Transact-SQL 對稱加密技術 (二)：搭配憑證 (SQL Server Symmetric Encryption － Using Certificate)

說明如何以憑證 (Certificate) 結合對稱金鑰 (Symmetric Key) 方式進行對稱加密作業。


前置作業

建立測試 Database(TEST), Table(MEMBER_ENC)：

create database TEST
go

use TEST;
create table MEMBER_ENC (SN varbinary(max), English_Name varbinary(max), Chinese_Name varbinary(max));

資料加密

1) 建立 Master Key：每個資料庫只有一個 Master Key

use TEST;

if not exists (select name, is_master_key_encrypted_by_server from sys.databases where name = 'master' and is_master_key_encrypted_by_server = 1)
   create master key
      encryption by password = 'My_Master_Key_Pwd';   --> 此密碼用於保護 Master Key

2) 檢視 Master Key： 執行以下查詢指令。若 is_master_key_encrypted_by_server 欄位值等於 1，則表示 Master Key 已存在。

use TEST;
select name, is_master_key_encrypted_by_server from sys.databases where name = 'TEST';

3) 建立憑證

use TEST;

create certificate TESTCert
  with subject = 'Testing_Certificate',
  start_date   = '20120101',
  expiry_date  = '20991231';

4) 檢視憑證：於 SQL Server Management Studio 物件總管中，TEST 資料庫的 安全性\憑證 找到 TESTCert 。

5) 建立對稱金鑰
SQL Server 支援 DES, TRIPLE_DES, TRIPLE_DES_3KEY, RC2, RC4, RC4_128, DESX, AES_128, AES_192, AES_256 等對稱加密邏輯，此處採用 AES_256；並以 ServerCert 憑證進行加密。

use TEST;

create symmetric key SK_JRY    
 with algorithm = AES_256
 encryption by certificate TESTCert;

6) 檢視對稱金鑰：於 SQL Server Management Studio 物件總管中，該資料庫的 安全性\對稱金鑰 找到 SK_JRY。 (亦可執行查詢指令：Select * From 資料庫名稱.sys.symmetric_keys)

7) 開啟金鑰

use TEST;
open symmetric key SK_JRY decryption by certificate TESTCert;

8) 資料加密

use TEST;

insert into MEMBER_ENC (sn, english_name, chinese_name)
 values( EncryptByKey(Key_GUID('SK_JRY'), cast(1 as varchar(10))),
   EncryptByKey(Key_GUID('SK_JRY'), 'Tom Smith'),
   EncryptByKey(Key_GUID('SK_JRY'), N'湯姆 史密斯')
     );

insert into MEMBER_ENC (sn, english_name, chinese_name)
 values( EncryptByKey(Key_GUID('SK_JRY'), cast(2 as varchar(10))),
   EncryptByKey(Key_GUID('SK_JRY'), 'Jack Smith'),
   EncryptByKey(Key_GUID('SK_JRY'), N'傑克 史密斯')
     );
 

9) 檢視加密內容

use TEST;

select sn, english_name, chinese_name 
  from MEMBER_ENC;
 

資料解密

1) 檢視解密內容

use TEST;

select convert(varchar(max),  DecryptByKey(sn))           as sn,
    convert(varchar(max),  DecryptByKey(english_name)) as english_name,
    convert(nvarchar(max), DecryptByKey(chinese_name)) as chinese_name
  from MEMBER_ENC;
 

2) 關閉金鑰

use TEST;
close symmetric key SK_JRY;
 

清除測試環境

use master;
drop database TEST;
 

相關文章

Transact-SQL 對稱加密技術 (一)：搭配密碼 (SQL Server Symmetric Encryption － Using Password)