SQL 2008 資料加密

SQL 2008 資料加密
以下範例建立一個具有儲存加密，讀取解密的資料表

參考網址：
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