SQL语言与数据库完整性和安全性

一、数据库完整性

1、概念

**数据库完整性(DB Integrity)**是指DBMS应保证的DB的一种特性–在任何情况下的正确性、有效性和一致性。

1. 广义完整性：语义完整性、并发控制、安全控制、DB故障恢复等
2. 狭义完整性：专指语义完整性，DBMS通常有专门的完整性管理机制与程序来处理语义完整性问题。(本讲专指语义完整性)

数据库完整性问题的产生原因
不正当的数据库操作，如输入错误、操作失误、程序处理失误等

数据库完整性管理的作用

1. 防止和避免数据库中不合理数据的出现
2. DBMS应尽可能地自动防止DB中语义不合理现象

DBMS怎样自动保证完整性
DBMS允许用户定义一些完整性约束规则(用SQL-DDL来定义)
当有DB更新操作时，DBMS自动按照完整性约束条件进行检查，以确保更新操作符合语义完整性

2、约束条件

完整性约束条件(或称完整性约束规则)的一般形式
Integrity Constraint ::=(O，P，A，R)
O：数据集合：约束的对象：列、多列(元组)、元组集合
P：谓词条件:什么样的约束?
A：触发条件：什么时候检查？
R：响应动作：不满足时怎么办?

3、数据库完整性分类

(1) 按约束对象分类

1. 域完整性约束条件
   施加于某一列上，对给定列上所要更新的某一候选值是否可以接受进行约束条件判断，这是孤立进行的。
2. 关系完整性约束条件
   施加于关系/table上，对给定table上所要更新的某一候选元组是否可以接受进行约束条件判断，或是对一个关系中的若千元组和另一个关系中的若千元组间的联系是否可以接受进行约束条件判断。

(2) 按约束来源分类

1. 结构约束
   来自于模型的约束，例如函数依赖约束、主键约束(实体完整性)、外键约束(参照完整性)，只关心数值相等与否、是否允许空值等;
2. 内容约束
   来自于用户的约束，如用户自定义完整性，关心元组或属性的取值范围。例如Student表的Sage属性值在15岁至40岁之间等。

(3)按约束状态分类

1. 静态约束
   要求DB在任一时候均应满足的约束;例如Sage在任何时候都应满足大于0而小于150(假定人活最大年龄是150)。
2. 动态约束
   要求DB从一状态变为另一状态时应满足的约束；例如工资只能升，不能降：工资可以是800元，也可以是1000元;可以从800元更改为1000元，但不能从1000元更改为800元。

二、利用SQL语言实现数据库的静态完整性

SQL语言支持如下几种约束

1. 静态约束
   列完整性—域完整性约束
   表完整性–关系完整性约束
2. 动态约束：触发器
   

1、SQL语言实现约束的方法-Create Table

Create Table有三种功能：定义关系模式、定义完整性约束和定义物理存储特性

定义完整性约束条件
列完整性
表完整性
CREATE TABLE tablename
( ( colname datatype [ DEFAULT{ default_constant | NULL}]
[ col_constr {col_constr… .}]
l , table_constr
{, { colname datatype [DEFAULT { default_constant |NULL}]
[col_constr {col_constr. . .} ]
l , table_constr }
…});

(1) Col_constr列约束

一种域约束类型，对单一列的值进行约束

{ NOT NULL I
// 列值非空
[CONSTRAINT constraintname ]
// 为约束命名,便于以后撤消
{ UNIQUE
// 列值是唯一
| PRIMARY KEY
// 列为主键
| CHECK (search_cond)
// 列值满足条件,条件只能使用列当前值
|REFERENCES tablename [(colname)]
[ON DELETE{ CASCADE I SET NULL }1 }}
// 引用另一表tablename的列colname的值，如有ON DELETE CASCADE或ON DELETE SETNULL语句，则删除被引用表的某列值v时，要将本表该列值为v的记录删除或列值更新为null;缺省为无操作。

Col_constr列约束:只能应用在单一列上，其后面的约束如UNIQUE,PRIMARY KEY及search_cond只能是单一列唯一、单一列为主键、和单一列相关

示例
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));
// 假定Ssex只能取{男,女}，1=<Sage<=150，D#是外键

示例
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 );
// 假定每门课学分最多5分,最少0分
ON DELETE CASCADE是对于外键的引用操作，可以实现在从父表中删除数据时自动删除子表中的数据。

(2) table_constr表约束

一种关系约束类型，对多列或元组的值进行约束

[ CONSTRAINT constraintname ]
//为约束命名,便于以后撤消
{ UNIQUE (colname {, colname. .}
// 几列值组合在一起是唯一
| PRIMARY KEY (colname {, colname…})
//几列联合为主键
| CHECK(search_condition)
//元组多列值共同满足条件
// 条件中只能使用同一元组的不同列当前值
| FOREIGN KEY(colname {, colname… .})
REFERENCES tablename [(colname {, colname. . .})][ON DELETE CASCADE]}
//引用另一表tablename的若干列的值作为外键

table_constr表约束:是应用在关系上，即对关系的多列或元组进行约束，列约束是其特例

示例
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) , primary key(S#));

示例
Create Table Course ( C# char(3)，Cname char(12), Chours integer, Credit float(1) constraint ctcredit check (Credit >=0.0 andCredit<=5.0 )，T#char(3) references Teacher(T#) on delete cascade, primary key(C#), constraint ctcc check(Chours/Credit= 20));
//假定严格约束20学时一个学分

示例
Create Table sC (S# char(8), C# char(3), Score float(1) constraint ctscore check (Score>=0.0 andScore<=100.0), forergn key (S#) references student(S#) on delete cascade,forergn key (C#) references course(C#) on delete cascade );

check中的条件可以是Select-From-Where内任何Where后的语句，包含子查询。

示例
Create Table sc ( S# char(8) check( S# in (select s# from student))， C#char(3) check( C# in (select C# from course))， Score float(1) constraint ctscore check (Score>=0.0 andScore<=100.0),

Create Table中定义的表约束或列约束可以在以后根据需要进行撤消或追加**。撤消或追加约束的语句是 Alter Table(不同系统可能有差异)**

ALTER TABLE tblname
[ADD( f colname datatype [DEFAULT {default_const|NULL} ]
[col_constr {col_constr… ] l , table_constr }
{, colname …})) ]

[DROP { COLUMN columnname | (columnname {, 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 Tablesc Modify ( Score float(1) constraint nctscore check (Score>=0.0 and score<=150.0));

有些DBMS支持独立的追加约束,注意书写格式可能有些差异示例:
Alter Table sc Add Constraint nctscore check (Score>=0.0 and Score<=150.0) );

2、SQL语言实现约束的方法-断言

1. 一个断言就是一个谓词表达式，它表达了希望数据库总能满足的条件
2. 表约束和列约束就是一些特殊的断言
3. SQL还提供了复杂条件表达的断言。其语法形式为:
   CREATE ASSERTION CHECK
   当一个断言创建后，系统将检测其有效性，并在每一次更新中测试更新是否违反该断言。

断言测试增加了数据库维护的负担,要小心使用复杂的断言。

**示例：**每笔贷款,要求至少一位借款者账户中存有最低数目的余额,例如1000元
create assertion balance_constraint check (not exists ( select * from loanwhere not exists ( select * from borrower, depositor, account where loan.loan_number = borrower.loan_number and borrower.customer_name = depositor.customer_nameand depositor.account_number = account.account_numberand 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 accountwhere account.branch_name = branch.branch_name )))

三、利用SQL语言实现数据库的动态完整性

1、实现数据库动态完整的方法—触发器Trigger

触发器Trigger是一种过程完整性约束(相比之下，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 STATEMENTJ
//对更新操作的每一条结果(前者)，或整个更新操作完成(后者)
[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
更新前的旧元组命别名为
l NEW[ROW] [AS] new_row_corr_name
更新后的新元组命别名为
l OLD TABLE [AS] old_table_corr_name
更新前的旧Table命别名为
l NEW TABLE [AS] new_table_corr_name
更新后的新Table命别名为
}

corr_name_def将在检测条件或后面的动作程序段中被引用处理

示例一：设计一个触发器当进行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'); // 此条语句为Oracle的错误处理函数 end;

示例二:假设student(S, Sname, SumCourse), SumCourse为该同学已学习课程的门数，初始值为0，以后每选修一门都要对其增1。设计一个触发器自动完成上述功能。
create trigger sumc after insert on sc referencing new row newi for each row begin update student set SumCourse = SumCourse+1 where S# = :newi.S# ; end;

示例三：假设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# = :oldi.S# ;end;

示例四：假设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;

示例五假设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;

四、数据库安全性

数据库安全性是指DBMS应该保证的数据库的一种特性(机制或手段)：免受非法、非授权用户的使用、泄漏、更改或破坏。

DBMS的安全机制

1. 自主安全性机制：存取控制(Access Control)
   通过权限在用户之间的传递，使用户自主管理数据库安全性
2. 强制安全性机制:
   通过对数据和用户强制分类，使得不同类别用户能够访问不同类别的数据

1、数据库自主安全性机制

通常情况下，自主安全性是通过授权机制来实现的。
用户在使用数据库前必须由DBA处获得一个账户，并由DBA授予该账户一定的权限，该账户的用户依据其所拥有的权限对数据库进行操作；同时，该帐户用户也可将其所拥有的权利转授给其他的用户(账户)，由此实现权限在用户之间的传播和控制。

授权者：决定用户权利的人
授权：授予用户访问的权利

DBMS怎样自动实现自主安全性呢?
DBMS允许用户定义一些安全性控制规则(用SQL-DCL来定义)
当有DB访问操作时，DBMS自动按照安全性控制规则进行检查，检查通过则允许访问，不通过则不允许访问

2、数据库自主安全性访问规则

DBMS将权利和用户(账户)结合在一起，形成一个访问规则表，依据该规则表可以实现对数据库的安全性控制
AccessRule ::=(S，O，t，P)

S：请求主体(用户)
O：访问对象
t：访问权利
P：谓词

1. {AccessRule }通常存放在数据字典或称系统目录中，构成了所有用户对DB的访问权利；
2. 用户多时，可以按用户组建立访问规则
3. 访问对象可大可小(目标粒度Object granularity):属性/字段、记录/元组、关系、数据库
4. 权利：包括创建、增、删、改、查等
5. 谓词:拥有权利需满足的条件

3、自主安全性的实现方式

(1) 存储矩阵

(2) 视图

视图是安全性控制的重要手段。
通过视图可以限制用户对关系中某些数据项的存取，例如：
视图1：Create EmpV1 as select * from Employee
视图2：Create EmpV2 as select Pname, D# from Employee

通过视图可将数据访问对象与谓词结合起来，限制用户对关系中某些元组的存取，例如:
视图1： Create EmpV3 as select * from Employee where P#= :UserId
视图2: Create EmpV4 as select * from Employee whereHead = :Userld

用户定义视图后，视图便成为一新的数据对象，参与到存储矩阵与能力表中进行描述

五、利用SQL语言实现数据库自主安全性

1、SQL语言的用户与权利

SQL语言包含了DDL，DML和DCL。数据库安全性控制是属于DCL范畴>授权机制—自主安全性;视图的运用

关系级别(普通用户）账户级别(程序员用户) 超级用户(DBA)

1. 级别1：Select :读(读DB,Table, Record, Attribute, … )
2. 级别2：Modify :更新
   Insert:插入(插入新元组,.…. )
   Update :更新(更新元组中的某些值，…)√Delete :删除(删除元组，.….)
3. 级别3：Create :创建(创建表空间、模式、表、索引、视图等)
   Create:创建
   Alter :更新
   Drop :删除

级别高的权利自动包含级别低的权利。如某人拥有更新的权利，它也自动拥有读的权利。在有些DBMS中，将级别3的权利称为账户级别的权利，而将级别1和2称为关系级别的权利。

2、SQL-DCL的控制安全性示例

授权命令
GRANT {fall 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选项是允许被授权者传播这些权利

示例假定高级领导为Emp0001,部门领导为Emp0021,员工管理员为Emp2001,收发员为Emp5001(均为Userld,也即员工的P#)
Grant All Priviledges ON Employee TO Emp2001;
Grant SELECT ON EmpV2TO Emp5001 ;
Grant SELECT ON EmpV3 TO public;
Grant SELECT ON EmpV4 TO Emp0021;

授予视图访问的权利，并不意味着授予基本表访问的权利(两个级别:基本关系级别和视图级别)
授权者授予的权利必须是授权者已经拥有的权利

收回授权命令
REVOKE {all privilEges | priv {, priv…}} ON tablename | viewname
FROM {public / user {, user…]} };

示例
revoke select on employee from UserB;

六、安全性控制的其他简介

1、自主安全性的授权过程及其问题

1. DBA创建DB,并为每一个用户创建一个账户。
   假定建立了五个用户:UserA, UserB, UserC, UserD, UserE。
2. DBA授予某用户账户级别的权利。
   假定授予UserA。
3. 具有账户级别的用户可以创建基本表或视图，他也自动成为该表或该视图的属主账户，拥有该表或该视图的所有访问权利。
   假定UserA创建了Employee,则UserA就是Employee表的属主账户
4. 拥有属主账户的用户可以将其中的一部分权利授予另外的用户，该用户也可将权利进一步授权给其他的用户…
   假定UserA将读权限授予UserB,而userB又将其拥有的权限授予UserC,如此将权利不断传递下去。

注意授权的传播范围

1. 传播范围包括两个方面:水平传播数量和垂直传播数量
2. 水平传播数量是授权者的再授权用户数目(树的广度)
3. 垂直传播数量是授权者传播给被授权者，再被传播给另一个被授权者,…传播的深度(树的深度)

有些系统提供了传播范围控制，有些系统并没有提供，SQL标准中也并没有限制。

当一个用户的权利被收回时,通过其传播给其他用户的权利也将被收回
如果一个用户从多个用户处获得了授权，则当其中某一个用户收回授权时，该用户可能仍保有权利。例如UserC从UserB和UserE处获得了授权，当UserB收回时，其还将保持UserE赋予其的权利。

2、强制安全性机制

强制安全性通过对数据对象进行安全性分级：绝密(Top Secret),机密(Secret)，可信(Confidential)和无分类(Unclassified)。同时对用户也进行上述的安全性分级。从而强制实现不同级别用户访问不同级别数据的一种机制。

访问规则
用户S，不能读取数据对象O，除非Level(S)>=Level(O)用户S，不能写数据对象，除非Level(S)<=Level(O)。高级别用户写低级别数据后，低级别的数据就会改变为高级别用户的级别，所以高级别用户不能修改低级别数据。