sqlserver授權(quán)

1、第八章,資料庫安全與交易管理,第八章 資料庫安全與交易管理,2,章節(jié)概要,8.1 資料庫安全8.2 交易管理8.3 並行控制,第八章 資料庫安全與交易管理,3,8.1 資料庫安全,關(guān)聯(lián)式資料庫都可以採用如下兩種機制來控制資料庫的安全：使用SQL指令中的GRANT(授權(quán))與REVOKE(撤回)兩個命令來指定使用者的權(quán)限。利用視界(View)配合GRANT和REVOKE作為確保資料安全的機制。,第八章 資料庫安全與交易管理,4,使用

2、GRANT授權(quán)-SQL Server授權(quán),於SQL Server中使用指令(陳述式)授權(quán)的語法如下：【指令或陳述式授權(quán)】 GRANT {ALL|指令一,指令二,...} TO 安全帳號列表其中可授權(quán)的個別指令為：CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE、CREATE VIEW、BACKUP DA

3、TABASE及BACKUP LOG等等。,第八章 資料庫安全與交易管理,5,SQL Server授權(quán)(續(xù)),例如授權(quán)資料庫使用者register及Window NT使用者 DTCHANG\dtchang可建立資料庫(CREATE DATABASE)及建立資料表(CREATE TABLE)，則可下達如下的指令：(原register使用者無法存取master資料庫，授權(quán)的資料表位於master資料庫中)USE masterGOEXEC

4、 sp_adduser 'scott', 'register', 'public‘GOGRANT CREATE DATABASE, CREATE TABLE TO register, [DTCHANGdtchang]GO,第八章 資料庫安全與交易管理,6,MySQL權(quán)限授權(quán),於MySQL中使用GRANT授權(quán)使用者的使用權(quán)限之指令格式為：【MySQL權(quán)限授權(quán)】GRANT {ALL|權(quán)限一,

5、權(quán)限二,...} ON {資料表|*|*.*|資料庫名稱.*} TO 安全帳號列表 [IDENTIFIED BY 密碼][WITH GRANT OPTION]其中安全帳號的名稱使用類似Email帳戶的格式表示法，如：scott@140.126.147.109，表示來自140.126.147.109的使用者scott。帳戶名稱可以使用萬用字，如：scott@'140.126.147.%' 則表示任意來自140.126.1

6、47子網(wǎng)域的使用者scott。若單純只給定scott未給定網(wǎng)域名稱，則同義於 scott@'%'。,第八章 資料庫安全與交易管理,7,MySQL權(quán)限授權(quán)(續(xù)),MySQL中授權(quán)的權(quán)限等級分成四個等級：(以GRANT ALL為例)全域等級： GRANT ALL ON *.*資料庫等級：GRANT ALL ON 資料庫名稱.*資料表等級：GRANT ALL ON 資料庫名稱.資料表名稱欄位等級,第八章 資料庫安全與交

7、易管理,8,MySQL權(quán)限授權(quán)(續(xù)),假如我們想增加使用者monty使用資料庫orders的權(quán)限，但限制只能INSERT及UPDATE，則可下達如下的指令：GRANT INSERT, UPDATE ON orders.* TO monty@'localhost';接著下達更新權(quán)限表的指令：FLUSH PRIVILEGES;,第八章 資料庫安全與交易管理,9,使用REVOKE移除授權(quán)-移除SQL Server授權(quán),於

8、SQL Server中使用REVOKE移除指令(陳述式)授權(quán)的語法如下：【移除指令或陳述式授權(quán)】REVOKE {ALL|指令一,指令二,...} FROM 安全帳號列表而移除使用物件權(quán)限授權(quán)的語法則如下：【移除物件權(quán)限授權(quán)】REVOKE [GRANT OPTION FOR]{ALL|權(quán)限一,權(quán)限二,...} ON 物件FROM 安全帳號列表,第八章 資料庫安全與交易管理,10,移除SQL Server授權(quán)(續(xù)),例如移除reg

9、ister對pubs資料庫中authors資料表的INSERT、UPDATE和DELETE權(quán)限，則可下達如下的指令：USE pubsGOREVOKE INSERT, UPDATE, DELETEON authorsFROM registerGO,第八章 資料庫安全與交易管理,11,移除MySQL授權(quán)(續(xù)),於MySQL中使用REVOKE移除授權(quán)使用者的使用權(quán)限之指令格式為：【移除MySQL權(quán)限授權(quán)】REVOKE {AL

10、L|權(quán)限一,權(quán)限二,...} ON {資料表|*|*.*|資料庫名稱.*} FROM 安全帳號列表例如移除 monty@'localhost' 對資料庫orders的INSERT及UPDATE權(quán)限，則可下達如下的指令：REVOKE INSERT, UPDATE ON orders.* FROM monty@'localhost';FLUSH PRIVILEGES;,第八章 資料庫安全與交易管理,12

11、,8.2 交易管理,當(dāng)我們對資料庫存取資料時，如果有多個存取操作必須被完整地執(zhí)行，這些操作必須被視為一個不可被分割的單位，我們稱之為一個交易(Transaction)或異動。換言之，一個交易的成功必須是其不可被分割的各項操作皆須成功地被執(zhí)行。交易管理保證一個交易中的一序列的動作若不是全部完成，就應(yīng)該連一個動作也沒有被執(zhí)行。,第八章 資料庫安全與交易管理,13,,第八章 資料庫安全與交易管理,14,COMMIT確認與ROLLBACK撤回,

12、在同一個交易內(nèi)進行的資料操作，如：新增、更新和刪除資料列的操作等，並不會立即真實地反應(yīng)到資料庫，必須等到執(zhí)行COMMIT(確認或認可)的處理後，交易內(nèi)的多項操作才會一同反應(yīng)到資料庫。經(jīng)過COMMIT的動作之後，交易的異動才正式確立。經(jīng)過COMMIT後的資料庫就無法回復(fù)到交易前的狀態(tài)。,第八章 資料庫安全與交易管理,15,COMMIT確認與ROLLBACK撤回(續(xù)),然而，如果交易之內(nèi)的處理發(fā)生問題時，我們就可以執(zhí)行ROLLBACK(撤

13、回)的動作，將整個交易的處理回復(fù)到交易前的狀態(tài)。當(dāng)我們下達ROLLBACK後，所有的交易內(nèi)的操作動作都會被取消。因此，一個交易若不是以COMMIT成功地完成整個交易，即以ROLLBACK取消整個交易來結(jié)束這個交易。,第八章 資料庫安全與交易管理,16,COMMIT確認與ROLLBACK撤回(續(xù)),使用交易時，我們可以使用SQL中的資料控制語言COMMIT指令來下達確認的動作，其語法如下：【COMMIT語法】COMMIT若要執(zhí)行撤

14、回的動作，則可下達ROLLBACK指令，其語法如下：【ROLLBACK語法】ROLLBACK,第八章 資料庫安全與交易管理,17,成功的交易,所謂交易成功，則須滿足四項基本的交易屬性：單元性(Atomicity)一致性(Consistency)隔離性(Isolation)永久性(Durability)以該四屬性的第一個字母組合成簡稱 ACID 交易。,第八章 資料庫安全與交易管理,18,,第八章 資料庫安全與交易管理,1

15、9,,第八章 資料庫安全與交易管理,20,SQL Server,SQL Server中和交易相關(guān)的SQL指令和語法如下：【交易開始的指令】 BEGIN TRANSACTION [交易名稱 | @交易名稱變數(shù) [WITH MARK ['說明']]]【設(shè)定交易儲存點的指令】 SAVE TRANSACTION {儲存點名稱 | @儲存點名稱變數(shù)}【COMMIT指令】 COMMIT [TRANSACTION [

16、交易名稱 | @交易名稱變數(shù)]] COMMIT [WORK]【ROLLBACK指令】 ROLLBACK [TRANSACTION [交易名稱 | @交易名稱變數(shù) | 儲存點名稱 | @儲存點名稱]] ROLLBACK [WORK],第八章 資料庫安全與交易管理,21,SQL Server(續(xù)),以下則是示範(fàn)使用儲存點的用法：BEGIN TRANSACTIONGOUPDATE EmployeesSET EngName

17、 = 'Jennifer‘WHERE EmployeeID = 1001GOSAVE TRANSACTION AAAGOUPDATE EmployeesSET EngName = 'Mac‘WHERE EmployeeID = 1002GOROLLBACK TRANSACTION AAAGO,第八章 資料庫安全與交易管理,22,MySQL,對於MySQL而言，其所使用的語法和SQL Server雖然不

18、同，然而使用的邏輯則是差不多的。MySQL中和交易相關(guān)的SQL指令和語法如下：【交易開始的指令】BEGIN WORKBEGINSTART TRANSACTION【COMMIT指令】COMMIT【ROLLBACK指令】ROLLBACK,第八章 資料庫安全與交易管理,23,MySQL(續(xù)),MySQL的檔案儲存格式，有：MyISAM(使用硬碟儲存資料、快速但不支援交易、row locking；預(yù)設(shè)值)InnoDB(使用硬

19、碟儲存資料、支援交易、row locking)Memory(使用記憶體儲存資料，適用於暫存檔)MERGE(適用於log或文件資料檔)NDB(使用記憶體儲存資料、支援交易、row locking，適用於需要安全及即時之需求時)DBD(使用硬碟儲存資料、支援交易、page locking),第八章 資料庫安全與交易管理,24,二階段確認交易,對於分散式資料庫系統(tǒng)而言，需要特殊的確認處理，以避免在管理跨越多個資料庫伺服器的交易時發(fā)生問

20、題。確認大型交易時，因為記錄檔緩衝區(qū)轉(zhuǎn)存後釋放，所以需要相當(dāng)長的一段時間。而確認處理本身也會遇到強制復(fù)原的錯誤。如果交易管理員僅是要求各資料庫伺服器確認，那麼它可能會從一些資料庫伺服器處取得成功的狀態(tài)、而自某位資料庫伺服器處取得錯誤訊息。但這樣會產(chǎn)生衝突，因為所有分散式交易應(yīng)該復(fù)原，但有部分已經(jīng)被確認了。兩階段交易確認利用將確認分成兩個階段的方式，來找出問題的所在。將確認交易的方式分為兩個階段來執(zhí)行，稱為二階段確認交易(Two-Ph

21、ase Commit; 2PC) 。,第八章 資料庫安全與交易管理,25,第二階段,第八章 資料庫安全與交易管理,26,系統(tǒng)回復(fù),當(dāng)在使用資料庫的過程中可能會發(fā)生各式各樣的故障(Failure)，例如：系統(tǒng)當(dāng)機、硬碟故障……等。為了避免交易處理的結(jié)果因為故障而流失或產(chǎn)生錯亂，我們必須針對各種故障進行故障回復(fù)(Failure Recovery)或系統(tǒng)回復(fù)(System Recovery)。資料庫的故障大致可分為：交易處理的故障、系統(tǒng)故

22、障和硬碟故障等等類型。,第八章 資料庫安全與交易管理,27,交易處理的故障,針對這種類型的故障的對治方式就是不要執(zhí)行COMMIT動作，並執(zhí)行ROLLBACK動作，以讓資料庫回復(fù)到最近COMMIT時的狀態(tài)即可。,第八章 資料庫安全與交易管理,28,系統(tǒng)故障,系統(tǒng)故障指的是作業(yè)系統(tǒng)因某種原因?qū)е庐?dāng)機的故障問題，如：電力中斷，又稱為軟當(dāng)機(Soft Crash)。發(fā)生這種類型的故障時，於系統(tǒng)重新啟動時，資料庫系統(tǒng)可以回到資料庫上一次寫入更新資

23、料的檢查點(Check Point)來開始進行復(fù)原的工作。此時我們可以將交易分成五種狀況，如圖 8.5 所示。即：T1T2T3T4T5,第八章 資料庫安全與交易管理,29,第八章 資料庫安全與交易管理,30,硬碟故障情況,假如發(fā)生故障的原因是硬碟故障，那麼受損的狀況可能有三種：交易記錄受損：此時資料庫系統(tǒng)不會受到影響，但是無法使用交易記錄回復(fù)資料庫到故障前的狀態(tài)。資料庫受損：此時交易記錄未受損，故可載入先前備份的資料，並

24、使用交易記錄回復(fù)資料庫到故障前的狀態(tài)。交易記錄及資料庫皆受損：此時可載入先前備份的資料，然後以手動的方式儘量回復(fù)資料庫的內(nèi)容。,第八章 資料庫安全與交易管理,31,使用RAID預(yù)防硬碟故障,RAID(冗餘磁碟陣列)可分為如下不同的等級：RAID0：可將資料分散儲存多個磁碟中，即可將多個磁碟整合視為一個磁碟，稱為資料延展(Data Stripping)。RAID1：可將資料同時寫入到兩個完全相同的磁碟中，稱為映射(Mirroring

25、)。如果有一臺磁碟故障時，第二臺馬上取代之。RAID2：將資料以位元為單位分散儲存於多臺磁碟中，並用額外的一臺磁碟來儲存同位檢查位元(Parity Bit)。,第八章 資料庫安全與交易管理,32,使用RAID預(yù)防硬碟故障(續(xù)),RAID3：和RAID2相同，但是以位元組的方式來儲存資料。RAID4：和RAID2相同，但是以區(qū)塊(Block)的方式來儲存資料。RAID5：資料以區(qū)塊的方式分散儲存於多臺磁碟中，並將檢查位元分散儲存。(

26、目前常用)RAID6：和RAID5相同，但增加一個檢查位元之複本。,第八章 資料庫安全與交易管理,33,8.3 並行控制,由於資料庫系統(tǒng)大都允許多個使用者同時存取資料庫的資料，為了確保資料的一致性，資料庫系統(tǒng)必須使用並行控制(Concurrency Control)的方法來控制多個使用者的操作，以免產(chǎn)生如下常見之問題：更新遺失(Update Lost)未確認交易相依(Uncommitted Dependency) 不一致讀取(I

27、nconsistent Retrievals),第八章 資料庫安全與交易管理,34,更新遺失,第八章 資料庫安全與交易管理,35,未確認交易相依,ROLLBACK,第八章 資料庫安全與交易管理,36,不一致讀取,第八章 資料庫安全與交易管理,37,並行控制之二元鎖定,二元鎖定在鎖定資源時，只有鎖定(Locked)或非鎖定(Unlocked)兩種狀態(tài)。二元鎖定是一種互斥鎖定(0或1)的方法，它限制資源在同一時間內(nèi)只能被一個交易存取。如圖

28、8.9 所示為一資料表層級的鎖定示意圖。,第八章 資料庫安全與交易管理,38,第八章 資料庫安全與交易管理,39,並行控制之共享與互斥鎖定,二元鎖定是一種簡單的鎖定方法，但其對資源的使用效率並不是很好，會造成其他的交易浪費時間於等待。由於資料庫單元操作可分為讀取和寫入，因此共享與互斥鎖定是針對這兩種單元操作而設(shè)計，其方法如下所示：互斥鎖定/寫入鎖定(Write Lock) 共享鎖定/讀取鎖定(Read Lock),第八章 資料庫安

29、全與交易管理,40,第八章 資料庫安全與交易管理,41,死結(jié),雖然使用鎖定可以提供一個機制來確保資料庫的一致性，但是當(dāng)多個交易競爭鎖定資源時，可能會發(fā)生死結(jié)(Deadlock)的現(xiàn)象。死結(jié)是指多個使用者互相鎖定住對方所需的資源，導(dǎo)致多筆交易陷入等待的狀態(tài)而無法脫離。如圖 8.11 所示。,第八章 資料庫安全與交易管理,42,第八章 資料庫安全與交易管理,43,死結(jié)(續(xù)),為了避免因死結(jié)的發(fā)生而引發(fā)系統(tǒng)終止運作。DBMS可以使用如下的方

30、法：等待圖(Wait-For Graph) DBMS會檢查執(zhí)行中的交易的所有狀態(tài)，以確認是否有死結(jié)。 計時器監(jiān)視系統(tǒng)每隔一段時間就檢查是否有處理停止?fàn)顟B(tài)的交易，以確認是否有死結(jié)發(fā)生。若偵測到有死結(jié)，則使用ROLLBACK來解決交易處理。 二階段鎖定法交易執(zhí)行時，第一階段是請求鎖定所有需要鎖定的資源，若未能鎖定全部所需的資源，則等待。當(dāng)交易完成後，則進入第二階段，逐一解除鎖定。,第八章 資料庫安全與交易管理,44,死結(jié)(續(xù)),

31、除了使用鎖定法於並行處理外，也可以使用其他非鎖定的方法以處理並行處理。如：時間戳記法(Time-Stamp Order)針對執(zhí)行的交易設(shè)定一個唯一且遞增的時間戳記，然後利用這個時間戳記來決定那一個交易會被優(yōu)先處理，而沒被優(yōu)先處理的交易則執(zhí)行ROLLBACK。 樂觀控制法(Optimistic Control)是指各交易的資料皆會被先寫入資料庫，直到執(zhí)行確認(Commit)時才檢查會不會造成資料的不一致，若發(fā)生不一致時，就將該交易