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

第4章关系数据库标准语言SQL——视图和授权控制SQL语言概述SQL中的数据定义SQL中的数据查询SQL中的数据更新SQL中的视图SQL中的授权控制本章内容视图的概念视图的定义、删除视图的查询视图的更新视图的作用4.5SQL中的视图视图的概念视图是一个命名了的代数表达式，是从一个或几个基本表（或视图）导出的（虚的）关系变量，即视图是一个虚表。在数据库中只存放视图的定义，而不存放视图对应的数据。视图的值是定义表达式计算后所得到的结果。视图为用户提供了一个观察底层数据的窗口。基本表发生变化后，对应视图也就随之改变。用户能像操纵基本关系变量一样来操纵视图。对视图的操作将由DBMS转化为对相应基表的操作，任何对视图的更新将自动和实时地在相应基表中所映射的数据上进行。由用户定义，并为用户所使用。也可在视图之上再定义视图。4.5SQL中的视图视图的定义

一般格式为：CREATEVIEW视图名[（列名[，列名]…）]AS子查询；

[WITHCHECKOPTION]组成视图的属性列名或者全部指定或者全部省略。在SELECT子句存在下述情形时，需要指明视图的属性列。目标列中含有聚集函数或列表达式；目标列中含对多表查询产生的同名属性列；需要在视图中为某些列使用更合适的名字。

4.5SQL中的视图视图的定义

一般格式为：CREATEVIEW视图名[（列名[，列名]…）]AS子查询；

[WITHCHECKOPTION]子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDERBY子句和DISTINCT选项。WITHCHECKOPTION：表示对视图进行UPDATE和INSERT操作时要保证修改和插入的元组须满足视图定义中的谓词条件。DBMS执行语句的结果只是把视图的定义存入数据字典中，并不执行其中的子查询。

4.5SQL中的视图视图的定义

4.5SQL中的视图【例】建立数学系的学生视图。

CREATEVIEWM-S（M-SNO，M-SN，M-SA）

ASSELECTSNO，SN，SAFROMSWHERESD=‘数学’；视图的定义

4.5SQL中的视图【例】定义由学号及该学生的平均成绩构成的视图。

CREATEVIEWS-GRADE（SNO，SN，CNO，GRADE）

ASSELECTS.SNO，SN，CNO，GRADEFROMS，SCWHERES.SNO=SC.SNO；【例】建立学生成绩视图。CREATEVIEWS_AVE(SNO，GAVE)ASSELECTSNO，AVG(GRADE)FROMS_GRADEGROUPBYSNO视图的删除一般格式为：

DROPVIEW视图名[CASCADE]；CASCADE:把该视图和由它导出的所有视图一起删除4.5SQL中的视图

DROPVIEWM-S；【例】将前面建立的视图M-S删除。【例】将前面建立的视图S_GRADE及其上的视图S_AVE删除。

DROPVIEWS_GRADECASCADE；

视图的查询用户对视图的查询就如同对基表一样，其操作将由DBMS转化为对相应基表的操作。4.5SQL中的视图

SELECTSNO，SN，AVG(GRADE)FROMS-GRADEGROUPBYSNO;【例】查询学生平均成绩。

SELECTSNO，GAVEFROMS_AVE；视图的查询DBMS执行对视图的查询时，首先进行有效性检查，检查查询中涉及的基本表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户查询结合起来，转换成等价的对基本表的查询，然后再执行相应的查询。4.5SQL中的视图视图的查询4.5SQL中的视图SELECTSNO，SN，AVG(GRADE)FROMS-GRADE

GROUPBYSNO;

【例】查询学生平均成绩。

DBMS根据数据字典中对视图S-GRADE的定义，将其转换为等价的对基表的查询操作：

CREATEVIEWS-GRADE（SNO，SN，CNO，GRADE）

ASSELECTS.SNO，SN，CNO，GRADEFROMS，SCWHERES.SNO=SC.SNO；视图的查询4.5SQL中的视图SELECTSNO，SN，AVG(GRADE)FROMS-GRADEGROUPBYSNO;

【例4-41】查询学生平均成绩。

SELECTS.SNO，SN，AVG(GRADE)FROMS，SCWHERES.SNO=SC.SNO

GROUPBYSC.SNO

；DBMS根据数据字典中对视图S-GRADE的定义，将其转换为等价的对基表的查询操作：视图的更新通过视图来插入、删除和修改数据.对视图的更新要转换为对基本表的更新操作。若视图定义中有WITHCHECKOPTION选项，进行更新操作时，DBMS会检查视图定义中的条件，若不满足条件，则拒绝执行该操作。4.5SQL中的视图视图的更新4.5SQL中的视图【例】INSERTINTOM-SVALUES

（’S20’，‘黄海’，1990-10-15）；UPDATEM-SSETM-SN=‘华婷’

WHEREM-SNO=‘S16’；

DELETEFROMM-SWHERESNO=‘S8’；

INSERTINTOSVALUES（‘S20’，‘黄海’，‘数学’，

1990-10-15

）；

UPDATESSETSN=‘华婷’

WHERESNO=‘S16’ANDSD=‘数学’；

DELETEFROMSWHERESNO=‘S8’ANDSD=‘数学’；

CREATEVIEWM-S（M-SNO，M-SN，M-SA）

ASSELECTSNO，SN，SAFROMSWHERESD=‘数学系’；视图的更新4.5SQL中的视图【例】INSERTINTOM-SVALUES

（’S20’，‘黄海’，1990-10-15）；

CREATEVIEWM-S（M-SNO，M-SN，M-SA）

ASSELECTSNO，SN，SAFROMSWHERESD=‘数学系’

WITHCHECKOPTION；视图的更新不是所有的视图都是可更新的，有些视图的更新不能唯一地有意义地转换成对相应基本表的更新,就不能更新。对视图的更新通常要加以限制，否则会出现错误，或产生语义上的问题。

4.5SQL中的视图【例】

CREATEVIEWS-AVG(SNO，GAVE)ASSELECTSNO,AVE(GRADE)FROMSC

GROUPBYSNOUPDATES-AVGSETGAVG=95.0WHERESNO=‘S08’；

视图的更新对视图更新的一般限制有：由多表导出的视图不允许更新。若视图的属性列来自表达式或常数，则不允许执行INSERT和UPDATE操作，但允许执行DELETE操作。定义中用到GROUPBY子句或聚集函数的视图不允许更新。建立在一个不允许更新的视图上的视图不允许更新一般都只允许对行列子集视图（含有基表的主键）进行更新。4.5SQL中的视图视图的作用视图提供了一个简化用户操作的快捷方式。可以更清晰地表达查询，简化用户的操作。视图支持多用户同时以不同的方式对相同的数据进行查询。视图可以定制表的不同方面的结果集来满足来自不同用户的特殊需求，并可以使这些用户同时与同一个数据库交互。

视图对于隐藏的数据自动提供安全保护。视图可以为用户和应用程序提供逻辑上的数据独立性。可成长性和可重构性。4.5SQL中的视图视图的作用【例】把学生关系

S（SNO，SN，SD，SA）重构为两个表

SX（SNO，SN）

SY（SNO，SD，SA）

4.5SQL中的视图此时原表S是SX和SY自然联接的结果，可以建立一个视图S，解决数据的逻辑独立性。CREATEVIEWS（SNO，SN，SD，SA）

ASSELECTSX.SNO，SX.SN，SY.SD，SY.SAFROMSX，SYWHERESX.SNO=SY.SNO；小结视图的概念视图的定义、删除视图的查询视图的更新视图的作用SQL语言概述SQL中的数据定义SQL中的数据查询SQL中的数据更新SQL中的视图SQL中的授权控制本章内容安全系统的整体结构

用户访问监视器审计访问控制身份认证安全管理员授权数据库资源审计员管理引言数据加密存取控制的概念存取权限授权与回收数据库角色4.6SQL中的授权控制存取控制的概念数据库安全性所关心的问题。确保只授权给有资格的用户访问数据库的权限，同时令所有未被授权的人员无法获取数据。4.6SQL中的授权控制

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

存取权限用户的存取权限由两个要素组成数据库对象数据库、基本表、表中记录、属性值，视图、索引等。操作类型（数据）SELECTINSERTDELETEUPDATEREFERENCEALLPRIVILEGES4.6SQL中的授权控制

允许用户定义新关系时，引用其他关系的主键作为外键。存取权限用户对不同的数据库对象有不同的存取权限，不用的用户对同一数据对象也有不同的权限，用户还可将其拥有的存取权限转授给其他用户。通过SQL提供的GRANT和REVOKE语句定义用户权限，形成授权规则，并将其记录在数据字典中。4.6SQL中的授权控制

授权与回收GRANT授权语句的一般格式GRANT<权限表>ON<数据库对象>TO<用户表>[WITHGRANTOPTION];4.6SQL中的授权控制

SELECT,INSERT,UPDATE,DELETE,REFERENCES等被授权的用户可以将这些权限继续转授给其他用户

GRANTSELECT，UPDATE(Sno）ONSTOU4WITHGRANTOPTION;【例】把查询S表和修改学生学号的权限授给用户U4，并允许其将权限转授出去。授权与回收REVOKE授权语句的一般格式REVOKE[GRANTOPTIONFOR]<权限表>ON<数据库对象>FROM<用户表>[RESTRICT|CASCADE]

;4.6SQL中的授权控制

只有授权权限被收回CASCADE：把该用户所转授出去的权限同时收回；RESTRICT：当不存在连锁收回时，才能收回权限，否则系统拒绝回收。

REVOKESELECT，UPDATE(sno)ONSFROMU4CASCADE；【例】把用户U4查询和修改学生学号的权限收回，并级联收回所授出的权限。数据库角色数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合。可为一组具有相同权限的用户创建一个角色，使用角色来管理数据库权限可以简化授权的过程。4.6SQL中的授权控制

数据库角色TRANSACTSQL中角色的创建、授权、权限收回CREATEROLE<角色名列表>；GRANT<权限列表>ON<数据库对象列表>TO<角色名列表>;REVOKE<权限列表>ON<数据库对象列表>FROM<角色名列表>；4.6SQL中的授权控制

数据库角色TRANSACTSQL中角色的创建、授权、权限收回sp_addrolemember<