Microsoft TechNet 技術講座--資料庫技術升級系列三 精誠公司恆逸資訊 楊先民 SQL Server 2005 資料 的安全存取 精誠公司恆逸資訊 楊先民

上次回家作業解答 Q1: 在資料庫鏡像中，鏡像伺服器可否用來擔任報表的資料來源？ Q2: 如何增加備份媒體的可靠度？ 需搭配資料庫快照 利用鏡像備份，同時儲存多個備份

Microsoft TechNet 技術講座--資料庫技術升級系列三 大綱 帳號密碼安全性原則 使用者與結構定義分開 不同權限檢視不同的中繼資料 Execution Context SQL Injection Replication安全性設定 Agent、Database Mail安全性設定

Microsoft TechNet 技術講座--資料庫技術升級系列三 大綱—續 Application Role安全設定 利用 Stored Procrdure、View增進安全 Audit審核的機制 利用 DDL Trigger審核 SQL事件 SQL Server自訂帳號對應到 Windows帳號 支援憑證的對稱，非對稱加密 預設關閉各選項功能 限制 .NET程式碼執行

可賦予存取權限的物件(Securables) 帳號密碼安全性原則 可賦予存取權限的物件(Securables) 權限(Permissions) Principals Windows 群組 Files 網域使用者帳戶 ACL Registry Keys 本機使用者帳戶 Windows GRANT/REVOKE/DENY CREATE ALTER DROP CONTROL CONNECT SELECT EXECUTE UPDATE DELETE INSERT TAKE OWNERSHIP VIEW DEFINITION BACKUP SQL Server 登入帳戶 伺服器 伺服器角色 SQL Server 資料庫 User 資料庫角色 Schema Application Role Database Group

Microsoft TechNet 技術講座--資料庫技術升級系列三 安全性的基本 驗證(authentication) 檢查是否為正確的使用者 有 Windows驗證與 SQL驗證 授權(authorization) 給與使用者權力 利用 Fixed Role來設定 owner所建立的物件，該 owner有完全的權力

Microsoft TechNet 技術講座--資料庫技術升級系列三 2驗證(authentication) Windows驗證 需要有 Windows帳號或 AD帳號 SQL Server要允許登入(Grant) 適用在區域網路 SQL 驗證 只需 SQL Server允許登入即可 適用在前端非 Windows環境 程式撰寫連線資料庫 資料庫使用者(Database User) 使用資料庫前的使用者驗證

Microsoft TechNet 技術講座--資料庫技術升級系列三 設定帳號密碼的 policy SQL 驗證的密碼可設定 policy SQL Server 2005新增功能 需搭配 Windows 2003 Server 設定密碼的強度 密碼內容長度 密碼的複雜度 密碼過期的週期 99% = 87.6 hours = 3 days and 15.6 hours 99.9 % = 8.76 hours 99.99% = 52.56 minutes 99.999% = 5.26 minutes

Microsoft TechNet 技術講座--資料庫技術升級系列三 Demo 利用 Create指令建立登入帳號 設定 SQL驗證的 policy 第一次登入 SQL Server時，需要改變密碼

2授權(authorization) 給予權力 Server等級權力 Database等級權力 Schema等級權力 Object等級權力 可設定在 login帳號與 database user Server等級權力 建立DDL權力 修改 Database權力 Database等級權力 Schema等級權力 Object等級權力

Microsoft TechNet 技術講座--資料庫技術升級系列三 管理權限 如何管理 Server的權限 如何管理 Server-Scope 的權限 如何管理 Database的權限 如何管理 Database-Scope的權限 如何管理 Schema-Scope的權限

Microsoft TechNet 技術講座--資料庫技術升級系列三 如何管理 Server的權限 在 Object Explorer 連接伺服器 1 按登入帳號右鍵選擇屬性 2 Securables頁面，選擇 Add Objects 3 增加 <servername> 4 指定權限 5 USE master GRANT ALTER ANY DATABASE TO [SERVERX\Bill]

Microsoft TechNet 技術講座--資料庫技術升級系列三 如何管理Server-Scope權限 Server-scope 安全 HTTP endpoints Certificates USE master GRANT CONNECT ON HTTP ENDPOINT :: AWWebService TO [SERVERX\Bill]

Microsoft TechNet 技術講座--資料庫技術升級系列三 如何管理 Database權限 在 Object Explorer 1 連接伺服器 2 按資料庫右鍵選擇屬性 3 Permissions分頁 4 指定權限 USE AdventureWorks GRANT ALTER ANY USER TO Ted

如何管理Database-Scope 權限 Microsoft TechNet 技術講座--資料庫技術升級系列三 如何管理Database-Scope 權限 在 Object Explorer 連接伺服器 1 展開物件 2 按物件右鍵選擇屬性 3 4 Permissions分頁 The MSCS service account must be a part of the administrator group on each node of the cluster for SQL Server. The IsAlive thread runs under the context of the cluster service account, and not the SQL Server service account. If you remove the MSCS service account, the IsAlive thread will no longer be able to create a trusted connection, and you will lose access to the virtual server. 指定權限 5 USE AdventureWorks GRANT SELECT ON SCHEMA :: sales TO Ted

如何管理Schema-Scope 權限 1 2 3 4 5 在 Object Explorer 連接伺服器 展開物件 按物件右鍵選擇屬性 Permissions分頁 指定權限 5 USE AdventureWorks GRANT SELECT ON sales.orders TO Ted USE AdventureWorks GRANT EXECUTE ON TYPE :: addressType TO Ted

Microsoft TechNet 技術講座--資料庫技術升級系列三 Demo 授權給 login id，讓它可以登入 SQL Server 授權給資料庫使用者，讓它對 create table有權力(設定database權力)

使用者與 Schema 的分離 SQL Server 2000 SQL Server 2005 只有 dbo.object名稱 Server.database.dbo.object SQL Server 2005 支援 schema 原本的 dbo，置換成 schema

Microsoft TechNet 技術講座--資料庫技術升級系列三 用來解決物件使用的問題 Table View SP Function User 2 Database Object Owned By User 名稱解析問題 Eg: Select * from Foo CallingUser.foo Dbo.foo 刪除使用者也許會造成應用程式需要改變!

Microsoft TechNet 技術講座--資料庫技術升級系列三 刪除使用者 => 應用程式重寫 SELECT custID FROM User1.OrdersTable Owning user = User1 SCHEMA = User1 App1 Owning user = User2 SCHEMA = User2 App1 Modified OrdersTable SELECT custID FROM User2.OrdersTable Exec User1.InsertOrderProc (@orderid) App2 InsertOrderProc App2 modified Exec User2.InsertOrderProc (@orderid)

Microsoft TechNet 技術講座--資料庫技術升級系列三 解決方法Schema與 User分離 Table View Stored Proc Function User 2 Owned by Owned By Database Object contained in Owned by Schema User Most of the Availability Features fall within the Upgrade Immediate and Minimal Work to Leverage categories… But there are many other features to work with once you’ve designed your database to stay available. NOTE: The SQLCLR, Service Broker, etc. bullets appear on click. Mention that many of these features will be discussed in other sessions……. 刪除使用者不會影響到應用程式的改寫!!

可以利用 Default Schema解決問題 Microsoft TechNet 技術講座--資料庫技術升級系列三 可以利用 Default Schema解決問題 Table View Stored Proc Function User 2 Owned by contained in Schema Name Resolution Select * from foo S1. foo Dbo.foo Default Schema User1 User2 Default Schema S1 User3

Demo 設定 schema 設定 default schema 利用 schema完成資料存取

不同權限檢視不同的中繼資料 查詢靜態的中繼資料 查詢動態的中繼資料 使用者不可直接存取系統資料表 <資料庫名稱>\Views\System Views目錄 SELECT * FROM sys.tables 查詢動態的中繼資料 SELECT * FROM sys.db_tran_locks 使用者不可直接存取系統資料表 不同權限的使用者所得到的 Metadata結果不同 可設定不能讀取 store procedure的定義

Demo 不同權限檢視的 Metadata也有所不同 故意刪除沒有權力刪除的資料表 SELECT * FROM sys.tables 故意刪除沒有權力刪除的資料表 會出現資料表不存在或是沒有權限 可以設定 deny看 store procedure定義的權力

Execution Context 與 Broken Ownership Chaining Microsoft TechNet 技術講座--資料庫技術升級系列三 Execution Context 與 Broken Ownership Chaining 預存程序或自訂函數 建立者 John SELECT * FROM sales.orders ？ 資料庫物件 擁有者 Adonis 呼叫者 Allen Execution context 身分 CREATE PROCEDURE GetOrders WITH EXECUTE AS CALLER AS SELECT * FROM sales.orders Owner 'Rose' SELF

Microsoft TechNet 技術講座--資料庫技術升級系列三 中斷 Ownership Chains View裡面的 table，owner name不同，權限設定會 發生問題 範例: Maria 執行: Tom 執行: maria.view2 lucia.view1 GRANT SELECT ON view2 TO Tom lucia.table1 SELECT * FROM maria.view2

Execution Context SQL 2000 SQL Server 2005 User2.Proc1 User1.T1 User 3 ‘Execute AS ‘X’ ’ User 3 User2.Proc1 User1.T1 執行時檢查User3權限 檢查 ‘X’. 而不是 user3

Execution Context的使用 Execute AS CALLER Execute AS ‘UserName’ 若 schema名稱不同，檢查呼叫者有無權力 預設的設定，與 SQL Server 2000相似 Execute AS ‘UserName’ 以該 UserName身份執行程式 Execute AS SELF 以建立該物件的帳號執行 Execute AS OWNER 以 Owner的身份執行

Demo Execution Context的使用

Microsoft TechNet 技術講座--資料庫技術升級系列三 SQL Injection "SELECT UserName FROM ApplicationUsers WHERE UserId = '" + userId + "' AND Password = '" + password + "'" "SELECT UserName FROM ApplicationUsers WHERE UserId = '%' OR 1=1; -- AND Password = '1234'" 減少 SQL Server 被攻擊機會 檢查所有使用者的資料輸入 ü 抓取系統的錯誤訊息 ü 使用參數化的查詢與預存程序 ü

Microsoft TechNet 技術講座--資料庫技術升級系列三 Replication安全性設定 選擇適當的驗證模式 Windows驗證優於 Mixed驗證 使用 Replication Agent安全 SQL 2005中，每個 Agent皆可設定安全 使用 PAL(publication access list) 保護 snapshot目錄 Merge Distribution Agent需要讀的權力 Snapshot agent需要寫的權力 保護 Internet中的 replication 利用 VPN或是 SSL提升安全

Microsoft TechNet 技術講座--資料庫技術升級系列三 Demo Replication的 agent安全性設定

Agent、Database Mail安全性設定 Microsoft TechNet 技術講座--資料庫技術升級系列三 Agent、Database Mail安全性設定 必要時才打開 Database mail 可使用 Private Database mail profile 限制夾帶檔案大小及副檔名

Demo 設定 Database Mail

Application Role安全設定 允許使用者只能用某支應用程式讀取資料 新增sp_unsetapprole功能 Orders OrderID CustomerID EmployeeID 10248 10249 10250 VINET TOMSP HANAR 3 1 2 ... Microsoft Excel 自訂應用程式

Demo 利用 Application Role維護安全性

Audit審核的機制 決定哪些事件需要審核 審核資訊存放哪裡？ 審核對效能的影響 誰要負責分析審核事件資訊？

利用 DDL Trigger審核SQL事件 ü ü ü ü 評估是否 DDL triggers是適合作為審核 決定觸發程序的範圍 CREATE TRIGGER ddl_trig_login ON ALL SERVER FOR DDL_LOGIN_EVENTS AS PRINT 'Login Event Issued.' ... 評估是否 DDL triggers是適合作為審核 ü 決定觸發程序的範圍 ü 決定哪些事件需要審核 ü 指定哪些操作是被允許的 ü

Microsoft TechNet 技術講座--資料庫技術升級系列三 DDL Triggers Names DROP TABLE Names Database DDL_log ‘Table Dropped’ CREATE TRIGGER DDL_Trigger ON DATABASE AFTER DROP_TABLE AS INSERT INTO DDL_log VALUES (‘資料表被移除’) DDL_Trigger

Microsoft TechNet 技術講座--資料庫技術升級系列三 Demo 利用 DDL Trigger審核SQL事件

Microsoft TechNet 技術講座--資料庫技術升級系列三 SQL Agent Proxies 存取外部資源時使用 可多個 SQL 帳號對應 Windows帳號

Microsoft TechNet 技術講座--資料庫技術升級系列三 SQL Agent Proxies SQL Server SQL Agent Job ActiveX Subsystem User No ActiveX Access Agent Proxy ActiveX Subsystem

Microsoft TechNet 技術講座--資料庫技術升級系列三 Demo 設定 SQL帳號對應 Windows帳號 建立 SQL登入帳號 設定為msdb使用者，並具有 SQLAgentUserRole 設定Credentials，指向 Windows帳號 設定 Proxies，對應 Credentials，並加入 SQL Login帳號 建立 Job,Owner為 SQL登入帳號 在 step步驟，設定 run as Credential

HTTP Endpoints CREATE ENDPOINT HTTP Endpoint Stored Procedure Function SQL Server 2005 Client PC .NET Application Consumes Web Services

資料加密 Server Requested Client Requested 設定在 Server等級 適用在所有通訊協定 使用 self-signed certificate Server Requested 設定在 client端 伺服器需要certificate 需要為 self-signed certificate 做額外的組態 Client Requested

Microsoft TechNet 技術講座--資料庫技術升級系列三 資料庫等級的加密 分享同一把 public key 速度快 不用來做簽章 對稱 獨立的 key 速度較對稱來的慢 可用來做數位簽章 非對稱 包含憑證授權 驗證加密鑰匙 用來做數位簽章 憑證

Microsoft TechNet 技術講座--資料庫技術升級系列三 Certificates Third Party 預設為信任 較昂貴 網際網路應用程式 Root CA Windows Server CA 預設為不信任 網際網路應用程式 內建(利用 Create CERTIFICATE指令) Server-requested encryption Client 也需要組態 Self-signed

Microsoft TechNet 技術講座--資料庫技術升級系列三 Self-signed的加密原理

Microsoft TechNet 技術講座--資料庫技術升級系列三 Demo 設定資料表欄位對稱加密 設定資料表欄位非對稱加密

Surface Area Configuration Microsoft TechNet 技術講座--資料庫技術升級系列三 Surface Area Configuration 服務關閉，一些功能預設沒有開啟 全新安裝 SQL Server 2005 Surface Area Configuration 設定服務與連線 Surface Area Configuration 功能 一些功能預設隨著升級而啟動 SQL Server 2000 SQL Server 2005

Microsoft TechNet 技術講座--資料庫技術升級系列三 使用 SAC.EXE SAC out NY-SQL-02.out –S NY-SQL-02 –I MSSQLSERVER SAC in NY-SQL-02.out –S NY-SQL-01 NY-SQL-02 NY-SQL-01

Microsoft TechNet 技術講座--資料庫技術升級系列三 Demo 執行C:\Program Files\Microsoft SQL Server\90\Shared目錄下 SAC out output.out –S cenet –I MSSQLSERVER 可以匯入匯出某台電腦的組態設定

限制 .NET程式碼執行 有必要時才允許 CLR的程式 Safe External access Unsafe 限制使用，只限於內部資料 允許使用外部資源，例如如檔案或是網路系統 Unsafe 最不安全，毫無限制，不建議

Demo 限制 .NET程式碼的執行

結論 安全需要多做一些設定才能達到 愈安全，對使用者愈不方便 SQL 2005預設將所有安全選項都關閉

回家作業 Q1: 在 SQL Server 2005的環境中，何為Principals？ Q2: 試自行比較對稱與非對稱式加密的不同？

回家作業解答 Q1: 在 SQL Server 2005的環境中，何為Principals？ Q2: 試自行比較對稱與非對稱式加密的不同？ Windows群組、網域使用者帳戶、本機使用者帳戶、SQL登入帳戶、伺服器角色、資料庫角色、Application role都是 principals Q2: 試自行比較對稱與非對稱式加密的不同？ 對稱加密快但較不安全，加解密都用同一把key 非對稱加密慢但較安全，會由CA Server產生一組 public與private key