數(shù)據(jù)庫(kù)完整性與安全

第9章數(shù)據(jù)庫(kù)完整性與安全

現(xiàn)代學(xué)習(xí)理念的四大支柱是：學(xué)會(huì)認(rèn)知學(xué)會(huì)做事學(xué)會(huì)合作學(xué)會(huì)生存——《學(xué)會(huì)學(xué)習(xí)》一書的作者：方州數(shù)據(jù)庫(kù)系統(tǒng)原理與設(shè)計(jì)

(第2版)第9章數(shù)據(jù)庫(kù)完整性與安全數(shù)據(jù)庫(kù)的安全性：保護(hù)數(shù)據(jù)庫(kù)以防止不合法使用所造成的數(shù)據(jù)泄密、更改或破壞。數(shù)據(jù)庫(kù)的完整性：防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義的數(shù)據(jù)，其防范對(duì)象是不合語(yǔ)義的、不正確的數(shù)據(jù)。主要教學(xué)目標(biāo)如下：要求熟練掌握DBMS安全性保護(hù)的基本原理與方法，并能熟練運(yùn)用SQL中的GRANT和REVOKE語(yǔ)句進(jìn)行授權(quán)；要求熟練掌握DBMS完整性保護(hù)措施，并能熟練運(yùn)用SQL中的DDL語(yǔ)句進(jìn)行完整性約束定義；要求熟練掌握數(shù)據(jù)庫(kù)編程中的游標(biāo)概念及其使用方法；熟練運(yùn)用觸發(fā)器完成復(fù)雜的完整性約束和審計(jì)功能；熟練運(yùn)用存儲(chǔ)過(guò)程編寫復(fù)雜的業(yè)務(wù)處理和查詢統(tǒng)計(jì)功能。目錄9.4數(shù)據(jù)庫(kù)安全性

9.1數(shù)據(jù)庫(kù)完整性

9.29.3游標(biāo)

存儲(chǔ)過(guò)程觸發(fā)器應(yīng)用與安全設(shè)計(jì)

9.59.69.1

數(shù)據(jù)庫(kù)安全性

安全性問(wèn)題不是數(shù)據(jù)庫(kù)系統(tǒng)所獨(dú)有的，所有計(jì)算機(jī)系統(tǒng)都有這個(gè)問(wèn)題。數(shù)據(jù)庫(kù)系統(tǒng)中大量數(shù)據(jù)集中存放，且為許多最終用戶直接共享，安全性問(wèn)題更為突出。9.1.1數(shù)據(jù)庫(kù)安全的基本概念9.1.2安全標(biāo)準(zhǔn)9.1.3SQL存取控制機(jī)制9.1.4審計(jì)機(jī)制9.1.1數(shù)據(jù)庫(kù)安全的基本概念數(shù)據(jù)庫(kù)安全保護(hù)目標(biāo)是確保只有授權(quán)用戶才能訪問(wèn)數(shù)據(jù)庫(kù)，未被授權(quán)的人員則無(wú)法接近數(shù)據(jù)。安全措施是指計(jì)算機(jī)系統(tǒng)中用戶直接或通過(guò)應(yīng)用程序訪問(wèn)數(shù)據(jù)庫(kù)所要經(jīng)過(guò)的安全認(rèn)證過(guò)程。數(shù)據(jù)庫(kù)安全認(rèn)證過(guò)程如圖9-1所示9.1.1數(shù)據(jù)庫(kù)安全的基本概念用戶標(biāo)識(shí)與鑒別(identification&authentication)當(dāng)用戶訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，要先將其用戶名(username)與密碼(password)提交給數(shù)據(jù)庫(kù)管理系統(tǒng)進(jìn)行認(rèn)證；只有在確定其身份合法后，才能進(jìn)入數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)存取操作。數(shù)據(jù)庫(kù)安全保護(hù)通過(guò)身份認(rèn)證的用戶，擁有了進(jìn)入數(shù)據(jù)庫(kù)的“憑證”；用戶在數(shù)據(jù)庫(kù)中執(zhí)行什么操作，需通過(guò)“存取控制”或視圖進(jìn)行權(quán)限分配。9.1.1數(shù)據(jù)庫(kù)安全的基本概念存取控制：決定用戶對(duì)數(shù)據(jù)庫(kù)中的哪些對(duì)象進(jìn)行操作，進(jìn)行何種操作。存取控制機(jī)制主要包括兩部分：定義用戶權(quán)限及將用戶權(quán)限登記到數(shù)據(jù)字典中；合法權(quán)限檢查：當(dāng)用戶發(fā)出操作請(qǐng)求后，DBMS查找數(shù)據(jù)字典并根據(jù)安全規(guī)則進(jìn)行合法權(quán)限檢查，若操作請(qǐng)求超出了定義的權(quán)限，系統(tǒng)將拒絕執(zhí)行此操作。視圖：通過(guò)為不同的用戶定義不同的視圖，達(dá)到限制用戶訪問(wèn)范圍的目的。視圖機(jī)制能隱藏用戶無(wú)權(quán)存取的數(shù)據(jù)，從而自動(dòng)地對(duì)數(shù)據(jù)庫(kù)提供一定程度的安全保護(hù)；視圖的主要功能在于提供數(shù)據(jù)庫(kù)的邏輯獨(dú)立性，其安全性保護(hù)不太精細(xì)，往往不能達(dá)到應(yīng)用系統(tǒng)的要求；在實(shí)際應(yīng)用中，通常將視圖與存取控制機(jī)制結(jié)合起來(lái)使用，如先通過(guò)視圖屏蔽一部分保密數(shù)據(jù)，然后進(jìn)一步定義存取權(quán)限。9.1.1數(shù)據(jù)庫(kù)安全的基本概念審計(jì)：是一種監(jiān)視措施，用于跟蹤并記錄有關(guān)數(shù)據(jù)的訪問(wèn)活動(dòng)。審計(jì)追蹤把用戶對(duì)數(shù)據(jù)庫(kù)的所有操作自動(dòng)記錄下來(lái)，存放在審計(jì)日志(auditlog)中；審計(jì)日志的內(nèi)容一般包括：操作類型(如修改、查詢、刪除)；操作終端標(biāo)識(shí)與操作者標(biāo)識(shí)；操作日期和時(shí)間；操作所涉及到的相關(guān)數(shù)據(jù)(如基本表、視圖、記錄、屬性)；數(shù)據(jù)庫(kù)的前映像(即修改前的值)和后映像(即修改后的值)。利用這些信息，可找出非法存取數(shù)據(jù)庫(kù)的人、時(shí)間和內(nèi)容等；數(shù)據(jù)庫(kù)管理系統(tǒng)往往將審計(jì)作為可選特征，允許操作者打開(kāi)或關(guān)閉審計(jì)功能。9.1.1數(shù)據(jù)庫(kù)安全的基本概念操作系統(tǒng)安全保護(hù)通過(guò)操作系統(tǒng)提供的安全措施來(lái)保證數(shù)據(jù)庫(kù)的安全性數(shù)據(jù)密碼存儲(chǔ)訪問(wèn)控制和存取控制可將用戶的應(yīng)用系統(tǒng)訪問(wèn)范圍最小化和數(shù)據(jù)對(duì)象操作權(quán)限最低化，但對(duì)一些敏感數(shù)據(jù)進(jìn)行“加密存儲(chǔ)”也是系統(tǒng)提供的安全策略；數(shù)據(jù)加密(dataencryption)：防止數(shù)據(jù)庫(kù)中數(shù)據(jù)存儲(chǔ)和傳輸失密的有效手段；加密的基本思想：先根據(jù)一定的算法將原始數(shù)據(jù)(即明文,plaintext)加密為不可直接識(shí)別的格式(即密文,ciphertext)，然后數(shù)據(jù)以密文的方式存儲(chǔ)和傳輸。9.1.3SQL存取控制機(jī)制

SQL支持受控的存取保護(hù)：在自主存取控制中，用戶對(duì)不同的數(shù)據(jù)對(duì)象有不同的存取權(quán)限；不同的用戶對(duì)同一對(duì)象有不同的權(quán)限；用戶可將其擁有的存取權(quán)限轉(zhuǎn)授給其他用戶。自主存取控制通過(guò)SQL的GRANT和REVOKE語(yǔ)句實(shí)現(xiàn)。用戶權(quán)限：是指用戶可以在哪些數(shù)據(jù)對(duì)象上進(jìn)行哪些類型的操作。它由兩個(gè)要素組成：數(shù)據(jù)對(duì)象和操作類型。定義存取權(quán)限稱為授權(quán)(authorization)；授權(quán)粒度可以精細(xì)到字段級(jí)，也可以粗到關(guān)系級(jí)；授權(quán)粒度越細(xì)，授權(quán)子系統(tǒng)就越靈活，但是系統(tǒng)的開(kāi)銷也會(huì)相應(yīng)地增大。9.1.3SQL存取控制機(jī)制

授權(quán)分為數(shù)據(jù)庫(kù)級(jí)、表級(jí)和列級(jí)權(quán)限。在SQLServer中權(quán)限只能由擔(dān)任不同角色的用戶來(lái)分配；不同類型的用戶有不同的等級(jí)；下圖給出了授權(quán)等級(jí)圖。9.1.3SQL存取控制機(jī)制

GRANT和REVOKE語(yǔ)句向用戶授予或收回對(duì)數(shù)據(jù)的操作權(quán)限。對(duì)數(shù)據(jù)庫(kù)模式的授權(quán)則由DBA在創(chuàng)建用戶時(shí)實(shí)現(xiàn)。創(chuàng)建用戶的語(yǔ)法如下：

CREATEUSER

<username>

[WITH][DBA|RESOURCE|CONNECT]該語(yǔ)法在SQLServer2000中不支持；在SQLServer2000中使用系統(tǒng)存儲(chǔ)過(guò)程sp_addlogin和sp_adduser實(shí)現(xiàn)，詳見(jiàn)實(shí)驗(yàn)教材。9.1.3SQL存取控制機(jī)制

權(quán)限的授予與收回GRANT和REVOKE有兩種權(quán)限：目標(biāo)權(quán)限和命令權(quán)限。命令權(quán)限的授予與收回主要指DDL操作權(quán)限，語(yǔ)法分別為：

GRANT{all|<command_list>}TO{public|<username_list>}

REVOKE{all|<command_list>}FROM{public|<username_list>}<command_list>可以是createdatabase、createdefault、createfunction、createprocedure、createrule、createtable、createview、createindex、backupdatabase和backuplog等；一次可授多種權(quán)限，授多種權(quán)限時(shí)，權(quán)限之間用逗號(hào)分隔；如果具有創(chuàng)建對(duì)象的create權(quán)限，則自動(dòng)具有其創(chuàng)建對(duì)象的修改權(quán)限alter和刪除權(quán)限drop；9.1.3SQL存取控制機(jī)制

對(duì)于基本表，自動(dòng)具有在所創(chuàng)建表上創(chuàng)建、刪除和修改觸發(fā)器的權(quán)限；修改alter和刪除權(quán)限drop不額外授權(quán)；all：表示上述所有權(quán)限；public：表示所有的用戶；<username_list>：指定的用戶名列表。如果將某組權(quán)限同時(shí)授予多個(gè)用戶，則用戶名之間用逗號(hào)分隔。[例9.1]將創(chuàng)建表和視圖的權(quán)限授予user01和user02用戶：GRANTcreatetable,createviewTOuser01,user02[例9.2]從user02收回創(chuàng)建視圖的權(quán)限：REVOKEcreateviewFROMuser029.1.3SQL存取控制機(jī)制

目標(biāo)權(quán)限的授予和收回主要指DML操作權(quán)限，語(yǔ)法分別為：GRANT{all|<command_list>}ON<objectName>

[(<columnName_list>)]

TO{public|<username_list>}[WITHGRANTOPTION]

REVOKE{all|<command_list>}ON<objectName>[(<columnName_list>)]

FROM{public|<username_list>}[CASCADE|RESTRICT]<command_list>可以是update、select、insert、delete、excute和allexcute針對(duì)存儲(chǔ)過(guò)程授予執(zhí)行權(quán)限；update、select、insert、delete針對(duì)基本表和視圖授權(quán)；all表示所有的權(quán)限。對(duì)象的創(chuàng)建者自動(dòng)擁有該對(duì)象的插入、刪除、更新和查詢操作權(quán)限；過(guò)程的創(chuàng)建者自動(dòng)擁有所創(chuàng)建過(guò)程的執(zhí)行權(quán)限；9.1.3SQL存取控制機(jī)制CASCADE：級(jí)聯(lián)收回；RESTRICT：缺省值，若轉(zhuǎn)賦了權(quán)限，則不能收回；WITHGRANTOPTION：允許將指定對(duì)象上的目標(biāo)權(quán)限授予其它安全帳戶。不允許循環(huán)授權(quán)，即不允許將得到的權(quán)限授予其祖先，如下圖所示：9.1.3SQL存取控制機(jī)制[例9.3]將存儲(chǔ)過(guò)程proSearchBySno的執(zhí)行權(quán)限授予用戶u1、u2和u3：GRANTexcuteONproSearchBySnoTO

u1,u2,u3[例9.4]將對(duì)班級(jí)表Class的查詢、插入權(quán)限授予用戶u1，且用戶u1可以轉(zhuǎn)授其所獲得的權(quán)限給其它用戶：GRANTselect,insertONClassTO

u1WITHGRANTOPTION[例9.5]將對(duì)學(xué)生表的性別、出生日期的查詢和修改權(quán)限授予用戶u3、u4和u5，且不可以轉(zhuǎn)授權(quán)限：

GRANTselect,updateONStudent(sex,birthday)TO

u3,u4,u5如果是對(duì)列授予權(quán)限，命令項(xiàng)可以包括select或update或兩者組合；若使用了select*

，則必須對(duì)表的所有列賦予select權(quán)限。9.1.3SQL存取控制機(jī)制[例9.6]將表Score的若干權(quán)限分別授予用戶u1、u2、u3、u4、u5和u6。將表Score的所有權(quán)限授予用戶u1，且可以轉(zhuǎn)授權(quán)限GRANTallONScoreTO

u1WITHGRANTOPTION用戶u1將表Score的所有權(quán)限授予用戶u2，且可以轉(zhuǎn)授權(quán)限GRANTallONScoreTO

u2WITHGRANTOPTION用戶u2將表Score的查詢和插入權(quán)限授予用戶u5，且不可以轉(zhuǎn)授GRANTselect,insertONScoreTO

u5用戶u2將表Score的所有權(quán)限授予用戶u4，且可以轉(zhuǎn)授權(quán)限GRANTallONScoreTO

u4WITHGRANTOPTION用戶u4將表Score的查詢和刪除權(quán)限授予用戶u6，且可以轉(zhuǎn)授GRANTselect,deleteONScoreTOu6WITHGRANTOPTION通過(guò)上述的授權(quán)，用戶u1、u2、u3、u4、u5和u6分別得到的權(quán)限如下圖所示：9.1.3SQL存取控制機(jī)制[例9.7]用戶u2將轉(zhuǎn)授給用戶u4的對(duì)表Score的修改和查詢權(quán)限收回：REVOKEselect,updateONScoreFROM

u4CASCADE本例必須級(jí)聯(lián)收回，因?yàn)閡4將該表的查詢和刪除權(quán)限轉(zhuǎn)授給了u6。[例9.8]用戶u4將轉(zhuǎn)授給用戶u6的對(duì)表Score的查詢權(quán)限收回：REVOKEselectONScoreFROMu6數(shù)據(jù)庫(kù)角色被命名的一組與數(shù)據(jù)庫(kù)操作相關(guān)的權(quán)限；角色是權(quán)限的集合，可以為一組具有相同權(quán)限的用戶創(chuàng)建一個(gè)角色；角色簡(jiǎn)化了授權(quán)操作。9.1.3SQL存取控制機(jī)制角色的創(chuàng)建、授權(quán)、轉(zhuǎn)授和收回語(yǔ)句的語(yǔ)法如下：角色的創(chuàng)建，在SQLServer2000中，使用系統(tǒng)存儲(chǔ)過(guò)程sp_addrole創(chuàng)建角色：

sp_addrole<roleName>給角色授權(quán)：GRANT{all|<command_list>}ON<objectName>TO<roleName_list>將角色授予其他的角色或用戶：GRANT<roleName_list>TO<roleName_list>|<username_list>［WITHADMINOPTION］角色權(quán)限的收回：REVOKE{all|<command_list>}ON<objectName>FROM<roleName_list>從角色或用戶中收回角色：REVOKE<roleName>FROM{<roleName_list>|<username_list>}9.1.3SQL存取控制機(jī)制[例9.9]通過(guò)角色實(shí)現(xiàn)將一組權(quán)限授予一個(gè)用戶。創(chuàng)建一個(gè)角色R1：sp_addrole

R1使用GRANT語(yǔ)句，使角色R1擁有Student表的select、update、insert權(quán)限：GRANTselect,update,insertONStudentTO

R1將角色R1授予用戶u1、u2和u3，使他們具有角色R1所包含的全部權(quán)限：GRANT

R1TOu1,u2,u3通過(guò)角色R1可以一次性地收回已授予用戶u1的這3個(gè)權(quán)限：REVOKE

R1FROM

u19.1.3SQL存取控制機(jī)制[例9.10]將對(duì)表Student的刪除權(quán)限授予角色R1，并收回查詢權(quán)限。

GRANTdeleteONStudentTO

R1

REVOKEselectONStudentFROM

R1通過(guò)修改角色的權(quán)限，一次性地將用戶u2和u3的權(quán)限全部修改了。目錄9.4數(shù)據(jù)庫(kù)安全性

9.1數(shù)據(jù)庫(kù)完整性

9.29.3游標(biāo)

存儲(chǔ)過(guò)程觸發(fā)器應(yīng)用與安全設(shè)計(jì)

9.59.69.2

數(shù)據(jù)庫(kù)完整性

數(shù)據(jù)庫(kù)系統(tǒng)在運(yùn)行過(guò)程中，用戶無(wú)論通過(guò)什么方式對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行操作，都必須保證數(shù)據(jù)的正確性。如，在學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)ScoreDB中，必須保證學(xué)生表Student中的學(xué)號(hào)是唯一的，性別只能取“男”和“女”；在學(xué)生成績(jī)表Score中，課程成績(jī)必須在0～100分之間，且學(xué)號(hào)必須在Student表中存在（即只有是本校的學(xué)生才可以選課），等等。數(shù)據(jù)庫(kù)的完整性是針對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行正確性的維護(hù)，防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義、不正確的數(shù)據(jù)。9.2

數(shù)據(jù)庫(kù)完整性

為維護(hù)數(shù)據(jù)庫(kù)的完整性，數(shù)據(jù)庫(kù)管理系統(tǒng)提供：完整性約束條件定義完整性約束條件也稱為完整性規(guī)則，是數(shù)據(jù)庫(kù)中的數(shù)據(jù)必須滿足的語(yǔ)義約束條件；由SQL的DDL實(shí)現(xiàn)，作為模式的一部分存入數(shù)據(jù)庫(kù)中。完整性檢查方法檢查數(shù)據(jù)是否滿足已定義的完整性約束條件稱為完整性檢查；一般在insert、delete、update執(zhí)行后開(kāi)始檢查，或事務(wù)提交時(shí)進(jìn)行檢查。違約處理若發(fā)現(xiàn)用戶操作違背了完整性約束條件，應(yīng)采取一定的措施，如拒絕操作等。商用DBMS都支持完整性控制。定義數(shù)據(jù)庫(kù)模式時(shí)，除了非常復(fù)雜的約束外，都可以很明確地對(duì)完整性約束加以說(shuō)明。9.2

數(shù)據(jù)庫(kù)完整性

9.2.1完整性約束條件9.2.2實(shí)體完整性9.2.3參照完整性9.2.4用戶自定義完整性9.2.5完整性約束的修改9.2.1完整性約束條件完整性約束條件作用的對(duì)象可以是關(guān)系、元組、列三種：列約束主要是列的類型、取值范圍、精度、是否允許空值等的約束條件；元組約束是元組中屬性間聯(lián)系的約束；關(guān)系約束是若干元組間、關(guān)系集合上以及關(guān)系之間的聯(lián)系的約束。完整性約束，其狀態(tài)可是靜態(tài)的，也可是動(dòng)態(tài)的。靜態(tài)約束：指數(shù)據(jù)庫(kù)每一確定狀態(tài)時(shí)的數(shù)據(jù)對(duì)象所應(yīng)滿足的約束條件。反映數(shù)據(jù)庫(kù)狀態(tài)合理性的約束；靜態(tài)約束主要表現(xiàn)在：9.2.1完整性約束條件靜態(tài)列級(jí)約束：對(duì)列的取值域的說(shuō)明，包括以下幾方面：對(duì)數(shù)據(jù)類型的約束，包括數(shù)據(jù)的類型、長(zhǎng)度、單位、精度等；對(duì)數(shù)據(jù)格式的約束；對(duì)取值范圍或取值集合的約束；對(duì)空值的約束；其他約束。靜態(tài)元組約束：規(guī)定元組的各個(gè)列之間的約束關(guān)系。靜態(tài)關(guān)系約束：在一個(gè)關(guān)系的各個(gè)元組之間或者若干關(guān)系之間常存在各種聯(lián)系或約束。常見(jiàn)的靜態(tài)關(guān)系約束有：實(shí)體完整性約束；參照完整性約束；函數(shù)依賴約束，大部分函數(shù)依賴約束都在關(guān)系模式中定義；統(tǒng)計(jì)約束，即字段值與關(guān)系中多個(gè)元組的統(tǒng)計(jì)值之間的約束關(guān)系.9.2.1完整性約束條件動(dòng)態(tài)約束：指數(shù)據(jù)庫(kù)從一種狀態(tài)轉(zhuǎn)變?yōu)榱硪环N狀態(tài)時(shí)的新、舊值之間所應(yīng)滿足的約束條件。反映數(shù)據(jù)庫(kù)狀態(tài)變遷的約束；動(dòng)態(tài)約束主要表現(xiàn)在：動(dòng)態(tài)列級(jí)約束：修改列定義或列值時(shí)應(yīng)滿足的約束條件。修改列定義時(shí)的約束。如，將允許空值的列改為不允許空值時(shí)，如果該列已存在空值，則拒絕這種修改。修改列值時(shí)的約束。修改列值有時(shí)需要參照其舊值，并且新舊值之間需要滿足某種約束條件。例如，職工工資調(diào)整不得低于其原來(lái)工資，學(xué)生年齡只能增長(zhǎng)等。9.2.1完整性約束條件動(dòng)態(tài)元組約束：指修改元組的值時(shí)元組中各個(gè)字段間需要滿足某種約束條件。例如，職工工資調(diào)整時(shí)新工資不得低于原工資+工齡*1.5等。動(dòng)態(tài)關(guān)系約束：動(dòng)態(tài)關(guān)系約束是加在關(guān)系變化前后狀態(tài)上的限制條件。例如，事務(wù)一致性、原子性等約束條件。完整性約束又分為立即執(zhí)行的約束和延遲執(zhí)行的約束：立即執(zhí)行約束(immediateCONSTRAINTS)：檢查是否違背完整性約束的時(shí)機(jī)是在一條語(yǔ)句執(zhí)行完后立即檢查。延遲執(zhí)行約束(deferredCONSTRAINTS)：需要延遲到整個(gè)事務(wù)執(zhí)行結(jié)束后再進(jìn)行檢查。9.2.1完整性約束條件在SQL中，所有的完整性約束，用戶既可以對(duì)其命名（使用CONSTRAINT），也可由具體的數(shù)據(jù)庫(kù)系統(tǒng)取默認(rèn)的名字。如果是用戶所命名的約束，修改約束時(shí)比較方便；如果由系統(tǒng)自動(dòng)給約束命名，則必須通過(guò)訪問(wèn)系統(tǒng)的數(shù)據(jù)字典查到相應(yīng)的約束名稱，才可以對(duì)其進(jìn)行修改。9.2.2實(shí)體完整性實(shí)體完整性要求基本表的主碼值唯一且不允許為空值。在SQL中：實(shí)體完整性定義使用CREATETABLE語(yǔ)句中的PRIMARYKEY短語(yǔ)實(shí)現(xiàn)；或使用ALTERTABLE語(yǔ)句中的ADDPRIMARYKEY短語(yǔ)實(shí)現(xiàn)；有關(guān)CREATETABLE、ALTERTABLE語(yǔ)句的語(yǔ)法詳見(jiàn)第3章3.8節(jié)（P112～115）。對(duì)單屬性構(gòu)成的主碼可定義為列級(jí)約束，也可定義為表級(jí)約束；對(duì)多個(gè)屬性構(gòu)成的主碼，只能定義為表級(jí)約束。9.2.2實(shí)體完整性實(shí)體完整性定義[例9.11]在班級(jí)表Class中將classNo定義為主碼。CREATETABLEClass(

classNo char(6) NOTNULL,--班級(jí)號(hào)

classNamevarchar(30)uniqueNOTNULL,--班級(jí)名

institutevarchar(30)NOTNULL,--所屬學(xué)院

gradesmallintdefault0NOTNULL,--年級(jí)

classNumtinyint NULL,--班級(jí)人數(shù)

CONSTRAINTClassPKPRIMARYKEY(classNo))本例將classNo定義為主碼，使用CONSTRAINT短語(yǔ)為該約束命名為ClassPK；該主碼定義為表級(jí)約束。9.2.2實(shí)體完整性該例還可按下面的方式定義：CREATETABLEClass(

classNochar(6)NOTNULLPRIMARYKEY,--班級(jí)號(hào)

...)將主碼classNo定義為列級(jí)約束，且由系統(tǒng)取約束名稱。可為約束取名，如：CREATETABLEClass(

classNo char(6) NOTNULL--班級(jí)號(hào)

CONSTRAINTClassPKPRIMARYKEY,...)將主碼classNo定義為列級(jí)約束，且約束取名為ClassPK。9.2.2實(shí)體完整性[例9.12]在學(xué)生成績(jī)表Score中將studentNo、courseNo定義為主碼。CREATETABLEScore(

studentNochar(7) NOTNULL,--學(xué)號(hào)

courseNochar(3) NOTNULL,--課程號(hào)

score numeric(5,1)default0NOTNULL,--成績(jī)

/*主碼由兩個(gè)屬性構(gòu)成，必須作為表級(jí)完整性進(jìn)行定義*/

CONSTRAINTScorePKPRIMARYKEY(studentNo,courseNo))也可以寫成：CREATETABLEScore(

studentNochar(7) NOTNULL,--學(xué)號(hào)

courseNochar(3) NOTNULL,--課程號(hào)

score numeric(5,1)default0NOTNULL,--成績(jī)

/*主碼由兩個(gè)屬性構(gòu)成，必須作為表級(jí)完整性進(jìn)行定義*/

PRIMARYKEY(studentNo,courseNo))由系統(tǒng)自動(dòng)為約束取名。9.2.2實(shí)體完整性實(shí)體完整性的檢查和違約處理當(dāng)插入或?qū)χ鞔a列進(jìn)行更新操作時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)按照實(shí)體完整性規(guī)則自動(dòng)進(jìn)行檢查，包括：檢查主碼值是否唯一，如果不唯一則拒絕插入或修改；檢查主碼值的唯一性，可采用全表掃描或B+樹(shù)索引掃描。全表掃描法：從外存依次將該表的每一數(shù)據(jù)塊讀入內(nèi)存；判斷塊中的每一條記錄的主碼值與待插入(或修改)記錄的主碼值是否相同；如果相同，則阻止插入(或修改)！全表掃描法如圖9-6所示：9.2.2實(shí)體完整性全表掃描法十分耗費(fèi)系統(tǒng)資源。數(shù)據(jù)庫(kù)管理系統(tǒng)一般對(duì)主碼建立一個(gè)B+樹(shù)索引。通過(guò)掃描索引來(lái)查找基本表中是否存在相同的主碼值。B+樹(shù)索引掃描法如圖9-7所示：9.2.2實(shí)體完整性采用B+樹(shù)將極大地提高系統(tǒng)效率：查找的結(jié)點(diǎn)數(shù)僅為B+樹(shù)的高度；使用B+樹(shù)索引提高了查找速度，但增加了索引的維護(hù)代價(jià)。如果插入或修改的主碼值在索引中不存在，系統(tǒng)需要?jiǎng)討B(tài)維護(hù)索引。檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為空則拒絕插入或修改。9.2.3參照完整性

參照完整性為若干個(gè)表中的相應(yīng)元組建立聯(lián)系。在SQL中：參照完整性定義使用CREATETABLE語(yǔ)句中的FOREIGNKEY和REFERENCES短語(yǔ)來(lái)實(shí)現(xiàn)；或通過(guò)使用ALTERTABLE語(yǔ)句中的ADDFOREIGNKEY短語(yǔ)來(lái)實(shí)現(xiàn)；FOREIGNKEY指出定義哪些列為外碼；REFERENCES短語(yǔ)指明這些外碼參照哪些關(guān)系；給出FOREIGNKEY定義的關(guān)系稱為參照關(guān)系；由REFERENCES指明的表稱為被參照關(guān)系。9.2.3參照完整性

參照完整性的定義[例9.13]在學(xué)生成績(jī)表Score中將studentNo、courseNo定義為外碼。CREATETABLEScore(

studentNochar(7) NOTNULL,--學(xué)號(hào)

courseNochar(3) NOTNULL,--課程號(hào)

score numeric(5,1)default0NOTNULL--成績(jī)

CHECK(scoreBETWEEN0.0AND100.0),

/*主碼由兩個(gè)屬性構(gòu)成，必須作為表級(jí)完整性進(jìn)行定義*/

CONSTRAINTScorePKPRIMARYKEY(studentNo,courseNo),/*表級(jí)完整性約束條件，studentNo是外碼，被參照表是Student*/

CONSTRAINTScoreFK1FOREIGNKEY(studentNo)

REFERENCESStudent(studentNo),/*表級(jí)完整性約束條件，courseNo是外碼，被參照表是Course*/

CONSTRAINTScoreFK2FOREIGNKEY(courseNo)

REFERENCESCourse(courseNo))9.2.3參照完整性

本例中，Score為參照表，Student和Course為被參照表。Score中studentNo屬性列參照Student的studentNo列，其含義為：Score中studentNo列的取值必須是Student中studentNo列的某個(gè)屬性值；即不存在一個(gè)未注冊(cè)的學(xué)生選修了課程。Score中courseNo屬性列參照Course的courseNo列，其含義為：Score中courseNo列的取值必須是Course中courseNo列的某個(gè)屬性值;即不存在學(xué)生選修了一門不存在的課程。本例也可改寫為：

CREATETABLEScore(.../*表級(jí)完整性約束條件，studentNo是外碼，被參照表是Student*/

FOREIGNKEY(studentNo)REFERENCESStudent(studentNo),/*表級(jí)完整性約束條件，courseNo是外碼，被參照表是Course*/

FOREIGNKEY(courseNo)REFERENCESCourse(courseNo))外碼約束由系統(tǒng)自動(dòng)命名。9.2.3參照完整性

在實(shí)現(xiàn)參照完整性時(shí)，提供定義外碼列是否允許空值的機(jī)制：如果外碼是主碼的一部分，則外碼不允許為空值；本例的兩個(gè)外碼皆不允許為空值。參照完整性的檢查和違約處理違約處理的策略如下：拒絕（NOACTION）執(zhí)行，是系統(tǒng)的默認(rèn)策略：當(dāng)在被參照關(guān)系中刪除元組時(shí)，僅當(dāng)參照關(guān)系中沒(méi)有任何元組的外碼值與被參照關(guān)系中要?jiǎng)h除元組的主碼值相同時(shí)，系統(tǒng)才執(zhí)行刪除操作，否則拒絕此操作。如要?jiǎng)h除學(xué)生表Student中學(xué)號(hào)為“0700001”的記錄，系統(tǒng)不允許，因?yàn)閷W(xué)號(hào)為“0700001”的同學(xué)在成績(jī)表Score中選修了課程。參照關(guān)系中可以隨意刪除元組。9.2.3參照完整性當(dāng)在參照關(guān)系中修改元組時(shí)，僅當(dāng)參照關(guān)系中修改后的元組的外碼值依然在被參照關(guān)系中，系統(tǒng)才執(zhí)行修改操作，否則拒絕。如在成績(jī)表Score中修改“0700001”同學(xué)的學(xué)號(hào)為“0700006”，系統(tǒng)不允許，因?yàn)閷W(xué)號(hào)“0700006”同學(xué)在學(xué)生表Student中不存在。在被參照關(guān)系中修改元組：僅當(dāng)被參照關(guān)系中修改前的元組的主碼值沒(méi)有出現(xiàn)在參照關(guān)系的外碼中，系統(tǒng)才執(zhí)行修改操作，否則拒絕該操作。如在學(xué)生表Student中修改“0700001”同學(xué)的學(xué)號(hào)為“0700006”，系統(tǒng)不允許，因?yàn)閷W(xué)號(hào)“0700001”同學(xué)在選課表Scoret中已經(jīng)選修了課程.在參照關(guān)系中插入元組：僅當(dāng)參照關(guān)系中插入的元組的外碼值等于被參照關(guān)系中某個(gè)元組的主碼值時(shí)，系統(tǒng)才執(zhí)行插入操作，否則拒絕該操作。如在成績(jī)表Score中插入一條記錄“0700006,001,78”，系統(tǒng)不允許，因?yàn)閷W(xué)號(hào)“0700006”同學(xué)在學(xué)生表Student中不存在。被參照關(guān)系可以隨意插入新元組。9.2.3參照完整性級(jí)聯(lián)(CASCADE)操作。當(dāng)刪除或修改被參照關(guān)系的某些元組造成了與參照關(guān)系的不一致時(shí)，則刪除或修改參照表中所有不一致的元組。例如，刪除學(xué)生表Student中學(xué)號(hào)為“0700001”的記錄，則自動(dòng)刪除被參照關(guān)系成績(jī)表Score中學(xué)號(hào)為“0700001”的所有選課記錄。修改學(xué)生表Student中的學(xué)號(hào)，由“0700001”改為“0700006”，則自動(dòng)修改被參照關(guān)系成績(jī)表Score中學(xué)號(hào)為“0700001”的所有選課記錄，將“0700001”全部改為“0700006”。級(jí)聯(lián)(CASCADE)操作必須在定義外碼時(shí)給出顯示定義。設(shè)置為空值(SETNULL)：對(duì)于參照完整性，除了定義外碼，還應(yīng)定義外碼列是否允許空值。如果外碼是主碼的一部分，則外碼不允許為空值。置空值刪除(NULLIFIES)：刪除被參照關(guān)系的元組，并將參照關(guān)系中相應(yīng)元組的外碼值置空值.9.2.3參照完整性

[例9.14]在學(xué)生成績(jī)表Score中將studentNo、courseNo定義為外碼，且studentNo外碼定義為級(jí)聯(lián)刪除和修改操作，courseNo外碼定義為級(jí)聯(lián)修改操作。

CREATETABLEScore(

studentNochar(7) NOTNULL,--學(xué)號(hào)

courseNochar(3) NOTNULL,--課程號(hào)

score numeric(5,1)default0NOTNULL,--成績(jī)

/*主碼由兩個(gè)屬性構(gòu)成，必須作為表級(jí)完整性進(jìn)行定義*/

CONSTRAINTScorePKPRIMARYKEY(studentNo,courseNo),

/*表級(jí)完整性約束條件，studentNo是外碼，被參照表是Student*/

CONSTRAINTScoreFK1FOREIGNKEY(studentNo)

REFERENCESStudent(studentNo)

ONDELETE

CASCADE

/*級(jí)聯(lián)刪除Score表中相應(yīng)的元組*/

ONUPDATECASCADE,

/*級(jí)聯(lián)更新Score表中相應(yīng)的元組*/9.2.3參照完整性/*表級(jí)完整性約束條件，courseNo是外碼，被參照表是Course*/

CONSTRAINTScoreFK2FOREIGNKEY(courseNo)

REFERENCESCourse(courseNo)

ONDELETENOACTION

/*該定義為默認(rèn)值，可以不定義*/

/*當(dāng)更新course表中的courseNo時(shí)，級(jí)聯(lián)更新Score表中相應(yīng)的元組*/

ONUPDATECASCADE)9.2.4用戶自定義完整性用戶自定義完整性就是定義某一具體應(yīng)用中數(shù)據(jù)必須滿足的語(yǔ)義要求，由RDBMS提供，而不必由應(yīng)用程序承擔(dān)。用戶自定義完整性包括屬性上的約束和元組上的約束兩種。屬性上的約束屬性上的約束包括：列值非空、列值唯一、設(shè)置默認(rèn)值、滿足CHECK(<predicate>)定義等；屬性上的約束是當(dāng)往表中插入或修改屬性值時(shí)，系統(tǒng)檢查是否滿足約束條件，如果不滿足，則拒絕相應(yīng)的操作。9.2.4用戶自定義完整性

[例9.15]在學(xué)生表Student中定義屬性studentNo取值必須為數(shù)字，性別只能取‘男’或‘女’，民族缺省值為‘漢族’。CREATETABLEStudent(

studentNochar(7)NOTNULL

CHECK(studentNoLIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

--學(xué)號(hào),由7位數(shù)字組成

studentName varchar(20)NOTNULL,--姓名,不允許為空值/*性別,允許為空值,僅取男或女兩個(gè)值*/sex char(2) NULLCHECK(sexIN

('男','女')),birthdaydatetime NULL,--出生日期,允許為空值

nativevarchar(20)NULL,--籍貫,允許為空值

nation varchar(30)default'漢族'NULL,

--民族,允許為空值,默認(rèn)“漢族”

classNo char(6) NULL,

--所屬班級(jí),允許為空值

CONSTRAINTStudentPKPRIMARYKEY(studentNo),

CONSTRAINTStudentFKFOREIGNKEY(classNo)

REFERENCESClass(classNo))9.2.4用戶自定義完整性[例9.16]在班級(jí)表中定義班級(jí)名稱唯一。CREATETABLEClass(classNo char(6)NOTNULL,

--班級(jí)號(hào),不允許為空值

/*班級(jí)名,必須唯一,不允許為空值*/

classNamevarchar(30)uniqueNOTNULL,institutevarchar(30)NOTNULL,

--所屬學(xué)院,不允許為空值/*年級(jí),默認(rèn)值為0,不允許為空值*/gradesmallintdefault0NOTNULL,

classNumtinyint NULL,--班級(jí)人數(shù),允許為空值

CONSTRAINTClassPKPRIMARYKEY(classNo))9.2.4用戶自定義完整性元組上的約束元組上的約束可以設(shè)置不同屬性之間的取值的相互約束條件；用短語(yǔ)CHECK(<predicate>)引出的約束；插入元組或修改屬性的值時(shí)，RDBMS檢查元組上的約束條件是否被滿足，如果不滿足則操作被拒絕執(zhí)行。[例9.17]在學(xué)生表Student中定義：如果是男同學(xué)，則其姓名不能以劉開(kāi)頭

CREATETABLEStudent(..../*性別,允許為空值,僅取男和女兩個(gè)值*/

sex char(2) NULLCHECK(sexIN

(‘男’,‘女’)),

--列約束

...--以下是元組約束

CONSTRAINTSexCKCHECK(sex='女'ORstudentNameNOTLIKE'劉%'),...)本例給出了性別sex屬性列與姓名studentName屬性列之間必須滿足的約束條件9.2.4用戶自定義完整性屬性級(jí)與元組級(jí)約束在定義上的區(qū)別如果在定義屬性的同時(shí)定義約束條件，則為列級(jí)約束；如果單獨(dú)定義約束條件，則為元組級(jí)的約束。[例9.18]列級(jí)約束CREATETABLEStudent(

sNo

char(5)NOTNULL--學(xué)號(hào)/*列級(jí)主碼約束和列級(jí)用戶自定義約束*/

CHECK

(sNoLIKE'[0-9][0-9][0-9][0-9][0-9]')PRIMARYKEY,sNamechar(10)NULLunique,

--姓名,列級(jí)唯一約束sSex

char(2)

NULL,

--性別sDatedatetimeNULL,

--出生日期sAge

tinyintDEFAULT17NOTNULL--年齡,列級(jí)約束

CHECK(sAge>0

AND

sAge<60),sMZ

char(10)NULL,

--民族

sDeptchar(2)NOTNULL,

--所在系sTotaldecimal(8,2)DEFAULT0.00NOTNULL--總分)9.2.4用戶自定義完整性[例9.19]元組級(jí)約束。CREATETABLEStudent(sNochar(5)NOTNULL,

--學(xué)號(hào)

--元組級(jí)約束

CONSTRAINTsNoCKCHECK

(sNoLIKE'[0-9][0-9][0-9][0-9][0-9]'),sNamechar(10)NULLUNIQUE,

--姓名

sSexchar(2)NULL,--性別

sDatedatetimeNULL,--出生日期

sAgetinyintDEFAULT17NOTNULL,--年齡

sMZchar(10)NULL,--民族

sDeptchar(2)NOTNULL,--所在系

sTotaldecimal(8,2)DEFAULT0.00NOTNULL,--總分

CONSTRAINTageCKCHECK(sAge>0ANDsAge<60),--元組級(jí)約束

PRIMARYKEY(sNo)--元組級(jí)約束)9.2.5完整性約束的修改用戶可以為完整性約束命名，命名格式如下：[CONSTRAINT<constraintName>]

PRIMARYKEY

(<constraintExpr>)[CONSTRAINT<constraintName>]

FOREIGNKEY

(<constraintExpr>)REFERENCE<refTable>(<constraintExpr>)

[CONSTRAINT<constraintName>]

CHECK

(<constraintExpr>)用戶命名有兩點(diǎn)好處：一是便于理解約束的含義；二是修改約束方便，不必查詢數(shù)據(jù)字典。使用ALTERTABLE語(yǔ)句修改表中的完整性約束。要修改約束，首先必須刪除約束，然后加入新的約束。9.2.5完整性約束的修改刪除約束：ALTERTABLE<tableName>

DROPCONSTRAINT<constraintName>添加約束：ALTERTABLE<tableName>

ADDCONSTRAINT<constraintName>

<CHECK|UNIQUE|PRIMARYKEY|FOREIGNKEY>(<constraintExpr>)其中<tableName>為欲修改約束所在的表名；<constraintName>為欲修改的約束名稱。目錄9.4數(shù)據(jù)庫(kù)安全性

9.1數(shù)據(jù)庫(kù)完整性

9.29.3游標(biāo)

存儲(chǔ)過(guò)程觸發(fā)器應(yīng)用與安全設(shè)計(jì)

9.59.69.4存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程是為了完成特定功能匯集而成的一組命名了的SQL語(yǔ)句集合該集合編譯后存放在數(shù)據(jù)庫(kù)中，可根據(jù)實(shí)際情況重新編譯；存儲(chǔ)過(guò)程可直接運(yùn)行，也可遠(yuǎn)程運(yùn)行；存儲(chǔ)過(guò)程直接在服務(wù)器端運(yùn)行。使用存儲(chǔ)過(guò)程具有如下優(yōu)點(diǎn)：將業(yè)務(wù)操作封裝可為復(fù)雜的業(yè)務(wù)操作編寫存儲(chǔ)過(guò)程，放在數(shù)據(jù)庫(kù)中；用戶可調(diào)用存儲(chǔ)過(guò)程執(zhí)行，而業(yè)務(wù)操作對(duì)用戶是不可見(jiàn)的；若存儲(chǔ)過(guò)程僅修改了執(zhí)行體，沒(méi)有修改接口（即調(diào)用參數(shù)），則用戶程序不需要修改，達(dá)到業(yè)務(wù)封裝的效果。便于事務(wù)管理事務(wù)控制可以用在存儲(chǔ)過(guò)程中；用戶可依據(jù)業(yè)務(wù)的性質(zhì)定義事務(wù)，并對(duì)事務(wù)進(jìn)行相應(yīng)級(jí)別的操作。9.4存儲(chǔ)過(guò)程

實(shí)現(xiàn)一定程度的安全性保護(hù)存儲(chǔ)過(guò)程存放在數(shù)據(jù)庫(kù)中，且在服務(wù)器端運(yùn)行；對(duì)于不允許用戶直接操作的表或視圖，可通過(guò)調(diào)用存儲(chǔ)過(guò)程來(lái)間接地訪問(wèn)這些表或視圖，達(dá)到一定程度的安全性；這種安全性緣于用戶對(duì)存儲(chǔ)過(guò)程只有執(zhí)行權(quán)限，沒(méi)有查看權(quán)限；擁有存儲(chǔ)過(guò)程的執(zhí)行權(quán)限，自動(dòng)獲取了存儲(chǔ)過(guò)程中對(duì)相應(yīng)表或視圖的操作權(quán)限；這些操作權(quán)限僅能通過(guò)執(zhí)行存儲(chǔ)過(guò)