iT邦幫忙

2017 iT 邦幫忙鐵人賽
DAY 8
1
Security

資料安全與簡單加密演算法見面會 系列 第 8

[Day08] 資料儲存安全(SQL Always Encrypted)下

Day07 資料儲存安全(SQL Always Encrypted),我們把SQL一律加密(Always Encrypted)的環境準備好了,今天來從DB Server端觀察實際AP Server寫入資料及查詢資料的條件值。

記得今年3月老師介紹當時,還只能使用.NET Framework 4.6來加解密資料,半年後,新版的SQL管理工具(SSMS)也支援加密輸入並解密結果集(resultset)了,我們今天一起來試試。

.NET程式端寫入資料


這邊我們使用ADO.NET Sqlcommand。

先打開SQL Server Profiler程式

http://ithelp.ithome.com.tw/upload/images/20161223/20103434xvSd5CVPKX.jpg

啟動SQL Server Profiler

http://ithelp.ithome.com.tw/upload/images/20161223/20103434OLcf6O8tFs.jpg

執行.NET程式,關鍵有兩個:

  • Connectionstring中加了一組參數Column Encryption Setting=Enabled
  • .NET專案目標framework必須為.NET 4.6
using (SqlConnection conn = new SqlConnection(@"Data Source=資料庫;Initial Catalog=SecurityDB;User Id=帳號;Password=密碼;Column Encryption Setting=Enabled"))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("", conn))
    {
        cmd.CommandText = @"INSERT INTO [dbo].[Patients] ([Name],[SSN],[Country]) VALUES (@Name, @SSN, 'USA');";

        SqlParameter paramSSN = cmd.CreateParameter();
        paramSSN.ParameterName = @"@SSN";
        paramSSN.DbType = DbType.AnsiStringFixedLength;

        SqlParameter paramName = cmd.CreateParameter();
        paramName.ParameterName = @"@Name";
        paramName.DbType = DbType.AnsiStringFixedLength;
        
        //Jane Doe
        cmd.Parameters.Clear();
        paramName.Value = "Jane Doe";
        cmd.Parameters.Add(paramName);
        paramSSN.Value = "198-33-0986";
        cmd.Parameters.Add(paramSSN);
        cmd.ExecuteNonQuery();
    }
}

目標framework必須為.NET 4.6
http://ithelp.ithome.com.tw/upload/images/20161223/20103434LhEy0l2fIq.jpg

執行程式完畢後,打開SSMS(SQL Server Management Studio),執行SQL查詢

SELECT * from [dbo].[Patients]

SQL Server回傳的結果集也是加密的。

http://ithelp.ithome.com.tw/upload/images/20161223/20103434tqsqedQ7f3.png

觀察SQL Server Profiler:

http://ithelp.ithome.com.tw/upload/images/20161223/20103434V9AMYQDXuu.jpg

傳送過來的SSN社會安全編號就是加密過後的,這邊算是確保傳輸過程也是加密的。

.NET端查詢資料


剛剛使用ADO.NET Sqlcommand,這次改用輕量化的ORM利器Dapper。

執行.NET程式

 //使用Dapper查詢
using (SqlConnection conn = new SqlConnection(@"Data Source=資料庫;Initial Catalog=SecurityDB;User Id=帳號;Password=密碼;Column Encryption Setting=Enabled"))
 {
     //成功
     string query = "select * from Patients where SSN = @SSN  ";
     //一定要參數化+指定型別才能查詢
     var Results = db.Query<patient>(query, new { SSN = new DbString() { Value = "198-33-0986", IsAnsi = true, Length = 11 } });
     //顯示資料
     foreach (var item in Results)
     {
         Console.WriteLine("Name:{0}   SSN:{1}", item.Name, item.SSN, item.Country);
     }
 }

執行查詢時,DB會回傳已加密的結果集,資料會在AP端自動解密,執行結果:

http://ithelp.ithome.com.tw/upload/images/20161223/20103434dMq8ogIxHu.jpg

觀察SQL Profiler:
http://ithelp.ithome.com.tw/upload/images/20161223/20103434QRzOdzTLdb.jpg

傳送過來的SSN社會安全編號也是加密過後的,確保傳輸過程也是加密的。

SSMS(SQL Server Management Studio)查詢資料


首先需要安裝SSMS 17.0版本以上

下載 SQL Server Management Studio (SSMS)

安裝中
http://ithelp.ithome.com.tw/upload/images/20161223/201034344OfI9tnVjx.jpg

安裝完畢後,打開SSMS,點開選項
http://ithelp.ithome.com.tw/upload/images/20161223/2010343423rV2NxmII.jpg

連接屬性 勾選加密連接
http://ithelp.ithome.com.tw/upload/images/20161223/201034346m7v92IviT.jpg

選取 [其他屬性] 索引標籤,然後輸入 Column Encryption Setting=Enabled

http://ithelp.ithome.com.tw/upload/images/20161223/20103434q01R8DrUTO.jpg

執行SQL查詢

SELECT * from [dbo].[Patients]

噹噹噹噹!SSN終於可以看得見了!
http://ithelp.ithome.com.tw/upload/images/20161223/20103434AaueyuaBZW.jpg

不過前提是使用SSMS的機器還是有安裝Day07 資料儲存安全(SQL Always Encrypted)上所製作的憑證

SSMS(SQL Server Management Studio)寫入資料


可以查詢資料,那來試試寫入資料!

從SSMS輸入以下指令,一律加密的資料行SSN社會安全編號必須以參數方式帶入。

DECLARE @SSN  CHAR(11) = '198-33-0987'
INSERT INTO [dbo].[Patients] (Name,SSN,Country)
VALUES ('Jim Gray',@SSN  ,'USA')

DECLARE @SSN2  CHAR(11) = '198-33-0988'
INSERT INTO [dbo].[Patients] (Name,SSN,Country)
VALUES ('John smith',@SSN2 ,'USA')

SELECT * FROM [Patients]

執行結果:

http://ithelp.ithome.com.tw/upload/images/20161223/20103434094bjtpBsD.png

按照圖示來查詢微軟科學家jim gray(致力貢獻資料庫及交易處理研究,1998年獲得圖靈獎)

http://ithelp.ithome.com.tw/upload/images/20161223/20103434RbD53fy3M2.png

查詢條件也必須輸入參數。

DECLARE @SSN  CHAR(11) = '198-33-0987'
SELECT * FROM [Patients]
WHERE SSN = @SSN

http://ithelp.ithome.com.tw/upload/images/20161223/20103434uoh87xTtg9.jpg

一律加密(Always Encrypted)的優缺點


優點:

  • 傳輸過程加密,儲存加密。
  • 與資料庫分開保管資料、金鑰(CEK)及主要金鑰(CMK)。
  • 不需要改動AP程式碼邏輯,只需要調整連線字串參數,
  • 新版SSMS也支援查詢解密及輸入加密功能。
  • 由於SQL參數化的徹底要求,可以避免SQL Injection

缺點:

  • 設定步驟較多。
  • SQL Server 2016才開始支援。

參考:


下載 SQL Server Management Studio (SSMS)

使用 SQL Server Management Studio 設定永遠加密

Configure Always Encrypted using SQL Server Management Studio


柳川
http://ithelp.ithome.com.tw/upload/images/20161223/201034347k8EVegfce.jpg
2016.04攝於柳川,九州


上一篇
[Day07] 資料儲存安全(SQL Always Encrypted)上
下一篇
[Day09] 資料儲存安全(磁碟加密BitLocker )
系列文
資料安全與簡單加密演算法見面會 30

尚未有邦友留言

立即登入留言