2012年11月5日 星期一

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)