西華師范大學計算機學院.ppt

《西華師范大學計算機學院.ppt》由會員分享，可在線閱讀，更多相關《西華師范大學計算機學院.ppt（52頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、西華師范大學計算機學院,第三章 關系數(shù)據(jù)庫標準語言SQL (續(xù)2）,第三章 關系數(shù)據(jù)庫標準語言SQL,3.1 SQL概述 3.2 數(shù)據(jù)定義 3.3 查詢 3.4 數(shù)據(jù)更新 3.5 視圖 3.6 數(shù)據(jù)控制 3.7 嵌入式SQL 3.8 小結,3.6 數(shù)據(jù)控制,概述 3.6.1 授權 3.6.2 收回權限 小結,概述,數(shù)據(jù)控制亦稱為數(shù)據(jù)保護，包括數(shù)據(jù)的： 安全性控制 完整性控制 并發(fā)控制 恢復,SQL語言的數(shù)據(jù)控制功能,SQL語言提供了數(shù)據(jù)控制功能，能夠在一定程度上保證數(shù)據(jù)庫中數(shù)據(jù)的安全性、完整性，并提供了一定的并發(fā)控制及恢復能力。,1. 完整性,數(shù)據(jù)庫系統(tǒng)必須保證數(shù)據(jù)庫中的數(shù)據(jù)是完整的。在更新數(shù)

2、據(jù)庫時，關系中不能出現(xiàn)不符合完整性要求的元組，這樣才能給用戶提供正確、有效的信息。實現(xiàn)這一目的的最直接方法，是要求用戶在編寫數(shù)據(jù)庫應用程序時，對每個插入、刪除、修改操作，都加入必要的完整性檢查代碼。,實體完整性和主碼 在SQL中，實體完整性是通過主碼（Primary Key）來實現(xiàn)的。一旦某個屬性或屬性組被定義為主碼，該主碼的每個屬性就不能為空值，并且在關系中不能出現(xiàn)主碼值完全相同的兩個元組。主碼可在定義關系的CREATE TABLE語句中使用PRIMARY KEY關鍵字加以定義。有兩種定義主碼的方法，一種是在屬性后增加關鍵字，另一種是在屬性表中加入額外的定義主碼的子句： PRIMARY

3、KEY(主碼屬性名表),例 在employee表中說明eno為主碼，有兩種方法： 1)使eno為主碼 CREATE TABLE employee (eno char(4) PRIMARY KEY, name varchar(8), sex char(2), age integer, dno char(2));,2)單獨說明主碼 CREATE TABLE employee (eno char(4), name varchar(8), sex char(2), age integer, dno char(2), PRIMARY KEY(eno));,如果關系的主碼只含有

4、單個屬性，上面的兩種方法都可以使用。如果主碼由兩個或兩個以上屬性組成，只能使用第2)種方法。如itememp表的主碼由ino和eno組成，則只能采用第2)種方法，PRIMARY KEY子句應說明成： PRIMARY KEY(ino，eno),SQL中，并沒有強制規(guī)定必須為每個關系指定主碼，但為每個關系指定主碼通常更好一些。除了主碼，SQL中使用關鍵字UNIQUE，說明該屬性（或屬性組）的值不能重復出現(xiàn)。但說明為UNIQUE的屬性可以定義為空值，與主碼又有所不同。并且一個表中只能有一個主碼，但可以有多個“UNIQUE”說明。 例 說明employee表中職工不能重名，有兩種方法： (1

5、)在name屬性后面加上UNIQUE說明： name varchar(8) UNIQUE， (2)在屬性列表后使用UNIQUE說明： UNIQUE（name）,外部碼約束的說明 說明外部碼的方法有兩種： (1)在該屬性的說明（屬性名、類型）后直接加上關鍵字REFERENCES，后跟對應表的主碼說明。格式為 REFERENCES () 其中表名為參照關系名，屬性名為參照關系的主碼。 (2)在CREATE TABLE語句的屬性清單后，加上外部碼的說明子句，格式為 FOREIGN KEY REFERENCES (） 其中，屬性名表中的屬性可以多于一個，但必須前后

6、對應。,例 說明employee表中dno為外部碼，參照關系為department。兩種方法分別如下： 1) CREATE TABLE employee (eno char(4) PRIMARY KEY, name verchar(8), sex char(2), age integer, dno char(2) REFERENCES department(dno));,2) CREATE TABLE employee (eno char(4) PRIMARY KEY, name varchar(8), sex char(2), age i

7、nteger, dno char(2), FOREIGN KEY dno REFERENCES department(dno)); 注意，在說明dno為employee的外部碼時，department關系中的dno必須已被說明為主碼。,用戶自定義完整性約束 用戶自定義完整性約束取決于應用環(huán)境的需要，因此不同數(shù)據(jù)庫應用系統(tǒng)的自定義完整性要求是千差萬別的，有些較簡單，有些則較復雜。SQL中提供了非空約束、對屬性的CHECK約束、對元組的CHECK約束、觸發(fā)器等來實現(xiàn)用戶的各種完整性要求。,一、基于屬性的CHECK約束 使用CHECK（檢查）子句可保證屬性值滿足某些前提條件。CH

8、ECK子句的一般格式為 CHECK 屬性的CHECK約束既可跟在屬性的定義后，也可在定義語句中另增一子句加以說明。,例：規(guī)定employee表中屬性age的值不能小于18、大于65。只需將age屬性說明為如下的形式： age integer CHECK(age=18 AND age=18 AND age<=65) CHECK還可以模擬枚舉類型，例如，可在CREATE TABLE語句中用以下子句說明屬性sex的取值只能取“男”或“女”： Sex char(2) CHECK(sex IN(男，女)),例如 CREATE TABLE publishers ( pub_i

9、d char(4) NOT NULL CHECK (pub_id IN (1389, 0736, 0877, 1622, 1756) OR pub_id LIKE 990-90-9), pub_name varchar(40) NULL, city varchar(20) NULL, state char(2) NULL, country varchar(30) NULL DEFAULT(USA) ),二、基于元組的約束 對表內元組說明約束時，在CREATE TABLE語句中的屬性表、主碼、外部碼的說明之后加上CHECK子句。每當對元組進行插入或修改操作時，都要對CHECK子句的條件表達

10、式求值，如果條件為假，違背了約束，系統(tǒng)將拒絕該插入或修改操作。,例： 工資表salary的定義： CREATE TABLE salary (Eno char(4) PRIMARY KEY, Basepay decimal(7，2)， Service decimal(7，2)， Price decimal(7，2)， Rest decimal(7，2)， Insure decimal(7，2)， Fund decimal(7，2)， CHECK(Insure+Fund

11、需要在定義約束時對約束進行命名，在約束前加上關鍵字CONSTRAINT和該約束的名稱。,例如說明employee表中的主碼和外部碼時，分別命名為PK-employee和FK-employee： Eno char(4) CONSTRAINT PK-employee PRIMARY KEY， dno char(4) CONSTRAINT FK-employee FOREIGN KEY REFERENCES department(dno) 上例中的約束可以在定義時命名： CONSTRAINT RightSalary CHECK(Insure+Fund

12、例 刪除employee表中的外部碼約束FK-employee： ALTER TABLE employee DROP CONSTRAINT FK-employee； 例： 修改對Salary的約束RightSalary中的表達式為： Insure+Fund

13、+Fund

14、或屬性組的修改為觸發(fā)條件。事件的觸發(fā)還有兩個相關的時間：Before和After。Before觸發(fā)器是在事件發(fā)生之前觸發(fā)，After觸發(fā)器是在事件發(fā)生之后觸發(fā)。,觸發(fā)動作實際上是一系列SQL語句，可以有兩種方式： (1)對被事件影響的每一行（FOR EACH ROW）每一元組執(zhí)行觸發(fā)過程，稱為行級觸發(fā)器。 (2)對整個事件只執(zhí)行一次觸發(fā)過程（FOR EACH STATEMENT），稱為語句級觸發(fā)器。該方式是觸發(fā)器的默認方式。,SQL中的存儲過程,存儲過程類似于高級語言中的函數(shù)，同觸發(fā)器一樣，由一組SQL語句組成，預先編譯后存儲在數(shù)據(jù)庫中，可供應用程序多次調用，類似于高級語言中的函數(shù)。使用存儲過

15、程既能方便軟件開發(fā)，又減少了解釋執(zhí)行SQL語句時句法分析和查詢優(yōu)化的時間，提高了效率。 如在客戶/服務器體系結構下，應用程序（客戶端）只需向服務器發(fā)出一次請求調用一個存儲過程，服務器上就可一次執(zhí)行一批SQL命令，中間結果不用送回客戶端，大大降低了網(wǎng)絡流量和服務器的開銷。,2. 并發(fā)控制,并發(fā)控制: 當多個用戶并發(fā)地對數(shù)據(jù)庫進行操作時，對他們加以控制、協(xié)調，以保證并發(fā)操作正確執(zhí)行，保持數(shù)據(jù)庫的一致性。 SQL語言并發(fā)控制能力： 提供事務、事務開始、事務結束、提交等概念 所謂事務，就是用戶定義的一個數(shù)據(jù)庫操作序列，是一個不可分割的工作單位。不論有無故障，數(shù)據(jù)庫系統(tǒng)必須保證事務的正確執(zhí)行或者執(zhí)行整個

16、事務或者屬于該事務的操作一個也不執(zhí)行。SQL的一個事務，可以是一條SQL語句、一組SQL語句或整個程序。一般地，一個程序中包含有多個事務。,3. 恢復,恢復: 當發(fā)生各種類型的故障導致數(shù)據(jù)庫處于不一致狀態(tài)時，將數(shù)據(jù)庫恢復到一致狀態(tài)的功能。 SQL語言恢復功能： 提供事務回滾、重做等概念 （UNDO、REDO）,4. 安全性,安全性：保護數(shù)據(jù)庫，防止不合法的使用所造成的數(shù)據(jù)泄露和破壞。 保證數(shù)據(jù)安全性的主要措施 存取控制：控制用戶只能存取他有權存取的數(shù)據(jù) 規(guī)定不同用戶對于不同數(shù)據(jù)對象所允許執(zhí)行的操作,DBMS實現(xiàn)數(shù)據(jù)安全性保護的過程,用戶或DBA把授權決定告知系統(tǒng) SQL的GRANT和R

17、EVOKE DBMS把授權的結果存入數(shù)據(jù)字典 當用戶提出操作請求時，DBMS根據(jù)授權定義進行檢查，以決定是否執(zhí)行操作請求,安全性(續(xù)),誰定義？ DBA和表的建立者（即表的屬主） 如何定義？ SQL語句： GRANT REVOKE,3.6.1 授 權,GRANT語句的一般格式： GRANT ,... ON TO ,... WITH GRANT OPTION; 誰定義？DBA和表的建立者（即表的屬主） REVOKE功能：將對指定操作對象的指定操作權限授予指定的用戶。,(1) 操作權限,(2) 用戶的權限,建表（CREATETAB）的權限:屬于DBA DBA授予--普通用戶

18、基本表或視圖的屬主擁有對該表或視圖的一切操作權限 接受權限的用戶: 一個或多個具體用戶 PUBLIC（全體用戶）,(4) WITH GRANT OPTION子句,指定了WITH GRANT OPTION子句: 獲得某種權限的用戶還可以把這種權限再授予別的用戶。 沒有指定WITH GRANT OPTION子句: 獲得某種權限的用戶只能使用該權限，不能傳播該權限,例題,例1 把查詢Student表權限授給用戶U1 GRANT SELECT ON TABLE Student TO U1;,,例題（續(xù)）,例2 把對Student表和Course表的全部權限授予用戶U2和U3 GRANT ALL

19、 PRIVILIGES ON TABLE Student, Course TO U2, U3;,例4,例題（續(xù)）,例3 把對表SC的查詢權限授予所有用戶 GRANT SELECT ON TABLE SC TO PUBLIC;,,例題（續(xù)）,例4 把查詢Student表和修改學生學號的權限授給用戶U4 GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;,,例題（續(xù)）,例5 把對表SC的INSERT權限授予U5用戶，并允許他再將此權限授予其他用戶 GRANT INSERT ON TABLE SC TO U5 WITH GRANT O

20、PTION;,傳播權限,執(zhí)行例5后，U5不僅擁有了對表SC的INSERT權限， 還可以傳播此權限： GRANT INSERT ON TABLE SC TO U6 WITH GRANT OPTION; 同樣，U6還可以將此權限授予U7： GRANT INSERT ON TABLE SC TO U7; 但U7不能再傳播此權限。 U5-- U6-- U7,例題（續(xù)）,例6 DBA把在數(shù)據(jù)庫S_C中建立表的權限授予用戶U8 GRANT CREATETAB ON DATABASE S_C TO U8;,例題,下面的語句將把對Northwind數(shù)據(jù)庫中customers表的查詢權限賦予用戶test：

21、 use northwind grant select on customers to test 可以用以下語句查詢表的存取權限 exec sp_helprotect customers,3.6 數(shù)據(jù)控制,概述 3.6.1 授權 3.6.2 收回權限 小結,SQL收回權限的功能,REVOKE語句的一般格式為： REVOKE ,... ON FROM ,...; 功能：從指定用戶那里收回對指定對象的指定權限,例題,例7 把用戶U4修改學生學號的權限收回 REVOKE UPDATE(Sno) ON TABLE Student FROM U4;,例題（續(xù)）,例8 收回所有用戶對表SC的查詢權

22、限 REVOKE SELECT ON TABLE SC FROM PUBLIC;,例題（續(xù)）,例9 把用戶U5對SC表的INSERT權限收回 REVOKE INSERT ON TABLE SC FROM U5;,例題,下面的語句將收回用戶test對Northwind數(shù)據(jù)庫中customers表的查詢權限： use northwind revoke select on customers to test,權限的級聯(lián)回收,系統(tǒng)將收回直接或間接從U5處獲得的對SC 表的INSERT權限: --U5-- U6-- U7 收回U5、U6、U7獲得的對SC表的INSERT 權限: <--U5<-- U6<-- U7,小結:SQL靈活的授權機制,DBA擁有對數(shù)據(jù)庫中所有對象的所有權限，并可以根據(jù)應用的需要將不同的權限授予不同的用戶。 用戶對自己建立的基本表和視圖擁有全部的操作權限，并且可以用GRANT語句把其中某些權限授予其他用戶。 被授權的用戶如果有“繼續(xù)授權”的許可，還可以把獲得的權限再授予其他用戶。 所有授予出去的權力在必要時又都可以用REVOKE語句收回。,