以下範例建立一個具有儲存加密,讀取解密的資料表
參考網址:
http://dotnetframework.blogspot.tw/2011/01/blog-post.html
http://www.dotblogs.com.tw/dc690216/archive/2009/09/10/10559.aspx
1.建立非對稱金鑰。指定name=mykey, pwd=p@ssw0rd
CREATE ASYMMETRIC KEY mykey WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'p@ssw0rd'; GO
2.查詢第1步驟所建立的key是否成功。
select * from sys.asymmetric_keys
3.建立測試資料表
4.建立新增、更新、刪除資料表的預存程序。
USE [EIP_new] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_BPM_Modify_t_BPM_Seal_type_E] @action_type NCHAR(1) = '' , @id INT = 0, @username NVARCHAR(50) = '', @uid NVARCHAR(10) = '', @pwd NVARCHAR(30) = '', @sort INT = 0, @cdt DateTime AS BEGIN SET NOCOUNT ON; --傳入資料檢核 IF (@action_type NOT IN ('A','D','U')) BEGIN DECLARE @DBID INT; SET @DBID = DB_ID(); DECLARE @DBNAME NVARCHAR(128); SET @DBNAME = DB_NAME(); RAISERROR (N'資料庫 ID:%d, 資料庫名稱: %s。 錯誤訊息:傳入的Action_Mode,必須是 A, U, D 其中一種', 10, -- Severity. 1, -- State. @DBID, -- First substitution argument. @DBNAME); -- Second substitution argument. END --宣告加密用變數 DECLARE @encryp_uid NVARCHAR(max), @encryp_pwd NVARCHAR(max), @encryp_sort NVARCHAR(max) --SET @encryp_uid = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),@uid) SET @encryp_uid = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(nvarchar(max), @uid)) --SET @encryp_pwd = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),@pwd) SET @encryp_pwd = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(nvarchar(max), @pwd)) SET @encryp_sort = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(NVARCHAR(10),@sort)) SET @encryp_uid = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(nvarchar(max), @uid)) --插入 IF (@action_type = 'A') BEGIN INSERT INTO t_BPM_Seal_type_E ( username , [uid] , pwd , sort , cdt ) VALUES ( @username, @encryp_uid, @encryp_pwd, @encryp_sort, @cdt ) END --更新 IF (@action_type = 'U') BEGIN UPDATE t_BPM_Seal_type_E SET username=@username, [uid]=@encryp_uid, pwd=@encryp_pwd, cdt=@cdt WHERE id=@id END --刪除 IF (@action_type = 'D') BEGIN DELETE t_BPM_Seal_type_E WHERE id=@id END END GO
5.建立檢視資料表,用來讀取被加密的資料表
CREATE VIEW [dbo].[v_BPM_Seal_type_E] AS SELECT id, username, CONVERT(NVARCHAR(50), DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), uid, N'p@ssw0rd')) AS uid, CONVERT(NVARCHAR(50), DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), pwd, N'p@ssw0rd')) AS pwd, CONVERT(NVARCHAR(50), DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), sort, N'p@ssw0rd')) AS sort, cdt FROM dbo.t_BPM_Seal_type_E
6.最後查詢被加密的資料表。
上面是未解密原資料表,下面是已解密的檢視(使用第5步驟建立的檢視表)
7.直接查詢,不透過檢視表(View)
Select id, ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'), CONVERT(nvarchar(max), username)) as '加密' From dbo.t_BPM_Seal_type_E
Select id, CONVERT(nvarchar(10), DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), uid, N'p@ssw0rd')) as '解密' From dbo.t_BPM_Seal_type_E
沒有留言:
張貼留言