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

對稱加密 (Symmetric Encryption)，簡單來說，就是用相同的一組字串(金鑰)來進行資料的加密、解密作業，可針對某些敏感 (Sensitive) 欄位資料進行保護；本文將說明如何以密碼 (Password) 結合對稱金鑰 (Symmetric Key) 方式實現對稱加密。


前置作業

於資料庫中，執行以下指令建立測試資料表：

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

資料加密

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

create symmetric key SK_JRY    
 with algorithm = AES_256
 encryption by password = N'jerry';

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

3) 開啟金鑰

open symmetric key SK_JRY DECRYPTION BY PASSWORD = N'jerry';

4) 資料加密

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'傑克 史密斯')
     );

-- 檢視加密內容
select sn, english_name, chinese_name 
  from MEMBER_ENC;
 

資料解密

-- 檢視解密內容
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;


-- 關閉金鑰
close symmetric key SK_JRY;

相關文章

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