SQL語言與資料庫完整性和安全性

哈哈哈捧場王發表於2020-12-13
SQL資料庫

一、資料庫完整性的概念及分類

1.1 什麼是資料庫完整性

  • 資料庫完整性(DB Integrity)：是指DBMS應保證的DB的一種特性--在任何情況下的正確性、有效性和一致性
    • 廣義完整性：語義完整性、併發控制、安全控制、DB故障恢復等
    • 狹義的完整性：專指語義完整性，DBMS通常有專門的完整性管理機制與程式來處理語義完整性問題。
  • 完整性涉及到
    • 關係模型中的完整性要求
      • 實體完整性
      • 參照完整性
      • 使用者自定義完整性

1.2 為什麼會產生完整性問題

  • 不正當的資料庫操作，如輸入錯誤、操作失誤、程式處理失誤等
  • 資料庫完整性管理的作用
    • 防止和避免資料庫中不合理資料的出現
    • DBMS應儘可能地自動防止DB中語義不合理現象

1.3 怎樣保證資料庫的完整性

  • DBMS允許使用者定義一些完整性約束規則(用SQL-DDL來定義)
  • 當有DB更新操作時，DBMS自動按照完整性約束條件進行檢查，以確保更新操作符合語義完整性

  • 完整性約束條件(或稱完整性約束規則等)的一般形式
  • Integrity Constraint ::=(O,P,A,R)
    • O：資料集合：約束的物件
      • 列、多列(元組)、元組集合
    • P：謂詞條件，什麼樣的約束
    • A：觸發條件：什麼時候檢查
    • R：響應動作：不滿足時怎麼辦

1.4 完整性約束的分類

1.4.1 按約束物件分類

  • 域完整性約束條件
    • 施加於某一列上，對給定列上所要更新的某一候選值是否可以接受進行約束條件判斷，這是孤立的
  • 關係完整性約束條件
    • 施加於關係/table上，對給定table上所要更新的某一候選元組是否可以接受進行約束條件判斷，或是對一個關係中的若干元組和另一個關係中的若干元組間的聯絡是否可以接受進行約束條件判斷

1.4.2 按約束來源分類

  • 結構約束
    • 來自於模型的約束，例如函式依賴約束、主鍵約束(實體完整性約束)、外來鍵約束(參照完整性)，只關心數值等相等與否、是否允許空值等
  • 內容約束
    • 來自於使用者的約束，如果使用者自定義完整性，關心元組或屬性的取值範圍。例如：Student表的Sage屬性值在15歲至40歲之間等

1.4.3 按約束狀態分類

  • 靜態約束
    • 要求DB在任一時候均滿足的約束；例如Sage在任何時候都應該滿足大於0而小於150(假定人活最大年齡是150)
  • 動態約束
    • 要求DB從一狀態變為另一狀態時應滿足的約束；例如工資只能升，不能降

二、利用SQL語言實現資料庫的靜態完整性

2.1 SQL語言支援如下幾種約束

  • 靜態約束
    • 列完整性——域完整性約束
    • 表完整性——關係完整性約束
  • 動態約束
    • 觸發器

2.2 SQL語言實現約束的方法-Create Table

  • create table有三種功能：定義關係模式、定義完整性約束、和定義物理儲存特性
  • 定義完整性約束條件
    • 列完整性
    • 表完整性
create table tablename
(colname datatype [default {default_constant|NULL}]
    [col_constr {col_constr...}]
|, table_constr
{, {colname datatype [default {default_constant|NULL}]
    [col_constr {col_constr...}]
|, table_constr}
...});
  • col_constr列約束：一種域約束型別，對單一值進行約束
{not null |    // 列值非空
    [constraint constraintname]    // 為約束命名，便於以後撤銷
        { unique    // 列值唯一
          | primary key    // 列為主鍵
          | check (search_cond)    // 列值滿足條件，條件只能使用列當前值
          | references tablename [(colname)] [on delete {cascade|set null}]}} // 引用另一個表tablename的列colname的值，如有on delete cascade 或on delete set null語句，則刪除被引用表的某列值v時，要將本表該列值為v的記錄刪除或列值更新為null;預設為無操作
  • Col_constr列約束：只能應用在單一列上，其後面的約束如unqiue，primary key及search_cond只能是單一列唯一、單一列為主鍵、單一列相關
  • 例1
# 假定Ssex只能取{男，女}, 1=<Sage<=150, D#是外來鍵
create table student(
`S#` char(8) not null unique,
Sname char(10),
Ssex char(2) constraint ctssex check (Ssex='男' or Ssex='女'),
Sage Integer check(Sage>=1 and sage<150),
`D#` char(2) references Dept(D#) on delete cascade,
Sclass char(6));

  • 例
//假定每門課學分最多5分，最少0分
create table Course(
C# char(3),
Cname char(12),
Chours integer,
Credit float(1) constraint ctcredit check (Credit >= 0.0 and Credit <= 5.0),
T# char(3) references Teacher(T#) on delete cascade);

  • table_constr表約束：是應用在關係上，即應用在關係上，即對關係的多列或元組進行約束，列約束是其特例
  • 一種關係約束型別，對多列或元組的值進行約束
[constraint constraintname]    // 為約束命名，便於以後撤銷
    {unique (colname {, colname...})    //幾列值組合在一起是唯一
    | primary key (colname {, colname...})    //幾列值聯合為主鍵
    | check(search_condition)    // 元組多列值共同滿足條件，條件中只能使用同一元組的不同列當前值
    | foreing key(colname {,colname...})
    references tablename [(colname {, colname...})]
    [on delete cascade]    // 引用另一表tablename的若干列的值作為外來鍵
}
  • check中的條件可以是select-from-where內任何where後的語句，包含子查詢
  • create table中定義的表約束或列約束可以在以後根據需要進行撤銷或追加。撤銷或追加約束的語句是alter table(不同的系統可能有差異)
alter table tablename
[add ({colname datatype [default {default_const|null}]
        [col_constr{col_constr}]|,table_constr}
    {, colname....})]
[drop {column culumnname|(columname{,columnname...})}]
[modify (columnname data-type
    [default {default_const|null}] [[not] null]
    {,columnname...})]
[add constraint constr_name]
[drop constraint constr_name]
[drop primary key];
  • 例：撤銷SC表的ctscore約束(由此可見，未命名的約束是不能撤銷的)
alter table SC
drop constraint ctscore;
  • 例：若要再對SC表的score進行約束，比如分數在0-150之間，則可新增加一個約束。在Oracle中增加新約束，需要通過修改列的定義來完成
alter table SC
modify (Score float(1) constraint nctscore check (Score>=0.0 and Score<=150.0));

2.3 SQL語言實現約束的方法-斷言

2.3.1 斷言ASSERTION

  • 一個斷言就是一個謂詞表示式，它表達了希望資料庫總能滿足的條件
  • 表約束和列約束就是一些特殊的斷言
  • SQL還提供了複雜條件表達的斷言。其語法形式為：
create assertion <assertion-name> check <predicate>
  • 當一個斷言建立後，系統將檢測其有效性，並在每一次更新中測試更新是否違反該斷言
  • 斷言測試增加了資料庫維護的負擔，要小心使用複雜的斷言
  • 例：每筆貸款,要求至少一位借款者賬戶中存有最低數目的餘額，例如1000元
create assertion balance_constraint check
(not exists (
    select * from loan
    where not exists (
        select * from borrower, depositor, account
        where loan.loan_number = borrower.loan_number
            and borrow.customer_name = depositor.customer_name
            and depositor.account_number = account.account_number
            and account.balance >= 1000)));

  • 例：每個分行的貸款總量必須小於該分行所有賬戶的餘額總和
create assertion sum_constraint check
(not exists (select * from branch
            where (select sum(amount) from loan
                    where loan.branch_name = 
                            branch.branch_name
                    >=(select sum(balance) from account
                    where account.branch_name = 
                        branch.branch_name)));

三、利用SQL語言實現資料庫的動態完整性

3.1 實現資料庫動態完整性的方法——觸發器Trigger

  • create table中的表約束和列約束基本上都是靜態的約束，也基本上都是對單一列或單一元組的約束(儘管有參照完整性)，為實現動態約束以及多個元組之間的完整性約束，就需要觸發器技術Tigger
  • Tigger是一種過程完整性約束(相比之下，create table中定義的都是非過程性約束)，是一段程式，該程式可以在特定的時刻被自動觸發執行，比如在一次更新操作之前執行，或在更新之後執行。
  • 基本語法：
create trigger trigger_name before|after
    {insert|delete|update [of colname {, colname...}]}
    on tablename [referencing corr_name_def {, corr_name_def...}]
    [for each row|for each statement]    // 對更新操作的每一條結果(前者)，或整個更新操作完成(後者)
    [when (search_condition)]    // 檢查條件，如滿足執行下述程式
        { statement    // 單行程式直接書寫，多行程式要用下行方式
        |begin atomic statement; {statement;...} end};
  • 觸發器Trigger意義：當某一個事件發生時（before|after），對該事件產生的結果(或是每一元組，或是整個操作的所有元組)，檢查條件search_condition，如果滿足條件，則執行後面的程式段。條件或程式段中引用的變數可用corr_name_def來限定
  • 事件：before|after {insert | delete | update ...}
    • 當一個事件(insert，delete，或update)發生之前before或發生之後after觸發
    • 操作發生，執行觸發器操作需處理兩組值；更新前的值和更新後的值，這個值由corr_name_def的使用來區分

  • corr_name_def的定義
{ old [row] [as] old_row_corr_name    // 更新前的舊元組命別名為
| new [row] [as] new_row_corr_name    // 更新後的新元組命別名為
old [row] [as] old_row_corr_name    // 更新前的舊元組命別名為
| new [row] [as] new_row_corr_name    // 更新後的新元組命別名為
}
  • corr_name_def將在檢測條件或後面的動作程式中被引用處理
  • 例1:設計一個觸發器當進行Teacher表更新元組時, 使其工資只能升不能降
create trigger teacher_chgsal before update of salary
on teacher
referencing new x, old y
for each row when (x.salary < y.salary)
    begin
raise_application_error(-20003, 'invalid salary on update');  // 此條語句是ORcale的錯誤處理函式
    end;
  • 例2:假設student(S#, Sname, SumCourse), SumCourse為該同學已學習課程的門數，初始值為0，以後每選修一門都要對其增1 。設計一個觸發器自動完成上 述功能。
create trigger sumc after insert on sc
    refercing new row newi
    for each row
        begin
            update student set SumCourse = SumCourse + 1
            where S#=:newi.S#;
        end;
  • 例3:假設student(S#, Sname, Sage, Ssex, Sclass)中某一學生要變更其主碼S#的值，如使其原來的98030101變更為99030131, 此時sc表中該同學已選課記錄 的S#也需自動隨其改變。設計一個觸發器完成上述功能
create trigger updS# after update of S# on student
referencing old oldi, new newi
for each row
begin
update sc set S#=newi.S# where S#=:old.S#;
end;
  • 例4:假設student(S#, Sname, SumCourse), 當刪除某一同學S#時，該同學的所 有選課也都要刪除。設計一個觸發器完成上述功能
create trigger delS# after delete on Student
referencing old oldi
for each row
begin
delete sc where S#=:oldi.S#;
end;
  • 例5:假設student(S#, Sname, SumCourse), 當刪除某一同學S#時，該同學的所 有選課中的S#都要置為空值。設計一個觸發器完成上述功能
create trigger delS# after delete on Student
referencing old oldi
for each row
begin
    update sc set S# = NULL where S# = :oldi.S#;
end;
  • 例六:假設Dept(D#, Dname, Dean), 而Dean一定是該系教師Teacher(T#, Tname, D#, Salary)中工資最高的教師。設計一個觸發器完成上述功能
create trigger upddean before update of Dean on Dept
referencing old oldi, new newi
for each row when (dean not in
select Tname from Teacher where D#=:newi.D#
and salary >=all(select salary from Teacher where D#=:newi.D#))
begin 
raise_application_error(-20003,'invalid Dean on update');
end;

三、資料庫安全性的概念和分類

3.1 資料庫安全性的概念

  • 資料庫安全性是指DBMS應該保證的資料庫的一種特性(機制或手段)：免受非法、非法授權使用者的使用、洩漏、更改或破壞
  • 資料庫安全性管理涉及到許多方面
    • 社會法律及倫理方面：私人資訊收到保護，未授權人員訪問私人資訊違法
    • 公共政策/制度方面：例如：政府或組織的資訊公開或非公開制度
    • 安全策略：政府、企業或組織所實施的安全性策略，如集中管理和分散管理，需者方知策略(也稱最少特權策略)
    • 資料的安全級別：絕密(Top Secret)，機密(Secret)，可信(Confidential)和無分類(Unclassified)
    • 資料庫系統DBS的安全級別：物理控制、網路控制、作業系統控制、DBMS控制

3.2 資料庫安全性的分類

  • 自主安全性機制：存取控制(Access Control)
    • 通過限制在使用者之間的傳遞，使使用者自主管理資料庫安全性
  • 強制安全性機制
    • 通過對資料和使用者強制分類，使得不同類別使用者能夠訪問不同類別的資料
  • 推斷控制機制
    • 防止通過歷史資訊，推斷出不該被其知道的資訊
    • 防止通過公開資訊(通常是一些聚集資訊)推斷出私密資訊(個體資訊)，通常在一些由個體資料構成的公共資料庫中此問題尤為重要
  • 資料加密儲存機制
    • 通過加密、解密保護資料，金鑰、加密/解密方法與傳輸

3.3 資料庫管理員的責任與義務

  • 熟悉熟悉相關的法規、政策，協助組織的決策者制定好相關的安全策略
  • 規劃好安全控制保障措施，例如，系統安全級別、不同級別上的安全控制措施，對安全遭破壞的響應
  • 劃分好資料的安全級別以及使用者的安全級別
  • 實施安全性控制：DBMS專門提供一個DBA賬戶，該賬戶是一個超級使用者 或稱系統使用者。DBA利用該賬戶的特權可以進行使用者賬戶的建立以及許可權授 予和撤消、安全級別控制調整等

3.4 資料庫自主安全性機制

3.4.1 資料庫自主安全性

  • 自主安全性機制
    • 通常情況下，自主安全性是通過授權機制來實現的
    • 使用者在使用資料庫前必須由DBA處獲得一個賬戶，並由DBA授予該賬戶一定的許可權，該賬戶的使用者依據其所擁有的許可權對資料庫進行操作；同時，該賬戶使用者也可將其所擁有的權利轉授給其他的使用者，由此可以實現許可權在使用者之間的傳播和控制
      • 授權者：決定使用者權利的人
      • 授權：授予使用者訪問的權利

3.4.2 DBMS怎樣實現資料庫自主安全性

  • DBMS允許使用者定義一些安全性控制規則(用SQL-DCL來定義)
  • 當有DB訪問操作時，DBMS自動按照安全性控制規則進行檢查，檢查通過則允許訪問，不通過則不允許訪問

3.4.3 資料庫自主安全性訪問規則

  • DBMS將權利和使用者(賬戶)結合在一起，形成一個訪問規則表，依據該規則表可以實現對資料庫的安全性控制
  • AccessRule ::= (S,O,t.P)
    • S：請求主體(使用者)
    • O：訪問物件
    • t：訪問權利
    • P：謂詞
  • {AccessRule}通常存放在資料字典或稱系統目錄中，構成了所有使用者對DB的訪問權利
  • 使用者多時，可以按使用者組建立訪問規則
  • 訪問物件可大可小(目標粒度Object granularity)：屬性/欄位、記錄/元組、關係、資料庫
  • 權利：包括建立、增、刪、改、查等
  • 謂詞：擁有權利需滿足的條件

3.4.4 自主安全性的實現方式一：儲存矩陣

3.4.5 自主安全性的實現方式二：檢視

  • 檢視是安全性控制的重要手段
  • 通過檢視可以限制使用者對關係中某些資料項的存取
  • 通過檢視可以將資料訪問物件與謂詞結合起來，限制使用者對關係元組的存取
  • 使用者定義檢視後，檢視變成為一新的資料物件，參與到儲存矩陣和能力表中進行描述

四、利用SQL語言實現資料庫自主安全性

4.1 SQL語言的使用者與權利

  • SQL語言包含了DDL、DML和DCL。資料庫安全性控制是屬於DCL範疇
  • 授權機制——自主安全性；檢視的運用
  • 關係級別(普通使用者)\leftarrow賬戶級別(程式設計師使用者)\leftarrow超級使用者(DBA)
    • (級別1)select：讀(讀DB\Talbe\Tecord\Attribute\...)
    • (級別2)Modify：更新
      • Insert：插入插入新元組,...)
      • Update：更新(更新元組中的某些值,...)
      • Delete：刪除(刪除元組,...)
    • (級別3)Create：建立(建立表空間、模式、表、索引、檢視等)
      • create：建立
      • alter：更新
      • drop：刪除
  • 級別搞的權利自動包含級別低的權利。如某人擁有更新的權利，它也自動擁有讀的權利。在有些DBMS中，將級別3的權利稱為賬戶級別的權利，而將級別1和2稱為關係級別權利

4.2 SQL-DCL的命令及其應用

  • 授權命令
grant {all privileges|privilege{,privilege...}}
on [table] tablename|viewname
to {public|user-id {,user-id...}}
[with grant option];
  • user-id，某一個使用者賬戶，由DBA建立的合法賬戶
  • public，允許所有有效使用者使用授權的權利
  • privilege是下面的權利
    • select | insert | update | delete | all priviledges
  • with grant option選項是允許被授權者傳播這些權利
  • 收回授權命令
revoke {all privileges|priv {,priv...}} on tablename|viewname
from {public|user{,user...}};

4.3 安全性控制的其他簡介

4.3.1 自主安全性的授權過程及其問題

  • 授權過程
    • DBA建立DB，併為每一個使用者建立一個賬戶
    • DBA授予某使用者賬戶級別的權利
    • 具有賬戶級別的使用者可以建立基本表或檢視，他也自動成為該表或該檢視的屬主賬戶，擁有該表或該檢視的所有訪問權利
    • 擁有屬主賬戶的使用者可以將其中的一部分權利授予另外的使用者，該使用者也可以將權利進一步授權給其他的使用者

  • 注意授權的傳播範圍
    • 傳播範圍包括兩個方面：水平傳播數量和垂直傳播數量
      • 水平傳播數量是授權者的再授權使用者數目(樹的廣度)
      • 垂直傳播數量是授權者傳播給被授權者，在被傳播給另一個被授權者,...傳播的深度(樹的深度)
    • 有些系統提供了傳播範圍控制，有些系統並沒有提供，SQL標準中也並沒有限制。

  • 當一個使用者的權利被收回時，通過其傳播給其他使用者的權利也將被收回
  • 如果一個使用者從多個使用者處獲得了授權，則當其中某一個使用者收回授權時，該使用者可能仍包郵權利。

4.4 強制安全性機制

4.4.1 強制安全性機制

  • 強制安全性通過對資料物件進行安全性分級
    • 絕密(Top Secret)，機密(Secret)，可信(Confidential)和無分類(Unclassified)
  • 同時對使用者也進行上述安全性分級
  • 從而強制實現不同級別使用者訪問不同級別資料的一種機制

4.4.2 訪問規則

  • 使用者S，不能讀取資料物件O，除非Level(S)>=Level(O)
  • 使用者S，不能寫資料物件，除非Level(S)<=Level(O)

4.4.3 強制安全性機制的實現

  • DBMS引入強制安全性機制，可以通過擴充套件關係模式來實現
    • 關係模式：R(A1:D1,A2:D2,...,An:Dn)
    • 對屬性和元組引入安全性分級特性或稱分類特性
      • R(A1:D1,C1,A2,D2,C2,...,An:Dn,Cn,TC)
      • 其中C1,C2,...,Cn分別為屬性D1,D2,...,Dn的安全分類特性；TC為元組的分類特性
    • 這樣關係中的每個元組，都將擴充套件為帶有安全分級的元組，例如

  • 強制安全性機制使得關係形成為多級關係(不同級別使用者所能看到的關係的子集)，也出現多重例項、多級關係完整性等許多新的問題或新的處理技巧，在使用中需要注意仔細研究

相關文章