[SQL]為資料做加解密處理

  • 8066
  • 0
  • SQL
  • 2013-01-07

[SQL]為資料做加解密處理

前言

有時我們需要將存到資料庫中的資料加密!

以下介紹如何使用SQL Server的加密機制來處理!

實作

以下用一個Employee的Table來說明整套加解密的流程。

Employee資料表中有員工的名稱、生日及薪資,其中生日及薪資需要將它加密存放!

 

1.產生對稱金鑰並設定密碼為 rainmaker

CREATE SYMMETRIC KEY DB_KEY1 WITH ALGORITHM = TRIPLE_DES 
    ENCRYPTION BY PASSWORD = 'rainmaker'
GO

 

2.建立測試的Employee TABLE

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Employee]') AND type IN (N'U')) 
BEGIN 
   DROP TABLE [Employee] 
END 
GO 

		
CREATE TABLE [Employee]( 
   [EmployeeID] [int] NOT NULL PRIMARY KEY, 
   [FirstName] VARCHAR(250) NOT NULL, 
   [LastName] VARCHAR(250) NOT NULL, 
   [BirthDay] DATE,
   [Salary] DECIMAL,
   [EN_BirthDay] VARBINARY(8000), --放BirthDay加密的資料
   [EN_Salary] VARBINARY(8000)  --放Salary加密的資料
)  
GO 

 

3.建立Trigger,存入資料時,將資料加密放到EN_開頭的欄位之中

CREATE TRIGGER TR_Employee
ON [Employee]
FOR INSERT, UPDATE
AS 
BEGIN
    --取得金鑰名稱
    DECLARE @KeyGUID AS UNIQUEIDENTIFIER
    SELECT @KeyGUID = KEY_GUID('DB_KEY1')
    
    --將資料放到加密欄位之中,並更新原本的欄位為其他值
    UPDATE [Employee]
        SET 
            [EN_BirthDay]= ENCRYPTBYKEY(@KeyGUID, CAST(i.BirthDay AS varbinary) )
            , BirthDay = '1970/10/10'
            , [EN_Salary]= ENCRYPTBYKEY(@KeyGUID, CAST(i.Salary AS varbinary) )
            , Salary = 0
            FROM inserted i 
                JOIN [Employee] ON(i.EmployeeID=[Employee].EmployeeID)
END;

 

4.建立將資料解密的View

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[vwEN_Employee]'))
DROP VIEW [vwEN_Employee]
GO

		
CREATE VIEW [vwEN_Employee]
AS
SELECT [EmployeeID], [FirstName], [LastName]
, [BirthDay] = CAST(DECRYPTBYKEY([EN_BirthDay]) AS DATE) --將資料解密轉成DATE
, [Salary] = CAST(DECRYPTBYKEY([EN_Salary]) AS DECIMAL) --將資料解密轉成DECIMAL
FROM [Employee];

 

5.開始測試

5.1.為對稱金鑰解密,讓它能夠使用。


		
OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker';

 

5.2.新增一筆員工資料

INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [BirthDay], [Salary]) 
VALUES (1, N'Eric', N'Lin', '1965/4/3', 47000 );

 

5.3.查詢員工的資料

SELECT * FROM Employee;

image

 

5.4.透過解密的View來查詢員工資料

SELECT * FROM vwEN_Employee;

image

image

 

5.5.關閉對稱金鑰

CLOSE SYMMETRIC KEY DB_KEY1;

 

結論

所以依上面的做法,AP只要儲存好要金鑰密碼,在需要讀取及寫入這些機敏資料時,先OPEN KEY,處理完成後,再CLOSE KEY。

而原本SELECT TABLE的方式,就改成SELECT VIEW。

這樣AP就不用花太多心力在資料的加解密上,而那些欄位要加解密控制在資料庫中的Trigger & View。

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^