sqlserver授權(quán)

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

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

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

4、 sp_adduser 'scott', 'register', 'public‘GOGRANT CREATE DATABASE, CREATE TABLE TO register, [DTCHANGdtchang]GO,第八章 資料庫(kù)安全與交易管理,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 {資料表|*|*.*|資料庫(kù)名稱.*} TO 安全帳號(hào)列表 [IDENTIFIED BY 密碼][WITH GRANT OPTION]其中安全帳號(hào)的名稱使用類似Email帳戶的格式表示法，如：scott@140.126.147.109，表示來(lái)自140.126.147.109的使用者scott。帳戶名稱可以使用萬(wàn)用字，如：scott@'140.126.147.%' 則表示任意來(lái)自140.126.1

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

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

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

9、ister對(duì)pubs資料庫(kù)中authors資料表的INSERT、UPDATE和DELETE權(quán)限，則可下達(dá)如下的指令：USE pubsGOREVOKE INSERT, UPDATE, DELETEON authorsFROM registerGO,第八章 資料庫(kù)安全與交易管理,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 {資料表|*|*.*|資料庫(kù)名稱.*} FROM 安全帳號(hào)列表例如移除 monty@'localhost' 對(duì)資料庫(kù)orders的INSERT及UPDATE權(quán)限，則可下達(dá)如下的指令：REVOKE INSERT, UPDATE ON orders.* FROM monty@'localhost';FLUSH PRIVILEGES;,第八章 資料庫(kù)安全與交易管理,12

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

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

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

14、回的動(dòng)作，則可下達(dá)ROLLBACK指令，其語(yǔ)法如下：【ROLLBACK語(yǔ)法】ROLLBACK,第八章 資料庫(kù)安全與交易管理,17,成功的交易,所謂交易成功，則須滿足四項(xiàng)基本的交易屬性：?jiǎn)卧?Atomicity)一致性(Consistency)隔離性(Isolation)永久性(Durability)以該四屬性的第一個(gè)字母組合成簡(jiǎn)稱 ACID 交易。,第八章 資料庫(kù)安全與交易管理,18,,第八章 資料庫(kù)安全與交易管理,1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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