第10講SQL中的視圖與授權控制

1、第4章 關 系 數(shù) 據(jù) 庫 標準語言SQL 視圖和授權控制視圖和授權控制第第10講講 SQL的視圖及授權控制的視圖及授權控制SQL語言概述SQL中的數(shù)據(jù)定義SQL中的數(shù)據(jù)查詢SQL中的數(shù)據(jù)更新SQL中的視圖SQL中的授權控制本章內容第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的概念 視圖的定義、刪除視圖的查詢視圖的更新視圖的作用 4.5 SQL中的視圖 第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的概念視圖是一個命名了的視圖是一個命名了的代數(shù)表達式代數(shù)表達式，是從一個或幾個基，是從一個或幾個基本表（或視圖）本表（或視圖）導出的（虛的）導出的（虛的）關系變量，即視圖是關系

2、變量，即視圖是一個虛表。在數(shù)據(jù)庫中只存放視圖的定義，而不存放一個虛表。在數(shù)據(jù)庫中只存放視圖的定義，而不存放視圖對應的數(shù)據(jù)。視圖的值是定義表達式計算后所得視圖對應的數(shù)據(jù)。視圖的值是定義表達式計算后所得到的結果。到的結果。視圖為用戶提供了一個觀察底層數(shù)據(jù)的視圖為用戶提供了一個觀察底層數(shù)據(jù)的窗口窗口?；颈??；颈戆l(fā)生變化后，對應視圖也就隨之改變。發(fā)生變化后，對應視圖也就隨之改變。用戶能像操縱基本關系變量一樣來操縱視圖。對視圖用戶能像操縱基本關系變量一樣來操縱視圖。對視圖的操作將由的操作將由DBMS轉化為對相應基表的操作，任何對轉化為對相應基表的操作，任何對視圖的更新將自動和實時地在相應基表中所映射

3、的數(shù)視圖的更新將自動和實時地在相應基表中所映射的數(shù)據(jù)上進行。據(jù)上進行。由用戶定義，并為用戶所使用。也可在視圖之上再定由用戶定義，并為用戶所使用。也可在視圖之上再定義視圖。義視圖。4.5 SQL中的視圖 第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的定義 一般格式為：一般格式為：CREATE VIEW 視圖名視圖名（列名列名，列名，列名） AS 子查詢子查詢； WITH CHECK OPTION -組成視圖的屬性列名或者全部指定或者全部省略。在組成視圖的屬性列名或者全部指定或者全部省略。在SELECT子子句存在下述情形時，需要指明視圖的屬性列。句存在下述情形時，需要指明視圖的屬性列。

4、目標列中含有聚集函數(shù)或列表達式；目標列中含有聚集函數(shù)或列表達式；目標列中含對多表查詢產(chǎn)生的同名屬性列；目標列中含對多表查詢產(chǎn)生的同名屬性列；需要在視圖中為某些列使用更合適的名字。需要在視圖中為某些列使用更合適的名字。 4.5 SQL中的視圖 第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的定義 一般格式為：一般格式為：CREATE VIEW 視圖名視圖名（列名（列名，列名，列名） AS 子查詢子查詢； WITH CHECK OPTION -子查詢可以是任意復雜的子查詢可以是任意復雜的SELECT語句，但通常不允許含有語句，但通常不允許含有ORDER BY子句和子句和DISTINCT選

5、項。選項。-WITH CHECK OPTION：表示對視圖進行：表示對視圖進行UPDATE和和INSERT操作時要保證修改和插入的元組須滿足視圖定義中的謂詞條件。操作時要保證修改和插入的元組須滿足視圖定義中的謂詞條件。-DBMS執(zhí)行語句的結果只是把視圖的定義存入數(shù)據(jù)字典中，并不執(zhí)行語句的結果只是把視圖的定義存入數(shù)據(jù)字典中，并不執(zhí)行其中的子查詢。執(zhí)行其中的子查詢。 4.5 SQL中的視圖 第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的定義 4.5 SQL中的視圖 【例例】建立數(shù)學系的學生視圖。建立數(shù)學系的學生視圖。 CREATE VIEW M-S（M-SNO，M-SN，M-SA） A

6、S SELECT SNO，SN，SA FROM S WHERE SD =數(shù)學數(shù)學；第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的定義 4.5 SQL中的視圖 【例例】定義由學號及該學生的平均成績構成的視圖。定義由學號及該學生的平均成績構成的視圖。 CREATE VIEW S-GRADE（SNO，SN，CNO，GRADE） AS SELECT S.SNO，SN，CNO，GRADE FROM S，SC WHERE S.SNO = SC.SNO；【例例】建立學生成績視圖。建立學生成績視圖。CREATE VIEW S_AVE (SNO，GAVE) AS SELECT SNO，AVG(GRA

7、DE) FROM S_GRADE GROUP BY SNO第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的刪除 一般格式為：一般格式為： DROP VIEW 視圖名視圖名CASCADE；CASCADE:把該視圖和由它導出的所有視圖一起刪除把該視圖和由它導出的所有視圖一起刪除4.5 SQL中的視圖 DROP VIEW M-S；【例例】將前面建立的視圖將前面建立的視圖M-S刪除。刪除?！纠繉⑶懊娼⒌囊晥D將前面建立的視圖S_GRADE 及其上的視圖及其上的視圖S_AVE刪除。刪除。 DROP VIEW S_GRADE CASCADE； 第第10講講 SQL的視圖及授權控制的視圖及授權

8、控制視圖的查詢用戶對視圖的查詢就如同對基表一樣，其操作將由用戶對視圖的查詢就如同對基表一樣，其操作將由DBMS轉化為對相應基表的操作。轉化為對相應基表的操作。4.5 SQL中的視圖 SELECT SNO，SN，AVG(GRADE) FROM S-GRADE GROUP BY SNO; 【例例】查詢學生平均成績。查詢學生平均成績。 SELECT SNO，GAVEFROM S_AVE；第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的查詢 DBMS 執(zhí)行對視圖的查詢時，首先進行有效執(zhí)行對視圖的查詢時，首先進行有效性檢查，檢查查詢中涉及的基本表、視圖等是性檢查，檢查查詢中涉及的基本表、視圖等

9、是否存在。否存在。 如果存在，則從數(shù)據(jù)字典中取出視圖的定義，如果存在，則從數(shù)據(jù)字典中取出視圖的定義，把定義中的子查詢和用戶查詢結合起來，轉換把定義中的子查詢和用戶查詢結合起來，轉換成等價的對基本表的查詢，然后再執(zhí)行相應的成等價的對基本表的查詢，然后再執(zhí)行相應的查詢。查詢。4.5 SQL中的視圖 第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的查詢 4.5 SQL中的視圖 SELECT SNO，SN，AVG(GRADE) FROM S-GRADE GROUP BY SNO; 【例例】查詢學生平均成績。查詢學生平均成績。 DBMS根據(jù)數(shù)據(jù)字典中對視圖根據(jù)數(shù)據(jù)字典中對視圖S-GRADE的定

10、義，將其轉換的定義，將其轉換為等價的對基表的查詢操作：為等價的對基表的查詢操作： CREATE VIEW S-GRADE（SNO，SN，CNO，GRADE） AS SELECT S.SNO，SN，CNO，GRADE FROM S，SC WHERE S.SNO = SC.SNO；第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的查詢 4.5 SQL中的視圖 SELECT SNO，SN，AVG(GRADE) FROM S-GRADE GROUP BY SNO; 【例例4-41】查詢學生平均成績。查詢學生平均成績。 SELECT S.SNO，SN，AVG(GRADE) FROM S，SC W

11、HERE S.SNO=SC.SNO GROUP BY SC.SNO ；DBMS根據(jù)數(shù)據(jù)字典中對視圖根據(jù)數(shù)據(jù)字典中對視圖S-GRADE的定義，將其轉換的定義，將其轉換為等價的對基表的查詢操作：為等價的對基表的查詢操作：第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的更新 通過視圖來插入、刪除和修改數(shù)據(jù)通過視圖來插入、刪除和修改數(shù)據(jù). . 對視圖的更新要轉換為對基本表的更新操作。對視圖的更新要轉換為對基本表的更新操作。 若若視圖定義中有視圖定義中有WITH CHECK OPTION選項選項，進行更新操作時，進行更新操作時，DBMS會檢查視圖定義中會檢查視圖定義中的條件，若不滿足條件，則拒

12、絕執(zhí)行該操作。的條件，若不滿足條件，則拒絕執(zhí)行該操作。4.5 SQL中的視圖 第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的更新4.5 SQL中的視圖 【例例】 INSERT INTO M-S VALUES （S20，黃海黃海，1990-10-15）；）； UPDATE M-S SET M-SN=華婷華婷 WHERE M-SNO=S16； DELETE FROM M-S WHERE SNO=S8； INSERT INTO S VALUES （S20，黃海黃海，數(shù)學數(shù)學， 1990-10-15 ）；）； UPDATE S SET SN=華婷華婷 WHERE SNO=S16 AND S

13、D=數(shù)學數(shù)學； DELETE FROM S WHERE SNO=S8 AND SD=數(shù)學數(shù)學； CREATE VIEW M-S（M-SNO，M-SN，M-SA） AS SELECT SNO，SN，SA FROM S WHERE SD =數(shù)學系數(shù)學系；第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的更新4.5 SQL中的視圖 【例例】 INSERT INTO M-S VALUES （S20，黃海黃海，1990-10-15）；）； CREATE VIEW M-S（M-SNO，M-SN，M-SA） AS SELECT SNO，SN，SA FROM S WHERE SD =數(shù)學系數(shù)學系 WI

14、TH CHECK OPTION ；第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的更新 不是所有的視圖都是可更新的，有些視圖的更不是所有的視圖都是可更新的，有些視圖的更新不能唯一地有意義地轉換成對相應基本表的新不能唯一地有意義地轉換成對相應基本表的更新更新, ,就不能更新。就不能更新。 對視圖的更新通常要加以限制，否則會出現(xiàn)錯對視圖的更新通常要加以限制，否則會出現(xiàn)錯誤，或產(chǎn)生語義上的問題。誤，或產(chǎn)生語義上的問題。 4.5 SQL中的視圖 【例例】 CREATE VIEW S-AVG (SNO，GAVE) AS SELECT SNO, AVE(GRADE) FROM SC GROUP

15、BY SNOUPDATE S-AVGSET GAVG=95.0WHERE SNO=S08； 第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的更新 對視圖更新的一般限制有：對視圖更新的一般限制有：由由多表導出多表導出的視圖不允許更新。的視圖不允許更新。若視圖的屬性列來自若視圖的屬性列來自表達式或常數(shù)表達式或常數(shù)，則不允許執(zhí)行，則不允許執(zhí)行INSERT和和UPDATE操作，但允許執(zhí)行操作，但允許執(zhí)行DELETE操操作。作。定義中用到定義中用到GROUP BY子句子句或或聚集函數(shù)聚集函數(shù)的視圖不允的視圖不允許更新。許更新。 建立在一個建立在一個不允許更新不允許更新的視圖上的視圖不允許更新的

16、視圖上的視圖不允許更新 一般都只允許對一般都只允許對行列子集視圖行列子集視圖（含有基表的主（含有基表的主鍵）進行更新。鍵）進行更新。4.5 SQL中的視圖 第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的作用 視圖提供了一個視圖提供了一個簡化簡化用戶操作的快捷方式。用戶操作的快捷方式?？梢愿逦乇磉_查詢，簡化用戶的操作?？梢愿逦乇磉_查詢，簡化用戶的操作。 視圖支持視圖支持多用戶同時多用戶同時以不同的方式對相同的數(shù)以不同的方式對相同的數(shù)據(jù)進行查詢。據(jù)進行查詢。視圖可以定制表的不同方面的結果集來滿足來自不同用戶的視圖可以定制表的不同方面的結果集來滿足來自不同用戶的特殊需求，并可以使

17、這些用戶同時與同一個數(shù)據(jù)庫交互。特殊需求，并可以使這些用戶同時與同一個數(shù)據(jù)庫交互。 視圖對于視圖對于隱藏的隱藏的數(shù)據(jù)自動提供數(shù)據(jù)自動提供安全保護安全保護。 視圖可以為用戶和應用程序提供視圖可以為用戶和應用程序提供邏輯上的數(shù)據(jù)邏輯上的數(shù)據(jù)獨立性獨立性?？沙砷L性和可成長性和可重構性可重構性。4.5 SQL中的視圖 第第10講講 SQL的視圖及授權控制的視圖及授權控制視圖的作用【例例】把學生關系把學生關系 S（SNO，SN，SD，SA）重構為兩個表重構為兩個表 SX（SNO，SN） SY（SNO，SD，SA） 4.5 SQL中的視圖 此時原表此時原表S是是SX和和SY自然聯(lián)接的結果，可以建立一個自然

18、聯(lián)接的結果，可以建立一個視圖視圖S，解決數(shù)據(jù)的邏輯獨立性。，解決數(shù)據(jù)的邏輯獨立性。 CREATE VIEW S（SNO，SN，SD，SA） AS SELECT SX.SNO，SX.SN，SY.SD，SY.SA FROM SX，SY WHERE SX.SNO=SY.SNO；第第10講講 SQL的視圖及授權控制的視圖及授權控制小結視圖的概念視圖的概念 視圖的定義、刪除視圖的定義、刪除視圖的查詢視圖的查詢視圖的更新視圖的更新視圖的作用視圖的作用 第第10講講 SQL的視圖及授權控制的視圖及授權控制SQL語言概述SQL中的數(shù)據(jù)定義SQL中的數(shù)據(jù)查詢SQL中的數(shù)據(jù)更新SQL中的視圖SQL中的授權控制本章

19、內容第第10講講 SQL的視圖及授權控制的視圖及授權控制安全系統(tǒng)的整體結構 用用戶戶訪問監(jiān)視器訪問監(jiān)視器審計審計訪問控制訪問控制身份認證身份認證安全管理員安全管理員授權數(shù)據(jù)庫授權數(shù)據(jù)庫資源資源審計員審計員 管理管理引言數(shù)據(jù)加密數(shù)據(jù)加密第第10講講 SQL的視圖及授權控制的視圖及授權控制存取控制的概念 存取權限授權與回收數(shù)據(jù)庫角色4.6 SQL中的授權控制 第第10講講 SQL的視圖及授權控制的視圖及授權控制存取控制的概念 數(shù)據(jù)庫安全性所關心的問題。數(shù)據(jù)庫安全性所關心的問題。 確保只授權給有資格的用戶訪問數(shù)據(jù)庫確保只授權給有資格的用戶訪問數(shù)據(jù)庫的權限，同時令所有未被授權的人員無的權限，同時令所有

20、未被授權的人員無法獲取數(shù)據(jù)。法獲取數(shù)據(jù)。4.6 SQL中的授權控制 第第10講講 SQL的視圖及授權控制的視圖及授權控制存取控制的概念 數(shù)據(jù)庫的存取控制機制包括數(shù)據(jù)庫的存取控制機制包括定義用戶權限，并將其登記在數(shù)據(jù)字典中。定義用戶權限，并將其登記在數(shù)據(jù)字典中。 用戶對某一數(shù)據(jù)對象的用戶對某一數(shù)據(jù)對象的操作權力操作權力稱為權限。稱為權限。 DBMS提供適當?shù)恼Z言提供適當?shù)恼Z言定義用戶權限定義用戶權限授權規(guī)則授權規(guī)則。合法權限檢查合法權限檢查 當用戶發(fā)出存取數(shù)據(jù)庫的操作請求后，當用戶發(fā)出存取數(shù)據(jù)庫的操作請求后，DBMS查找查找數(shù)據(jù)字典，根據(jù)授權規(guī)則進行合法權限檢查。數(shù)據(jù)字典，根據(jù)授權規(guī)則進行合法權

21、限檢查。4.6 SQL中的授權控制 第第10講講 SQL的視圖及授權控制的視圖及授權控制存取權限用戶的存取權限由兩個要素組成用戶的存取權限由兩個要素組成數(shù)據(jù)庫對象數(shù)據(jù)庫對象數(shù)據(jù)庫、基本表、表中記錄、屬性值，視圖、索引等。數(shù)據(jù)庫、基本表、表中記錄、屬性值，視圖、索引等。操作類型（數(shù)據(jù)）操作類型（數(shù)據(jù)）SELECTINSERTDELETEUPDATEREFERENCEALL PRIVILEGES4.6 SQL中的授權控制 允許用戶定義新關允許用戶定義新關系時，引用其他關系時，引用其他關系的主鍵作為外鍵。系的主鍵作為外鍵。第第10講講 SQL的視圖及授權控制的視圖及授權控制存取權限 用戶對不同的數(shù)據(jù)

22、庫對象有不同的存取權限，用戶對不同的數(shù)據(jù)庫對象有不同的存取權限，不用的用戶對同一數(shù)據(jù)對象也有不同的權限，不用的用戶對同一數(shù)據(jù)對象也有不同的權限，用戶還可將其擁有的存取權限轉授給其他用戶用戶還可將其擁有的存取權限轉授給其他用戶。 通過通過SQL提供的提供的GRANT和和REVOKE語句定義語句定義用戶權限，形成授權規(guī)則，并將其記錄在數(shù)據(jù)用戶權限，形成授權規(guī)則，并將其記錄在數(shù)據(jù)字典中。字典中。4.6 SQL中的授權控制 第第10講講 SQL的視圖及授權控制的視圖及授權控制授權與回收 GRANT授權語句授權語句的一般格式的一般格式GRANT ON TO WITH GRANT OPTION ;4.6

23、SQL中的授權控制 SELECT,INSERT,UPDATE,DELETE, REFERENCES等等被授權的用戶可以被授權的用戶可以將這些權限繼續(xù)轉將這些權限繼續(xù)轉授給其他用戶授給其他用戶 GRANT SELECT， UPDATE (Sno） ON S TO U4 WITH GRANT OPTION; 【例例】把查詢把查詢S表和修改學生學號的權限授給用戶表和修改學生學號的權限授給用戶U4，并，并允許其將權限轉授出去。允許其將權限轉授出去。第第10講講 SQL的視圖及授權控制的視圖及授權控制授權與回收 REVOKE授權語句授權語句的一般格式的一般格式REVOKE GRANT OPTION FO

24、R ON FROM RESTRICT | CASCADE ;4.6 SQL中的授權控制 只有授權權限被收回只有授權權限被收回CASCADE：把該用戶所轉：把該用戶所轉授出去的權限同時收回；授出去的權限同時收回；RESTRICT：當不存在連：當不存在連鎖收回時，才能收回權限鎖收回時，才能收回權限，否則系統(tǒng)拒絕回收。，否則系統(tǒng)拒絕回收。 REVOKE SELECT，UPDATE (sno) ON S FROM U4 CASCADE；【例例】把用戶把用戶U4查詢和修改學生學號的權限收回，并級聯(lián)查詢和修改學生學號的權限收回，并級聯(lián)收回所授出的權限。收回所授出的權限。第第10講講 SQL的視圖及授權控制

25、的視圖及授權控制數(shù)據(jù)庫角色 數(shù)據(jù)庫角色是被命名的一組與數(shù)據(jù)庫操數(shù)據(jù)庫角色是被命名的一組與數(shù)據(jù)庫操作相關的權限，角色是作相關的權限，角色是權限的集合權限的集合。 可為一組具有相同權限的用戶創(chuàng)建一個可為一組具有相同權限的用戶創(chuàng)建一個角色，使用角色來管理數(shù)據(jù)庫權限可以角色，使用角色來管理數(shù)據(jù)庫權限可以簡化授權的過程。簡化授權的過程。4.6 SQL中的授權控制 第第10講講 SQL的視圖及授權控制的視圖及授權控制數(shù)據(jù)庫角色 TRANSACT SQL中角色的創(chuàng)建、授權中角色的創(chuàng)建、授權、權限、權限收回收回CREATE ROLE ；GRANT ON TO ; REVOKE ON FROM ；4.6 SQL中的授權控制 第第10講講 SQL的視圖及授權控制的視圖及授權控制數(shù)據(jù)庫角色 TRANSACT SQL中角色的創(chuàng)建、授權、權限中角色的創(chuàng)建、授權、權限收回收回sp_addrolemember , ；sp_droprolemember , ； 4.6 SQL中的授權控制 用戶所擁有的權限就用戶所擁有的權限就是所承擔的全部角色是所承擔的全部角色所包含的權限的總和。所包含的權限的總和。第第10講講 SQL的視圖及授權控制的視圖及授