《技术讲座-资料库技术升级系列》由会员分享，可在线阅读，更多相关《技术讲座-资料库技术升级系列（58页珍藏版）》请在金锄头文库上搜索。
1、資料的安全存取2上次回家作業解答l lQ1: 在資料庫鏡像中，鏡像伺服器可否用來擔任報表的資料來源？需搭配資料庫快照需搭配資料庫快照l lQ2: 如何增加備份媒體的可靠度？利用鏡像備份利用鏡像備份，同時儲存多個備份同時儲存多個備份3大綱大綱l l帳號密碼安全性原則l l使用者與結構定義分開l l不同權限檢視不同的中繼資料l lExecution Contextl lSQL Injectionl lReplication安全性設定l lAgent、Database Mail安全性設定4大綱大綱續續l lApplication Role安全設定l l利用 Stored Procrdure、View
2、增進安全l lAudit審核的機制l l利用 DDL Trigger審核 SQL事件l lSQL Server自訂帳號對應到 Windows帳號l l支援憑證的對稱，非對稱加密l l預設關閉各選項功能l l限制 .NET程式碼執行5帳號密碼安全性原則帳號密碼安全性原則Principals伺服器角色SQL Server 登入帳戶Windows 群組網域使用者帳戶本機使用者帳戶User資料庫角色Application RoleGroupSQL ServerDatabaseWindowsFilesRegistry KeysCREATEALTERDROPCONTROLCONNECTSELECTEXEC
3、UTEUPDATEDELETEINSERTTAKE OWNERSHIPVIEW DEFINITIONBACKUP權限(Permissions)可賦予存取權限的物件(Securables)GRANT/REVOKE/DENYACL伺服器Schema資料庫6l l驗證(authentication)檢查是否為正確的使用者檢查是否為正確的使用者有有 WindowsWindows驗證與驗證與 SQLSQL驗證驗證l l授權(authorization)給與使用者權力給與使用者權力利用利用 Fixed RoleFixed Role來設定來設定ownerowner所建立的物件所建立的物件，該該 ownero
4、wner有完全的權力有完全的權力安全性的基本安全性的基本72 2驗證驗證(authentication)(authentication)l lWindows驗證需要有需要有 WindowsWindows帳號或帳號或 ADAD帳號帳號SQL ServerSQL Server要允許登入要允許登入(Grant)(Grant)適用在區域網路適用在區域網路l lSQL 驗證只需只需 SQL ServerSQL Server允許登入即可允許登入即可適用在前端非適用在前端非 WindowsWindows環境環境程式撰寫連線資料庫程式撰寫連線資料庫l l資料庫使用者(Database User)使用資料庫前的
5、使用者驗證使用資料庫前的使用者驗證8設定帳號密碼的設定帳號密碼的 policypolicyl lSQL 驗證的密碼可設定 policySQL Server 2005SQL Server 2005新增功能新增功能需搭配需搭配 Windows 2003 ServerWindows 2003 Serverl l設定密碼的強度密碼內容長度密碼內容長度密碼的複雜度密碼的複雜度密碼過期的週期密碼過期的週期9DemoDemol l利用利用 Create指令建立登入帳號指令建立登入帳號l l設定設定 SQL驗證的驗證的 policyl l第一次登入第一次登入 SQL Server時，需要改變密碼時，需要改變密
6、碼102 2授權授權(authorization)(authorization)l l給予權力可設定在可設定在 loginlogin帳號與帳號與 database userdatabase userl lServer等級權力建立建立DDLDDL權力權力修改修改 DatabaseDatabase權力權力l lDatabase等級權力l lSchema等級權力l lObject等級權力11管理權限管理權限l l如何管理 Server的權限l l如何管理 Server-Scope 的權限l l如何管理 Database的權限l l如何管理 Database-Scope的權限l l如何管理 Schem
7、a-Scope的權限12如何管理如何管理 ServerServer的權限的權限USE masterGRANT ALTER ANY DATABASE TO SERVERXBill在在 Object Explorer連接伺服器連接伺服器1按按登入帳號登入帳號右鍵選擇屬性右鍵選擇屬性2Securables頁面，選擇頁面，選擇 Add Objects3增加增加 4指定權限指定權限513如何管理如何管理Server-ScopeServer-Scope權限權限l lServer-scope 安全HTTP endpointsHTTP endpointsCertificatesCertificatesUSE
8、masterGRANT CONNECTON HTTP ENDPOINT : AWWebServiceTO SERVERXBill14USE AdventureWorksGRANT ALTER ANY USERTO Ted在在 Object Explorer連接伺服器連接伺服器1按資料庫右鍵選擇屬性按資料庫右鍵選擇屬性2Permissions分頁分頁3指定權限指定權限4如何管理如何管理 DatabaseDatabase權限權限15如何管理如何管理Database-Scope Database-Scope 權限權限USE AdventureWorksGRANT SELECTON SCHEMA :
9、salesTO Ted在在 Object Explorer連接伺服器連接伺服器1展開物件展開物件2按物件右鍵選擇屬性按物件右鍵選擇屬性3Permissions分頁分頁4指定權限指定權限516如何管理如何管理Schema-Scope Schema-Scope 權限權限USE AdventureWorksGRANT EXECUTEON TYPE : addressTypeTO Ted在在 Object Explorer連接伺服器連接伺服器1展開物件展開物件2按物件右鍵選擇屬性按物件右鍵選擇屬性3Permissions分頁分頁4指定權限指定權限5USE AdventureWorksGRANT SEL
10、ECTON sales.ordersTO Ted17DemoDemol l授權給 login id，讓它可以登入 SQL Serverl l授權給資料庫使用者，讓它對 create table有權力(設定database權力)18使用者與使用者與 Schema Schema 的分離的分離l lSQL Server 2000只有只有 dbo.objectdbo.object名稱名稱不支援不支援Schema Schema Server.database.dbo.objectServer.database.dbo.objectl lSQL Server 2005支援支援 schemaschema原本
11、的原本的 dbodbo，置換成置換成 schemaschema19用來解決物件使用的問題用來解決物件使用的問題UserUserDatabase Database ObjectObjectOwned ByTableTableViewViewSPSPFunctionFunctionUser 2User 2名稱解析問題名稱解析問題名稱解析問題名稱解析問題EgEg: Select * from Foo: Select * from Fool lCallingUser.fooCallingUser.fool lDbo.fooDbo.foo刪除使用者也許會造成應用程式需要改變刪除使用者也許會造成應用程式需
12、要改變刪除使用者也許會造成應用程式需要改變刪除使用者也許會造成應用程式需要改變! !20刪除使用者刪除使用者 = = 應用程式重寫應用程式重寫App1App1App2App2SELECT SELECT custIDcustID FROM FROM User1User1.OrdersTable.OrdersTableApp1 ModifiedApp1 ModifiedApp2 modifiedApp2 modifiedOrdersTableOrdersTableInsertOrderProcInsertOrderProcSELECT SELECT custIDcustID FROM FROM U
13、ser2User2.OrdersTable.OrdersTableExec Exec User1User1.InsertOrderProc (.InsertOrderProc (orderidorderid) )Exec Exec User2User2.InsertOrderProc (.InsertOrderProc (orderidorderid) )Owning user = User1 Owning user = User1 SCHEMA = User1SCHEMA = User1Owning user = User2 Owning user = User2 SCHEMA = User
14、2SCHEMA = User221解決方法解決方法SchemaSchema與與 UserUser分離分離UserUserDatabase Database ObjectObjectSchemaSchemacontained incontained inOwned byOwned byOwned ByOwned ByTableTableViewViewStored ProcStored ProcFunctionFunctionUser 2User 2Owned byOwned by刪除使用者不會影響到應用程式的改寫刪除使用者不會影響到應用程式的改寫刪除使用者不會影響到應用程式的改寫刪除使用者不會
15、影響到應用程式的改寫! !22可以利用可以利用 Default SchemaDefault Schema解解決問題決問題SchemaSchemacontained inTableTableViewViewStored ProcStored ProcFunctionFunctionUser 2User 2Owned byDefault SchemaDefault SchemaUser1User1Default Schema S1Default Schema S1User2User2User3User3Name ResolutionName ResolutionSelect * from Sele
16、ct * from foofoo S1. S1. foofoo Dbo.fooDbo.foo23DemoDemol l設定 schemal l設定 default schemal l利用 schema完成資料存取24不同權限檢視不同的中繼資料不同權限檢視不同的中繼資料l l查詢靜態的中繼資料 ViewsSystem ViewsViewsSystem Views目錄目錄SELECT * FROM SELECT * FROM sys.tablessys.tablesl l查詢動態的中繼資料 ViewsSystem ViewsViewsSystem Views目錄目錄SELECT * FROM SE
17、LECT * FROM sys.db_tran_lockssys.db_tran_locksl l使用者不可直接存取系統資料表l l不同權限的使用者所得到的Metadata結果不同l l可設定不能讀取 store procedure的定義25DemoDemol l不同權限檢視的 Metadata也有所不同SELECT * FROM sys.tablesSELECT * FROM sys.tablesl l故意刪除沒有權力刪除的資料表會出現資料表不存在或是沒有權限會出現資料表不存在或是沒有權限 l l可以設定 deny看 store procedure定義的權力26CREATE PROCEDUR
18、E GetOrdersWITH EXECUTE AS CALLERASSELECT * FROM sales.ordersExecution Context Execution Context 與與 Broken Ownership ChainingBroken Ownership ChainingSELF呼叫者呼叫者AllenExecution context 身分身分OwnerRose資料庫物件資料庫物件擁有者擁有者 Adonis預存程序或自訂函數預存程序或自訂函數建立者建立者 JohnSELECT *FROM sales.orders？27l lView裡面的 table，ownerl
19、lname不同，權限設定會l l發生問題l l範例: Maria Maria 執行執行: :Tom Tom 執行執行: :中斷中斷 Ownership ChainsOwnership ChainsGRANT SELECT ON view2 TO TomSELECT * FROM maria.view2maria.view2lucia.view1lucia.table128 Execution Context Execution ContextUser 3User 3執行時檢執行時檢執行時檢執行時檢查查查查User3User3權限權限權限權限執行時檢執行時檢執行時檢執行時檢查查查查User3Us
20、er3權限權限權限權限User1.Proc1User1.Proc1User1.T1User1.T1執行時檢執行時檢執行時檢執行時檢查查查查User3User3權限權限權限權限不不不不用檢用檢用檢用檢查查查查 User3User3權限權限權限權限User 3User 3User2.Proc1User2.Proc1User1.T1User1.T1Execute AS X Execute AS X 執行時檢執行時檢執行時檢執行時檢查查查查User3User3權限權限權限權限檢檢檢檢查查查查 X.X. 而不是而不是而不是而不是 user3user3SQL SQL ServerServer2005200
21、5SQL SQL 20002000User2.Proc1User2.Proc1User1.T1User1.T129Execution ContextExecution Context的使用的使用l lExecute AS CALLER若若 schemaschema名稱不同名稱不同，檢查呼叫者有無權力檢查呼叫者有無權力預設的設定預設的設定，與與 SQL Server 2000SQL Server 2000相似相似l lExecute AS UserName以該以該 UserNameUserName身份執行程式身份執行程式l lExecute AS SELF以建立該物件的帳號執行以建立該物件的帳號
22、執行l lExecute AS OWNER以以 OwnerOwner的身份執行的身份執行30DemoDemol lExecution Context的使用的使用SQL InjectionSQL InjectionSELECT UserName FROM ApplicationUsersWHERE UserId = + userId + AND Password = + password + SELECT UserName FROM ApplicationUsersWHERE UserId = % OR 1=1; - AND Password = 1234減少 SQL Server 被攻擊機會檢
23、查所有使用者的資料輸入檢查所有使用者的資料輸入抓取系統的錯誤訊息抓取系統的錯誤訊息使用參數化的查詢與預存程序使用參數化的查詢與預存程序 32ReplicationReplication安全性設定安全性設定l l選擇適當的驗證模式WindowsWindows驗證優於驗證優於 MixedMixed驗證驗證l l使用 Replication Agent安全SQL 2005SQL 2005中中，每個每個 AgentAgent皆可設定安全皆可設定安全l l使用 PAL(publication access list)l l保護 snapshot目錄Merge Distribution AgentMerg
24、e Distribution Agent需要讀的權力需要讀的權力Snapshot agentSnapshot agent需要寫的權力需要寫的權力l l保護 Internet中的 replication利用利用 VPNVPN或是或是 SSLSSL提升安全提升安全33DemoDemol lReplication的 agent安全性設定34AgentAgent、Database MailDatabase Mail安全性設定安全性設定l l必要時才打開 Database maill l可使用 Private Database mail profilel l限制夾帶檔案大小及副檔名35DemoDemol
25、 l設定 Database Mail36Application RoleApplication Role安全設定安全設定l l允許使用者只能用某支應用程式讀取資料l l新增sp_unsetapprole功能OrdersOrdersOrderID CustomerID EmployeeID102481024910250VINETTOMSPHANAR312 .Microsoft Excel自訂應用程式自訂應用程式37DemoDemol l利用 Application Role維護安全性38AuditAudit審核的機制審核的機制l l決定哪些事件需要審核l l審核資訊存放哪裡？l l審核對效能的影
26、響l l誰要負責分析審核事件資訊？39利用利用 DDL TriggerDDL Trigger審核審核SQLSQL事件事件CREATE TRIGGER ddl_trig_login ON ALL SERVER FOR DDL_LOGIN_EVENTS AS PRINT Login Event Issued.評估是否評估是否 DDL triggers是適合作為審核是適合作為審核決定觸發程序的範圍決定觸發程序的範圍 決定哪些事件需要審核決定哪些事件需要審核 指定哪些操作是被允許的指定哪些操作是被允許的 40DDL TriggersDDL TriggersNamesDDL_logDDL_Trigger
27、DatabaseCREATE TRIGGER DDL_Trigger ON DATABASEAFTER DROP_TABLE AS INSERT INTO DDL_log VALUES (資料表被移除資料表被移除)DROP TABLE NamesTable Dropped41DemoDemol l利用 DDL Trigger審核SQL事件42SQL Agent ProxiesSQL Agent Proxiesl l存取外部資源時使用存取外部資源時使用l l可多個可多個 SQL 帳號對應帳號對應 Windows帳號帳號43SQL Agent ProxiesSQL Agent ProxiesSQL
28、 ServerSQL ServerUserUserNo ActiveX AccessNo ActiveX AccessSQL Agent JobSQL Agent JobActiveX SubsystemActiveX SubsystemAgent ProxyAgent ProxyActiveX SubsystemActiveX Subsystem44DemoDemol l設定 SQL帳號對應 Windows帳號建立建立 SQLSQL登入帳號登入帳號設定為設定為msdbmsdb使用者使用者，並具有並具有 SQLAgentUserRoleSQLAgentUserRole設定設定Credentia
29、lsCredentials，指向指向 WindowsWindows帳號帳號設定設定 ProxiesProxies，對應對應 CredentialsCredentials，並加入並加入 SQL LoginSQL Login帳號帳號建立建立 Job,OwnerJob,Owner為為 SQLSQL登入帳號登入帳號在在 stepstep步驟步驟，設定設定 run as Credentialrun as Credential45HTTP EndpointsHTTP EndpointsSQL Server 2005HTTP EndpointStored ProcedureFunctionClient PC
30、.NET ApplicationConsumes Web ServicesCREATE ENDPOINT46資料加密資料加密l l設定在設定在設定在設定在 clientclient端端端端l l伺服器需要伺服器需要伺服器需要伺服器需要certificatecertificatel l需要為需要為需要為需要為 self-signed certificate self-signed certificate 做做做做額外的組態額外的組態額外的組態額外的組態ServerRequestedClient Requestedl l設定在設定在設定在設定在 ServerServer等級等級等級等級l l適用在
31、所有通訊協定適用在所有通訊協定適用在所有通訊協定適用在所有通訊協定l l使用使用使用使用 self-signed certificateself-signed certificate47資料庫等級的加密資料庫等級的加密l l分享同一把分享同一把分享同一把分享同一把 public keypublic keyl l速度快速度快速度快速度快l l不用來做簽章不用來做簽章不用來做簽章不用來做簽章對稱對稱非對稱非對稱憑證憑證l l獨立的獨立的獨立的獨立的 keykeyl l速度較對稱來的慢速度較對稱來的慢速度較對稱來的慢速度較對稱來的慢l l可用來做數位簽章可用來做數位簽章可用來做數位簽章可用來做數位簽
32、章l l包含憑證授權包含憑證授權包含憑證授權包含憑證授權l l驗證加密鑰匙驗證加密鑰匙驗證加密鑰匙驗證加密鑰匙l l用來做數位簽章用來做數位簽章用來做數位簽章用來做數位簽章48CertificatesCertificatesl l預設為信任預設為信任預設為信任預設為信任l l較昂貴較昂貴較昂貴較昂貴l l網際網路應用程式網際網路應用程式網際網路應用程式網際網路應用程式l lWindows Server CAWindows Server CAl l預設為不信任預設為不信任預設為不信任預設為不信任l l網際網路應用程式網際網路應用程式網際網路應用程式網際網路應用程式l l內建內建內建內建( (利用
33、利用利用利用 Create CERTIFICATECreate CERTIFICATE指指指指令令令令) ) l lServer-requested encryptionServer-requested encryptionl lClient Client 也需要組態也需要組態也需要組態也需要組態Third PartyThird PartyRoot CARoot CASelf-signedSelf-signed49Self-signedSelf-signed的加密原理的加密原理50DemoDemol l設定資料表欄位對稱加密l l設定資料表欄位非對稱加密51Surface Area Confi
34、gurationSurface Area Configuration全新安裝SQL Server 2005SQL Server 2000SQL Server 2005服務關閉，一些功能預設沒有開啟一些功能預設隨著升級而啟動Surface Area Configuration 設定服務與連線Surface Area Configuration 功能SAC out NY-SQL-02.out S NY-SQL-02 I MSSQLSERVER使用使用 SAC.EXESAC.EXENY-SQL-02NY-SQL-01SAC in NY-SQL-02.out S NY-SQL-0153DemoDemo
35、l l執行執行C:Program FilesMicrosoft SQL Server90Shared目錄下目錄下l lSAC out output.out S cenet I MSSQLSERVERl l可以匯入匯出某台電腦的組態設定可以匯入匯出某台電腦的組態設定54限制限制 .NET.NET程式碼執行程式碼執行l l有必要時才允許 CLR的程式l lSafe限制使用限制使用，只限於內部資料只限於內部資料l lExternal access允許使用外部資源允許使用外部資源，例如如檔案或是網路系統例如如檔案或是網路系統l lUnsafe最不安全最不安全，毫無限制毫無限制，不建議不建議55Demo
36、Demol l限制 .NET程式碼的執行56結論結論l l安全需要多做一些設定才能達到l l愈安全，對使用者愈不方便l lSQL 2005預設將所有安全選項都關閉57回家作業l lQ1: 在 SQL Server 2005的環境中，何為Principals？l lQ2: 試自行比較對稱與非對稱式加密的不同？58回家作業解答l lQ1: 在 SQL Server 2005的環境中，何為Principals？WindowsWindows群組群組、網域使用者帳戶網域使用者帳戶、本機使用者本機使用者帳戶帳戶、SQLSQL登入帳戶登入帳戶、伺服器角色伺服器角色、資料庫角資料庫角色色、Application roleApplication role都是都是 principalsprincipalsl lQ2: 試自行比較對稱與非對稱式加密的不同？對稱加密快但較不安全對稱加密快但較不安全，加解密都用同一把加解密都用同一把keykey非對稱加密慢但較安全非對稱加密慢但較安全，會由會由CA ServerCA Server產生產生一組一組 publicpublic與與private keyprivate key
