数据库原理与应用视图与授权 (2)

1、数据库原理与应用数据库原理与应用 南方医科大学生物医学工程学院信息技术系 视 图 视图的特点 虚表，是从一个或几个基本表（或视图）导出的 表 只存放视图的定义，不会出现数据冗余 基表中的数据发生变化，从视图中查询出的数据 也随之改变 视 图 1、建立视图 2、删除视图 3、查询视图 4、更新视图 1、建立视图 语句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION； 子查询不允许含有ORDER BY子句和DISTINCT 短语。 WITH CHECK OPTION表示对视图进行增删改 操作时，不得破坏视图定义中的谓词条件 （即子查询中的条件表达式）。 常见的视图形

2、式 行列子集视图 WITH CHECK OPTION的视图 基于多个基表的视图 基于视图的视图 带表达式的视图 分组视图 行列子集视图 从单个基本表导出，只是去掉了基本 表的某些行和某些列，而保留了码。 例1 建立销售部员工的视图。 CREATE VIEW Sales_Stuff AS SELECT Sno,Sname,Sage FROM Stuff WHERE Sdept= Sales; WITH CHECK OPTION的视图 例2 建立销售部员工的视图，并要 求透过该视图进行的更新操作只涉及 销售部员工。 CREATE VIEW Sales_Stuff AS SELECT Sno,Sna

3、me,Sage FROM Stuff WHERE Sdept= Sales WITH CHECK OPTION; 基于多个基表的视图 例3 建立销售部售卖了1号商品的员工视 图。 CREATE VIEW Sales_S1(Sno,Sname,Grade) AS SELECT Stuff.Sno,Sname,Grade FROM Student,SG WHERE Sdept= Sales AND Stuff.Sno=SG.Sno AND SG.Gno= 1; 基于视图的视图 例4 建立信息系售卖了1号商品且业 绩绩在90以上的员工的视图。 CREATE VIEW Sales_S2 AS SELE

4、CT Sno,Sname,Grade FROM Sales_S1 WHERE Grade=90； 带表达式的视图 定义视图时可以设置一些派生属性列, 也称 为虚拟列。 例5 定义一个反映员工出生年份的视图。 CREATE VIEW BT_S(Sno,Sname,Sbirth) AS SELECT Sno,Sname,2015-Sage FROM Stuff 带表达式的视图必须明确定义组成视图的 各个属性列名。 建立分组视图 可以用带有集函数和GROUP BY子句的查 询来定义视图。 例6 将员工的工号及他的平均业绩定义 为一个视图。（假设SG表中“成绩”列 Grade为数字型） CREATE

5、VIEW S_G(Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SG GROUP BY Sno; 视 图 1、建立视图 2、删除视图 3、查询视图 4、更新视图 5、视图的作用 2、删除视图 DROP VIEW ； 该语句从数据字典中删除指定的视图定义 由该视图导出的其他视图定义仍在数据字 典中，但已不能使用，必须显式删除 删除基表时，由该基表导出的所有视图定 义都必须显式删除 例9 删除视图Sales_S1。 DROP VIEW Sales_S1; 视 图 1、建立视图 2、删除视图 3、查询视图 4、更新视图 5、视图的作用 3、查询视图 从用户角度：查询

6、视图与查询基本表相同 DBMS实现视图查询的方法 视图实体化法（View Materialization） 有效性检查：检查所查询的视图是否存在 执行视图定义，将视图临时实体化，生成临时表 查询视图转换为查询临时表 查询完毕删除被实体化的视图(临时表) 查询视图 例1 在销售部员工的视图中找出年龄小 于30岁的员工。 SELECT Sno,Sage FROM Sales_Stuff WHERE Sage=90; S_G视图定义： CREATE VIEW S_G (Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SG GROUP BY Sno; 视 图 1、建立视

7、图 2、删除视图 3、查询视图 4、更新视图 5、视图的作用 4、更新视图 用户角度：更新视图与更新基本表相同 DBMS实现视图更新的方法 视图实体化法（View Materialization） 视图消解法（View Resolution） 指定WITH CHECK OPTION子句后 DBMS在更新视图时会进行检查，防止用户 通过视图对不属于视图范围内的基本表数据 进行更新 更新视图 例1 将销售部员工视图IS_Stuff中学号 95002的员工姓名改为“刘辰”。 UPDATE Sales_Stuff SET Sname= 刘辰 WHERE Sno= 95002; 转换后的语句： UPDA

8、TE Stuff SET Sname= 刘辰 WHERE Sno= 95002 AND Sdept= Sales; 更新视图 例2 向销售部员工视图Sales_Stuff中插 入一个新的员工记录：95029，赵新，25 岁 INSERT INTO Sales_Stuff VALUES(95029, 赵新,25); 转换为对基本表的更新： INSERT INTO Stuff(Sno,Sname,Sage,Sdept) VALUES(95029, 赵新,20,Sales ); 更新视图 例3 删除视图IS_Stuff中工号为95029的 记录。 DELETE FROM Sales_Stuff WH

9、ERE Sno= 95029; 转换为对基本表的更新： DELETE FROM Stuff WHERE Sno= 95029 AND Sdept= Sales; 更新视图的限制 一些视图是不可更新的，因为对这些视图 的更新不能唯一地有意义地转换成对相应 基本表的更新(对两类方法均如此) 例：视图S_G为不可更新视图。 CREATE VIEW S_G (Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SG GROUP BY Sno; 更新视图 对于如下更新语句： UPDATE S_G SET Gavg=90 WHERE Sno= 95001; 无论实体化法还是消解

10、法都无法将其 转换成对基本表SG的更新。 实际系统对视图更新的限制 允许对行列子集视图进行更新 对其他类型视图的更新，不同系统有不同限 制 课堂练习1 建立销售部售卖了编号为92001商品的员工 视图。 在销售部员工的视图中找出年龄大于30 岁的员工。 向销售部员工视图Sales_Stuff中插入一 个新的员工记录：95028，刘晨，27岁 授 权 将对指定操作对象的指定操作权限授予 指定的用户。 授 权 GRANT语句的一般格式： GRANT ,. ON TO ,. WITH GRANT OPTION; (1) 操作权限 对对象象 对对象象类类型型 操操 作作 权权 限限 属属性性列列 T

11、TA AB BL LE E S SE EL LE EC CT T, ,I IN NS SE ER RT T, ,U UP PD DA AT TE E， D DE EL LE ET TE E, ,A AL LL L R RI IV VI IE EG GE ES S 视视图图 T TA AB BL LE E S SE EL LE EC CT T, ,I IN NS SE ER RT T, ,U UP PD DA AT TE E， D DE EL LE ET TE E, ,A AL LL L R RI IV VI IE EG GE ES S 基基本本表表 T TA AB BL LE E S SE E

12、L LE EC CT T, ,I IN NS SE ER RT T, ,U UP PD DA AT TE E， D DE EL LE ET TE E A AL LT TE ER R, , I IN ND DE EX X, , A AL LL L P PR RI IV VI IE EG GE ES S 数数据据库库 D DA AT TA AB BA AS SE E C CR RE EA AT TE ET TA AB B (2) 用户的权限 建表（CREATETAB）的权限:属于DBA DBA授予-普通用户 基本表或视图的属主拥有对该表或视图的一切操 作权限 接受权限的用户: 一个或多个具体用户

13、PUBLIC（全体用户） (4) WITH GRANT OPTION子句 指定了WITH GRANT OPTION子句: 获得某种权限的用户还可以把这种权限再授予别 的用户。 未指定WITH GRANT OPTION子句: 获得某种权限的用户只能使用该权限，不能传播 该权限。 例题 例1 把查询Stuff表权限授给用户U1。 GRANT SELECT ON TABLE Stuff TO U1; SQL Server中取消了中取消了TABLE关键字。关键字。 例题 例2 把对Stuff表和Goods表的全部 权限授予用户U2和U3。 GRANT ALL PRIVILEGES ON TABLE S

14、tuff, Goods TO U2, U3; SQL Server中一条中一条GRANT语句只能对一张语句只能对一张 表或视图进行授权。表或视图进行授权。 例题 例3 把对表SG的查询权限授予所有用户。 GRANT SELECT ON TABLE SG TO PUBLIC; 例题 例4 把查询Stuff表和修改员工号的权限授给用 户U4。 GRANT UPDATE(Sno), SELECT ON TABLE Stuff TO U4; 例题 例5 把对表SG的INSERT权限授予U5用户，并允 许他再将此权限授予其他用户。 GRANT INSERT ON TABLE SG TO U5 WITH

15、GRANT OPTION; 收回权限 REVOKE语句的一般格式为： REVOKE ,. ON FROM ,.; 功能： 从指定用户那里收回对指定对象的指定权限 例题 例7 把用户U4修改员工号的权限收回。 REVOKE UPDATE(Sno) ON TABLE Stuff FROM U4; 例题 例8 收回所有用户对表SG的查询权限。 REVOKE SELECT ON TABLE SG FROM PUBLIC; 例题 例9 把用户U5对SG表的INSERT权限收回。 REVOKE INSERT ON TABLE SG FROM U5; SQL灵活的授权机制 DBA拥有对数据库中所有对象的所有

16、权限， 并可以根据应用的需要将不同的权限授予 不同的用户。 用户对自己建立的基本表和视图拥有全部 的操作权限，并且可以用GRANT语句把其 中某些权限授予其他用户。 被授权的用户如果有“继续授权”的许可， 还可以把获得的权限再授予其他用户。 所有授予出去的权力在必要时又都可以用 REVOKE语句收回。 课堂练习2 把查询Stuff表和修改员工号的权限授给用 户U4 把对表stuff的INSERT权限授予U5用户，并 允许他再将此权限授予其他用户 收回所有用户对表SG的查询权限 * 嵌 入 式 SQL SQL语言提供了两种不同的使用方式： 交互式 嵌入式 为什么要引入嵌入式SQL SQL语言是非过程性语言 事务处理应用需要高级语言 这两种方式细节上有差别，在程序设 计的环境下，SQL语句要做某些必要 的扩充 DBMS处理宿主型数据库语言SQL 的方法 修改和扩充主语言使之能处理SQL语句（少 用） 预编译（多用） 由DBMS的预处理程序对源程序进行扫描，识别 出SQL语句； 把它们转换成主语言调用语句，以使主语言编译 程序能识别它； 最后由主语言的编译程序将整个源程序编译成目 标码。 嵌入式SQL的预编译处理