Stored Procedure 加密(with encryption)
with encryption
在某些狀況，我們會希望系統的資料加密，防止其它不明用意的人士偷偷進行資料變更，造成系統錯誤。那我們如何將目前資料庫的資料，進行加密同時系統不需要大幅度進行修改呢？
基本上有四個步驟：
- 建立資料庫非對稱式憑證 (記得設定密碼)
- 建立儲存加密資料的資料表
- 建立維護資料用的 Stored Procedure
- 建立檢視資料用的 View
1. 建立資料庫非對稱式憑證
CREATE ASYMMETRIC KEY 非對稱式金鑰
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = '1234567890';
GO
你會在Microsoft SQL Server Management Studio 的Object Explorer裏看到下列結果：
2.建立儲存加密資料的資料表
將產生的CREATE TABLE Script中的Table Name 後面加上「_E」以區別這個Table是有應用加密技術。另外要儲存加密資料的欄位，無論是NVARCHAR, INT…，一律都改成NVARCHAR(255)，如下圖所示
3.建立維護資料用的 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:
結果資料被加密
4.建立檢視用的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
執行結果:
'
