如何設計資料庫資料加密的機制

在某些狀況，我們會希望系統的資料加密，防止其它不明用意的人士偷偷進行資料變更，造成系統錯誤。那我們如何將目前資料庫的資料，進行加密同時系統不需要大幅度進行修改呢？

基本上有四個步驟：

• 建立資料庫非對稱式憑證 (記得設定密碼)
• 建立儲存加密資料的資料表
• 建立維護資料用的 Stored Procedure
• 建立檢視資料用的 View

建立資料庫非對稱式憑證

   1: CREATE ASYMMETRIC KEY 非對稱式金鑰

   2:     WITH ALGORITHM = RSA_2048

   3:     ENCRYPTION BY PASSWORD = 'KevinTsui';

   4: GO

你會在Microsoft SQL Server Management Studio 的Object Explorer裏看到下列結果：

建立儲存加密資料的資料表

選擇一個要加密的資料表，按右鍵產生相對應的T-SQL。如下圖所示：

將產生的CREATE TABLE Script中的Table Name 後面加上「_E」以區別這個Table是有應用加密技術。另外要儲存加密資料的欄位，無論是NVARCHAR, INT…，一律都改成NVARCHAR(255)，如下圖所示

建立維護資料用的 Stored Procedure

由於維護資料時，皆必須額外考慮加密的問題，就我的建議直接使用Stored Procedure處理會比較直覺，所有的人都只呼叫Stored Procedure，把明碼(畫面上的值直接傳入即可)，不需要再針對每一支需要變更此Table的程式都進行相關修改。Stored Procedure的寫法可以參考以下的範例，另外有一件事要特別注意：由於加密的欄位都修改成NVARCHAR(255)，為了保持程式的一致性，Stored Procedure傳入參數的部份，請依照原本的Table設計，該是NVARCHAR(10)，該是INT，該是SMALLINT，一律維持原樣：

 

   1: SET ANSI_NULLS ON

   2: GO

   3: SET QUOTED_IDENTIFIER ON

   4: GO

   5:  

   6:  

   7: CREATE PROCEDURE sw_ModifyProductModule 

   8:     @action_type    NCHAR(1) = '' ,

   9:     @productId        nvarchar(10) = '', 

  10:     @moduleId        NVARCHAR(10) = '',

  11:     @moduleCName    NVARCHAR(100) = '',

  12:     @moduleEName    NVARCHAR(10) = '',

  13:     @is_active        SMALLINT = 0

  14: AS

  15: BEGIN

  16:     SET NOCOUNT ON;

  17:  

  18:     --傳入資料檢核

  19:     IF (@action_type NOT IN ('A','D','U'))

  20:     BEGIN

  21:         DECLARE @DBID INT;

  22:         SET @DBID = DB_ID();

  23:  

  24:         DECLARE @DBNAME NVARCHAR(128);

  25:         SET @DBNAME = DB_NAME();

  26:  

  27:         RAISERROR

  28:             (N'資料庫 ID:%d, 資料庫名稱: %s。 錯誤訊息：傳入的Action_Mode，必須是 A, U, D 其中一種',

  29:             10, -- Severity.

  30:             1, -- State.

  31:             @DBID, -- First substitution argument.

  32:             @DBNAME); -- Second substitution argument.    

  33:     END

  34:  

  35:     --宣告加密用變數

  36:     DECLARE 

  37:         @encryp_productId        NVARCHAR(255),

  38:         @encryp_moduleId        NVARCHAR(255),

  39:         @encryp_moduleCName        NVARCHAR(255),

  40:         @encryp_moduleEName        NVARCHAR(255),

  41:         @encryp_is_active        NVARCHAR(255)

  42:  

  43:     SET @encryp_productId = ENCRYPTBYASYMKEY(ASYMKEY_ID('SwEncryKey100'),@productId)

  44:     SET @encryp_moduleId = ENCRYPTBYASYMKEY(ASYMKEY_ID('SwEncryKey100'),@moduleId)

  45:     SET @encryp_moduleCName = ENCRYPTBYASYMKEY(ASYMKEY_ID('SwEncryKey100'),@moduleCName)

  46:     SET @encryp_moduleEName = ENCRYPTBYASYMKEY(ASYMKEY_ID('SwEncryKey100'),@moduleEName)

  47:     SET @encryp_is_active = ENCRYPTBYASYMKEY(ASYMKEY_ID('SwEncryKey100'),CONVERT(NVARCHAR(1),@is_active))

  48:  

  49:     IF (@action_type = 'A')

  50:     BEGIN

  51:         INSERT INTO Production.SW_PRODUCT_MODULE_E

  52:         ( PRODUCT_ID ,

  53:           MODULE_ID ,

  54:           MODULE_CNAME ,

  55:           MODULE_ENAME ,

  56:           IS_ACTIVE 

  57:         )

  58:         VALUES  (             

  59:         @productId,

  60:         @moduleId,

  61:         @encryp_moduleCName,

  62:         @encryp_moduleEName,

  63:         @encryp_is_active

  64:         )

  65:          

  66:     END

  67:     

  68:     IF (@action_type = 'U')

  69:     BEGIN

  70:         UPDATE Production.SW_PRODUCT_MODULE_E

  71:         SET MODULE_CNAME = @encryp_moduleCName,

  72:             MODULE_ENAME = @encryp_moduleEName,

  73:             IS_ACTIVE = @encryp_is_active

  74:         WHERE PRODUCT_ID = @productId

  75:         AND MODULE_ID = @moduleId

  76:     END

  77:     

  78:     IF (@action_type = 'D')

  79:     BEGIN

  80:         DELETE Production.SW_PRODUCT_MODULE_E

  81:         WHERE PRODUCT_ID = @productId

  82:         AND MODULE_ID = @moduleId

  83:     END

  84:  

  85: END

  86: GO

接下來，我們來新增一筆資料

   1: EXEC dbo.sw_ModifyProductModule 

   2:     @action_type = 'A', -- nchar(1)

   3:     @productId = 'PWP', -- nvarchar(10)

   4:     @moduleId = 'Security', -- nvarchar(10)

   5:     @moduleCName = '權限控管模組', -- nvarchar(100)

   6:     @moduleEName = 'Security Module', -- nvarchar(10)

   7:     @is_active = 1 -- smallint  

   8:   

 

結果你會發現資料真的被加密了

那我們該如何讀取資料呢？

建立檢視用的View

為了讀取加密資料，同時又不想修改所有相關的SQL Statement，加上解密相關的語法，讓程式變得更複雜及更難維護，所以我會採取建立View的方式解決這個問題。

所以先來建立一個View

   1: USE [PWP_NEW]

   2: GO

   3:  

   4: /****** Object:  View [dbo].[vwSW_PRODUCT_MODULE]    Script Date: 01/06/2011 13:58:55 ******/

   5: SET ANSI_NULLS ON

   6: GO

   7:  

   8: SET QUOTED_IDENTIFIER ON

   9: GO

  10:  

  11:  

  12:  

  13: CREATE VIEW [Production].[vwSW_PRODUCT_MODULE]

  14: AS

  15:     SELECT     

  16:             DATAID, 

  17:             PRODUCT_ID, 

  18:             MODULE_ID,

  19:             CONVERT(NVARCHAR(10),DECRYPTBYASYMKEY(ASYMKEY_ID('SwEncryKey100'),MODULE_CNAME, N'KevinTsui')) AS MODULE_CNAME,

  20:             CONVERT(NVARCHAR(50),DECRYPTBYASYMKEY(ASYMKEY_ID('SwEncryKey100'), MODULE_ENAME, N'KevinTsui')) AS MODULE_ENAME,

  21:             CONVERT(SMALLINT,CONVERT(NVARCHAR(1), DECRYPTBYASYMKEY(ASYMKEY_ID('SwEncryKey100'), IS_ACTIVE, N'KevinTsui'))) AS IS_ACTIVE,

  22:             DATAFLAG

  23:     FROM Production.SW_PRODUCT_MODULE_E

  24:  

  25: GO

  26:  

  27:  

  28:  

  29:  

再來看一下執行結果

最後系統對應此架構做一個簡單的調整就可以執行了