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

1、第4章 关 系 数 据 库 标准语言SQL 视图和授权控制视图和授权控制第第10讲讲 SQL的视图及授权控制的视图及授权控制SQL语言概述SQL中的数据定义SQL中的数据查询SQL中的数据更新SQL中的视图SQL中的授权控制本章内容第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的概念 视图的定义、删除视图的查询视图的更新视图的作用 4.5 SQL中的视图 第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的概念视图是一个命名了的视图是一个命名了的代数表达式代数表达式，是从一个或几个基，是从一个或几个基本表（或视图）本表（或视图）导出的（虚的）导出的（虚的）关系变量，即视图是关系

2、变量，即视图是一个虚表。在数据库中只存放视图的定义，而不存放一个虚表。在数据库中只存放视图的定义，而不存放视图对应的数据。视图的值是定义表达式计算后所得视图对应的数据。视图的值是定义表达式计算后所得到的结果。到的结果。视图为用户提供了一个观察底层数据的视图为用户提供了一个观察底层数据的窗口窗口。基本表。基本表发生变化后，对应视图也就随之改变。发生变化后，对应视图也就随之改变。用户能像操纵基本关系变量一样来操纵视图。对视图用户能像操纵基本关系变量一样来操纵视图。对视图的操作将由的操作将由DBMS转化为对相应基表的操作，任何对转化为对相应基表的操作，任何对视图的更新将自动和实时地在相应基表中所映射

3、的数视图的更新将自动和实时地在相应基表中所映射的数据上进行。据上进行。由用户定义，并为用户所使用。也可在视图之上再定由用户定义，并为用户所使用。也可在视图之上再定义视图。义视图。4.5 SQL中的视图 第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的定义 一般格式为：一般格式为：CREATE VIEW 视图名视图名（列名列名，列名，列名） AS 子查询子查询； WITH CHECK OPTION -组成视图的属性列名或者全部指定或者全部省略。在组成视图的属性列名或者全部指定或者全部省略。在SELECT子子句存在下述情形时，需要指明视图的属性列。句存在下述情形时，需要指明视图的属性列。

4、目标列中含有聚集函数或列表达式；目标列中含有聚集函数或列表达式；目标列中含对多表查询产生的同名属性列；目标列中含对多表查询产生的同名属性列；需要在视图中为某些列使用更合适的名字。需要在视图中为某些列使用更合适的名字。 4.5 SQL中的视图 第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的定义 一般格式为：一般格式为：CREATE VIEW 视图名视图名（列名（列名，列名，列名） AS 子查询子查询； WITH CHECK OPTION -子查询可以是任意复杂的子查询可以是任意复杂的SELECT语句，但通常不允许含有语句，但通常不允许含有ORDER BY子句和子句和DISTINCT选

5、项。选项。-WITH CHECK OPTION：表示对视图进行：表示对视图进行UPDATE和和INSERT操作时要保证修改和插入的元组须满足视图定义中的谓词条件。操作时要保证修改和插入的元组须满足视图定义中的谓词条件。-DBMS执行语句的结果只是把视图的定义存入数据字典中，并不执行语句的结果只是把视图的定义存入数据字典中，并不执行其中的子查询。执行其中的子查询。 4.5 SQL中的视图 第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的定义 4.5 SQL中的视图 【例例】建立数学系的学生视图。建立数学系的学生视图。 CREATE VIEW M-S（M-SNO，M-SN，M-SA） A

6、S SELECT SNO，SN，SA FROM S WHERE SD =数学数学；第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的定义 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(GRA

7、DE) FROM S_GRADE GROUP BY SNO第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的删除 一般格式为：一般格式为： DROP VIEW 视图名视图名CASCADE；CASCADE:把该视图和由它导出的所有视图一起删除把该视图和由它导出的所有视图一起删除4.5 SQL中的视图 DROP VIEW M-S；【例例】将前面建立的视图将前面建立的视图M-S删除。删除。【例例】将前面建立的视图将前面建立的视图S_GRADE 及其上的视图及其上的视图S_AVE删除。删除。 DROP VIEW S_GRADE CASCADE； 第第10讲讲 SQL的视图及授权控制的视图及授权

8、控制视图的查询用户对视图的查询就如同对基表一样，其操作将由用户对视图的查询就如同对基表一样，其操作将由DBMS转化为对相应基表的操作。转化为对相应基表的操作。4.5 SQL中的视图 SELECT SNO，SN，AVG(GRADE) FROM S-GRADE GROUP BY SNO; 【例例】查询学生平均成绩。查询学生平均成绩。 SELECT SNO，GAVEFROM S_AVE；第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的查询 DBMS 执行对视图的查询时，首先进行有效执行对视图的查询时，首先进行有效性检查，检查查询中涉及的基本表、视图等是性检查，检查查询中涉及的基本表、视图等

9、是否存在。否存在。 如果存在，则从数据字典中取出视图的定义，如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户查询结合起来，转换把定义中的子查询和用户查询结合起来，转换成等价的对基本表的查询，然后再执行相应的成等价的对基本表的查询，然后再执行相应的查询。查询。4.5 SQL中的视图 第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的查询 4.5 SQL中的视图 SELECT SNO，SN，AVG(GRADE) FROM S-GRADE GROUP BY SNO; 【例例】查询学生平均成绩。查询学生平均成绩。 DBMS根据数据字典中对视图根据数据字典中对视图S-GRADE的定

10、义，将其转换的定义，将其转换为等价的对基表的查询操作：为等价的对基表的查询操作： CREATE VIEW S-GRADE（SNO，SN，CNO，GRADE） AS SELECT S.SNO，SN，CNO，GRADE FROM S，SC WHERE S.SNO = SC.SNO；第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的查询 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 W

11、HERE S.SNO=SC.SNO GROUP BY SC.SNO ；DBMS根据数据字典中对视图根据数据字典中对视图S-GRADE的定义，将其转换的定义，将其转换为等价的对基表的查询操作：为等价的对基表的查询操作：第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的更新 通过视图来插入、删除和修改数据通过视图来插入、删除和修改数据. . 对视图的更新要转换为对基本表的更新操作。对视图的更新要转换为对基本表的更新操作。 若若视图定义中有视图定义中有WITH CHECK OPTION选项选项，进行更新操作时，进行更新操作时，DBMS会检查视图定义中会检查视图定义中的条件，若不满足条件，则拒

12、绝执行该操作。的条件，若不满足条件，则拒绝执行该操作。4.5 SQL中的视图 第第10讲讲 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 S

13、D=数学数学； 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 =数学系数学系；第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的更新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 =数学系数学系 WI

14、TH CHECK OPTION ；第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的更新 不是所有的视图都是可更新的，有些视图的更不是所有的视图都是可更新的，有些视图的更新不能唯一地有意义地转换成对相应基本表的新不能唯一地有意义地转换成对相应基本表的更新更新, ,就不能更新。就不能更新。 对视图的更新通常要加以限制，否则会出现错对视图的更新通常要加以限制，否则会出现错误，或产生语义上的问题。误，或产生语义上的问题。 4.5 SQL中的视图 【例例】 CREATE VIEW S-AVG (SNO，GAVE) AS SELECT SNO, AVE(GRADE) FROM SC GROUP

15、BY SNOUPDATE S-AVGSET GAVG=95.0WHERE SNO=S08； 第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的更新 对视图更新的一般限制有：对视图更新的一般限制有：由由多表导出多表导出的视图不允许更新。的视图不允许更新。若视图的属性列来自若视图的属性列来自表达式或常数表达式或常数，则不允许执行，则不允许执行INSERT和和UPDATE操作，但允许执行操作，但允许执行DELETE操操作。作。定义中用到定义中用到GROUP BY子句子句或或聚集函数聚集函数的视图不允的视图不允许更新。许更新。 建立在一个建立在一个不允许更新不允许更新的视图上的视图不允许更新的

16、视图上的视图不允许更新 一般都只允许对一般都只允许对行列子集视图行列子集视图（含有基表的主（含有基表的主键）进行更新。键）进行更新。4.5 SQL中的视图 第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的作用 视图提供了一个视图提供了一个简化简化用户操作的快捷方式。用户操作的快捷方式。可以更清晰地表达查询，简化用户的操作。可以更清晰地表达查询，简化用户的操作。 视图支持视图支持多用户同时多用户同时以不同的方式对相同的数以不同的方式对相同的数据进行查询。据进行查询。视图可以定制表的不同方面的结果集来满足来自不同用户的视图可以定制表的不同方面的结果集来满足来自不同用户的特殊需求，并可以使

17、这些用户同时与同一个数据库交互。特殊需求，并可以使这些用户同时与同一个数据库交互。 视图对于视图对于隐藏的隐藏的数据自动提供数据自动提供安全保护安全保护。 视图可以为用户和应用程序提供视图可以为用户和应用程序提供逻辑上的数据逻辑上的数据独立性独立性。可成长性和可成长性和可重构性可重构性。4.5 SQL中的视图 第第10讲讲 SQL的视图及授权控制的视图及授权控制视图的作用【例例】把学生关系把学生关系 S（SNO，SN，SD，SA）重构为两个表重构为两个表 SX（SNO，SN） SY（SNO，SD，SA） 4.5 SQL中的视图 此时原表此时原表S是是SX和和SY自然联接的结果，可以建立一个自然

18、联接的结果，可以建立一个视图视图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；第第10讲讲 SQL的视图及授权控制的视图及授权控制小结视图的概念视图的概念 视图的定义、删除视图的定义、删除视图的查询视图的查询视图的更新视图的更新视图的作用视图的作用 第第10讲讲 SQL的视图及授权控制的视图及授权控制SQL语言概述SQL中的数据定义SQL中的数据查询SQL中的数据更新SQL中的视图SQL中的授权控制本章

19、内容第第10讲讲 SQL的视图及授权控制的视图及授权控制安全系统的整体结构 用用户户访问监视器访问监视器审计审计访问控制访问控制身份认证身份认证安全管理员安全管理员授权数据库授权数据库资源资源审计员审计员 管理管理引言数据加密数据加密第第10讲讲 SQL的视图及授权控制的视图及授权控制存取控制的概念 存取权限授权与回收数据库角色4.6 SQL中的授权控制 第第10讲讲 SQL的视图及授权控制的视图及授权控制存取控制的概念 数据库安全性所关心的问题。数据库安全性所关心的问题。 确保只授权给有资格的用户访问数据库确保只授权给有资格的用户访问数据库的权限，同时令所有未被授权的人员无的权限，同时令所有

20、未被授权的人员无法获取数据。法获取数据。4.6 SQL中的授权控制 第第10讲讲 SQL的视图及授权控制的视图及授权控制存取控制的概念 数据库的存取控制机制包括数据库的存取控制机制包括定义用户权限，并将其登记在数据字典中。定义用户权限，并将其登记在数据字典中。 用户对某一数据对象的用户对某一数据对象的操作权力操作权力称为权限。称为权限。 DBMS提供适当的语言提供适当的语言定义用户权限定义用户权限授权规则授权规则。合法权限检查合法权限检查 当用户发出存取数据库的操作请求后，当用户发出存取数据库的操作请求后，DBMS查找查找数据字典，根据授权规则进行合法权限检查。数据字典，根据授权规则进行合法权

21、限检查。4.6 SQL中的授权控制 第第10讲讲 SQL的视图及授权控制的视图及授权控制存取权限用户的存取权限由两个要素组成用户的存取权限由两个要素组成数据库对象数据库对象数据库、基本表、表中记录、属性值，视图、索引等。数据库、基本表、表中记录、属性值，视图、索引等。操作类型（数据）操作类型（数据）SELECTINSERTDELETEUPDATEREFERENCEALL PRIVILEGES4.6 SQL中的授权控制 允许用户定义新关允许用户定义新关系时，引用其他关系时，引用其他关系的主键作为外键。系的主键作为外键。第第10讲讲 SQL的视图及授权控制的视图及授权控制存取权限 用户对不同的数据

22、库对象有不同的存取权限，用户对不同的数据库对象有不同的存取权限，不用的用户对同一数据对象也有不同的权限，不用的用户对同一数据对象也有不同的权限，用户还可将其拥有的存取权限转授给其他用户用户还可将其拥有的存取权限转授给其他用户。 通过通过SQL提供的提供的GRANT和和REVOKE语句定义语句定义用户权限，形成授权规则，并将其记录在数据用户权限，形成授权规则，并将其记录在数据字典中。字典中。4.6 SQL中的授权控制 第第10讲讲 SQL的视图及授权控制的视图及授权控制授权与回收 GRANT授权语句授权语句的一般格式的一般格式GRANT ON TO WITH GRANT OPTION ;4.6

23、SQL中的授权控制 SELECT,INSERT,UPDATE,DELETE, REFERENCES等等被授权的用户可以被授权的用户可以将这些权限继续转将这些权限继续转授给其他用户授给其他用户 GRANT SELECT， UPDATE (Sno） ON S TO U4 WITH GRANT OPTION; 【例例】把查询把查询S表和修改学生学号的权限授给用户表和修改学生学号的权限授给用户U4，并，并允许其将权限转授出去。允许其将权限转授出去。第第10讲讲 SQL的视图及授权控制的视图及授权控制授权与回收 REVOKE授权语句授权语句的一般格式的一般格式REVOKE GRANT OPTION FO

24、R ON FROM RESTRICT | CASCADE ;4.6 SQL中的授权控制 只有授权权限被收回只有授权权限被收回CASCADE：把该用户所转：把该用户所转授出去的权限同时收回；授出去的权限同时收回；RESTRICT：当不存在连：当不存在连锁收回时，才能收回权限锁收回时，才能收回权限，否则系统拒绝回收。，否则系统拒绝回收。 REVOKE SELECT，UPDATE (sno) ON S FROM U4 CASCADE；【例例】把用户把用户U4查询和修改学生学号的权限收回，并级联查询和修改学生学号的权限收回，并级联收回所授出的权限。收回所授出的权限。第第10讲讲 SQL的视图及授权控制

25、的视图及授权控制数据库角色 数据库角色是被命名的一组与数据库操数据库角色是被命名的一组与数据库操作相关的权限，角色是作相关的权限，角色是权限的集合权限的集合。 可为一组具有相同权限的用户创建一个可为一组具有相同权限的用户创建一个角色，使用角色来管理数据库权限可以角色，使用角色来管理数据库权限可以简化授权的过程。简化授权的过程。4.6 SQL中的授权控制 第第10讲讲 SQL的视图及授权控制的视图及授权控制数据库角色 TRANSACT SQL中角色的创建、授权中角色的创建、授权、权限、权限收回收回CREATE ROLE ；GRANT ON TO ; REVOKE ON FROM ；4.6 SQL中的授权控制 第第10讲讲 SQL的视图及授权控制的视图及授权控制数据库角色 TRANSACT SQL中角色的创建、授权、权限中角色的创建、授权、权限收回收回sp_addrolemember , ；sp_droprolemember , ； 4.6 SQL中的授权控制 用户所拥有的权限就用户所拥有的权限就是所承担的全部角色是所承担的全部角色所包含的权限的总和。所包含的权限的总和。第第10讲讲 SQL的视图及授权控制的视图及授