GSS 技術部落格
在這個園地裡我們將從技術、專案管理、客戶對談面和大家分享我們多年的經驗，希望大家不管是喜歡或是有意見，都可以回饋給我們，讓我們有機會和大家對話並一起成長！
若有任何問題請來信：gss_crm@gss.com.tw
前言
常常有人看了 [SQL]為資料做加解密處理 透過SQL SERVER的加解密處理方式後，會問說，那AP要如何整合起來呢? 以下筆者會一步步介紹如何整合進系統。資料準備
使用 [SQL]為資料做加解密處理 的範例(資料表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] MONEY,
[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 MONEY) --將資料解密轉成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 );
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [BirthDay], [Salary])
VALUES (2, N'Lily', N'Kuo', '1970/10/10', 51000 );
--5.3.查詢員工的資料
SELECT * FROM Employee;
--5.4.透過解密的View來查詢員工資料
SELECT * FROM vwEN_Employee;
--5.5.關閉對稱金鑰
CLOSE SYMMETRIC KEY DB_KEY1;
實作
1.建立一個Windows Form應用程式，在Form上面拉了一些控制項，說明如下，
| bindingSource1 | 設定控制項的Binding |
| dataGridView1 | 顯示Employee的資料 |
| lblEmployeeId | 顯示Employee某筆資料的EmployeeID |
| txtSalary | 顯示Employee某筆資料的Salary |
| lblFirstName | 顯示Employee某筆資料的FirstName |
| btnGetEmployee | 取得vwEN_Employee的資料，但是沒有先OPEN SYMMETRIC KEY |
| btnGetEmployeeWithKey | 先OPEN SYMMETRIC KEY，取得vwEN_Employee的資料，最後再CLOSE SYMMETRIC KEY |
| btnUpdEmpWithKey | 先OPEN SYMMETRIC KEY，更新Employee的資料，最後再CLOSE SYMMETRIC KEY |
2.程式的初始設定及控制項的Binding設定
private string ConnectionString
{
get
{
return System.Configuration.ConfigurationManager.ConnectionStrings["DEMO"].ConnectionString;
}
}
private void SetControlBindings(DataTable employeeData){
bindingSource1.DataSource = employeeData;
dataGridView1.DataSource = bindingSource1;
txtSalary.DataBindings.Clear();
txtSalary.DataBindings.Add("Text", bindingSource1, "Salary");
lblEmployeeId.DataBindings.Clear();
lblEmployeeId.DataBindings.Add("Text", bindingSource1, "EmployeeID");
lblFirstName.DataBindings.Clear();
lblFirstName.DataBindings.Add("Text", bindingSource1, "FirstName");
}3.直接從vwEN_Employee取得資料，呈現到畫面上，如下。
private void GetEmployeeDataWithoutKey(){
string queryString = @"SELECT [EmployeeID], [FirstName], [LastName], [BirthDay], [Salary]
FROM vwEN_Employee";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter(queryString, conn);
DataTable employeeData = new DataTable();
da.Fill(employeeData);
SetControlBindings(employeeData);
}
MessageBox.Show("GetEmployeeDataWithoutKey OK!");
}
從上圖可發現，如果直接從vwEN_Employee取得資料時，BirthDay及Salary欄位都是NULL。
4.加入OPEN/CLOSE SYMMETRIC KEY。
因為直接從vwEN_Employee取得資料時，會因為沒有OPEN SYMMETRIC KEY，而導致BirthDay及Salary欄位都是NULL。所以在從vwEN_Employee取得資料前，先OPEN SYMMETRIC KEY，最後再CLOSE SYMMETRIC KEY，如下，
private void GetEmployeeDataWithKey(){
string queryString = @"SELECT [EmployeeID], [FirstName], [LastName], [BirthDay], [Salary]
FROM vwEN_Employee";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try {
conn.Open();
//1.要先下 OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker'; SqlCommand cmd = new SqlCommand(@"OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker'", conn);
cmd.ExecuteNonQuery();
//2.再取資料 SqlDataAdapter da = new SqlDataAdapter(queryString, conn);
DataTable employeeData = new DataTable();
da.Fill(employeeData);
SetControlBindings(employeeData);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally {
//3.最後，要把Key Close SqlCommand cmd = new SqlCommand(@"CLOSE SYMMETRIC KEY DB_KEY1", conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
MessageBox.Show("GetEmployeeDataWithKey OK!");
}
5.新增或更新資料前，也要加入OPEN/CLOSE SYMMETRIC KEY，如下，
private void UpdateEmployeeDataWithKey(){
string queryString = @"UPDATE Employee SET Salary = @Salary Where EmployeeID = @EmployeeID";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
try {
conn.Open();
//1.要先下 OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker'; SqlCommand cmd = new SqlCommand(@"OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker'", conn);
cmd.ExecuteNonQuery();
//2.再將資料更新 SqlCommand updCmd = new SqlCommand(queryString, conn);
updCmd.Prepare();
updCmd.Parameters.Clear();
var empIdParam = updCmd.Parameters.Add("EmployeeID", SqlDbType.Int);
empIdParam.Value = lblEmployeeId.Text;
var salaryParam = updCmd.Parameters.Add("Salary", SqlDbType.Money);
salaryParam.Value = txtSalary.Text;
updCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally {
//3.最後，要把Key Close SqlCommand cmd = new SqlCommand(@"CLOSE SYMMETRIC KEY DB_KEY1", conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
MessageBox.Show("UpdateEmployeeDataWithKey OK!");
}
6.將OPEN/CLOSE SYMMETRIC KEY抽出成另外的Method。
private void OpenSymmetricKey(SqlConnection conn){
//1.要先下 OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker'; SqlCommand cmd = new SqlCommand(@"OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker'", conn);
cmd.ExecuteNonQuery();
}
private void CloseSymmetricKey(SqlConnection conn){
//3.最後，要把Key Close SqlCommand cmd = new SqlCommand(@"CLOSE SYMMETRIC KEY DB_KEY1", conn);
cmd.ExecuteNonQuery();
}
結論
以上是透過Windows Form應用程式來示範如何應用SQL SERVER的加解密處理。Demo Code: DataEncryptionDemo.zip
本文也發表於亂馬客Blog
相關文章
評論
尚無評論