第10讲SQL中的视图与授权控制.ppt

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第,10,讲,SQL,的视图及授权控制,第,4,章 关 系 数 据 库 标准语言,SQL,视图和授权控制,SQL,语言概述,SQL,中的数据定义,SQL,中的数据查询,SQL,中的数据更新,SQL,中的视图,SQL,中的授权控制,本章内容,视图的概念,视图的定义、删除,视图的查询,视图的更新,视图的作用,4.5 SQL,中的视图,视图的概念,视图是一个命名了的,代数表达式,，是从一个或几个基本表（或视图）,导出的（虚的）,关系变量，即视图是一个虚表。在数据库中只存放视图的定义，而不存放视图对应的数据。视图的值是定义表达式计算后所得到的结果。,视图为用户提供了一个观察底层数据的,窗口,。基本表发生变化后，对应视图也就随之改变。,用户能像操纵基本关系变量一样来操纵视图。对视图的操作将由,DBMS,转化为对相应基表的操作，任何对视图的更新将自动和实时地在相应基表中所映射的数据上进行。,由用户定义，并为用户所使用。也可在视图之上再定义视图。,4.5 SQL,中的视图,视图的定义,一般格式为：,CREATE VIEW,视图名,（,列名,，列名,）,AS,子查询,；,WITH CHECK OPTION,组成视图的属性列名或者全部指定或者全部省略。在,SELECT,子句存在下述情形时，需要指明视图的属性列。,目标列中含有聚集函数或列表达式；,目标列中含对多表查询产生的同名属性列；,需要在视图中为某些列使用更合适的名字。,4.5 SQL,中的视图,视图的定义,一般格式为：,CREATE VIEW,视图名,（列名,，列名,）,AS,子查询,；,WITH CHECK OPTION,子查询可以是任意复杂的,SELECT,语句，但通常不允许含有,ORDER BY,子句和,DISTINCT,选项。,WITH CHECK OPTION,：表示对视图进行,UPDATE,和,INSERT,操作时要保证修改和插入的元组须满足视图定义中的谓词条件。,DBMS,执行语句的结果只是把视图的定义存入数据字典中，并不执行其中的子查询。,4.5 SQL,中的视图,视图的定义,4.5 SQL,中的视图,【,例,】,建立数学系的学生视图。,CREATE VIEW M-S,（,M-SNO,，,M-SN,，,M-SA,）,AS SELECT SNO,，,SN,，,SA,FROM S,WHERE SD=,数学,；,视图的定义,4.5 SQL,中的视图,【,例,】,定义由学号及该学生的平均成绩构成的视图。,CREATE VIEW S-GRADE,（,SNO,，,SN,，,CNO,，,GRADE,）,AS SELECT S.SNO,，,SN,，,CNO,，,GRADE,FROM S,，,SC,WHERE S.SNO=SC.SNO,；,【,例,】,建立学生成绩视图。,CREATE VIEW S_AVE(SNO,，,GAVE),AS SELECT SNO,，,AVG(GRADE),FROM S_GRADE,GROUP BY SNO,视图的删除,一般格式为：,DROP VIEW,视图名,CASCADE,；,CASCADE:,把该视图和由它导出的所有视图一起删除,4.5 SQL,中的视图,DROP VIEW M-S,；,【,例,】,将前面建立的视图,M-S,删除。,【,例,】,将前面建立的视图,S_GRADE,及其上的视图,S_AVE,删除。,DROP VIEW S_GRADE CASCADE,；,视图的查询,用户对视图的查询就如同对基表一样，其操作将由,DBMS,转化为对相应基表的操作。,4.5 SQL,中的视图,SELECT SNO,，,SN,，,AVG(GRADE),FROM S-GRADE,GROUP BY SNO;,【,例,】,查询学生平均成绩。,SELECT SNO,，,GAVE,FROM S_AVE,；,视图的查询,DBMS,执行对视图的查询时，首先进行有效性检查，检查查询中涉及的基本表、视图等是否存在。,如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户查询结合起来，转换成等价的对基本表的查询，然后再执行相应的查询。,4.5 SQL,中的视图,视图的查询,4.5 SQL,中的视图,SELECT SNO,，,SN,，,AVG(GRADE),FROM S-GRADE,GROUP BY SNO,;,【,例,】,查询学生平均成绩。,DBMS,根据数据字典中对视图,S-GRADE,的定义，将其转换为等价的对基表的查询操作：,CREATE VIEW S-GRADE,（,SNO,，,SN,，,CNO,，,GRADE,）,AS SELECT S.SNO,，,SN,，,CNO,，,GRADE,FROM S,，,SC,WHERE,S.SNO=SC.SNO,；,视图的查询,4.5 SQL,中的视图,SELECT SNO,，,SN,，,AVG(GRADE),FROM S-GRADE,GROUP BY SNO;,【,例,4-41】,查询学生平均成绩。,SELECT S.SNO,，,SN,，,AVG(GRADE),FROM S,，,SC,WHERE,S.SNO=SC.SNO,GROUP BY SC.SNO,；,DBMS,根据数据字典中对视图,S-GRADE,的定义，将其转换为等价的对基表的查询操作：,视图的更新,通过视图来插入、删除和修改数据,.,对视图的更新要转换为对基本表的更新操作。,若,视图定义中有,WITH CHECK OPTION,选项，进行更新操作时，,DBMS,会检查视图定义中的条件，若不满足条件，则拒绝执行该操作。,4.5 SQL,中的视图,视图的更新,4.5 SQL,中的视图,【,例,】,INSERT,INTO M-S,VALUES,（,S20,，,黄海,，,1990-10-15,）；,UPDATE M-S,SET M-SN=,华婷,WHERE M-SNO=S16,；,DELETE,FROM M-S,WHERE SNO=S8,；,INSERT,INTO S,VALUES,（,S20,，黄海,，数学,，,1990-10-15,）；,UPDATE S,SET SN=,华婷,WHERE SNO=S16 AND SD=,数学；,DELETE,FROM S,WHERE SNO=S8 AND SD=,数学,；,CREATE VIEW M-S,（,M-SNO,，,M-SN,，,M-SA,）,AS SELECT SNO,，,SN,，,SA,FROM S,WHERE SD=,数学系；,视图的更新,4.5 SQL,中的视图,【,例,】,INSERT,INTO M-S,VALUES,（,S20,，,黄海,，,1990-10-15,）；,CREATE VIEW M-S,（,M-SNO,，,M-SN,，,M-SA,）,AS SELECT SNO,，,SN,，,SA,FROM S,WHERE SD=,数学系,WITH CHECK OPTION,；,视图的更新,不是所有的视图都是可更新的，有些视图的更新不能唯一地有意义地转换成对相应基本表的更新,就不能更新。,对视图的更新通常要加以限制，否则会出现错误，或产生语义上的问题。,4.5 SQL,中的视图,【,例,】,CREATE VIEW S-AVG(SNO,，,GAVE),AS SELECT SNO,AVE(GRADE),FROM SC,GROUP BY SNO,UPDATE S-AVG,SET GAVG=95.0,WHERE SNO=S08,；,视图的更新,对视图更新的一般限制有：,由,多表导出,的视图不允许更新。,若视图的属性列来自,表达式或常数,，则不允许执行,INSERT,和,UPDATE,操作，但允许执行,DELETE,操作。,定义中用到,GROUP BY,子句,或,聚集函数,的视图不允许更新。,建立在一个,不允许更新,的视图上的视图不允许更新,一般都只允许对,行列子集视图,（含有基表的主键）进行更新。,4.5 SQL,中的视图,视图的作用,视图提供了一个,简化,用户操作的快捷方式。,可以更清晰地表达查询，简化用户的操作。,视图支持,多用户同时,以不同的方式对相同的数据进行查询。,视图可以定制表的不同方面的结果集来满足来自不同用户的特殊需求，并可以使这些用户同时与同一个数据库交互。,视图对于,隐藏的,数据自动提供,安全保护,。,视图可以为用户和应用程序提供,逻辑上的数据独立性,。,可成长性和,可重构性,。,4.5 SQL,中的视图,视图的作用,【,例,】,把学生关系,S,（,SNO,，,SN,，,SD,，,SA,）,重构为两个表,SX,（,SNO,，,SN,）,SY,（,SNO,，,SD,，,SA,）,4.5 SQL,中的视图,此时原表,S,是,SX,和,SY,自然联接的结果，可以建立一个视图,S,，解决数据的逻辑独立性。,CREATE,VIEW S,（,SNO,，,SN,，,SD,，,SA,）,AS SELECT SX.SNO,，,SX.SN,，,SY.SD,，,SY.SA,FROM SX,，,SY,WHERE SX.SNO=SY.SNO,；,小结,视图的概念,视图的定义、删除,视图的查询,视图的更新,视图的作用,SQL,语言概述,SQL,中的数据定义,SQL,中的数据查询,SQL,中的数据更新,SQL,中的视图,SQL,中的授权控制,本章内容,安全系统的整体结构,用,户,访问监视器,审计,访问控制,身份认证,安全管理员,授权数据库,资源,审计员,管理,引言,数据加密,存取控制的概念,存取权限,授权与回收,数据库角色,4.6 SQL,中的授权控制,存取控制的概念,数据库安全性所关心的问题。,确保只授权给有资格的用户访问数据库的权限，同时令所有未被授权的人员无法获取数据。,4.6 SQL,中的授权控制,存取控制的概念,数据库的存取控制机制包括,定义用户权限，并将其登记在数据字典中。,用户对某一数据对象的,操作权力,称为权限。,DBMS,提供适当的语言,定义用户权限,授权规则。,合法权限检查,当用户发出存取数据库的操作请求后，,DBMS,查找数据字典，根据授权规则进行合法权限检查。,4.6 SQL,中的授权控制,存取权限,用户的存取权限由两个要素组成,数据库对象,数据库、基本表、表中记录、属性值，视图、索引等。,操作类型（数据）,SELECT,INSERT,DELETE,UPDATE,REFERENCE,ALL PRIVILEGES,4.6 SQL,中的授权控制,允许用户定义新关系时，引用其他关系的主键作为外键。,存取权限,用户对不同的数据库对象有不同的存取权限，不用的用户对同一数据对象也有不同的权限，用户还可将其拥有的存取权限转授给其他用户。,通过,SQL,提供的,GRANT,和,REVOKE,语句定义用户权限，形成授权规则，并将其记录在数据字典中。,4.6 SQL,中的授权控制,授权与回收,GRANT,授权语句,的一般格式,GRANT,ON,TO,WITH GRANT OPTION,;,4.6 SQL,中的授权控制,SELECT,INSERT,UPDATE,DELETE,REFERENCES,等,被授权的用户可以将这些权限继续转授给其他用户,GRANT SELECT,，,UPDATE(Sno,）,ON S,TO U4,WITH GRANT OPTION;,【,例,】,把查询,S,表和修改学生学号的权限授给用户,U4,，并允许其将权限转授出去。,授权与回收,REVOKE,授权语句,的一般格式,REVOKE,GRANT OPTION FOR,ON,FROM,RESTRICT|CASCADE,;,4.6 SQL,中的授权控制,只有授权权限被收回,CASCADE,：把该用户所转授出去的权限同时收回；,RESTRICT,：当不存在连锁收回时，才能收回权限，否则系统拒绝回收。,REVOKE SELECT,，,UPDATE(sno),ON S,FROM U4,CASCADE,；,【,例,】,把用户,U4,查询和修改学生学号的权限收回，并级联收回所授出的权限。,数据库角色,数据库角色是被命名的一组与数据库操作相关的权限，角色是,权限的集合,。,可为一组具有相同权限的用户创建一个角色，使用角色来管理数据库权限可以简化授权的过程。,4.6 SQL,中的授权控制,数据库角色,TRANSACT SQL,中角色的创建、授权,、权限收回,CREATE ROLE,；,GRANT,ON ,TO ;,REVOKE ,ON,FROM,；,4.6 SQL,中的授权控制,数据库角色,TRANSACT SQL,中角色的创建、授权、权限收回,sp_addrolemember ,；,sp_droprolemember ,；,4.6 SQL,中的授权控制,用户所拥有的权限就是所承担的全部角色所包含的权限的总和。,数据库角色,角色的创建、授权、权限收回,CREATE ROLE R1,；,GRANT SELECT,，,UPDATE,，,INSERT,ON TABLE S TO R1,；,GRANT DELETE ON S TO R1,；,REVOKE SELECT ON S FROM R1,；,Exec sp_addrolemember R1,王平；,Exec sp_droprolemember R1,王平；,4.6 SQL,中的授权控制,视图的权限控制,DBMS,可通过为不同的用户定义不同的视图，来隐藏针对某用户的敏感数据。并可进一步通过为用户授权来访问视图，提高隐藏数据的安全性。,4.6 SQL,中的授权控制,【,例,】,建立计算机系的学生视图。把对该视图的,SELECT,权限授予王平，把该视图上的所有操作权限授予张明。,CREATE VIEW CS-S,AS SELECT *,FROM S,WHERE SD=,计算机；,GRANT SELECT ON CS-S TO,王平；,GRANT ALL PRIVILEGES ON CS-S TO,张明；,小结,GRANT,语句,REVOKE,语句,CREATE ROLE,小结,SQL,语言概述,SQL,中的数据定义,SQL,中的数据查询,SQL,中的数据更新,SQL,中的视图,SQL,中的授权控制,作业,一、二、三（,6-10,）,四（,2.12-2.18,）,