【数据库系统原理】第六章 SQL语言之数据完整性、安全性、事务

版权声明:本人学习所用,转载请标明出处,图片侵删 https://blog.csdn.net/qq_39582960/article/details/88086525

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

假设有如下关系:

则有:

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

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

  • 主码: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,假设仓库存在如下关系

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. 删除权限:允许删除关系

权限的授予:

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

要求:

  • 授权图中的所有边必须是某条从数据库管理员出发的路径的一部分
  • 上图,若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

(完)

展开阅读全文

没有更多推荐了,返回首页