数据库学习基础篇②

基础篇内容

一、数据库安全性

二、数据库完整性


数据库安全性

计算机安全性概述

 数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄露、更改或破坏。
  安全性问题不是数据库系统所独有的,所有计算机系统都有这个问题。只是在数据库系统中大量数据集中存放,而且为许多最终用户直接共享,从而使安全性问题更为突出。 系统安全保护措施是否有效是数据库系统的主要指标之一。 数据库的安全性和计算机系统的安全性,包括操作系统、网络系统的安全性是紧密联系、相互支持的。

计算机系统的三类安全性问题

所谓计算机系统安全性,是指为计算机系统建立和采取的各种安全保护措施,以保护计算机系统中的硬件、软件及数据,防止其因偶然或恶意的原因使系统遭到破坏,数据遭到更改或泄露等。

计算机系统安全性问题可以分成三大类:

  • 技术安全类: 指计算机系统中采用具有一定安全性的硬件、软件来实现对计算机系统及其所存数据的安全保护,当计算机系统受到无意或恶意的攻击时仍能保证系统正常运行,保证系统内的数据不增加、不丢失、不泄露。

  • 管理安全类: 软硬件意外故障、场地的意外事故、管理不善导致的计算机设备和数据介质的物理破坏、丢失等安全问题。

  • 政策法律类 : 政府部门建立的有关计算机犯罪、数据安全保密的法律道德准则和政策法规、法令。

该笔记只包含技术安全

数据库安全性控制

用户标识与鉴别

系统提供的最外层安全保护措施。

基本方法是:
系统提供一定的方式让用户标识自己的名字或身份;
系统内部记录着所有合法用户的标识;
每次用户要求进入系统时,由系统核对用户提供的身份标识;
通过鉴定后才提供机器使用权。
用户标识和鉴定可以重复多次。

常用的方法有:

  • 用户标识(User Identification):
    用一个用户名(User Name)或用户标识号(UID)来标明用户身份。
  • 口令(Password):
    系统核对口令以鉴别用户身份。
    通过用户名和口令的方法简单易行,但这些信息容易被人窃取。可以采取更复杂的方法 —— 每个用户预先约定好一个计算过程或者函数。
    系统提供一个随机数
    用户根据自己预先约定的计算过程或者函数进行计算
    系统根据用户计算结果是否正确鉴定用户身份

存取控制

数据库安全性所关心的主要是DBMS的存取控制机制。

数据库安全最重要的一点就是确保只授权给有资格的用户访问数据库的权限,同时令所有未授权的人员无法接近数据,这主要通过数据库系统的存取控制机制实现。
存取控制机制主要包括两部分:

  • 定义用户权限
  • 合法权限检查

用户权限定义和合法权检查机制一起组成了DBMS的安全子系统

常用存取控制方法有:

  • 自主存取控制(Discretionary Access Control,简称DAC)
    同一用户对于不同的数据对象有不同的存取权限
    不同的用户对同一对象也有不同的权限
    用户还可将其拥有的存取权限转授给其他用户
  • 强制存取控制(Mandatory Access Control,简称 MAC)
    每一个数据对象被标以一定的密级
    每一个用户也被授予某一个级别的许可证
    对于任意一个对象,只有具有合法许可证的用户才可以存取

自主存取控制(DAC)方法

主要通过 SQL 的 GRANT 语句和 REVOKE 语句实现自主存取控制。

用户权限由两个要素组成:

  • 数据库对象
  • 操作类型

定义用户存取权限,是指定义用户可以在哪些数据库对象上进行哪些类型的操作。定义存取权限称为授权。

1
2
3
4
5
6
7
8
9
10
11
12
	        关系数据库系统中的存取权限
———————————————————————————————————————————————————————
对象类型 对象 操作类型

———————————————————————————————————————————————————————
数据库 模式 CREATE SCHEMA
基本表 CREATE TABLE ,ALTERTABLE
模式 视图 CREATE VIEW
索引 CREATE INDEX
数据 基本表和视图 SELECT ,INSERT,UPDATE,DELETE ,REFERENCES,ALL PRIVILEGES
数据 属性列 SELECT,INSERT,UPDATE,REFERENCES,ALL PRIVILEGES
———————————————————————————————————————————————————————

自主存取控制的缺点是:

  • 可能存在数据的“无意泄露”。

原因在于:

  • 这种机制仅仅通过对数据的存取权限来进行安全控制,而数据本身并无安全性标记。

解决方法:
对系统控制下的所有主客体实施强制存取控制策略。


授权与回收

授权
SQL中用GRANT语句向用户授权。GRANT语句的一般格式为:

1
2
3
4
GRANT <权限> [,<权限>]... 
[ ON <对象类型> <对象名> ]
TO <用户>[,<用户>]...
[ WITH GRANT OPTION ];

语义:将对指定操作对象的指定操作权限授予指定的用户

一 、GRANT
发出GRANT的可以是:

  • DBA
  • 数据库对象创建者(即属主Owner)
  • 拥有该权限的用户
    接受权限的用户可以是:
  • 一个或多个具体用户
  • PUBLIC(全体用户)

如果指定了WITH GRANT OPTION 子句,则获得某种权限的用户还可以把这种权限再授予其他用户,没有指定WITH GRANT OPTION子句,则获得某种权限的用户只能使用该权限,不能传播该权限。不允许循环授权。

例 把查询Student表的权限授给U1

1
2
3
GRANT SELECT 
ON TABLE Student
TO u1;

例 把对Student表和Course表的全部操作权授予用户U2和U3

1
2
3
GRANT ALL PRIVILEGES
ON TABLE Student ,Course
TO U2 U3;

例 把对表SC 和查询权限授予所有用户

1
2
3
GRANT SELECT
ON TABLE SC
TO PUBLIC;

例 把查询Student表和修改学生学号的权限授给用户U4

1
2
3
GRANT UPDATE(SNO),SELECT
ON TABLE Student
TO U4;

对属性列的授权时必须明确指出相应属性列名

例 把对表SC的INSERT权限授予U5 用户,并允许将此权限授予给其他用户

1
2
3
4
GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;

执行此sql语句后,u5不仅拥有了对表SC的insert权限,还能传播此权限。
例如U5还可以将权限授予U6

1
2
3
4
GRANT INSERT
ON TABLE SC
TO U6
WITH GRANT OPTION;

U6还可以授予给U7

1
2
3
GRANT INSERT
ON TABLE SC
TO U7;

因为U6未给U7传播的权限,所以U7不能再传播此权限。

二、 回收REVOKE
授予的权限可以由DBA或其他授权者用REVOKE语句收回
REVOKE语句的一般格式为:

1
2
3
REVOKE <权限>[,<权限>]... 
[ON <对象类型> <对象名>]
FROM <用户>[,<用户>]...;

例: 把用户U4修改学生学号的权限收回

1
2
3
REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;

例 :回收所有用户对表SC的查询权限

1
2
3
REVOKE SELECT
ON TABLE SC
FROM PUBLIC;

例: 把用户U5对SC表的INSERT权限收回

1
2
3
REVOKE SELECT
ON TABLE SC
FROM U5 CASCADE

将用户U5的insert权限收回的时候,必须级联(CASCADE)收回,不然系统将拒绝(restrict)执行该命令,因为在上面授权的例子中,U5将对SC表的insert权限授予了U6,而U6又授予给了U7

注意:这里缺省值为restrict,有的DBMS缺省值为CASCADE,会自动执行级联操作而不必明显的写出CASCADE,如果U6或者U7还从其他用户处获得对SC表的insert权限,那么他们仍然具有此权限。系统只回收直接或间接从U5处获得的权限

SQL灵活的授权机制:

  • DBA:
    拥有所有对象的所有权限
    不同的权限授予不同的对象
  • 用户 :
    拥有自己建立的对象的全部的操作权限
    GRANT 授予其他用户
  • 被授权的用户
    继续授权许可:再授予
  • 所有授予出去的权利在必要时都可以用revoke语句收回。

三、 创建数据库模式的权限

对数据库模式的授权,DBA 在创建用户时实现
一般CREATE USER格式:

1
2
CREATE USER <username>
[WITH] [ DBA | RESOURCE | CONNECT]

对该语句的说明:

  • 只有系统的超级用户才有权创建一个新的数据库用户
  • 新创建的数据库用户有三种权限:CONNECT , RESOURCE ,DBA
  • CREATE USER命令中没有指定权限的话,默认创建的是CONNECT权限。拥有CONNECT权限的用户不能创建新用户,不能创建模式,也不能创建基本表,只能登陆数据库。然后由DBA或者其他用户授予他应有的权限,根据获得的授权权限他可以对数据库对象进行权限范围内的操作。
  • 拥有RESOURCE权限的用户能创建基本表和视图,称为所创建对象的属主,但是不能创建模式,不能创建新的用户。数据库对象的属主可以使用GRANT语句把对象上的权限授予其他用户。
  • 拥有DBA的用户是系统中的超级用户,可以创建新的用户,创建模式,创建基本表和视图等,DBA拥有对所有数据库对象的存取权限,还可以把这些权限授予一般用户。

一个DBA用户可以拥有CREATE USER, CREATE SCHEMA和CREATE TABLE 权限,一个RESOURCE用户可以拥有CREATE TABLE权限。

1个数据库下,可以有多个模式。
1个模式下,可以有0个或多个表 。
首先我来做一个比喻,什么是User,什么是Database,什么是Schema,什么是Table,什么是列,什么是行,?
我们可以把Database看作是一个大仓库,仓库分了很多很多的房间,Schema就是其中的房间,一个Schema代表一个房间,Table可以看作是每个Schema中的床,Table就被放入每个房间中,不能放置在房间之外。然后床上可以放置很多物品,就好比Table上可以放置很多列和行一样。所以Schema包含的是Object,而不是User。
User是每个对应数据库的主人,既然有操作数据库的权利,就肯定有操作数据库中每个Schema(房间)的权利。换句话说,如果他是某个仓库的主人,那么这个仓库的使用权和仓库中的所有东西都是他的,他有完全的操作权,。我们还可以给User分配具体的权限,也就是他到某一个房间能做些什么,是只能看(Read-Only),还是可以像主人一样有所有的控制权(R/W),这个就要看这个User所对应的角色Role了.
在SQL Server2005中,当我们用Create User创建数据库用户时,我们可以为该用户指定一个已经存在的Schema作为默认Schema,如果我们不指定,则该用户所默认的Schema即为dbo 模式,dbo 房间(Schema)好比一个大的公共房间,在当前登录用户没有默认Schema的前提下,如果你在大仓库中进行一些操作,比如Create Tabe,如果没有指定特定的房间(Schema),那么你的物品就只好放进公共的dbo房间(Schema)了。但是如果当前登录用户有默认的Schema,那么所做的一切操作都是在默认Schema上进行(比如当前登录用户为login1。
在SQL Server2005中创建一个数据库的时候,会有一些Schema包括进去,被包括进去的Schema有:dbo,INFORMATION_SCHEMA,guest,sys等等。


数据库角色

数据库角色是被命令的一组与数据库操作相关的权限,角色是权限的集合。
因此可以为一组具有相同权限的用户创建一个角色,使用角色来管理数据库权限可以简化授权 。

首先用CREATE ROLE语句创建角色,然后用GRANT语句给角色授权

一、角色的创建
格式:

1
CREATE ROLE <角色名>

刚刚创建的角色是空的,没有任何内容,可以用GRANT为角色授权

二、给角色授权

1
2
3
GRANT <权限> [,<权限>].....
ON <对象类型>对象名
TO <角色> [,<角色>]....

DBA和用户可以利用GRANT语句将权限授予给一个或几个角色

三、将一个角色授予其他角色或用户

1
2
3
GRANT <角色1 > [,<角色2>]....
ON <角色3> [<用户1>]....
[WITH ADMIN OPTION]

该语句把橘色授予某个用户或另一个角色,这样一个角色(例如角色3)所拥有的权限就是授予它的全部角色(角色1和角色2)所包含的权限的总和。

授予者或者是角色的创建者,或者拥有在这个角色上的ADMIN OPTION。

如果指定了WITH ADMIN OPTION 子句,则获得某种权限的角色或用户还可以将权限再授予其他角色。

一个角色包含的权限包括直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限。

四、角色权限的收回

授予的权限可以由DBA或其他授权者用REVOKE语句收回
REVOKE语句的一般格式为:

1
2
3
REVOKE <权限>[,<权限>]... 
[ON <对象类型> <对象名>]
FROM <用户>[,<用户>]...;

用户可以回收角色的权限,从而修改角色拥有的权限

REVOKE动作的执行者或者是角色的创建者,或者拥有这个角色上的ADMIN OPTION

例 通过角色来实现将一组权限授予一个用户

步骤:

  1. 首先创建一个角色R1

    1
    CREATE ROLE R1;
  2. 然后使用GRANT语句,使角色R1拥有Student表的SELECT ,UPDATE ,INSERT权限

    1
    2
    3
    GRANT SELECT ,UPDATE,INSERT
    ON TABLE Stdent
    TO R1;
  3. 将这个角色授予王平,张明,赵玲,使他们具有角色R1包含的全部权限

    1
    2
    GRANT R1 
    TO 王平,张明,赵玲;
  4. 当然也可以一次性的通过R1来回收王平的三个权限

    1
    2
    REVOKE R1
    FROM 王平;

例 : 角色的权限修改

1
2
3
GRANT DELETE
ON TABLE Student
TO R1;

使角色R1在原来的基础上增加了Student表的DELETE权限


1
2
3
REVOKE SELECT
ON TABLE Student
FROM R1;

使R1减少了select权限


强制存取控制(MAC)方法

  • MAC是系统为保证更高程度的安全性,按照TDI/TCSEC标准中安全策略的要求,所采取的强制存取检查手段。
  • MAC不是用户能直接感知或进行控制的。
  • MAC适用于对数据有严格而固定密级分类的部门,例如军事部门或政府部门。

在MAC中,DBMS所管理的全部实体被分为两大类:

  1. 主体 —— 系统中的活动实体,包括:
    • DBMS所管理的实际用户
    • 代表用户的各进程
  2. 客体 —— 系统中的被动实体,是受主体操纵的,包括:
    • 文件
    • 基表
    • 索引
    • 视图 等

对于主体和客体,DBMS为它们的每个实例(值)指派一个敏感度标记(Label)。敏感度标记被分为若干级别,例如:

  • 绝密(Top Secret)
  • 机密(Secret)
  • 可信(Confidential)
  • 公开(Public) 等
    主体的敏感度标记称为许可证级别(Clearance Level);
    客体的敏感度标记称为密级(Classification Level)。
    MAC机制就是通过对比主体的Label和客体的Label,最终确定主体是否能够存取客体。

强制存取控制规则:
当某一用户(或某一主体)以标记Label注册入系统时,系统要求他对任何客体的存取必须遵循下面两条规则:

  1. 仅当主体的许可证级别大于或等于客体的密级时,该主体才能读取相应的客体。
  2. 仅当主体的许可证级别等于客体的密级时,该主体才能写相应的客体。

某些系统将第(2)条修正为:
仅当主体的许可证级别小于或等于客体的密级时,主体才能写客体。
也就是说,用户可为写入的数据对象赋予高于自己的许可证级别的密级
这样,一旦数据被写入,该用户自己也不能再读该数据对象了。
这两种规则的共同点是:禁止了拥有高许可证级别的主体更新低密级的数据对象,从而防止了敏感数据的泄露。

强制存取控制(MAC)是对数据本身进行密级标记,无论数据如何复制,标记与数据是一个不可分的整体,只有符合密级标记的用户才可以操纵数据,从而提供了更高级别的安全性。

DAC与MAC共同构成DBMS的安全机制

实现MAC时要首先实现DAC
原因是:较高安全性级别提供的安全保护要包含较低级别的所有保护。

DAC + MAC安全检查:
先进行DAC检查
通过DAC检查的数据对象再由系统进行MAC检查
只有通过MAC检查的数据对象方可存取。


视图机制

可以通过视图机制把要保密的数据对无权存取这些数据的用户隐藏起来,对数据提供一定程度的安全保护。
视图机制的主要功能在于提供数据独立性,其安全保护功能太不精细,往往远不能达到应用系统的要求。
可以将视图机制与授权机制配合使用:

  • 首先用视图机制屏蔽掉一部分保密数据
  • 视图上面再进一步定义存取权限
  • 间接实现了支持存取谓词的用户权限定义

例 建立计算机系学生的视图,把对该视图的SELECT权限授于王平,把该视图上的所有操作权限授于张明。

①首先建立计算机系学生的视图CS_Student

1
2
3
4
5
CREATE VIEW CS_Student
AS
SELECT *
FROM Student
WHERE Sdept = 'CS'

② 然后在视图上进一步定义存取权限

1
2
3
4
5
6
GRANT  SELECT
ON CS_Student
TO 王平 ;
GRANT ALL PRIVILIGES
ON CS_Student
TO 张明;

审计

什么是审计?

  • 启用一个专用的审计日志(Audit Log),将用户对数据库的所有操作记录在上面;
  • DBA可以利用审计日志中的追踪信息,找出非法存取数据的人、时间和内容。

C2以上安全级别的DBMS必须具有审计功能

审计是很费时间和空间的,所以DBMS往往将其作为可选特征,允许DBA根据应用对安全性的要求,灵活地打开或关闭审计功能。
审计功能一般主要用于安全性要求较高的部门。

审计一般可以分为:
用户级审计:

  • 任何用户都可设置的审计,主要针对用户自己创建的数据库表或视图进行审计,记录所有用户对这些表或视图的一切成功和(或)不成功的访问要求以及各种类型的SQL操作。

系统级审计 :

  • 只能由DBA设置,用以监测成功或失败的登录要求、监测GRANT和REVOKE操作以及其他数据库级权限下的操作。

AUDIT语句:设置审计功能
NOAUDIT语句:取消审计功能

例 对修改SC表结构或修改SC表数据的操作进行审计

1
2
AUDIT ALTERUPDATE  
ON SC;

例 取消对SC表的审计

1
2
NOAUDIT ALTERUPDATE  
ON SC;

数据加密

数据加密
防止数据库中数据在存储和传输中失密的有效手段。

加密的基本思想
根据一定的算法将原始数据(术语为明文,Plain text)
变换为不可直接识别的格式(术语为密文,Cipher text)。
不知道解密算法的人无法获知数据的内容。

加密方法主要有:

  1. 替换方法
    使用密钥(Encryption Key)将明文中的每一个字符转换为密文中的一个字符。
  2. 置换方法
    将明文的字符按不同的顺序重新排列
  3. 混合方法
    典型实例:美国1977年制定的官方加密标准——数据加密标准(Data Encryption Standard,简称DES)

DBMS中的数据加密:

  • 有些数据库产品提供了数据加密例行程序
  • 有些数据库产品本身未提供加密程序,但提供了接口

数据加密功能通常也作为可选特征,允许用户自由选择

  • 数据加密与解密是比较费时的操作
  • 数据加密与解密程序会占用大量系统资源
  • 应该只对高度机密的数据加密

统计数据库安全性

统计数据库

  • 允许用户查询聚集类型的信息(如合计,平均值等)
  • 不允许查询单个记录信息

统计数据库中特殊的安全性问题

  • 隐蔽的信息通道
  • 能从合法的查询中推导出不合法的信息

可以制定规则来防范

  • 规则1:任何查询至少要查询N(N足够大)个以上的记录
  • 规则2:任意两个查询的相交数据项不能超M个
  • 规则3:任一用户的查询次数不能超过1+(N-2)/M

数据库安全机制的设计目标:
试图破坏安全的人所花费的代价 >> 得到的利益

小结

数据库的安全性
是指保护数据库,防止因用户非法使用数据库造成数据泄露、更改或破坏。

实现数据库系统安全性的技术和方法

  1. 用户标识与鉴别
    2.存取控制
  • 自主存取控制方法
  • 强制存取控制方法
  1. 视图机制
  2. 审计
  3. 数据加密

数据库完整性

数据库的完整性是指:数据的正确性和相容性。

例如:学生的学号必须存在而且唯一;性别只能是“男”或“女”;学生选修的课程必须是学校开设的课程,等等。

数据的完整性和安全性是两个不同概念。

  • 数据的完整性,是指防止数据库中存在不符合语义的数据,也就是防止数据库中存在不正确的数据。
    所防范的对象是:不合语义的、不正确的数据。
  • 数据的安全性,是指保护数据库防止恶意的破坏和非法的存取
    所防范的对象是:非法用户和非法操作。

为维护数据库的完整性,DBMS必须:

  • 提供定义完整性约束条件的机制
    DBMS应提供定义数据库完整性约束条件(数据必须满足的语义约束条件)的机制,并把它们作为模式的一部分存入数据库中。
  • 提供完整性检查的方法
    检查用户发出的操作请求是否违背了完整性约束条件
  • 采取违约处理
    如果发现用户的操作请求使数据违背了完整性约束条件,则采取一定的动作来保证数据的完整性。

实体完整性

实体完整性定义

关系模型的实体完整性
在CREATE TABLE中用PRIMARY KEY定义。

对单属性构成的码有两种说明方法:
定义为列级约束条件
定义为表级约束条件

对多个属性构成的码只有一种说明方法:
定义为表级约束条件

例 将Student表中的Sno属性定义为码

1
2
3
4
5
6
7
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /* 在列级定义主码*/
Sname CHAR(20) NOT NULL,
Ssex CHAR(2) ,
Sage SMALLINT,
Sdept CHAR(20)
);

或者

1
2
3
4
5
6
7
8
CREATE TABLE Student
(Sno CHAR(9),
Sname CHAR(20) NOT NULL,
Ssex CHAR(2) ,
Sage SMALLINT,
Sdept CHAR(20),
PRIMARY KEY (Sno) /*在表级定义主码*/
);

例 将SC表中的Sno,Cno属性组定义为码

1
2
3
4
5
6
CREATE TABLE SC
(Sno CHAR(9) NOT NULL
Cno CHAR(4) NOT NULL
Grade SMALLINT
PRIMARY KEY (Sno,Cno) /* 对于多个属性构成的主码,只能在表级定义*/
);

↑一个候选码中有多个属性,候选码可以有多种组合,而主码只是选择了其中一种组合而已,所以主码也可以有多个属性构成,而不是有多个主码


实体完整性检查和违约处理

用PRIMARY KEY定义了关系的主码之后,每当对基本表插入记录或对码列进行更新操作时,RDBMS按照实体完整性规则 自动进行检查。包括:

  • 检查主码值是否唯一,如果不唯一则拒绝插入或修改
  • 检查主码的各个属性是否为空,只要有一个为空就拒绝插入或修改

从而保证了实体完整性。

检查记录中的主码值是否唯一的一种方法是进行全表扫描,依次判断中表中的每一条记录的主码值和将插入记录上的主码值(或者修改的新主码值)是否相同,如下图所示

全表扫描是十分耗时的。为了避免对基本表进行全表扫描,RDBMS核心一般都在主码上自动建立一个索引,如下图的B+树索引。通过索引查找基本表中是否已经存在新的主码值,将大大提高效率。

参照完整性

参照完整性定义

关系模型的参照完整性:
在CREATE TABLE中用FOREIGN KEY定义外码
用REFERENCES指明这些外码参照哪些表的主码。

例如,关系SC中一个元组表示一个学生选修的某门课程的成绩,(Sno,Cno)是主码。Sno,Cno分别参照引用Student表的主码和Course表的主码。

例 定义SC中的参照完整性

1
2
3
4
5
6
7
8
CREATE TABLE SC
(Sno CHAR(9) NOT NULL
Cno CHAR(4) NOT NULL
Grade SMALLINT
PRIMARY KEY (Sno,Cno), /* 在表级定义实体完整性 */
FOREIGN KEY (Sno) REFERENCES Student(Sno), /* 在表级定义参照完整性 */
FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 在表级定义参照完整性 */
);

参照完整性检查和违约处理

一个参照完整性将两个表中的相应元组联系起来了。因此,对被
参照表和参照表进行增删操作时有可能破坏参照完整性,必须进行检查。

对表SC和Student有四种可能破坏参照完整性的情况:

  1. SC表中增加一个元组,该元组的Sno属性的值在表Student中找不到一个元组,其Sno属性的值与之相等。

  2. 修改SC表中的一个元组,修改后该元组的Sno属性的值在表Student中找不到一个元组,其Sno属性的值与之相等。

  3. 从Student表中删除一个元组,造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组,其Sno属性的值与之相等。

  4. 修改Student表中的一个元组的Sno属性,造成SC表中某些元组的Sno属性的值在表Student中找不到一个元组,其Sno属性的值与之相等。

当上述的不一致发生时,系统就可以采用以下的策略加以处理。

  1. 拒绝(NO ACTION)执行

  2. 级连(CASCADE)操作

  3. 设置为空值(SET-NULL)

当删除或修改被参照表的一个元组时造成了不一致,则将参照表中的所有造成不一致的元组的对应属性设置为空值。

例如,有下面2个关系

学生(学号,姓名,性别,专业号,年龄)
专业(专业号,专业名)

学生关系的“专业号”是外码,因为专业号是专业关系的主码。

假设专业表中某个元组被删除,专业号为12,按照设置为了空值策略,就要把学生表中专业号=12的所有元组的专业号设置为空值。这对应了这样的语义:某个专业删除了,该专业的所有学生专业未定,等待重新分配专业。因此对于参照完整性时,除了应该定义外码,还应定义外码列是否允许空值。

例 显式说明参照完整性的违约处理事例

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE SC
(Sno CHAR (9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno), /*在表级定义实体完整性*/
FOREIGN KEY(Sno)REFERENCES Student(Sno) /*在表级定义参照完整性*/
ON DELETE CASCADE /*当删除Student表中的元组时,级连删除SC表中相应的元组*/
ON UPDATE CASCADE, /*当更新Student表中的Sno时,级连更新SC表中相应的元组*/
FOREIGN KEY(Cno)REFERENCES Course(Cno) /*在表级定义参照完整性*/
ON DELETE NO ACTION /*当删除course表中的元组造成了与SC表不一致时拒绝删除*/
ON UPDATE CASCADE /*当更新course表中的cno时,级连更新SC表中相应的元组*/
);

可以对DELETE和UPDATE采用不同的策略,如上面删除被参照表Course表中的元组,造成了不一致时,拒绝删除被参照表的元组,而对更新操作则采取级联更新的策略。

从上面的讨论看到RSBMS在实现参照完整性时,除了要提供定义主码、外码的机制外,还需要提供不同的策略供用户选择。选择哪种策略,要根据应用环境的要求确定。

用户定义的完整性

①用户自定义约束条件
是针对某一具体应用的数据必须满足的语义要求。

  • RDBMS提供了定义和检验这类完整性的机制,而不必由应用程序承担。

② 用户自定义的约束条件检查和违约处理

一、 属性上的约束条件的定义

在CREATE TABLE中定义属性时,可以定义属性或元组上的约束条件,包括:

  • 列值非空(NOT NULL)
  • 列值唯一(UNIQUE)
  • 检查列值是否满足一个布尔表达式(CHECK)
  1. 不允许空值
    例 在定义SC表时,说明Sno和Cno属性不允许取空值
1
2
3
4
5
6
7
8
9
CREATE TABLE SC
( Sno CHAR(9) NOT NULL, /* Sno属性不允许取空值 */
Cno CHAR(4) NOT NULL, /* Cno属性不允许取空值 */
Grade SMALLINT NOT NULL, /*Grade属性不允许取空值*/
PRIMARY KEY (Sno, Cno), /* 如果在表级定义实体完整性,隐含了Sno,Cno不允许取空值,则在列级不允许取空值的定义就不必写了 * /
..
..
..
);
  1. 列值唯一
    例 建立部门表DEPT,要求部门名称Dname列取值唯一,部门编号Deptno列为主码。
1
2
3
4
5
6
CREATE TABLE DEPT
(Deptno NUMERIC(2),
Dname CHAR(9) UNIQUE, /*要求Dname列值唯一*/
Location CHAR(10),
PRIMARY KEY (Deptno)
);
  1. 用check短语指定列值应该满足的条件
    例 Student表的Ssex只允许取“男”或“女”
1
2
3
4
5
6
7
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /*在列级定义主码*/
Sname CHAR(8) NOT NULL, /*Sname属性不允许取空值*/
Ssex CHAR(2) CHECK(Ssex in (‘男’或‘女’)), /*性别属性Ssex只允许取男或女*/
Sage SMALLINT,
Sdept CHAR(20)
);

SC表的Grade的值应该在0到100之间。

1
2
3
4
5
6
7
8
CREATE TABLE SC
(Sno CHAR(9)NOT NULL,
Cno CHAR(4)NOT NULL,
Grade SMALLINT CHECK (Grade>=0 AND Grade <=100),
PRIMARY KEY (Sno,Cno),
FOREIGN (Sno) PEFERENCES Student(Sno),
FOREIGN (Cno)REFERENCES Course(Cno)
);

二、 属性上的约束条件检查和违约处理

在往表中插入元组或修改属性的值时,RDBMS会检查属性或元组上的约束条件是否被满足,如果不满足则操作被拒绝执行。

三、 元组上的约束条件的定义

与属性上约束条件的定义类似,在CREATE TABLE语句中可以用CHECK短语定义元组上的约束条件,即元组级的限制。同属性值限制相比,元组级的限制可以设置不同属性之间的取值的相互约束条件。

例 当学生的性别是男孩时,其名字不能以Ms.打头。

1
2
3
4
5
6
7
8
9
CREATE TABLE Student
(Sno CHAR(9),
Sname CHAR(8) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20),
PRIMARY KEY (Sno),
CHECK (Ssex= ‘女’ OR Sname NOT LIKE‘Ms.%’
);/*定义了元组中Sname和Ssex两个属性值之间的约束条件*/

四、元组上的约束条件检查和违约处理

当往表中插入元组或修改属性的值时,RDBMS就检查元组上的约束条件是否被满足,如果不满足则操作被拒绝执行。

完整性约束命名子句

  1. 完整性的约束命名子句

CONSTRAINT〈完整性约束条件名〉[PRIMARY KEY短语 | FOREIGN KEY短语 | CHECK短语]

例 建立学生登记表Student,要求学号在90000-99999之间,姓名不能取空值,年龄小于30,性别只能是“男”或“女”。

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Student
(Sno NUMERIC(6)
CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
Sname CHAR(20)
CONSTRAINT C2 NOT NULL,
Sage NUMERIC(3)
CONSTRAINT C3 CHECK (Sage<30),
Ssex CHAR (2)
CONSTRAINT C4 CHECK (Ssex IN (‘男’,‘女’)),
CONSTRAINT StudentKey PRIMARY KEY(Sno)
);

在Student表上建立了5个约束条件,包括主码约束(命名为StudentKey)以及C1、C2、C3、C4四个列级约束。

例 建立教师表TEACHER,要求每个教师的应发工资不低于3000元。应发工资实际上就是实发工资列Sal与扣除项Deduct之和。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE TEACHER
(Eno NUMERIC(4) PRIMARY KEY,
Ename CHAR(10),
Job CHAR(8),
Sal NUMERIC(7,2),
Deduct NUMERIC(7,2),
Deptno NUMERIC(2),
CONSTRAINT EMPFKey FOREIGN KEY (Deptno) REFERENCES DEPT(Deptno),
CONSTRAINT C1 CHECK (Sal + Deduct >=3000)
);
  1. 修改表中的完整性限制
    我们可以使用ALTER TABLE 语句修改表中的完整性限制。

例 去掉上面student表中对性别的限制。

1
2
 ALTER TABLE Student
DROP CONSTRAINT C4;

例 修改表Student中的约束条件,要求学号改为在900000-999999之间,年龄由小于30改为小于40。

可以先删除原来的约束条件,再增加新的约束条件。

1
2
3
4
5
6
7
8
ALTER TABLE Student
DROP CONSTRAINT C1;
ALTER TABLE Student
ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999),
ALTER TABLE Student
DROP CONSTRAINT C3;
ALTER TABLE Student
ADD CONSTRAINT C3 CHECK (Sage < 40)

域中的完整性限制

SQL支持域的概念,并可以用CREATE DOMAIN语句建立一个域应该满足的完整性约束条件

  1. 建立一个性别域,并声明性别域的取值范围
1
2
CREATE DOMAIN GenderDomain CHAR(2)
CHECK(VALUE IN(‘男’,’女’));
  1. 对Ssex的说明可以改写为
1
Ssex GenderDomain
  1. 建立一个性别域GenderDomain,并对其中的限制命名。
1
2
CREATE DOMAIN GenderDomain CHAR(2)
CONSTRAINT GD CHECK(VALUE IN(‘男’,’女’));
  1. 删除域GenderDomain的限制条件GD。
1
2
ALTER DOMAIN Genderdomain
DROP CONSTRAINT GD;
  1. 在域GenderDomain上增加限制条件GDD
1
2
ALTER DOMAIN Genderdomain
ADD CONSTRAINT GDD CHECK (VALUE IN(‘1’,’0’));

这样,通过上面两个例子,就把性别的取值范围由(‘男’,’女’) 改为(‘1’,’0’)。


触发器

用户定义在关系表上的一类由事件驱动的特殊过程,一旦定义,任何用户对表的增加、修改和删除工作均由服务器自动激活相应的触发器,在DBMS核心层进行集中的完整性控制。
触发器类似于约束,但是比约束更加灵活,可以进行更为复杂的检查和操作,具有更精细和更强大的数据控制能力。

定义触发器

一般格式 :

1
2
3
4
5
CREATE TRIGGER <触发器名>
{ BEFORE | AFTER } <触发事件> ON <表名>
FOR EACH { ROW | STATEMENT }
[ WHEN <触发事件>]
<触发动作体>

各部分语法进行详细说明:

  1. 表的拥有者即创建表的用户才可以在表上创建触发器,并且一个表上只能创建一定数量的触发器。

  2. 触发器名:触发器名可以包含模式名,也可以不包含模式名。同一模式下,触发器名必须是唯一的;并且触发器名和<表名>必须在同一模式下。

  3. 表名:当这个表的数据发生变化时,将激活定义在该表上相应<触发事件>的触发器,因此,该表也称为触发器的目标表。

  4. 触发事件:触发事件可以是INSERT、DELETE或UPDATE,也可以是这几个事件的组合,如INSERT OR DELETE等。UPDATE后面还可以有OF 〈触发列…….〉,即进一步修改那些列时触发器激活。

  5. 触发器类型:触发器按照所触发动作的间隔尺寸可以分为行级触发器(FOR EACH ROW)和语句级触发器(FOR EACH STATEMENT)。

    例如,假设在前面的的TEACHER表上创建了一个AFTER UPDATE触发器。如果表TEACHER有1000行,
    执行如下语句:UPDATE TEACHER SET Deptone =5;
    如果该触发器为语句级触发器,那么执行完语句后,触发动作只发生一次。
    如果是行动级触发器,触发动作将执行1000次。

  6. 触发条件:触发器被激活时,只有当触发条件为真时触发动作体才执行;否则触发动作体不执行。如果省略WHEN触发条件,则触发动作体在触发器激活后立即执行。

    触发动作体:触发动作体既可以是一个匿名PL/SQL过程块,也可以是对已创建存储过程的调用。如果是行级触发器,在两种情况下,用户都可以在过程体中使用NEW和OLD引用UPDATE/INSERT事件之后的新值和UPDATE/INSERT事件之前的旧值。如果是语句级触发器,则不能在触发动作体中使用NEW和OLD进行引用。如果触发动作体执行失败,激活触发器的时间就会中止执行,触发器的目标表或触发器可能影响的其他对象不发生任何变化。

例 定义一个BEFORE行级触发器,为教师表TEACHER定义完整性规则“接受的工资不得低于4000元,如果低于4000元,自动改为4000元”。

1
2
3
4
5
6
7
8
CREATE TRIGGER Insert_Or_Update_Sal    /*在教师表Teacher上定义触发器*/
BEFORE INSERT OR UPDATE ON Teacher /*触发事件是插入或更新操作*/
FOR EACH ROW /*这是行级触发器*/
AS BEGIN /*定义触发动作体,这是一个PL/SQL过程块*/
If (new.job=’教授’) AND (new.Sal<4000) THEN
New.Sal=4000; /*因为是行级触发器,可在过程体中使用插入或更新操作后的新值*/
END IF;
END; /*触发动作体结束*/

例 定义AFTER行级触发器,当教师表Teacher的工资发生变化以后就自动在工资变化表Sal_log中增加一条相应的记录。
首先建立工资变化表Sal_log.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE Sal_log
(Eno NUMERIC(4) referencr teacher(eno),
Sal NUMERIC(7,2),
Username char(10),
Date TIMESTAMP)
);
CREATE TRIGGER Insert_Sal /*建立了一个触发器*/
AFTER INSERT ON Teacher /*触发事件是INSERT*/
FOR EACH ROW
AS BEGIN
INSERT INTO Sal_log VALUES(
New.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);
END;
CREATE TRIGGER Update_Sal /*建立了一个触发器*/
AFTER UPDATE ON Teacher /*触发事件是UPDATE*/
FOR EACH ROW
AS BEGIN
IF(new.Sal<>old.Sal) THEN
INSERT INTO Sal_log
VALUES(New.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);
END IF;
END;

例 建一个avg_sc(Sno, avg_grade)表;
为SC表建一个触发器,当为sc表插入、删除、修改记录时,自动修改avg_sc表中的值。

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER set_avg_trg 
AFTER INSERT OR UPDATE ON SC FOR EACH ROW
AS BEGIN
UPDATE avg_sc
SET avg_grade = ( SELECT AVG(Grade) FROM SC
WHERE SC.Sno = avg_sc.Sno )
WHERE Sno in (SELECT Sno FROM inserted );
INSERT INTO avg_sc (Sno, avg_grade)
SELECT Sno, AVG(Grade) FROM SC
WHERE Sno in (SELECT Sno FROM inserted) AND
Sno not in (SELECT Sno FROM avg_sc)
GROUP BY Sno;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TRIGGER sc_delete_trg
AFTER DELETE ON SC FOR EACH ROW
AS BEGIN
UPDATE avg_sc
SET avg_grade =(SELECT AVG(Grade)
FROM SC
WHERE SC.Sno=avg_sc.sno )
WHERE sno in ( SELECT Sno FROM deleted );
DELETE FROM avg_sc
WHERE Sno in (SELECT Sno FROM deleted
WHERE NOT EXISTS
( SELECT * FROM SC
WHERE SC.Sno=deleted.Sno))
END;

激活触发器

触发器的执行,是由触发事件激活的,并由数据库服务器自动执行的。一个数据表上可能定义了多个触发器,比如多个BEFORE触发器,多个AFTER触发器等。同一个表上的多个触发器激活时遵循如下的执行顺序:

(1) 执行该表上的BEFORE触发器;

(2) 激活触发器的SQL语句;

(3) 执行该表上的AFTER触发器。

对于同一个表上的多个BEFORE(或AFTER)触发器而言,遵循“谁先创建谁先执行”的原则。有些RDBMS是按照触发器的字母排序顺序执行触发器的。

例 执行修改某个教师工资的SQL语句,激活上述定义的触发器。

1
UPDATE Teacher SET Sal = 800 WHERE Ename = ‘陈平’;

执行顺序是:

(1) 执行触发器Insert_Or_Update_Sal;

(2) 执行SQL语句“UPDATE Teacher SET Sal = 800 WHERE Ename = ‘陈平’;”

(3) 执行触发器Insert_Sal;

(4) 执行触发器Update_Sal。

删除触发器

SQL语法如下:

1
DROP TRIGGER <触发器名> ON <表名>;

例 删除教师表Teacher上的触发器Insert_Sal.

1
DROP TRIGGER Insert_Sal ON Teacher;

小结

数据库的完整性是为了保证数据库中存储的数据是正确的。

RDBMS完整性实现的机制

  • 完整性约束定义机制
  • 完整性检查机制
  • 违背完整性约束条件时RDBMS应采取的动作

实体完整性
参照完整性
用户定义的完整性
触发器

这样基础篇的内容就到这里了

Just for fun!
------------- 文章已经到尾 -------------