[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;
5.4.透過解密的View來查詢員工資料
SELECT * FROM vwEN_Employee;
5.5.關閉對稱金鑰
CLOSE SYMMETRIC KEY DB_KEY1;
結論
所以依上面的做法，AP只要儲存好要金鑰密碼，在需要讀取及寫入這些機敏資料時，先OPEN KEY，處理完成後，再CLOSE KEY。
而原本SELECT TABLE的方式，就改成SELECT VIEW。
這樣AP就不用花太多心力在資料的加解密上，而那些欄位要加解密控制在資料庫中的Trigger & View。
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
