SQL Server 2008 對稱金鑰加密完整實作：建立、修改加密方式、資料加解密與驗證器防竄改

前言

對稱金鑰 使用單一金鑰同時加密與解密，效能優於非對稱金鑰與憑證，適合大量資料加密。但使用前必須在工作階段中 OPEN SYMMETRIC KEY。本文示範建立、查詢、修改加密層級、資料加解密，並針對「惡意替換密文」攻擊展示 驗證器 防禦機制。

範例一：建立對稱金鑰 (受非對稱金鑰保護)

USE DB_Encrypt_Demo; GO
CREATE ASYMMETRIC KEY symDemoKey WITH ALGORITHM = RSA_512 ENCRYPTION BY PASSWORD = 'TestSYM456!'; GO
CREATE SYMMETRIC KEY sym_Demo WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY ASYMMETRIC KEY symDemoKey; GO

範例二：查看對稱金鑰

SELECT name, algorithm_desc FROM sys.symmetric_keys;

範例三：修改加密方式 (以密碼取代非對稱金鑰)

OPEN SYMMETRIC KEY sym_Demo DECRYPTION BY ASYMMETRIC KEY symDemoKey WITH PASSWORD = 'TestSYM456!'; GO
ALTER SYMMETRIC KEY sym_Demo ADD ENCRYPTION BY PASSWORD = 'newnew!456'; GO
ALTER SYMMETRIC KEY sym_Demo DROP ENCRYPTION BY ASYMMETRIC KEY symDemoKey; GO
CLOSE SYMMETRIC KEY sym_Demo; GO

範例四：資料加解密基本用法

-- 建表
CREATE TABLE dbo.PWDQuestion (
    CustomerID int PRIMARY KEY,
    PasswordHintQuestion nvarchar(300),
    PasswordHintAnswer varbinary(200)
); GO

-- 加密寫入
OPEN SYMMETRIC KEY sym_Demo DECRYPTION BY PASSWORD = 'newnew!456';
INSERT dbo.PWDQuestion VALUES (12, N'您出生的醫院名稱？', EncryptByKey(Key_GUID('sym_Demo'), N'杭州市一'));
CLOSE SYMMETRIC KEY sym_Demo; GO

-- 解密讀取 (必須先 OPEN)
OPEN SYMMETRIC KEY sym_Demo DECRYPTION BY PASSWORD = 'newnew!456';
SELECT CustomerID, PasswordHintQuestion,
       CAST(DecryptByKey(PasswordHintAnswer) AS varchar(200)) AS PasswordHintAnswer
FROM dbo.PWDQuestion WHERE CustomerID = 12;
CLOSE SYMMETRIC KEY sym_Demo; GO

-- 未 OPEN 直接讀取 → 回傳 NULL
SELECT CAST(DecryptByKey(PasswordHintAnswer) AS varchar(200)) FROM dbo.PWDQuestion WHERE CustomerID = 12;

進階：驗證器防禦「惡意替換密文」攻擊

若攻擊者將 CustomerID=14 的密文複製覆蓋 CustomerID=13，且無驗證器，解密會成功並顯示 14 的明文。解決方法：加密時加入 驗證器 (如 CustomerID)，解密時必須提供相同驗證器，否則解密失敗。

-- 重設測試資料
TRUNCATE TABLE dbo.PWDQuestion; GO
INSERT dbo.PWDQuestion (CustomerID, PasswordHintQuestion, PasswordHintAnswer)
SELECT 13, N'您出生的醫院名稱？', CAST(N'浙江婦保院' AS varbinary) UNION ALL
SELECT 14, N'您出生的醫院名稱？', CAST(N'浙江婦保二院' AS varbinary); GO

-- 加密並加入驗證器 (CustomerID)
OPEN SYMMETRIC KEY sym_Demo DECRYPTION BY PASSWORD = 'newnew!456';
UPDATE dbo.PWDQuestion SET PasswordHintAnswer =
    EncryptByKey(Key_GUID('sym_Demo'), PasswordHintAnswer, 1, CAST(CustomerID AS varbinary))
WHERE CustomerID IN (13,14);
CLOSE SYMMETRIC KEY sym_Demo; GO

-- 正確解密 (需提供驗證器)
OPEN SYMMETRIC KEY sym_Demo DECRYPTION BY PASSWORD = 'newnew!456';
SELECT CustomerID, PasswordHintQuestion,
       CAST(DecryptByKey(PasswordHintAnswer, 1, CAST(CustomerID AS varbinary)) AS varchar(200)) AS PasswordHintAnswer
FROM dbo.PWDQuestion WHERE CustomerID = 13;
CLOSE SYMMETRIC KEY sym_Demo; GO

-- 惡意替換攻擊
UPDATE dbo.PWDQuestion SET PasswordHintAnswer =
    (SELECT PasswordHintAnswer FROM dbo.PWDQuestion WHERE CustomerID = 14)
WHERE CustomerID = 13; GO

-- 再次解密 → 13 號因驗證器不符而解密失敗 (回傳 NULL)，14 號正常
OPEN SYMMETRIC KEY sym_Demo DECRYPTION BY PASSWORD = 'newnew!456';
SELECT CustomerID, PasswordHintQuestion,
       CAST(DecryptByKey(PasswordHintAnswer, 1, CAST(CustomerID AS varbinary)) AS varchar(200)) AS PasswordHintAnswer,
       PasswordHintAnswer AS binaryValue
FROM dbo.PWDQuestion WHERE CustomerID IN (13,14);
CLOSE SYMMETRIC KEY sym_Demo; GO

範例五：刪除對稱金鑰

DROP SYMMETRIC KEY symDemoKey; -- 若金鑰處於 OPEN 狀態會失敗，需先 CLOSE

小結

1. 對稱金鑰適合大量資料加密，使用前必須 OPEN，用畢 CLOSE。
2. 修改加密層級需在 OPEN 狀態下執行 ALTER SYMMETRIC KEY。
3. 驗證器 能有效防禦密文被惡意複製替換的攻擊，強烈建議敏感資料啟用。