[SQL] 資料欄位的加解密

練習利用憑證（Certicate）加密金鑰（Key），再利用金鑰（Key）加密欄位資料（Data）來保護重要資料

建立測試基本資料

USE AdventureWorks2012 
GO

IF OBJECT_ID('PWData') IS NOT NULL
  DROP TABLE PWData

CREATE TABLE PWData
(
  MemID INT PRIMARY KEY CLUSTERED,
  MemName nvarchar(10),
  PW varchar(100) NOT NULL,
  EncryptPW varbinary(100) -- 資料型態也是重點之一
)
GO

INSERT INTO PWData (MemID,MemName,PW) VALUES
  (1,N'張三','123'),
  (2,N'李四','456'),
  (3,N'王五','789')
GO

加密流程

-- 1. 建立憑證需先建 Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

-- 2. 建立憑證
CREATE CERTIFICATE CertDemo
  AUTHORIZATION dbo
  WITH SUBJECT = 'CertDemo',
  START_DATE = '1/20/2014', -- 不指定為建立當下的日期
  EXPIRY_DATE = '1/20/2015' -- 不指定為 Start_Date 加 1 年為 Expiry_Date
GO

-- 3. 建立以憑證加密的對稱金鑰
CREATE SYMMETRIC KEY KeyDemo
  AUTHORIZATION dbo
  WITH ALGORITHM = TRIPLE_DES          -- 加密演算法
  ENCRYPTION BY CERTIFICATE CertDemo   -- 利用 CertDemo Certificate 來加密 KeyDemo Key
GO

-- 4. 對密碼欄位進行加密
-- 4.1 透過憑證解密來開啟對稱金鑰
OPEN SYMMETRIC KEY KeyDemo DECRYPTION BY CERTIFICATE CertDemo
GO

-- 4.2 使用 EncryptByKey 將密碼資料加密
UPDATE dbo.PWData
SET EncryptPW = EncryptByKey(Key_GUID('KeyDemo'),PW)
GO

-- 4.3 關閉所有對稱金鑰
CLOSE ALL SYMMETRIC KEYS
GO

-- 4.4 檢查加密結果
SELECT * FROM PWData

解密流程

-- 1. 透過憑證解密來開啟對稱金鑰
OPEN SYMMETRIC KEY KeyDemo DECRYPTION BY CERTIFICATE CertDemo

-- 2. 使用 DecryptByKey 讀取解密資料，並透過 CAST 轉回字元格式
SELECT 
  * ,
  CAST(DecryptByKey(EncryptPW) AS varchar(100)) AS DecryptPW
FROM dbo.PWData

--2.3.關閉所有對稱金鑰
CLOSE ALL SYMMETRIC KEYS

注意事項

要利用 EncryptByKey 加密或 DecryptByKey 解密時，一定要先把 Key Open，要不然欲加解密的資料，會變成 NULL，但不會出現任何 Error

SELECT * ,
 CAST(DecryptByKey(EncryptPW) AS varCHAR(100)) AS DecryptPW
FROM dbo.PWData

建立 Store Procedure 提供前端程式呼叫

• 檢查密碼是否正確

CREATE PROC CheckPW
  @MemID int,
  @inputPW varchar(100),
  @Result bit output
AS
  OPEN SYMMETRIC KEY KeyDemo DECRYPTION BY CERTIFICATE CertDemo

  IF EXISTS
      (
          SELECT 1 
          FROM PWData 
          WHERE MemID = @MemID
                  AND CAST(DecryptByKey(EncryptPW) AS varCHAR(100)) = @inputPW 
      )
        SET @Result = 1
  ELSE
        SET @Result = 0

  CLOSE ALL SYMMETRIC KEYS
GO

-- 測試
DECLARE @RetValue bit
EXEC dbo.CheckPW 1,'P@ssw0rd',@RetValue OUTPUT
SELECT @RetValue

• 新增資料

CREATE PROC InsertData 
  @MemID int,
  @MemName nvarchar(10),
  @PW varchar(100)
AS
  OPEN SYMMETRIC KEY KeyDemo DECRYPTION BY CERTIFICATE CertDemo

  INSERT INTO PWData (MemID,MemName,PW,EncryptPW)
    VALUES(@MemID,@MemName,@PW,EncryptByKey(Key_GUID('KeyDemo'),@PW))

  CLOSE ALL SYMMETRIC KEYS
GO

-- 測試
EXEC InsertData @MemID = 4,@MemName = N'劉六',@PW = 'ABC'
SELECT * FROM PWData WHERE MemID = 4

• 變更密碼

CREATE PROC ChangePW 
  @MemID int,
  @NewPW varchar(100)
AS
  OPEN SYMMETRIC KEY KeyDemo DECRYPTION BY CERTIFICATE CertDemo

  UPDATE dbo.PWData
  SET PW = @NewPW ,
      EncryptPW = EncryptByKey(Key_GUID('KeyDemo'),@NewPW)
  WHERE MemID = @MemID

  CLOSE ALL SYMMETRIC KEYS
GO

-- 測試
EXEC dbo.ChangePW 1,'P@$$W0rd'
SELECT * FROM PWData

參考資料
• 加密資料行
• SQL Server 欄位資料加解密介紹與使用
• [SQL]為資料做加解密處理
• [SQL] 資料庫加密 - 使用 Master key、Certificate 與 Symmetric Key