1、數(shù)據(jù)庫技術(shù)基礎(chǔ)第5章 數(shù)據(jù)庫管理第5章 數(shù)據(jù)庫管理5.1 學(xué)習(xí)目標(biāo)5.2 視圖5.3 索引5.4 安全性5.5 完整性5.6 系統(tǒng)表5.7 小結(jié)5.1 學(xué)習(xí)目標(biāo)學(xué)完本章后，讀者應(yīng)具備以下能力：理解視圖的概念和作用。能建立視圖，會使用視圖實現(xiàn)數(shù)據(jù)的邏輯獨立性。理解索引的概念和作用。掌握建立索引的一般規(guī)則，能利用索引改善查詢性能。理解數(shù)據(jù)庫的操作權(quán)限，掌握授權(quán)的方法。理解角色的概念，能利用角色簡化權(quán)限管理。理解參照完整性的概念以及對SQL語句的影響。基本掌握用戶定義完整性約束的方法。第5章 數(shù)據(jù)庫管理5.1 學(xué)習(xí)目標(biāo)5.2 視圖5.3 索引5.4 安全性5.5 完整性5.6 系統(tǒng)表5.7 小結(jié)5.

2、2 視圖視圖是從一個或多個表中導(dǎo)出的表，用戶可以像對表一樣對它進(jìn)行查詢，在SELECT語句中可以出現(xiàn)表的地方都可以出現(xiàn)視圖。視圖是一個虛表，在數(shù)據(jù)庫中只存儲視圖的定義（一個SELECT語句）而不存放視圖的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，直到用戶使用視圖時才去執(zhí)行視圖的定義，求出數(shù)據(jù)。5.2 視圖（續(xù)） 5.2.1 建立視圖 5.2.2 刪除視圖 5.2.3 查詢視圖 5.2.4 更新視圖 5.2.5 視圖的作用5.2.1 建立視圖SQL語言用CREATE VIEW命令建立視圖，其一般格式為：CREATE VIEW (， )AS WITH CHECK OPTION；組成視圖的列名可以全

3、部省略或者全部指定。5.2.1 建立視圖（續(xù)）下列三種情況下必須明確指定組成視圖的所有列名：某個目標(biāo)列不是單純的列名，而是聚集函數(shù)或列表達(dá)式。多表連接時選出了幾個同名列作為視圖的列。需要在視圖中為某個列啟用更合適的名字。子查詢可以是任意復(fù)雜的SELECT語句，但通常不允許含有ORDER BY子句和DISTINCT短語。WITH CHECK OPTION表示，對視圖進(jìn)行UPDATE和INSERT操作時，要保證更新后的元組和新插入的元組滿足視圖定義中子查詢的WHERE子句中的條件表達(dá)式。5.2.1 建立視圖（續(xù)） 例5.1 建立計算機(jī)學(xué)院學(xué)生的視圖。 CREATE VIEW Student_CS

4、AS SELECT * FROM Student WHERE Sdept = 計算機(jī)學(xué)院 例5.1在表Student上建立了視圖Student_CS，但是沒有明確指出視圖Student_CS的列名，則構(gòu)成視圖的列與SELECT子句相同。即Student_CS有Sno、Sname、Sgender、Sage和Sdept共5個列，這5個列分別對應(yīng)Student的Sno、Sname、Sgender、Sage和Sdept列。 CREATE VIEW語句的執(zhí)行結(jié)果是在DBMS的數(shù)據(jù)字典中保存了視圖名和SELECT語句。5.2.1 建立視圖(續(xù))例5.2 建立英語課（1156）成績單的視圖。CREATE V

5、IEW English_Grade(Sno, Sname, Grade)ASSELECT Student.Sno, Sname, GradeFROM Student JOIN SC ON Student.Sno = SC.Sno AND SC.Cno = 1156例2在表Student和SC上建立了視圖English_Grade。它有3個列：Sno、Sname和Grade，分別對應(yīng)表Student的Sno列、Sname列和表SC的Grade列。因為SELECT語句中包含了Student表與SC表的同名列Sno，所以必須在視圖名后面明確說明視圖的各個列名。5.2.1 建立視圖（續(xù)）例5.3 定義

6、一個反映學(xué)生出生年份的視圖。CREATE VIEW BT_S(Sno, Sname, Sbirthday)ASSELECT Sno, Sname, datepart(getdate() , year) SageFROM Student由于Student表的Sage列存放了學(xué)生的年齡，沒有存放其出生年份，例5.3定義的視圖由學(xué)號、學(xué)生姓名和學(xué)生出生年份三個列組成。getdate()返回系統(tǒng)日期，datepart函數(shù)求出日期中的年份。由于SELECT子句中出現(xiàn)了表達(dá)式，則必須指明視圖的列名。視圖不僅可以建立在一個或多個表上，也可以建立在一個或多個已定義好的視圖上，或建立在表與視圖上。5.2.1 建

7、立視圖（續(xù)）例5.4 建立英語課的成績在80分以上的學(xué)生的視圖。CREATE VIEW English_Grade_80ASSELECT Sno, Sname, GradeFROM English_GradeWHERE Grade = 80已經(jīng)定義過的視圖可以和表一樣使用。例5.4中的FROM子句中出現(xiàn)了在例5.2定義過的視圖English_Grade，因此，視圖English_Grade_80是建立在視圖English_Grade之上。5.2 視圖 5.2.1 建立視圖 5.2.2 刪除視圖 5.2.3 查詢視圖 5.2.4 更新視圖 5.2.5 視圖的作用5.2.2 刪除視圖當(dāng)不再需要一個

8、視圖時，可以刪除它，語句格式為：DROP VIEW 例5.5 刪除視圖Student_CS。DROP VIEW Student_CSDROP VIEW語句執(zhí)行后，DBMS從數(shù)據(jù)字典中刪除視圖Student_CS和定義它的SELECT語句。例5.6 刪除視圖English_Grade。DROP VIEW English_Grade5.2 視圖 5.2.1 建立視圖 5.2.2 刪除視圖 5.2.3 查詢視圖 5.2.4 更新視圖 5.2.5 視圖的作用5.2.3 查詢視圖定義視圖以后，就可以像對表一樣對視圖進(jìn)行查詢。例5.7 查找計算機(jī)學(xué)院年齡小于19歲的學(xué)生的姓名。視圖Student_CS包含

9、有計算機(jī)學(xué)院全體學(xué)生的信息，可以直接對視圖進(jìn)行查詢。SELECT SnameFROM Student_CSWHERE Sage 19 5.2.3 查詢視圖（續(xù)）對視圖進(jìn)行查詢時，DBMS要進(jìn)行視圖消解工作，把對視圖的查詢轉(zhuǎn)換為對基本表（定義視圖時涉及的表）的查詢，即把對視圖查詢的一個SQL語句，轉(zhuǎn)換為對基本表查詢的SQL語句。視圖消解的基本過程分為個步驟：從數(shù)據(jù)字典中取出定義視圖的子查詢（SELECT語句）。用子查詢的FROM子句替換要執(zhí)行的SELECT的FROM子句。根據(jù)定義視圖時，視圖的列和基本表的列的對應(yīng)關(guān)系，映射要執(zhí)行的SELECT子句的列到基本表的列。將定義視圖的子查詢的WHERE子

10、句的條件表達(dá)式合并到要執(zhí)行的SELECT語句的WHERE子句中，邏輯關(guān)系是與關(guān)系。5.2.3 查詢視圖（續(xù)）SELECT Sno, SageFROM Student_CSWHERE Sage 19 SELECT *FROM StudentWHERE Sdept = 計算機(jī)學(xué)院SELECT Student.Sno, Student.SageFROM StudentWHERE Sage = 80這時，DBMS無法像前面例5.7那樣得到一個等價的SELECT語句。DBMS采用第二種視圖消解方法。先執(zhí)行定義視圖S_G的SELECT語句，得到了一個結(jié)果，把它作為一個臨時表tmp_S_G，然后將上面的查詢

11、語句改寫為：SELECT *FROM tmp_S_GWHERE Gavg = 80同樣可以得到正確結(jié)果。因此，我們可以把視圖當(dāng)作表一樣進(jìn)行查詢，而不必關(guān)心DBMS如何進(jìn)行處理。5.2 視圖 5.2.1 建立視圖 5.2.2 刪除視圖 5.2.3 查詢視圖 5.2.4 更新視圖 5.2.5 視圖的作用5.2.4 更新視圖更新視圖是指向視圖中插入（INSERT）、刪除（DELETE）和更新（UPDATE）數(shù)據(jù)。對視圖的更新操作也要通過視圖消解轉(zhuǎn)換為對表的更新操作。不是所有的視圖都是可更新的，因為有些視圖的更新不能唯一有意義地轉(zhuǎn)換成對相應(yīng)表的更新。5.2.4 更新視圖（續(xù)）例如,例5.8定義的視圖S

12、_G是由“學(xué)號”和“平均成績”兩個屬性列組成的，其中“平均成績”一項是由Student表中對元組分組后計算平均值得來的。如果我們想把視圖S_G中學(xué)號為2007012的學(xué)生的平均成績改成90分，SQL語句如下：UPDATE S_GSET Gavg = 90WHERE Sno = 2007012但這個對視圖的更新是無法轉(zhuǎn)換成對表SC的更新的，因為系統(tǒng)無法修改各科成績，以使平均成績成為90。所以S_G視圖是不可更新的。5.2.4 更新視圖（續(xù)）行列子集視圖 若一個視圖是從單個表導(dǎo)出的，并且只是去掉了表的某些行和某些列，但保留了主碼，則這類視圖稱為行列子集視圖。應(yīng)該指出的是，不可更新的視圖與不允許更新

13、的視圖是兩個不同的概念。前者指理論上已證明其是不可更新的視圖。后者指實際系統(tǒng)中不支持其更新，但它本身有可能是可更新的視圖。對于行列子集視圖的更新，DBMS也要進(jìn)行視圖消解，把對視圖的更新轉(zhuǎn)換為對基本表的更新。5.2.4 更新視圖（續(xù)）例.9 將計算機(jī)學(xué)院的學(xué)生馬翔的姓名改為馬飛翔。UPDATE Student_CSSET Sname = 馬飛翔WHERE Sname = 馬翔DBMS進(jìn)行視圖消解后，得到下面的語句：UPDATE StudentSET Sname = 馬飛翔WHERE Sname = 馬翔 AND Sdept = 計算機(jī)學(xué)院 5.2.4 更新視圖（續(xù)）例5.10 計算機(jī)學(xué)院增加一

14、個新生，學(xué)號為2007015，姓名為趙新，年齡為20歲。INSERTINTO Student_CS(Sno, Sname, Sage)VALUES(2007015, 趙新, 20)轉(zhuǎn)換后的更新語句為：INSERTINTO Student(Sno, Sname, Sage)VALUES(2007015, 趙新, 20)5.2.4 更新視圖（續(xù)）例5.11 刪除計算機(jī)學(xué)院學(xué)生趙新，學(xué)號是2007015。DELETEFROM Student_CSWHERE Sno= 2007015轉(zhuǎn)換為對表的刪除操作：DELETEFROM StudentWHERE Sno= 2007015 AND Sdept =

15、計算機(jī)學(xué)院5.2.4 更新視圖（續(xù)）要防止用戶通過視圖對數(shù)據(jù)庫進(jìn)行增刪改時有意或無意地對不屬于視圖范圍內(nèi)（不滿足子查詢的過濾條件）的基本表數(shù)據(jù)進(jìn)行操作，則在視圖定義時要加上WITH CHECK OPTION子句。WITH CHECK OPTION短語相當(dāng)于在視圖上施加了一個元組級約束條件，更新前后的元組必須滿足定義視圖的子查詢的過濾條件。若操縱的元組不滿足條件，則拒絕執(zhí)行該操作。5.2.4 更新視圖（續(xù)）例5.12 建立計算機(jī)學(xué)院學(xué)生的視圖，要求進(jìn)行更新操作前后的元組要保證滿足視圖的過濾條件（即Sdept列上的值是計算機(jī)學(xué)院）。CREATE VIEW Student_CSASSELECT *F

16、ROM StudentWHERE Sdept= 計算機(jī)學(xué)院WITH CHECK OPTION由于在定義Student_CS視圖時加上了WITH CHECK OPTION子句，所以以后對該視圖進(jìn)行插入、修改時，DBMS會自動檢查插入的元組和修改后的元組在Sdept列上的值是否等于計算機(jī)學(xué)院。例如，DBMS會拒絕執(zhí)行下面的對視圖進(jìn)行修改的SQL語句。INSERTINTO Student_CS(Sno, Sname, Sage)VALUES(2007015, 趙新, 20) -新插入的元組在Sdept的值不等于計算機(jī)學(xué)院UPDATE Student_CSSET Sdept = 環(huán)境學(xué)院 -試圖將Sd

17、ept的值由計算機(jī)學(xué)院更改為環(huán)境學(xué)院WHERE Sno = 20070125.2 視圖 5.2.1 建立視圖 5.2.2 刪除視圖 5.2.3 查詢視圖 5.2.4 更新視圖 5.2.5 視圖的作用5.2.5 視圖的作用視圖能夠簡化用戶的操作視圖可以減少冗余數(shù)據(jù)視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨立性視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)5.2.5 視圖的作用（續(xù)）例如，將學(xué)生關(guān)系拆分為SX和SY兩個關(guān)系：Student(Sno，Sname，Ssex，Sage，Sdept) SX(Sno，Sname，Sage)SY(Sno，Ssex，Sdept)這時，表Student為SX表和SY表自然連接的結(jié)果。

18、如果建立一個視圖Student：CREATE VIEW Student(Sno， Sname， Ssex， Sage， Sdept)ASSELECT SX.Sno， SX.Sname， SY.Ssex， SX.Sage， SY.SdeptFROM SX， SYWHERE SX.Sno = SY.Sno；盡管數(shù)據(jù)庫的邏輯結(jié)構(gòu)改變了，但應(yīng)用程序并不必修改。當(dāng)然，應(yīng)用程序中修改數(shù)據(jù)的語句可能仍會因表結(jié)構(gòu)的改變而改變。第5章 數(shù)據(jù)庫管理5.1 學(xué)習(xí)目標(biāo)5.2 視圖5.3 索引5.4 安全性5.5 完整性5.6 系統(tǒng)表5.7 小結(jié)5.3 索引1.1 學(xué)習(xí)目標(biāo)1.2 數(shù)據(jù)庫系統(tǒng)的定義1.3 數(shù)據(jù)庫系統(tǒng)的特

19、點 123圖5.2 圖書目錄和圖書的關(guān)系5.3 索引（續(xù)） 5.3.1 索引的基本概念 5.3.2 索引的建立和維護(hù)5.3.1 索引的基本概念索引的定義 索引是一個獨立的、物理的數(shù)據(jù)庫結(jié)構(gòu)，基于表的一列或多列而建立，按照列值從小到大或從大到小排序，提供了一個新存取路徑。20070122007014200711320072562007278SnoSnameSgenderSageSdept2007012馬翔男19計算機(jī)學(xué)院2007113劉大方男18管理學(xué)院2007256史玉明女19管理學(xué)院2007278龔兵男19管理學(xué)院2007014張曉敏女18計算機(jī)學(xué)院圖5.3 建立在表Student的列Sno

20、上的索引5.3.1 索引的基本概念（續(xù)）圖5.3的左面給出了在表Student中學(xué)號列上建立的索引，索引項的值按照從小到大的次序排序。表本身提供了一個存取路徑，即順序訪問，按照元組存放的先后順序逐個訪問每個元組，2007012、2007113、2007256索引提供了另外一個存取路徑，順序訪問索引，讀出一條索引后，再根據(jù)指針讀取表中的元組。按照索引提供的訪問路徑，訪問元組的次序為2007012、2007014、2007113SnoSnameSgenderSageSdept2007012馬翔男19計算機(jī)學(xué)院2007113劉大方男18管理學(xué)院2007256史玉明女19管理學(xué)院2007278龔兵男1

21、9管理學(xué)院2007014張曉敏女18計算機(jī)學(xué)院0140121132562781130145.3.1 索引的基本概念（續(xù)）圖5.4 樹形式的索引圖5.3中以線性表（數(shù)組）的形式表示索引。實際上，在數(shù)據(jù)庫中，索引往往被組織成一棵B+樹。在B+樹中，所有的碼都出現(xiàn)在B+樹的葉子節(jié)點中，并按照碼值從小到大的順序組織成了一個鏈表。非葉子節(jié)點由n個碼和n+1個指針組成，碼和指針的排列次序為P0，K1，P1，K2，Pn，Kn，K1K2Kn，P0，P1，Pn指向了n+1個節(jié)點，指針Pi-1指向節(jié)點中的每個碼的值都小于等于Ki。B+樹的具體內(nèi)容請讀者參閱“數(shù)據(jù)結(jié)構(gòu)”或“數(shù)據(jù)庫原理”的相關(guān)書籍。圖5.3的索引被組

22、織成一個2階B+樹的形式如圖5.4（為了節(jié)省空間，節(jié)點中的碼只給出了后3位）。B+樹索引提供了另外一個存取路徑，可以快速地定位表中的某個元組。例如，要讀取元組2007014，首先在根節(jié)點中查找，然后根據(jù)指針P0，找到最左面的葉子節(jié)點，最后，根據(jù)葉子節(jié)點的指針，讀取所需要的元組。B+樹是一棵平衡樹，從根節(jié)點到任何一個葉子節(jié)點的路徑長度相同。因此，假設(shè)B+樹的深度為L，讀取表中任何一個元組需要讀B+樹中的L個節(jié)點。5.3.1 索引的基本概念（續(xù)）索引的分類按照表中建立索引的那一列（或列組合）中的數(shù)據(jù)是否各不相同，可以將索引分為唯一索引（UNIQUE）和非唯一索引（NOT UNIQUE）。按照索引的

23、結(jié)構(gòu)，可以將其劃分為兩大類，聚簇索引（Clustered Index）和非聚簇索引（Nonclustered index）。聚簇索引要求將表中的元組與索引鍵值以同樣的物理順序存儲，非聚簇索引則無此要求。聚簇索引能提高某些類型的查詢效率。每個表最多只能有一個聚簇索引。5.3.1 索引的基本概念（續(xù)）SnoSnameSgenderSageSdept2007012馬翔男19計算機(jī)學(xué)院2007113劉大方男18管理學(xué)院2007256史玉明女19管理學(xué)院2007278龔兵男19管理學(xué)院2007014張曉敏女18計算機(jī)學(xué)院student計算機(jī)學(xué)院管理學(xué)院圖5.5 非唯一索引5.3.1 索引的基本概念（續(xù)）

24、建立索引的原則值得建立索引：記錄有一定規(guī)模，而查詢只局限于少數(shù)記錄。索引用得上：索引列在WHERE子句中頻繁使用。通常，下列情況需要在表中的某一列或某些列上建立索引：經(jīng)常用作查詢條件的列。需要頻繁地按范圍搜索的列。連接中頻繁使用的列。在ORDER BY子句中經(jīng)常使用的列。主關(guān)鍵字或外關(guān)鍵字的列。值是唯一的列（如IDENTITY）。先裝數(shù)據(jù)，后建立索引。5.3.1 索引的基本概念（續(xù)）5.3 索引 5.3.1 索引的基本概念 5.3.2 索引的建立和維護(hù)5.3.2 索引的建立和維護(hù)建立索引格式 CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX ON (，

25、)；是要建索引的表的名字。索引可以建立在表的一列或多列上，各列名之間用逗號分隔每個后面還可以用指定索引值的排列次序，可選ASC（升序）或DESC（降序），默認(rèn)值為ASC。UNIQUE表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄。CLUSTERED表示要建立的索引是聚簇索引。NONCLUSTERED意味著建立非聚簇索引。例如，執(zhí)行下面的CREATE INDEX語句：CREATE CLUSTERED INDEX Stusname ON Student(Sname)將在Student表Sname列上建立一個聚簇索引，而且Student表中的記錄將按照Sno值的升序存放。5.3.1 索引的建立和維護(hù)（

26、續(xù)）例5.13 為學(xué)生-課程數(shù)據(jù)庫中的Student、Couse、SC三個表建立索引。其中Student表按學(xué)號升序建唯一索引，Course表按課程號降序建唯一索引，SC表按學(xué)號升序和課程號降序建唯一索引。CREATE UNIQUE INDEX Stusno ON Student(Sno)CREATE UNIQUE INDEX Coucno ON Course(Cno DESC)CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC)5.3.1 索引的建立和維護(hù)（續(xù)）刪除索引格式 DROP INDEX 例5.14刪除Student表的Stusname索

27、引。DROP INDEX Student.Stusno刪除索引時，系統(tǒng)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述，同時從數(shù)據(jù)區(qū)刪除索引的數(shù)據(jù)。5.3.1 索引的建立和維護(hù)（續(xù)）第5章 數(shù)據(jù)庫管理5.1 學(xué)習(xí)目標(biāo)5.2 視圖5.3 索引5.4 安全性5.5 完整性5.6 系統(tǒng)表5.7 小結(jié)5.4 安全性數(shù)據(jù)庫中存放了一個組織的全部數(shù)據(jù)，許多日常工作都要依賴于數(shù)據(jù)庫系統(tǒng)，如果數(shù)據(jù)庫的數(shù)據(jù)安全出現(xiàn)了問題，會影響到組織的正常運行。5.4 安全性 5.4.1 登錄名與用戶 5.4.2 權(quán)限 5.4.3 授權(quán) 5.4.4 收回權(quán)限 5.4.5 角色 5.4.6 一個實例 5.4.7 權(quán)限審核5.4.1 登錄名與用戶

28、增加登錄名格式一sp_addlogin loginame = login , passwd = password , defdb = database , deflanguage = language , sid = sid , encryptopt = encryption_option 登錄名用戶名圖5.6 使用SQL Server的登錄過程參數(shù)loginame = login：登錄的名稱。passwd = password：登錄密碼。defdb = database：登錄的默認(rèn)數(shù)據(jù)庫。deflanguage = language ：用戶登錄到 SQL Server 時系統(tǒng)指派的默認(rèn)語言。

29、sid = sid：安全標(biāo)識號 (SID)。encryptopt = encryption_option：指定當(dāng)密碼存儲在系統(tǒng)表中時，密碼是否要加密。功能：存儲過程sp_addlogin添加SQL Server登錄名。權(quán)限：只有 sysadmin 和 securityadmin 固定服務(wù)器角色的成員才可以執(zhí)行 sp_addlogin。5.4.1 登錄名與用戶（續(xù)）例5.15 增加登錄名st1、st2、st3、st4、U5、U6和U7，密碼使用默認(rèn)設(shè)置值NULL，默認(rèn)數(shù)據(jù)庫為S_C_SC。以sa登錄進(jìn)SQL Server，使用查詢分析器，執(zhí)行下面的語句：sp_addlogin loginame

30、= st1,defdb = S_C_SCGOsp_addlogin loginame = st2,defdb = S_C_SCGOsp_addlogin loginame = st3,defdb = S_C_SCGOsp_addlogin loginame = st4,defdb = S_C_SCGOsp_addlogin loginame = U5,defdb = S_C_SCGOsp_addlogin loginame = U6,defdb = S_C_SCGOsp_addlogin loginame = U7,defdb = S_C_SCGO5.4.1 登錄名與用戶（續(xù)）格式二：sp_g

31、rantlogin loginame = login參數(shù)：loginame = login表示要添加的 Windows NT 用戶或組的名稱。Windows NT 組和用戶必須用 Windows NT 域名限定，格式為域用戶，例如 LondonJoeb。功能：使 Windows NT 用戶或組賬戶得以使用 Windows 身份驗證連接到 SQL Server。權(quán)限：僅 sysadmin 或 securityadmin 固定服務(wù)器角色的成員可以執(zhí)行 sp_grantlogin。5.4.1 登錄名與用戶（續(xù)）例5.16 將計算機(jī)機(jī)lsnmobile的Windows本地用戶lsn增加為登錄名。sp_

32、grantlogin lsnmobilelsnGO5.4.1 登錄名與用戶（續(xù)）刪除登錄名格式一： sp_droplogin loginame = login參數(shù)： loginame = login表示將被刪除的登錄。login 必須已經(jīng)存在于 SQL Server 中。功能：刪除 SQL Server 登錄，以阻止使用該登錄名訪問 SQL Server。權(quán)限：只有 sysadmin 和 securityadmin 固定服務(wù)器角色的成員才能執(zhí)行 sp_droplogin。5.4.1 登錄名與用戶（續(xù)）格式二： sp_revokelogin loginame = login參數(shù)： loginam

33、e = login：是 Windows NT 用戶或組的名稱。功能：從 SQL Server 中刪除用 sp_grantlogin 或 sp_denylogin 創(chuàng)建的 Windows NT 用戶或組的登錄項。權(quán)限：只有 sysadmin 和 securityadmin 固定服務(wù)器角色的成員才能執(zhí)行 sp_revokelogin。5.4.1 登錄名與用戶（續(xù)）例5.17 刪除登錄名U5。sp_droplogin U5GO5.4.1 登錄名與用戶（續(xù)）增加數(shù)據(jù)庫用戶格式：sp_grantdbaccess loginame = login ,name_in_db = name_in_db OUTP

34、UT參數(shù)：loginame = login：SQL Server中的登錄名。name_in_db = name_in_db OUTPUT：數(shù)據(jù)庫中用戶的名稱5.4.1 登錄名與用戶（續(xù)）功能：為 SQL Server登錄或 Windows NT 用戶或組在當(dāng)前數(shù)據(jù)庫中添加一個用戶安全賬戶，并使其能夠被授予在數(shù)據(jù)庫中執(zhí)行活動的權(quán)限。權(quán)限：只有 sysadmin 固定服務(wù)器角色、db_accessadmin 和 db_owner 固定數(shù)據(jù)庫角色的成員才能執(zhí)行 sp_grantdbaccess。5.4.1 登錄名與用戶（續(xù)）例5.18 使登錄名st1、st2、st3、st4、u5、u6和u7分別成為

35、數(shù)據(jù)庫S_C_SC的用戶U1、U2、U3、U4、U5、U6和U7。USE S_C_SCGOsp_grantdbaccess st1,U1GOsp_grantdbaccess st2,U2GOsp_grantdbaccess st3,U3GOsp_grantdbaccess st4,U4GOsp_grantdbaccess u5,U5GO sp_grantdbaccess u6,U6GO sp_grantdbaccess u7,U7GO 5.4.1 登錄名與用戶（續(xù)）刪除數(shù)據(jù)庫用戶格式：sp_revokedbaccess name_in_db = name參數(shù)：name_in_db = name

36、：是要刪除的賬戶名。功能：從當(dāng)前數(shù)據(jù)庫中刪除安全賬戶。權(quán)限：只有 sysadmin 固定服務(wù)器角色成員及 db_accessadmin 和 db_owner 固定數(shù)據(jù)庫角色成員才能執(zhí)行 sp_revokedbaccess。例5.19 刪除數(shù)據(jù)庫S_C_SC的用戶U5。sp_revokedbaccess U5GO5.4.1 登錄名與用戶（續(xù)）5.4 安全性 5.4.1 登錄名與用戶 5.4.2 權(quán)限 5.4.3 授權(quán) 5.4.4 收回權(quán)限 5.4.5 角色 5.4.6 一個實例 5.4.7 權(quán)限審核5.4.2 權(quán)限Select, Insert, Update, Delete, AllSelect

37、, Insert, Update, Delete, Alter, Index, All數(shù)據(jù)庫表視圖列Connect, CreateUpdate圖5.6 數(shù)據(jù)庫對象的層次和權(quán)限5.4.2 權(quán)限（續(xù)）. 數(shù)據(jù)庫成為數(shù)據(jù)庫管理系統(tǒng)的用戶后，還要獲得對數(shù)據(jù)庫的Connect權(quán)限，才能連接到數(shù)據(jù)庫，使用數(shù)據(jù)庫。Create權(quán)限說明用戶可以在數(shù)據(jù)庫中使用CREATE語句創(chuàng)建視圖、表等數(shù)據(jù)庫對象。數(shù)據(jù)庫的創(chuàng)建者自動擁有對數(shù)據(jù)庫的全部權(quán)限。5.4.2 權(quán)限（續(xù)）2. 表 按照對表的操作類型劃分，有6類權(quán)限。如果某用戶對一個表擁有Select、Insert、Delete、Update權(quán)限，則表示 該用戶可以在S

38、ELECT、INSERT、DELETE、UPDATE語句中引用該表，即可以對表執(zhí)行選擇、插入、刪除和修改操作。Alter權(quán)限表示可以用ALTER語句改變表的模式以及施加在表上的各種約束。擁有Index權(quán)限意味著能用CREATE INDEX語句在表上建立索引。All是All Privileges的縮寫，即擁有對表的所有6種權(quán)限。 表的建立者或者表的所有者擁有對表所有的操作權(quán)利。3. 視圖 視圖的4種權(quán)限的含義同表，視圖的創(chuàng)建者自動擁有全部權(quán)限。4. 列 列的權(quán)限是對表權(quán)限的進(jìn)一步細(xì)化。一般地講，擁有對表的Update權(quán)限也就擁有了對表中所有列的Update權(quán)限，但是，在分配對表的Update權(quán)限

39、時，也可以排除對某些列的Update權(quán)限。5.4.2 權(quán)限（續(xù)）5.4 安全性 5.4.1 登錄名與用戶 5.4.2 權(quán)限 5.4.3 授權(quán) 5.4.4 收回權(quán)限 5.4.5 角色 5.4.6 一個實例 5.4.7 權(quán)限審核5.4.3 授權(quán)SQL語言用GRANT語句向用戶授予操作權(quán)限， GRANT語句的一般格式為：GRANT ， ON TO ，WITH GRANT OPTION；GRANT語句在數(shù)據(jù)庫管理系統(tǒng)的數(shù)據(jù)字典里記錄下哪個用戶擁有哪些（個）數(shù)據(jù)庫對象的何種權(quán)限。例5.20把查詢Student表的權(quán)限授給用戶U1。GRANT SELECT ON StudentTO U1請思考，誰有權(quán)限執(zhí)

40、行這個語句呢？例5.21把對Student表和Course表的全部操作權(quán)限授予用戶U2和U3。GRANT ALL PRIVILEGES ON Student TO U2, U3GOGRANT ALL PRIVILEGES ON Course TO U2, U3GO5.4.3 授權(quán)（續(xù)）例5.22把對表SC的查詢權(quán)限授予所有用戶。GRANT SELECT ON SC TO PUBLIC注意：PUBLIC代表所有的用戶，這些用戶既可以是目前數(shù)據(jù)庫管理系統(tǒng)中已經(jīng)有的用戶，也可以是目前沒有，以后才增加的用戶。例5.23把查詢Student表和修改學(xué)生學(xué)號的權(quán)限授給用戶U4。GRANT UPDATE(S

41、no), SELECTON StudentTO U4請注意，U4只能更改列Sno的值，而不能修改表Student的其他列。如果允許U4修改表Student的全部列，則應(yīng)該執(zhí)行下面的語句：GRANT UPDATE, SELECTON StudentTO U45.4.3 授權(quán)(續(xù))例5.24把對表SC的INSERT權(quán)限授予U5用戶，并允許將此權(quán)限再授予其他用戶。GRANT INSERTON SC TO U5 WITH GRANT OPTIONWITH GRANT OPTION表明，U5不僅擁有了對表SC的INSERT權(quán)限，還可以傳播此權(quán)限。例如U5可以將此權(quán)限授予U6：GRANT INSERT O

42、N SC TO U6 WITH GRANT OPTION同樣，U6還可以將此權(quán)限授予U7：GRANT INSERT ON SC TO U7因為U6未給U7傳播的權(quán)限，U7不能再傳播此權(quán)限。5.4.3 授權(quán)(續(xù))5.4 安全性 5.4.1 登錄名與用戶 5.4.2 權(quán)限 5.4.3 授權(quán) 5.4.4 收回權(quán)限 5.4.5 角色 5.4.6 一個實例 5.4.7 權(quán)限審核5.4.4 收回權(quán)限授予的權(quán)限可以由授予者用REVOKE語句收回。REVOKE語句的一般格式為：REVOKE ， ON FROM ，；例5.25把用戶U4修改學(xué)生學(xué)號的權(quán)限收回。REVOKE UPDATE(Sno)ON Stude

43、ntFROM U4例5.26收回所有用戶對表SC的查詢權(quán)限。REVOKE SELECTON SC FROM PUBLIC例5.27把用戶U5對SC表的INSERT權(quán)限收回。REVOKE INSERTON SC FROM U5 CASCADE5.4.4 收回權(quán)限5.4 安全性 5.4.1 登錄名與用戶 5.4.2 權(quán)限 5.4.3 授權(quán) 5.4.4 收回權(quán)限 5.4.5 角色 5.4.6 一個實例 5.4.7 權(quán)限審核5.4.5 角色角色是一個DBMS的用戶的集合，該集合中的用戶要操作相同的數(shù)據(jù)庫對象，需要擁有相同的權(quán)限。I角色1角色2U1U2U3U4U5T1T1T2T2T3USAS圖5.7 角

44、色及其授權(quán)1. 角色管理(1) 增加/刪除角色例5.28在數(shù)據(jù)庫中增加角色Managers，刪除角色Sales。sp_addrole ManagersGOsp_droprole SalesGO5.4.5 角色(續(xù))(2)給角色指派/撤銷成員例5.29使數(shù)據(jù)庫用戶John成為角色Sales的成員，從數(shù)據(jù)庫角色Manager中刪除成員Jeff。sp_addrolemember Sales, JohnGOsp_droprolemember Managers, JeffGO例5.30特別地，一個角色也可以作為另一個角色的成員，讓角色Managers成為角色Sales的成員。sp_droprolemem

45、ber Sales, ManagersGOManagers成為Sales的成員則意味著Managers的成員屬于Managers和Sales兩個角色。5.4.5 角色(續(xù))2. 固定角色(1)服務(wù)器角色Sysadmin： 能夠在SQL Server內(nèi)作任何事情。Serveradmin：能夠修改SQL Server的設(shè)置和停止SQL Server。Setupadmin：可以管理鏈接服務(wù)器和啟動過程。Securityadmin：管理服務(wù)器用戶。Processadmin：可以管理在 SQL Server 中運行的進(jìn)程。Dbcreator：能夠創(chuàng)建、修改和刪除數(shù)據(jù)庫。Diskadmin：能夠管理磁盤文

46、件。Bulkadmin：可以執(zhí)行BULK INSERT語句。5.4.5 角色(續(xù))(2)數(shù)據(jù)庫角色Db_Owner：在數(shù)據(jù)庫中有全部權(quán)限。Db_accessadmin：管理數(shù)據(jù)庫用戶。Db_datareader：可以讀取數(shù)據(jù)庫內(nèi)任何用戶表中的所有數(shù)據(jù)。Db_datawriter：可以更改數(shù)據(jù)庫內(nèi)任何用戶表中的所有數(shù)據(jù)。Db_ddladmin：可以發(fā)出所有的DDL，但不能發(fā)出GRANT、REVOKE語句。Db_backupoperator：具有備份數(shù)據(jù)庫的限利。5.4.5 角色(續(xù))Db_securityadmin可以管理角色和角色成員資格；用GRANT、REVOKE語句將任何數(shù)據(jù)對象的全部權(quán)限

47、授予任何數(shù)據(jù)庫用戶。Db_denydatareader不能讀取數(shù)據(jù)庫內(nèi)任何用戶表中的任何數(shù)據(jù)Db_denydatawriter不能更改數(shù)據(jù)庫內(nèi)任何用戶表中的任何數(shù)據(jù)。例5.31 使用戶DBManager擁有創(chuàng)建數(shù)據(jù)庫的權(quán)限。只要將DBmanager增加到服務(wù)器固定角色Dbcreator，用戶Dbmanager就擁有了創(chuàng)建數(shù)據(jù)庫的權(quán)限。sp_addsrvrolemember DBmanager, Dbcreator GO例5.32 使用戶Manager擁有管理數(shù)據(jù)庫的常用權(quán)限。-建立數(shù)據(jù)庫用戶Manager（登錄名也是Manager），由該用戶管理數(shù)據(jù)庫的用戶管理和權(quán)限管理5.4.5 角色(續(xù))

48、sp_grantdbaccess Manager,ManagerGOsp_addrolemember Db_accessadmin,Manager -管理數(shù)據(jù)庫用戶GO-可以發(fā)出所有的DDL，但不能發(fā)出GRANT、REVOKE語句sp_addrolemember Db_ddladmin,ManagerGOsp_addrolemember Db_securityadmin,Manager -可以管理角色和角色成員資格；-用GRANT、REVOKE語句將任何數(shù)據(jù)對象的全部權(quán)限授予任何數(shù)據(jù)庫用戶。GOsp_addrolemember Db_backupoperator,Manager -具有備份數(shù)據(jù)

49、庫的權(quán)利。GO5.4.5 角色(續(xù))5.4 安全性 5.4.1 登錄名/用戶 5.4.2 權(quán)限 5.4.3 授權(quán) 5.4.4 收回權(quán)限 5.4.5 角色 5.4.6 一個實例 5.4.7 權(quán)限審核5.4.6 一個實例在SQL Server中建立3.4節(jié)的實例數(shù)據(jù)庫，數(shù)據(jù)庫名稱為S_C_SC，數(shù)據(jù)庫中有Student、Course和SC三個表。假設(shè)由用戶DBManager負(fù)責(zé)創(chuàng)建數(shù)據(jù)庫和三個表，作為數(shù)據(jù)庫的所有者。由用戶Manager負(fù)責(zé)管理S_C_SC數(shù)據(jù)庫的用戶、角色，給用戶賦予必要的權(quán)限，創(chuàng)建索引、視圖等工作。使用數(shù)據(jù)庫的用戶有學(xué)生、職員和教師三類群體。所有的用戶都可以查詢Student、

50、Course和SC中的內(nèi)容。另外，教師需要給學(xué)生登記考試成績，要擁有對SC的Grade列的修改權(quán)限。職員負(fù)責(zé)更新表Student和Course，還要將學(xué)生的選課信息添加到SC表，因此，要擁有對SC表的INSERT權(quán)限。使用戶DBManage和Manager成為固定服務(wù)器和固定數(shù)據(jù)庫角色，獲得應(yīng)有的權(quán)限。參見例5.31和5.32。建立數(shù)據(jù)庫角色Professors、Clerks用戶Manager具有建立角色的權(quán)限，由他（她）負(fù)責(zé)用查詢分析器執(zhí)行下面的系統(tǒng)存儲過程，建立角色。sp_addrole Professors Gosp_addrole Clerks GO5.4.6 一個實例(續(xù))將用戶作為

51、成員加入角色。sp_addrolemember Professors, U5GO分配權(quán)限給角色-PUBLIC是固定數(shù)據(jù)庫角色，數(shù)據(jù)庫的所有用戶都是它的成員GRANT SELECT ON Student TO PUBLIC GRANT SELECT ON SC TO PUBLICGRANT SELECT ON Course TO PUBLICGOGRANT INSERT, DELETE, UPDATE ON Student TO ClerksGRANT INSERT, DELETE, UPDATE ON Course TO ClerksGRANT INSERT ON SC TO ClerksGO

52、GRANT UPDATE(Grade) ON SC TO ProfessorsGo5.4.6 一個實例(續(xù))5.4 安全性 5.4.1 登錄名/用戶 5.4.2 權(quán)限 5.4.3 授權(quán) 5.4.4 收回權(quán)限 5.4.5 角色 5.4.6 一個實例 5.4.7 權(quán)限審核5.4.7 權(quán)限審核用戶提交給DBMS的每條SQL語句在執(zhí)行前都要經(jīng)過權(quán)限審核，只有擁有必要的權(quán)限，才能執(zhí)行SQL語句。假設(shè)用戶U1要執(zhí)行SQL語句：UPDATE StudentSET Sage = Sage + 1WHERE Sno = 2000015DBMS逐一檢查下面的條件，如果滿足了其中的一條，則U1擁有對表Student

53、的Sage列的UPDATE權(quán)限，可以執(zhí)行UPDATE語句，否則，DBMS將拒絕執(zhí)行該語句。 如果U1是Student的創(chuàng)建者。 如果擁有對Student的UPDATE權(quán)限。 如果U1所在的某個角色擁有對Student的UPDATE權(quán)限。 如果PUBLIC擁有對Student的UPDATE權(quán)限。第5章 數(shù)據(jù)庫管理5.1 學(xué)習(xí)目標(biāo)5.2 視圖5.3 索引5.4 安全性5.5 完整性5.6 系統(tǒng)表5.7 小結(jié)5.5 完整性實體完整性（Entity Integrity）若屬性A是構(gòu)成關(guān)系R主碼的屬性組中的任何一個屬性，則任何一個元組在屬性A上不能取空值（Null）。參照完整性（Referential

54、Integrity）如果關(guān)系R的屬性組A是關(guān)系S的主碼，則稱A是關(guān)系R的外碼（FOREIGN KEY）。關(guān)系R的任何一個元組在外關(guān)鍵字F上的取值要么是空值，要么是被參照關(guān)系S中一個元組的主碼值。用戶定義的完整性（User-defined Integrity）用戶定義的完整性就是針對某一具體應(yīng)用環(huán)境而施加的約束條件。它反映某一具體應(yīng)用所涉及的數(shù)據(jù)必須滿足的語義要求。 5.5.1 實體完整性 5.5.2 參照完整性 5.5.3 屬性值限制 5.5.4 元組級限制 5.5.5 完整性修改 5.5.6 空值的處理5.5 完整性(碼)5.5.1 實體完整性例5.33 將Student表中的Sno屬性定義

55、為主碼。CREATE TABLE Student(Sno CHAR(7) PRIMARY KEY, -在列級定義主碼Sname VARCHAR(12) NOT NULL, Sgender CHAR(2),Sage SMALLINT,Sdept VARCHAR(30)或者CREATE TABLE Student(Sno CHAR(7),Sname VARCHAR(12) NOT NULL, Sgender CHAR(2),Sage SMALLINT,Sdept VARCHAR(30),PRIMARY KEY (Sno) ) -在表級定義主碼對多屬性構(gòu)成的主碼只有一種說明方法。例5.34 將SC表

56、中的Sno，Cno屬性組定義為主碼。CREATE TABLE SC(Sno CHAR(7), Cno CHAR(4), Grade SMALLINT,PRIMARY KEY (Sno, Cno) ) -在表級定義主碼5.5.1 實體完整性（續(xù)） 5.5.1 實體完整性 5.5.2 參照完整性 5.5.3 屬性值限制 5.5.4 元組級限制 5.5.5 完整性修改 5.5.6 空值的處理5.5.1 實體完整性（續(xù)）5.5.2 參照完整性關(guān)系SC中一個元組表示一個學(xué)生選修的某門課程的成績，Sno代表Student中的一個學(xué)生，Cno代表Course中的一門課程。因此Sno，Cno分別是對Stude

57、nt和Course的參照引用。例5.35 定義SC中的參照完整性CREATE TABLE SC(Sno CHAR(7), Cno CHAR(4), Grade SMALLINT,PRIMARY KEY (Sno， Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno),FOREIGN KEY (Cno) REFERENCES Course(Cno)對表SC和Student有4種可能破壞參照完整性的情況SC表中增加一個元組，該元組的Sno屬性的值在表Student中找不到一個元組其Sno屬性的值與之相等。修改SC表中的一個元組，修改后該元組的Sno屬性的值

58、在表Student中找不到一個元組其Sno屬性的值與之相等。5.5.2 參照完整性(續(xù))從Student表中刪除一個元組后，造成SC表中某些元組的Sno屬性的值在表Student中找不到一個元組其Sno屬性的值與之相等。修改Student表中的一個元組的Sno屬性后，造成SC表中某些元組的Sno屬性的值在表Student中找不到一個元組其Sno屬性的值與之相等。5.5.2 參照完整性(續(xù))處理策略拒絕(reject)。不允許該操作執(zhí)行。該策略一般設(shè)置為默認(rèn)策略。瀑布刪除(cascade)當(dāng)刪除或修改被參照表(上例中Student)的一個元組時造成了不一致，則刪除參照表中(上例中的SC)的所有造

59、成不一致的元組。設(shè)置為空值（set-null）5.5.2 參照完整性(續(xù))圖5.8 表之間的關(guān)系圖5.5.2 參照完整性(續(xù)) 5.5.1 實體完整性 5.5.2 參照完整性 5.5.3 屬性值限制 5.5.4 元組級限制 5.5.5 完整性修改 5.5.6 空值的處理5.5.2 參照完整性(續(xù))5.5.3 屬性值限制非空值限制例5.36 在定義SC表時，說明Grade屬性不允許取空值。Grade SMALLINT NOT NULL如果不明確說明的話，屬性的值允許取空值指定允許的取值范圍例5.37 Student表的Ssex只允許取“男”和“女”。Sgenderchar(2) CHECK (S

60、gender IN (男,女)例5.38 SC表的Grade的值應(yīng)該在0和100之間。Grade SMALLINT CHECK (Grade =0 AND Grade = 100)5.5.3 屬性值限制（續(xù)）5.5 完整性 5.5.1 實體完整性 5.5.2 參照完整性 5.5.3 屬性值限制 5.5.4 元組級限制 5.5.5 完整性修改 5.5.6 空值的處理5.5.4 元組級限制元組級的限制同屬性值限制相比，可以設(shè)置屬性之間取值的組合例5.39當(dāng)學(xué)生的性別是男時，其名字不能以Ms.打頭。CREATE TABLE Student(Sno CHAR(7) PRIMARY KEY, Sname