Microsoft SQL Server 2016 安全新功能 胡百敬http://byronhu.wordpress.com 集英信誠資深合夥顧問

SQL Server 各版新增的安全功能 認證 授權 加密 稽核 管理 2005 2008 2012 2014 2016 Microsoft Ignite 2015 9/10/2017 7:36 AM SQL Server 各版新增的安全功能 認證 授權 加密 稽核 管理 2005 密碼原則 Execute as 提供認證(credential)與Agent Services 的 Proxy 新增結構描述 CLR 程式碼存取安全與資料庫TRUSTWORTHY 透過系統檢視呈現 提供標準加密、雜湊函數 使用自我簽署憑證強制網路傳輸啟用 SSL 登入稽核 介面區組態 DDL Trigger Logon Trigger(SP2+) 2008 BUILTIN\Administrator 安裝時不再內建 EKM 與 HSM 外部協力廠商安全性模組 透明資料加密 強化對稱式加密鑰匙 安全性稽核 原則式管理 中央管理伺服器 擴充事件 2012 自主資料庫支援自訂使用者 Windows Group 的 Login 可以設 Default Schema 使用者自行定義伺服器等級角色 強化 Hash 演算法(多 SHA2_256、SHA2_512) 所有版本都支援伺服器等級稽核 虛擬服務帳號 2014 增加伺服器等級權限 備份加密 2016 記錄層級安全 增加資料庫等級權限 一律加密 動態遮罩 發揮 CPU 加解密的指令集以提升效能 透過 Temporal 可追蹤資料變化 SQL Server 2005 可以 disable 或 rename sa 帳號 SQL Server 2008 可延伸金鑰管理 (EKM) 在 Enterprise、Developer 和 Evaluation 版都有提供。EKM 可讓企業金鑰管理與硬體 安全性模組 (HSM) 解決方案的協力廠商，在 SQL Server 註冊他們的裝置。這些裝置一經註冊，使用者即可 使用儲存在這些模組上面的加密金鑰。 The Windows groups that are created for use by the SQL Server service, such as SQLServerMSSQLUser$ COMPUTERNAME $ INSTANCENAME and SQLServerSQLAgentUser$ COMPUTERNAME $ INSTANCENAME, are no longer included in the sysadmin fixed server role. Instead, the service account that is used to start the SQL Server service and SQL Server Agent service are individually granted sysadmin rights in SQL Server. When SQL Server is installed on the Windows Server 2008 or Windows Vista operating systems, a service SID is provisioned as a member of the sysadmin fixed server role. For more information, see Setting Up Windows Service Accounts. SQL Server 2014 新增的伺服器等級權限：CONNECT ANY DATABASE、IMPERSONATE ANY LOGIN、SELECT ALL USER SECURABLES SQL Server 2005 開始 "secure by design, secure by default, and secure in deployment” 並提供憑證、簽章、驗證… © 2015 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

SQL Azure DB支援 TDE, TDE 效能 Microsoft Ignite 2015 9/10/2017 7:36 AM 強化安全 Always Encrypted 強化加密 SQL Azure DB支援 TDE, TDE 效能 SQL Azure DB支援階層鑰匙加密 加密 開發 應用程 式安全 稽核 記錄層級安全 CLE for SQL DB：Azure SQL Database 提供 TDE 借助 Intel AES-NI：http://www.intel.com.tw/content/www/tw/zh/architecture-and-technology/advanced- encryption-standard--aes-/data-protection-aes-general-technology.html，硬體的加解密指令，加快加解密 速度 Power BI 可以連到 SQL Azure 稽核資料，分析與繪製圖表 美國的 Ignite 說 SQL Server 2016 的 Audit 會增加交易成功失敗，但找不到文件說明 強化 SQL Azure稽核 動態資料遮罩 © 2015 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

記錄層級安全 SQL Server 2016 SQL Database

客戶效益 細緻地存取控制 應用程式透通 集中的安全邏輯 9/10/2017 客戶效益 細緻地存取控制 藉由限制不同使用者使用相同資料表的記錄存取權限，確保多租戶使用的資料庫安全 應用程式透通 記錄層級安全(Row Level Security RLS) 在查詢時期透通地作業，不須修改應用程式。可與各領域的重要系統整合 集中的安全邏輯 強制控制權限的商業邏輯放在資料庫內，並與被保護的資料表繫結其結構(schema-bound)以提供較佳的安全。減少程式的維護與複雜性 將限制存取資料的邏輯做在DB端而非應用程式端 以使用者的執行內容（Execution context）來過濾資料列，而不是以使用者本身所擁有的權限 單一資料庫/資料表有多個使用者存取，憑各使用者可取得資料的範圍，以記錄層級安全同時限制讀/寫權限 © 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

記錄層級安全概念 判斷函數(Predicate function) 安全判斷述詞(Security predicate) 使用者定義內嵌資料表值函數(inline table-valued function iTVF)實作安全邏輯 可以實作複雜的邏輯，包含連結其他的資料表 安全判斷述詞(Security predicate) 賦予判斷函數到特定的資料表(賦予 SEMIJOIN) 兩種應用情境：filter predicates 和 blocking predicates 安全原則(Security policy) 安全判斷所成的集合，用來管理多個資料表的安全 CREATE TABLE Sales ( OrderID int, SalesRep sysname, Product varchar(10), Qty int ); INSERT Sales VALUES (1, 'Sales1', 'Valve', 5), (2, 'Sales1', 'Wheel', 2), (3, 'Sales1', 'Valve', 4), (4, 'Sales2', 'Bracket', 2), (5, 'Sales2', 'Wheel', 5), (6, 'Sales2', 'Seat', 5); GRANT SELECT ON Sales TO Manager; GRANT SELECT ON Sales TO Sales1; GRANT SELECT ON Sales TO Sales2; CREATE SCHEMA Security; CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager'; CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) ON dbo.Sales WITH (STATE = ON); ALTER SECURITY POLICY SalesFilter WITH (STATE = OFF); http://blogs.msdn.com/b/sqlsecurity/archive/2015/03/23/row-level-security-blocking-unauthorized- inserts.aspx CREATE SECURITY POLICY mySecurityPolicy ADD FILTER PREDICATE dbo.fn_securitypredicate(wing, startTime, endTime) ON dbo.patients, ADD FILTER PREDICATE…

記錄層級安全三步驟 安全 規範 二. 應用程式使用者 (例如：護士) 查詢 Patients 資料表 一. Microsoft Research 2013 9/10/2017 7:36 AM 記錄層級安全三步驟 二. 應用程式使用者 (例如：護士) 查詢 Patients 資料表 一. 規範管理員以 T-SQL 建立過濾用的判斷函數和安全規範，繫結判斷函數到 Patients 資料表 三. 安全規範通透地重寫查詢以套用過濾函數 護士 規範管理者 資料庫 過濾條件： INNER JOIN… 安全 規範 CREATE FUNCTION dbo.fn_securitypredicate(@wing int) RETURNS TABLE WITH SCHEMABINDING AS return SELECT 1 as [fn_securitypredicate_result] FROM StaffDuties d INNER JOIN Employees e ON (d.EmpId = e.EmpId) WHERE e.UserSID = SUSER_SID() AND @wing = d.Wing; CREATE SECURITY POLICY dbo.SecPol ADD FILTER PREDICATE dbo.fn_securitypredicate(Wing) ON Patients WITH (STATE = ON) 應用程式 Patients SELECT * FROM Patients SEMIJOIN APPLY dbo.fn_securitypredicate(patients.Wing); SELECT * FROM Patients SELECT Patients.* FROM Patients, StaffDuties d INNER JOIN Employees e ON (d.EmpId = e.EmpId) WHERE e.UserSID = SUSER_SID() AND Patients.wing = d.Wing; © 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Demo 記錄層級安全

一律加密 Always Encrypted SQL Server 2016 SQL Database

客戶效益 避免資料外洩 查詢加密的資料 應用程式透通 9/10/2017 客戶效益 避免資料外洩 機密資料在使用者端加密，使用的鑰匙決不會讓資料庫系統取得 查詢加密的資料 支援相等比對、包含、連結、群組、唯一等運算 應用程式透通 透過強化伺服器和前端程式資料庫，讓應用程式改寫最少 但仍要小心的是，銀行的保險箱被破壞了，你的資產也沒了，如同資料庫毀了，你的資料依然毀了。 讓客戶在信賴範圍之外，安全地存放機密資料。如同在銀行的保險箱存放你的 資產，而鑰匙在你手上 即便有高權限者如 DBA，依然無法存取資料 © 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

SQL Server 或 SQL Database 運作原理 已加密的機密資料和相關的鑰匙， 在 SQL Server 完全看不到原始文字 SQL Server 或 SQL Database 信任邊界 使用者端 "SELECT Name FROM Customers WHERE SSN = @SSN","111-22-3333" "SELECT Name FROM Customers WHERE SSN = @SSN", 0x7ff654ae6d 加密文字 ADO .NET 原始文字 結果集 結果集 Name 張三 Name 0x19ca706fbd9a dbo.Customers Name SSN Country 0x19ca706fbd9a 0x7ff654ae6d USA 加密文字

加密方式(這一版) 動態加密(RANDOMIZED) 固定加密(DETERMINISTIC) Microsoft Ignite 2015 9/10/2017 7:36 AM 加密方式(這一版) 動態加密(RANDOMIZED) Encrypt('123-45-6789') = 0x17cfd50a 再一次：Encrypt('123-45-6789') = 0x9b1fcf32 允許通透地取回加密資料，但無法比較 更安全，尤其針對整欄變化少的資料 固定加密(DETERMINISTIC) Encrypt('123-45-6789') = 0x85a55d3f 再一次：Encrypt('123-45-6789') = 0x85a55d3f 允許通透地取回加密資料，可以做相等比較 例如：在 WHERE 子句以及 joins、distinct、group by Always Encrypted 加密後的資料無法做 >、<、Like 這一類的運算 © 2015 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

欄位加密鍵(Column Encryption Key 準備鑰匙 1. 產生 CEKs 和 Master Key 欄位加密鍵(Column Encryption Key CEK) 欄位主鍵(Column Master Key CMK) 2. 加密 CEK 加密 CEK CMK 儲存區: 憑證儲存區 HSM Azure 鑰匙儲存區 … CMK 3. 安全地儲存主鍵 安全官 4. 將加密後的 CEK 放到 DB 加密後的 CEK 資料庫

Demo Always Encrypted

既有資料套用 Always Encrypted Microsoft Ignite 2015 9/10/2017 7:36 AM 既有資料套用 Always Encrypted 透過 ETL 工具搭配 .NET Framework 4.6 的 Column Encryption Setting 連接設定轉進資料 http://blogs.msdn.com/b/sqlsecurity/ http://blogs.msdn.com/b/sqlsecurity/archive/2015/07/28/encrypting-existing-data-with-always-encrypted.aspx © 2015 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

動態資料遮罩 SQL Server 2016 SQL Database

動態資料遮罩 符合法規 保護機密資料 強烈需求應用程式符合主管機關建議的隱私規範(privacy standards) 9/10/2017 動態資料遮罩 符合法規 強烈需求應用程式符合主管機關建議的隱私規範(privacy standards) 保護機密資料 保護機密資料，避免應用程式造成未經授權地存取 © 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

動態資料遮罩 admin1 登入 其他的登入 安全官 3) 動態資料遮罩依規範將回傳結果中的機密資料編碼 Microsoft Research 2013 9/10/2017 7:36 AM 動態資料遮罩 3) 動態資料遮罩依規範將回傳結果中的機密資料編碼 2) 應用程式依使用者身分查詢員工資料表 1) 安全官(Security officer) 針對員工資料表內的機密資料，以 T-SQL 定義動態資料遮罩的規範 ALTER TABLE [Employee] ALTER COLUMN [SocialSecurityNumber] ADD MASKED WITH (FUNCTION = 'SSN()') ALTER TABLE [Employee] ALTER COLUMN [Email] ADD MASKED WITH (FUNCTION = 'EMAIL()') GRANT UNMASK to admin1 SELECT Patients.* FROM Patients, StaffDuties d INNER JOIN Employees e ON (d.EmpId = e.EmpId) WHERE e.UserSID = SUSER_SID() AND Patients.wing = d.Wing; 安全官 admin1 登入 其他的登入 SELECT [Name], [SocialSecurityNumber], [Email], [Salary] FROM [Employee] SELECT * FROM patients SEMIJOIN APPLY dbo.fn_securitypredicate(patients.Wing); © 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Demo 動態資料遮罩

新的權限 資料庫等級權限 權限名稱 說明 ALTER ANY SECURITY POLICY 維護記錄層級權限 ALTER ANY MASK Microsoft Ignite 2015 9/10/2017 7:36 AM 新的權限 資料庫等級權限 權限名稱 說明 ALTER ANY SECURITY POLICY 維護記錄層級權限 ALTER ANY MASK 維護動態資料遮罩 UNMASK 檢視未被遮罩的原始資料 ALTER ANY COLUMN ENCRYPTION KEY、VIEW ANY COLUMN ENCRYPTION KEY、ALTER ANY COLUMN MASTER KEY DEFINITION 和 VIEW ANY COLUMN MASTER KEY DEFINITION 維護 Always Encrypted ALTER ANY EXTERNAL DATA SOURCE 和 ALTER ANY EXTERNAL FILE FORMAT 維護 Analytics Platform System 使用的外部資源 http://social.technet.microsoft.com/wiki/contents/articles/11842.sql-server-database-engine-permission- posters.aspx https://msdn.microsoft.com/en-us/library/ms191291.aspx https://msdn.microsoft.com/en-US/library/bb510411.aspx#Perms © 2015 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

不限身分 【TechDays期間攤位活動】 @二樓北側走廊 ★活動任務★ 學生、社會人士 IT Pro、Developer 初學者都可以參加 參加 資格 活動 內容 論壇發問 看影片學新知 撰寫部落格文章 開發UWP作品 超值 好禮 Surface Pro3 Xbox One 現金5000元 每月抽40名限量好禮 【TechDays期間攤位活動】 @二樓北側走廊 ★活動任務★ 在微軟MSDN/TechNet/Office365指定論壇發問一則，並註冊微軟社群之星學院。 ★抽獎資格★ 完成任務後向攤位工作人員領取抽獎編號。 每天下午4:20於攤位抽出20名學員贈送精美獎品!

歡迎上微軟MSDN, TechNet , Office365 論壇提問！ 熱心助人的專家幫忙解答 產品 項目 URL Microsoft Azure Azure 網站服務 http://aka.ms/tw-f-azureall Azure SQL 資料庫服務 Azure 虛擬機器 (IaaS) Azure 雲端服務 (PaaS) Windows Server 2012 Windows Server 2012 R2 / Server 2012 專區 http://aka.ms/tw-f-winserver2012 Windows Store App Windows 市集應用程式 http://aka.ms/tw-f-winstoreapp Windows 10 http://aka.ms/tw-f-w10 Office 365 http://aka.ms/tw-f-o365 MSDN/ TechNet/ Office365 Solutions, Product, Service Expert, Community Leader