6.1 数据完整性、安全性、事务

6.1.1 事务及完整性约束

事务定义：

  • 由查询和更新语句的序列组成。SQL标准规定当一条SQL语句被执行，就隐式地开始了一个事务。

事务的性质：

  • 原子性
  • 一致性
  • 隔离性
  • 持久性

事务的结束：

  • Commit work:提交当前事务，也就是将该事务所做的更新在数据库中持久保存。在事务被提交后，一个新的事务自动开始。
  • Rollback work：回滚当前事务，即撤销该事务中所有SQL语句对数据库的更新。这样，数据库就恢复到执行该事务第一条语句之前的状态 。
  • 在很多SQL实现中，默认方式下每个SQL语句自成一个事务，且一执行完就提交，一般SQL能够关闭单独SQL语句自动提交功能。

示例：

  • 可以将需要同时成功或者同时失败的原子性操作划为一个事务
UPDATE account SET balance = balance - 100
WHERE account_number = 'A-101';
UPDATE account SET balance = balance + 100
WHERE account_number = 'A-201';
COMMIT WORK;

完整性约束：

  • 目的：保证授权用户对数据库所做的修改不会破坏数据的一致性。
  • 约束类型：域完整性、实体完整性（主键的约束）、参照完整性（外键的约束）、用户自定义完整性。
  • 数据库实例必须遵循完整性约束，由DBMS自动进行维护。

① 单个关系上的约束

  • not null
  • unique
  • check(<谓词>)

例1

create table instructor2
(
			ID char(5) primary key,
			name varchar(20) not null,		--姓名不能为空
			dept_name varchar(20),
			salary numeric(8,2) not null,	--薪水不能为空
			check (salary>=0);				--薪水不能为负值

② 域约束

  • 是完整性约束的最基本形式，可用于检测插入到数据库中的数据的合法性。

例1，

/*定义域约束*/
create domain Dollars as numeric(12,2) not null
create domain Pounds as numeric(12,2);

create table instructor
(
			ID char(5) primary key,
			name varchar(20),
			dept_name varchar(20),
			salary Dollars,					--域约束指定薪水不能为空
			comm Pounds
);

例2，使用check子句保证教师工资域中只允许出现大于给定值的值

/*check子句也可应用于域上*/
create domain YearlySalary numeric(8,2)					--保证年薪大于或等于29000.0
constraint salary_value_test check(value>29000.0);		--可选子句，系统用sal...test指定更新违反了哪个操作

例3，使用in子句限定一个域只包含指定的一组值

create domain degree_level varchar(10)
constraint degree_level_test
check (value in('Bachelors','Masters','Doctorate'));	--约束学历水平只能是‘学士’，‘硕士’，‘博士’中的一个。

参照完整性：

  • 外码的作用：促使系统自动做参照完整性检查。
  • 在参照关系中，外码的值必须在被参照关系中实际存在或为null

假设有如下关系：



sqlalchemy 回滚事务 sql事务回滚机制_元组


则有：

  • 插入：向参照关系中插入元组，必须保证元组的外码存在于被参照关系的主码集合中。
  • 删除：从被参照关系中删除元组时，必须检查在参照关系中是否有与被删除元组主码对应的外码，而后，要么进行级联删除，要么不允许操作。
  • 对参照关系更新，且修改了外码的值：进行与插入类似的检查，必须保证修改后的外码存在于被参照关系的主码集合中。
  • 对被参照关系更新，且修改了主码的值：进行与删除类似的检查，而后，要么进行级联更新，要么不允许操作。

主码、外码、候选码的指明：

  • 主码：primary key
  • 候选码：unique
  • 外码：foreign key

例，

/*三种声明外码的方法*/
foreign key (dept_name) references department		--外码：dept_name,被参照关系：department

dept_name varchar(20) references department

foreign key (dept_name) references department (dept_name)

SQL中的级联动作：

create table course(
...
foreign key(dept_name) references department
[on delete cascade]			--此可选子句表示：如果删除元组导致参照完整性被违反，则进行级联删除
[on update cascade]			--更新同上
  • 参照完整性只在事务结束时检查，中间步骤可以破坏参照完整性，只要后续步骤取消这种破坏即可。

6.1.2 断言及触发器

断言（assertion）:

  • 是表达要求数据库永远满足的条件的谓词（复杂check条件）。
  • 基本格式：create assertion <断言名> check <谓词>
  • 系统将对每一个可能破坏该断言的数据库更新进行检测，会产生大量开销，应当谨慎使用。

例1，对于student关系中的每个元组，它在属性tot_cred上的取值必须等于其所成功修完课程的学分总和

create assertion credits_earned_constraint check		--学分挣得约束
(not exists										--集合不为空，检测结果为false
		(select ID
		 from student
		 where tot_cred <>(						--总学分不等于学分总和，则向集合中添加元素
		 					select sum(credits)					--计算学分总和
		 					from takes natural join course		--自然内连接，同名字段相等
		 					where grade is not null				--此门课程学分不为空
		 					and grade <>'F')));					--此门课程未不及格

例2，每位教师不能在同一个学期的同一个时间段在两个不同的教室授课

/*将一个老师同一学期同一时间段进行group by分组求和，
 *将和大于1的元素挑选出来放入集合，利用not exists进行集合是否非空的判断
 */
create assertion ins_teaches_constraint check
not exists(
			select ID,name,section_id,semester,years,tim_slot_id,count(distinct building,room_number)
			from instructor natural join teaches natural jion section
			group by (ID,name,section_id,semester,years,time_slot_id)
			having count(building,room_number)>1);

触发器（trigger）:

  • 是由数据库更新操作引起的被系统自动执行的语句
  • 设计触发器必须指明触发器被执行的条件、指明触发器执行时所做的具体操作

触发事件包括：

  1. insert
  2. delete
  3. update

例1，使用触发器来确保参照关系section中属性time_slot_id的参照完整性，即在被参照关系time_slot中存在

create trigger timeslot_check1 after insert on section	--创建一个名为timeslot_check1的触发器，触发条件是向section中插入语句
referencing new row as nrow								--引用插入的新行，命别名为nrow
for each row											
when(
	nrow.time_slot_id not in(							--当新行的time_slot_id不在集合中
							select time_slot_id
							from time_slot))			--将time_slot中的所有time_slot_id取出构成一个集合
begin													--执行回滚操作
	rollback
end;

例2，使用触发器来确保在删除被参照关系中的元组时，time_slot_id的参照完整性，即在参照关系section中未被引用

create trigger timeslot_check2 after delete on time_slot	--对表time_slot执行删除时触发
referencing old row as orow								--引用“旧”行，即被删除的行，命别名为orow
for each row
when(
	orow.time_slot_id not in							--再确认一遍已删除行的time_slot_id已经不存在
						(select time_slot_id
						 from time_slot)
	and orow.time_slot_id in							--且在section中仍有time_slot_id的引用
						(select time_slot_id
						from section))
begin
	rollback
end;

例3，针对update的触发器可以指定具体修改的属性

/*针对grade的update触发器*/
create trigger takes_trigger after update of takes on grade

/*引用修改前的元组*/
refercing old row as orow

/*引用修改后的元组*/
refercing new row as nrow

语句级触发器：

  • 触发器分为行级触发器和语句级触发器
  • 语句级触发器可以针对受到一个事务影响的所有行只执行一次操作，这种触发器对更新大量元组的SQL语句更高效。

外部动作：

  • 有时要求数据库更新能触发外部动作，例如当某种物品库存量小到一定程度就发订货单，或者打开报警灯，触发器不能直接实现这些外部动作，但是可以在某个表中记录将采取的行动，让另一个外部进程不断扫描该表并执行相应的外部动作。

例1，假设仓库存在如下关系



sqlalchemy 回滚事务 sql事务回滚机制_sqlalchemy 回滚事务_02


create trigger reorder_trigger after update of level on inventory	--针对表inventory中level的更新触发器
referencing old row as orow,new row as nrow			--更新前的库存量和更新后的库存量
for each row
when nrow.level <=									--当更新后的库存量不大于报警线
				(select level
				 from minlevel
				 where minlevel.item=nrow.item)
and orow.level >									--且更新前的库存量未达到报警线
				(select level
				 from minlevel
				 where minlevel.item=orow.item)
begin
	insert into orders								--则执行添加订单操作
					(select item,amount
					from reorder
					where reorder.item=orow.item)
end

其他：

  1. 早期的触发器用于维护综合数据或复制数据库，即记录特定关系的变化并由一单独进程将此变化反应到所有副本。
  2. 现代有更好的做法，如利用物化视图、利用数据库内置复制工具。

6.1.3 数据安全性

安全性机制：

  1. 数据库系统级：验证和授权机制
  2. 操作系统级：操作系统级安全机制
  3. 网络级：加密放置偷听（未授权的读取信息）和伪装（冒充授权用户）
  4. 物理级：物理访问时传统的锁钥安全手段，物理损坏时的修复
  5. 人员级：用户存取权的保护意识

对数据的授权：

  1. 读授权：允许读，但不允许更新数据
  2. 插入权限：允许插入新数据，但不允许更新现有数据
  3. 修改权限：允许修改，但不允许删除数据
  4. 删除权限：允许删除数据

对表的授权：

  1. 索引权限：允许创建和删除索引
  2. 资源权限：允许创建新关系
  3. 修改权限：允许增加或删除关系的属性
  4. 删除权限：允许删除关系

权限的授予：

  • 用授权图表示权限的传递关系



sqlalchemy 回滚事务 sql事务回滚机制_计算机理论与基础_03


要求：

  • 授权图中的所有边必须是某条从数据库管理员出发的路径的一部分
  • 上图，若DBA从U1收回权限，则必须从U4收回权限，不能从U5收回权限，因为有另外的授权路径到达U5
  • 必须防止不经过根节点的循环授权

授权语法：

grant <权限列表>
on <关系名或视图名> to <用户列表>			--<用户列表>：用户ID、public(所有合法用户)、角色
/*应注意：
1. 授予对视图的权限并不意味着授予对定义该视图的基础关系的权限
2. 权限的授予者本身必须拥有相应的权限
*/

例1，授予用户U1、U2、U3对instructor关系的select权限

grant select on instructor to U1,U2,U3
/*
类似的：
insert:允许插入元组
update:允许修改元组
delete:允许删除元组
references:创建关系时允许声明外键
all privileges:所有权限
*/

例2，授予U1对instruct的select权限并允许U1将此权限授予其他用户

/*with grant option:允许用户把被授予的权限再转授给其他用户*/
grant select on instructor to U1 with grant option

角色：

  • 通过创建角色可以一次性对一类用户指定其共同的权限，可以对角色授予或收回权限，角色可被赋予给用户或其他角色。

例1

create role instructor;					--创建角色命名为instructor

grant select on takes to instructor		--授予角色instructor对表takes的select权限

grant dean to Amit;						--把角色dean赋予给特定用户Amit

create role dean;						--创建角色命名为dean

grant instructor to dean;				--将角色instructor赋予给角色dean

grant dean to Satoshi;					--将角色dean赋予给用户Satoshi

权限的回收：

revoke<权限列表> on <关系名或视图名>			--权限列表如果是all的话表示所有权限
from <用户列表> [restrict|cascade]			--restrict表示不进行级联回收，cascade表示进行级联回收

例，

revoke select on instructor from U1,U2,U3 cascade	--收回U1,U2,U3对表instructor的select权限并收回它们授予出去的select权限,即级联收回

授予的局限性：

  • SQL不支持元组级的授权，即只能对表授权，如需授权给个别元组，可以由应用程序来实现。
  • 授权代码在总代码中占比较大，维护非常困难。

审计跟踪（autid trail）：

  • 是关于应用程序数据的所有更改的日志，以及一些信息，即哪个用户什么时候执行了什么更改。
  • 用于跟踪安全漏洞或错误更新
  • 可以用触发器实现，但很多数据库都提供有内置的机制创建审计跟踪。

Oracle中的审计：
语句审计：

audit <语句类型> [by <用户>]		--语句类型：table、view、role...，用户缺省表示对所有用户审计
[by session | access]			--by session:相同类型的语句仅记录一次，access:记录所有操作
[whenever successful | whenever not successful]	--只有当成功或者不成功时才会被记录进日志

例，

/*审计用户scott每次成功执行的有关table的语句*/
audit table by scott			--对用户scott进行table类型操作的审计
by access						--记录所有语句的操作
whenever successful				--仅当成功时记录

/*取消审计*/
noaudit ...

对象（实体）审计：

  • 实体审计对所有的用户都起作用
audit <对象类型> on <指定的对象表、视图名> | default	--对象类型：insert、delete、update...		
[by session | by access]
[whenever successful|whenever not successful]

例，

/*审计所有用户对student表的delete和update操作*/
audit delete,update on student;

/*取消审计*/
noaudit

(完)