1、第八章,資料庫安全與交易管理,第八章 資料庫安全與交易管理,2,章節概要,8.1 資料庫安全8.2 交易管理8.3 並行控制,第八章 資料庫安全與交易管理,3,8.1 資料庫安全,關聯式資料庫都可以採用如下兩種機制來控制資料庫的安全：使用SQL指令中的GRANT(授權)與REVOKE(撤回)兩個命令來指定使用者的權限。利用視界(View)配合GRANT和REVOKE作為確保資料安全的機制。,第八章 資料庫安全與交易管理,4,使用GRANT授權-SQL Server授權,於SQL Server中使用指令(陳述式)授權的語法如下：【指令或陳述式授權】 GRANT ALL|指令一,指令二,. TO
2、安全帳號列表其中可授權的個別指令為：CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE、CREATE VIEW、BACKUP DATABASE及BACKUP LOG等等。,第八章 資料庫安全與交易管理,5,SQL Server授權(續),例如授權資料庫使用者register及Window NT使用者 DTCHANGdtchang可建立資料庫(CREATE DATABASE)及建立資料表(CREATE TABLE)，則可下達如下的指令：(原register使用者無法存取m
3、aster資料庫，授權的資料表位於master資料庫中)USE masterGOEXEC sp_adduser scott, register, publicGOGRANT CREATE DATABASE, CREATE TABLE TO register, DTCHANGdtchangGO,第八章 資料庫安全與交易管理,6,MySQL權限授權,於MySQL中使用GRANT授權使用者的使用權限之指令格式為：【MySQL權限授權】GRANT ALL|權限一,權限二,. ON 資料表|*|*.*|資料庫名稱.* TO 安全帳號列表 IDENTIFIED BY 密碼WITH GRANT OPTION
4、其中安全帳號的名稱使用類似Email帳戶的格式表示法，如：scott140.126.147.109，表示來自140.126.147.109的使用者scott。帳戶名稱可以使用萬用字，如：scott140.126.147.% 則表示任意來自140.126.147子網域的使用者scott。若單純只給定scott未給定網域名稱，則同義於 scott%。,第八章 資料庫安全與交易管理,7,MySQL權限授權(續),MySQL中授權的權限等級分成四個等級：(以GRANT ALL為例)全域等級： GRANT ALL ON *.*資料庫等級：GRANT ALL ON 資料庫名稱.*資料表等級：GRANT A
5、LL ON 資料庫名稱.資料表名稱欄位等級,第八章 資料庫安全與交易管理,8,MySQL權限授權(續),假如我們想增加使用者monty使用資料庫orders的權限，但限制只能INSERT及UPDATE，則可下達如下的指令：GRANT INSERT, UPDATE ON orders.* TO montylocalhost;接著下達更新權限表的指令：FLUSH PRIVILEGES;,第八章 資料庫安全與交易管理,9,使用REVOKE移除授權-移除SQL Server授權,於SQL Server中使用REVOKE移除指令(陳述式)授權的語法如下：【移除指令或陳述式授權】REVOKE ALL|指令
6、一,指令二,. FROM 安全帳號列表而移除使用物件權限授權的語法則如下：【移除物件權限授權】REVOKE GRANT OPTION FORALL|權限一,權限二,. ON 物件FROM 安全帳號列表,第八章 資料庫安全與交易管理,10,移除SQL Server授權(續),例如移除register對pubs資料庫中authors資料表的INSERT、UPDATE和DELETE權限，則可下達如下的指令：USE pubsGOREVOKE INSERT, UPDATE, DELETEON authorsFROM registerGO,第八章 資料庫安全與交易管理,11,移除MySQL授權(續),於M
7、ySQL中使用REVOKE移除授權使用者的使用權限之指令格式為：【移除MySQL權限授權】REVOKE ALL|權限一,權限二,. ON 資料表|*|*.*|資料庫名稱.* FROM 安全帳號列表例如移除 montylocalhost 對資料庫orders的INSERT及UPDATE權限，則可下達如下的指令：REVOKE INSERT, UPDATE ON orders.* FROM montylocalhost;FLUSH PRIVILEGES;,第八章 資料庫安全與交易管理,12,8.2 交易管理,當我們對資料庫存取資料時，如果有多個存取操作必須被完整地執行，這些操作必須被視為一個不可被分
8、割的單位，我們稱之為一個交易(Transaction)或異動。換言之，一個交易的成功必須是其不可被分割的各項操作皆須成功地被執行。交易管理保證一個交易中的一序列的動作若不是全部完成，就應該連一個動作也沒有被執行。,第八章 資料庫安全與交易管理,13,第八章 資料庫安全與交易管理,14,COMMIT確認與ROLLBACK撤回,在同一個交易內進行的資料操作，如：新增、更新和刪除資料列的操作等，並不會立即真實地反應到資料庫，必須等到執行COMMIT(確認或認可)的處理後，交易內的多項操作才會一同反應到資料庫。經過COMMIT的動作之後，交易的異動才正式確立。經過COMMIT後的資料庫就無法回復到交易
9、前的狀態。,第八章 資料庫安全與交易管理,15,COMMIT確認與ROLLBACK撤回(續),然而，如果交易之內的處理發生問題時，我們就可以執行ROLLBACK(撤回)的動作，將整個交易的處理回復到交易前的狀態。當我們下達ROLLBACK後，所有的交易內的操作動作都會被取消。因此，一個交易若不是以COMMIT成功地完成整個交易，即以ROLLBACK取消整個交易來結束這個交易。,第八章 資料庫安全與交易管理,16,COMMIT確認與ROLLBACK撤回(續),使用交易時，我們可以使用SQL中的資料控制語言COMMIT指令來下達確認的動作，其語法如下：【COMMIT語法】COMMIT若要執行撤回的
10、動作，則可下達ROLLBACK指令，其語法如下：【ROLLBACK語法】ROLLBACK,第八章 資料庫安全與交易管理,17,成功的交易,所謂交易成功，則須滿足四項基本的交易屬性：單元性(Atomicity)一致性(Consistency)隔離性(Isolation)永久性(Durability)以該四屬性的第一個字母組合成簡稱 ACID 交易。,第八章 資料庫安全與交易管理,18,第八章 資料庫安全與交易管理,19,第八章 資料庫安全與交易管理,20,SQL Server,SQL Server中和交易相關的SQL指令和語法如下：【交易開始的指令】 BEGIN TRANSACTION 交易名稱
11、 | 交易名稱變數 WITH MARK 說明【設定交易儲存點的指令】 SAVE TRANSACTION 儲存點名稱 | 儲存點名稱變數【COMMIT指令】 COMMIT TRANSACTION 交易名稱 | 交易名稱變數 COMMIT WORK【ROLLBACK指令】 ROLLBACK TRANSACTION 交易名稱 | 交易名稱變數 | 儲存點名稱 | 儲存點名稱 ROLLBACK WORK,第八章 資料庫安全與交易管理,21,SQL Server(續),以下則是示範使用儲存點的用法：BEGIN TRANSACTIONGOUPDATE EmployeesSET EngName = Jenn
12、iferWHERE EmployeeID = 1001GOSAVE TRANSACTION AAAGOUPDATE EmployeesSET EngName = MacWHERE EmployeeID = 1002GOROLLBACK TRANSACTION AAAGO,第八章 資料庫安全與交易管理,22,MySQL,對於MySQL而言，其所使用的語法和SQL Server雖然不同，然而使用的邏輯則是差不多的。MySQL中和交易相關的SQL指令和語法如下：【交易開始的指令】BEGIN WORKBEGINSTART TRANSACTION【COMMIT指令】COMMIT【ROLLBACK指令】R
13、OLLBACK,第八章 資料庫安全與交易管理,23,MySQL(續),MySQL的檔案儲存格式，有：MyISAM(使用硬碟儲存資料、快速但不支援交易、row locking；預設值)InnoDB(使用硬碟儲存資料、支援交易、row locking)Memory(使用記憶體儲存資料，適用於暫存檔)MERGE(適用於log或文件資料檔)NDB(使用記憶體儲存資料、支援交易、row locking，適用於需要安全及即時之需求時)DBD(使用硬碟儲存資料、支援交易、page locking),第八章 資料庫安全與交易管理,24,二階段確認交易,對於分散式資料庫系統而言，需要特殊的確認處理，以避免在管理
14、跨越多個資料庫伺服器的交易時發生問題。確認大型交易時，因為記錄檔緩衝區轉存後釋放，所以需要相當長的一段時間。而確認處理本身也會遇到強制復原的錯誤。如果交易管理員僅是要求各資料庫伺服器確認，那麼它可能會從一些資料庫伺服器處取得成功的狀態、而自某位資料庫伺服器處取得錯誤訊息。但這樣會產生衝突，因為所有分散式交易應該復原，但有部分已經被確認了。兩階段交易確認利用將確認分成兩個階段的方式，來找出問題的所在。將確認交易的方式分為兩個階段來執行，稱為二階段確認交易(Two-Phase Commit; 2PC) 。,第八章 資料庫安全與交易管理,25,第二階段,第八章 資料庫安全與交易管理,26,系統回復,
15、當在使用資料庫的過程中可能會發生各式各樣的故障(Failure)，例如：系統當機、硬碟故障等。為了避免交易處理的結果因為故障而流失或產生錯亂，我們必須針對各種故障進行故障回復(Failure Recovery)或系統回復(System Recovery)。資料庫的故障大致可分為：交易處理的故障、系統故障和硬碟故障等等類型。,第八章 資料庫安全與交易管理,27,交易處理的故障,針對這種類型的故障的對治方式就是不要執行COMMIT動作，並執行ROLLBACK動作，以讓資料庫回復到最近COMMIT時的狀態即可。,第八章 資料庫安全與交易管理,28,系統故障,系統故障指的是作業系統因某種原因導致當機的
16、故障問題，如：電力中斷，又稱為軟當機(Soft Crash)。發生這種類型的故障時，於系統重新啟動時，資料庫系統可以回到資料庫上一次寫入更新資料的檢查點(Check Point)來開始進行復原的工作。此時我們可以將交易分成五種狀況，如圖 8.5 所示。即：T1T2T3T4T5,第八章 資料庫安全與交易管理,29,第八章 資料庫安全與交易管理,30,硬碟故障情況,假如發生故障的原因是硬碟故障，那麼受損的狀況可能有三種：交易記錄受損：此時資料庫系統不會受到影響，但是無法使用交易記錄回復資料庫到故障前的狀態。資料庫受損：此時交易記錄未受損，故可載入先前備份的資料，並使用交易記錄回復資料庫到故障前的狀
17、態。交易記錄及資料庫皆受損：此時可載入先前備份的資料，然後以手動的方式儘量回復資料庫的內容。,第八章 資料庫安全與交易管理,31,使用RAID預防硬碟故障,RAID(冗餘磁碟陣列)可分為如下不同的等級：RAID0：可將資料分散儲存多個磁碟中，即可將多個磁碟整合視為一個磁碟，稱為資料延展(Data Stripping)。RAID1：可將資料同時寫入到兩個完全相同的磁碟中，稱為映射(Mirroring)。如果有一台磁碟故障時，第二台馬上取代之。RAID2：將資料以位元為單位分散儲存於多台磁碟中，並用額外的一台磁碟來儲存同位檢查位元(Parity Bit)。,第八章 資料庫安全與交易管理,32,使用
18、RAID預防硬碟故障(續),RAID3：和RAID2相同，但是以位元組的方式來儲存資料。RAID4：和RAID2相同，但是以區塊(Block)的方式來儲存資料。RAID5：資料以區塊的方式分散儲存於多台磁碟中，並將檢查位元分散儲存。(目前常用)RAID6：和RAID5相同，但增加一個檢查位元之複本。,第八章 資料庫安全與交易管理,33,8.3 並行控制,由於資料庫系統大都允許多個使用者同時存取資料庫的資料，為了確保資料的一致性，資料庫系統必須使用並行控制(Concurrency Control)的方法來控制多個使用者的操作，以免產生如下常見之問題：更新遺失(Update Lost)未確認交易相
19、依(Uncommitted Dependency) 不一致讀取(Inconsistent Retrievals),第八章 資料庫安全與交易管理,34,更新遺失,第八章 資料庫安全與交易管理,35,未確認交易相依,ROLLBACK,第八章 資料庫安全與交易管理,36,不一致讀取,第八章 資料庫安全與交易管理,37,並行控制之二元鎖定,二元鎖定在鎖定資源時，只有鎖定(Locked)或非鎖定(Unlocked)兩種狀態。二元鎖定是一種互斥鎖定(0或1)的方法，它限制資源在同一時間內只能被一個交易存取。如圖 8.9 所示為一資料表層級的鎖定示意圖。,第八章 資料庫安全與交易管理,38,第八章 資料庫安
20、全與交易管理,39,並行控制之共享與互斥鎖定,二元鎖定是一種簡單的鎖定方法，但其對資源的使用效率並不是很好，會造成其他的交易浪費時間於等待。由於資料庫單元操作可分為讀取和寫入，因此共享與互斥鎖定是針對這兩種單元操作而設計，其方法如下所示：互斥鎖定/寫入鎖定(Write Lock) 共享鎖定/讀取鎖定(Read Lock),第八章 資料庫安全與交易管理,40,第八章 資料庫安全與交易管理,41,死結,雖然使用鎖定可以提供一個機制來確保資料庫的一致性，但是當多個交易競爭鎖定資源時，可能會發生死結(Deadlock)的現象。死結是指多個使用者互相鎖定住對方所需的資源，導致多筆交易陷入等待的狀態而無法
21、脫離。如圖 8.11 所示。,第八章 資料庫安全與交易管理,42,第八章 資料庫安全與交易管理,43,死結(續),為了避免因死結的發生而引發系統終止運作。DBMS可以使用如下的方法：等待圖(Wait-For Graph) DBMS會檢查執行中的交易的所有狀態，以確認是否有死結。 計時器監視系統每隔一段時間就檢查是否有處理停止狀態的交易，以確認是否有死結發生。若偵測到有死結，則使用ROLLBACK來解決交易處理。 二階段鎖定法交易執行時，第一階段是請求鎖定所有需要鎖定的資源，若未能鎖定全部所需的資源，則等待。當交易完成後，則進入第二階段，逐一解除鎖定。,第八章 資料庫安全與交易管理,44,死結(續),除了使用鎖定法於並行處理外，也可以使用其他非鎖定的方法以處理並行處理。如：時間戳記法(Time-Stamp Order)針對執行的交易設定一個唯一且遞增的時間戳記，然後利用這個時間戳記來決定那一個交易會被優先處理，而沒被優先處理的交易則執行ROLLBACK。 樂觀控制法(Optimistic Control)是指各交易的資料皆會被先寫入資料庫，直到執行確認(Commit)時才檢查會不會造成資料的不一致，若發生不一致時，就將該交易撤回(Rollback)。,
