第 章 SQLServer数据保护 ..5.1 数据保护 作为实用化最成功的软件系统之一,数据库系统如今已经成为全球化经济 基础设施的重要基础部件,对商务管理、事务管理、数据分析、电子商务、慕 课…… 来说,都是必不可少的,人类社会对数据的依赖达到了前所未有的地步, 数据安全关系到社会的每个组织单位及个人。数据安全建立在数据保密性、数 据完整性、数据可用性之上,数据库系统的特点之一就是由数据库管理系统提 供统一的机制保护数据的保密性、完整性、可用性。 数据保密性是指对数据资源的隐藏,通常在数据库中保护保密性就是指仅 允许经授权地读数据,需要对数据值保密和数据存在性保密。数据保密性的需 求源于数据的敏感性,如军事部门试图实现“需要知道”原则,企业公司对专利 设计数据的保护以免竞争对手获取设计成果等。数据存在性有时候比数据值 本身更需要保护,如确认美国政府曾监听某国政要远比监听到的数据本身更为 重要。 数据完整性指的是数据的可信度,保护数据完整性通常是指防止非法或者 未经授权的数据修改。数据完整性包括数据值的完整性和数据来源的完整性。 完整性遭到破坏即产生无效或损坏的数据,将导致用户由于错误的或无效的数 据做出错误的决策。 数据可用性是指对数据的期望使用能力,保护数据可用性通常指减少数据 库系统停工时间,保持数据持续可访问。可用性遭到破坏将影响用户正常获取 数据,意味着用户不能访问数据库或操作困难。 数据保护允许说明数据资源使用的安全策略并提供机制予以支持。策略 和机制不同。策略决定做什么,即是对允许什么、禁止什么的规定;机制决定怎 么做,即是实施策略的功能、方法、工具、规程。 本书专注于数据库系统层次上的安全保护,本章介绍SQLServer中提供的 与安全保护相关的语句,软件机制将在后续相应章节介绍。描述系统访问策略 的最简单模型是访问矩阵,矩阵的行是用户(角色), 列代表数据库对象,矩阵中 的元素表示相应用户对相应数据库对象的访问权限,访问控制矩阵通常依据用 户在系统应用中担任的角色确定,SQLServer中的授权和收权语句可以赋予或 第5章SQLServer数据保护109 撤销用户相应的访问权限,数据库管理系统确保只有获得授权的用户有资格访问数据库 对象,令所有未被授权的人员无法接近数据,保护数据保密性和完整性。视图作为外模式 的实现,支持“见之所需”原则,不仅简化了用户操作,而且结合访问控制可以保护视图数 据保密性和完整性,更重要的是提供了一种保护数据存在性的手段。SQLServer提供加 密函数,以允许对存储数据进行加密处理,支持保护数据保密性和完整性验证。数据库系 统都允许对数据库的并发访问以改进系统响应性能,提高可用性,但是如果对数据库访问 的并发执行不加控制将可能破坏数据完整性;另外,数据库系统总会面临这样那样的故 障,故障也可能破坏数据完整性。数据库管理系统通过将数据库操作分组为事务,以事务 为单位实施并发控制和故障恢复,SQLServer允许显式或隐式的事务边界定义。数据库 管理系统中的并发控制机制维护并发访问情况下的数据完整性;数据库管理系统中的故 障恢复机制不仅维护故障情况下的数据完整性,并且由于故障恢复机制对故障的有效处 理,它也是保护数据可用性的重要手段。 上述方法都是从数据值以外的因素考虑数据保护。理想情况下,数据值发生变化时, 比如进行插入或更新时,系统能够判断数据库中的各个数据项值是否与其对应的现实世 界状态一致,即数据是否真实正确。然而,这个目标是无法实现的。退而求其次,可以在 系统中定义一些正确数据应该满足的约束,系统自动检查数据库中的数据是否满足这些 约束条件,并且只允许满足这些约束条件的数据进入数据库。也就是说,软件系统无法保 证数据的真实正确性,但可以保证数据符合可明确定义的约束。这种约束通常称为完整 性约束。 总的来说,数据安全是数据库技术广泛使用的前提条件之一,也是数据库管理系统的 重要目标和优势之一。利用数据保护技术,保护数据安全是以数据库为中心的应用系统 必不可少的重要方面。当前的大数据管理技术,主要是利用多副本存储技术维护数据可 用性,关于专门针对大数据的保密性和完整性保护越来越受关注并有待进一步探索。 ..5.2 视图 视图主要有如下作用:视图能够简化用户的操作;视图使用户能从多种角度看待同 一数据;视图对重构数据库提供了一定程度的逻辑独立性;视图能够对数据存在性方面的 保密性提供安全保护;适当地利用视图可以更清晰地表达查询。 2.视图的创建和撤销 5.1 在SQLServer中,外模式一级数据结构的基本单位是视图,视图是从若干基本表和 (或)其他视图构造出来的“虚表”,采用SELECT 语句实现。在创建一个视图时,只是把 其视图的定义存放在数据字典中,而不存储视图对应的数据,在用户使用视图时才去查询 对应的数据。因此,视图被称为“虚表”。基表中的数据发生变化时,从视图中查询出的数 据也随之改变。 视图可以把基表结构细节封装起来,表可以随应用进化而变化,但视图以及基于视图 的应用程序不受表变化的影响。 1 10 数据库系统原理与实践 用户只能查询或修改通过视图所能见到的数据,看不见数据库其他数据;视图可以帮 助将授权限制到特定的行或/和列上。 1.视图的创建 创建视图可用“CREATEVIEW”语句实现。其句法如下。 CREATE VIEW [<所有者名>.]<视图名>[(<列名>[,<列名>]…)] AS <子查询> WITH CHECK OPTION; 组成视图的属性列名可以全部省略或全部指定,视图属性名省略时,取select结果关 系属性名。 [例5-1] 对于组卷系统数据库中的基本表eeexam,用户经常要用到考生考试号 eeid和平均成绩avgachieve这两列数据,那么可用下列语句建立视图。 CREARE VIEW avgachieve(eeid,average) AS SELECT eeid,AVG(achieve) FROM eeexam GROUP BY eeid; 此语句创建了视图,视图名为avgachieve,有两个属性(eeid和average),就好像有一 个表avgachieve有两个属性(eeid,average)。average属性的值就是对应eeid考生的平 均成绩。 也可以使用视图创建视图,例如,可以在examinee,eeexam,exampaper三个表的基 础上创建视图eeexamv1,语句如下。 CREATE VIEW eeexamv1 AS SELECT examinee.eeid, examinee.eedepa, exampaper.ename, eeexam.achieve FROM examinee, eeexam,exampaper WHERE examinee.eeid=eeexam.eeid AND eeexam.eeid=exampaper.eid; 这个语句创建视图eeexamv1,有四个属性:eeid,eedepa,ename,achieve,也就是考 生考号、考生院系名、试卷名和成绩。 然后,基于视图eeexamv1,可以进一步创建视图eeexamv2。语句如下。 CREATE VIEW eeexamv2 AS SELECT eeid,ename,achieve FROM eeexamv1 WHERE eedepa=‘历史学院’; 视图eeexamv2有三个属性:eeid,ename,achieve,也就是历史学院的考生考号、试卷 名和成绩。需要注意的是,虽然可以使用视图定义视图,但是视图定义不能递归。 第5章 SQLServer数据保护1 11 2.视图的撤销 在不需要视图时,可以用“DROPVIEW”语句把其从系统中撤销。其句法如下。 DROP VIEW <视图名> 该语句从数据字典中删除指定的视图定义,如果该视图上还导出了其他视图,使用 CASCADE级联删除语句,把该视图和由它导出的所有视图一起删除。删除基表时,由该 基表导出的所有视图定义都必须显式地使用DROPVIEW 语句删除。 [例5-2] 撤销avgachieve视图,可使用下列语句实现。 DROP VIEW avgachieve; 5.2.2 对视图的操作 1.对视图查询 创建视图后,就可以在SQL语句中使用,视图名能够出现在表名可以出现的任何地 方。比如已经定义了视图avgachieve(eeid,average),要查询平均86分以上的人数就可 以用这样的查询。 例如,可在avgachieve视图上进行如下查询。 SELECT count(*) FROM avgachieve WHERE avgachieve>=75; 同样也可以使用视图定义视图,首先使用examinee 表中的eeid、eedepa 属性, exampaper表中的ename属性和eeexam 表中的achieve属性定义视图eeexamv1,定义 eeexamv1使用如下语句实现。 CREARE VIEW eeexamv1 AS SELECT examinee.eeid, examinee.eedepa, exampaper.ename, eeexam.achieve FROM examinee,eeexam,exampaper WHERE examinee.eeid=eeexam.eeid AND eeexam.eid=exampaper.eid; 再使用视图eeexamv1定义由eeid、ename、achieve属性组成的视图eeexamv2,使用 如下语句实现。 CREARE VIEW eeexamv2 AS SELECT eeid,ename,achieve FROM eeexamv1 WHERE eedepa=‘历史学院’; 1 12 数据库系统原理与实践 SQLServer执行CREAREVIEW 语句时只是把视图定义存入数据字典,并不执行 其中的SELECT语句;在对视图查询时,按视图的定义从基本表中将数据查出。 虽然视图和表都是关系,都可在查询中直接使用,但是视图与表有一点重要不同:数 据库中存储表的模式定义和数据,而只存储视图的定义,不存储视图的数据,视图中的数 据是在使用视图时按照视图定义中的查询临时计算的。视图定义中引用的表称为基表。 当基表中的数据发生变化时,相应的视图数据也随之改变。 视图可以把基表结构细节封装起来,表可以随应用进化而变化,但视图以及基于视图 的应用程序可以尽可能少地受表变化的影响,这也就是数据的逻辑独立性。 通过视图,用户只能查询或修改视图中见得到的数据,看不见数据库中的其他数据, 这也就提供了数据存在性的保护。 2.视图上的修改 由于视图是基于对基表的查询来定义的,系统只存储视图的定义,不存储视图的数 据,对视图的修改通常是转换为基表的相应操作来执行,这就要求对视图的修改慎而又 慎。SQLServer只允许对可修改视图进行修改。 由于视图并不像基本表那样实际存在,有些视图的修改不能唯一地有意义地变换成 对相应基本表的修改,这些视图是不可修改的。 SQLServer只允许对可修改视图进行修改。可修改视图需要满足如下条件:视图 是从单个关系只使用投影、选择操作导出;SELECT子句中只包含属性名,不包含其他表 达式、聚集、DISTINCT声明,查询中不含有GROUPBY 或HAVING 子句;WHERE子 句子查询不出现基表名(其前面FROM 子句给出的表名);并且对视图的修改操作符合一 般修改语句的规则,如插入时主键不能为空,需要插入操作的视图的投影列需包含基础关 系的键。 [例5-3] 如果定义了一个有关男考生的视图: CREATE VIEW eemale AS SELECT eeid,eename,eeage FROM examinee WHERE eesex='男'; 视图eemale是考生表examinee中的男生考号、姓名和年龄的投影。由于这个视图 是从单个关系只使用选择和投影导出的,满足可修改视图需要满足的条件,是可修改的, 允许用户对视图进行插入、删除和更新操作。例如,由于包含主键eeid,可以执行插入 操作。 INSERT INTO eemale VALUES('201610012938','王涛',24); 系统会将对视图eemale的插入操作传递到基本表examinee上执行以下语句。 第5章 SQLServer数据保护1 13 INSERT INTO examinee(eeid,eename,eeage) VALUES(‘211610012938’,’王涛’, 24); 也等价于: INSERT INTO examinee VALUES(‘211610012938’,’王涛’, null,24,null); 如果在视图定义的末尾包含WITHCHECKOPTION,数据库管理系统自动检查对 视图的修改应满足视图定义中WHERE 的条件,这种情况下此插入操作便会被拒绝 执行。 .. 5.3 访问控制 SQLServer提供以下访问控制功能。 (1)使用GRANT 和REVOKE 语句实现授权/收权存入数据库管理系统的数据 字典。 (2)在角色提出操作请求时,按照授权状态进行检查,从而决定是否执行操作请求。 SQLServer权限管理机制允许对整个关系或一个关系的指定属性授予或收回权限。 通常不允许对特定元组的授权。 5.3.1 角色管理 SQLServer中角色是用来指定权限的一种数据库对象,每个数据库都有自己的角色 对象,可以为每个角色设置不同的权限。 SQLServer中角色分为服务器角色、数据库角色和应用程序角色三种,这里主要介 绍服务器角色和数据库角色。 服务器角色可以帮助用户管理服务器上的权限,其权限作用域为服务器范围。数据 库角色可以便于用户管理数据库上的权限,其权限作用域为数据库范围。 在SQLServer中,可以使用CREATESERVER ROLE 创建服务器角色,其句法 如下。 CREATE SERVER ROLE <角色名>[<角色的登录名>] [例5-4] 创建服务器角色yuxiaotong。 CREATE SERVER ROLE yuxiaotong; 数据库角色则可由CREATEROLE语句创建,其句法如下。 CREATE ROLE [<所有者名>.]<角色名> [例5-5] 创建数据库角色yanni。 1 14 数据库系统原理与实践 CREATE ROLE yanni; 还可以对角色进行如下一系列的操作。 . 修改服务器角色名yuxiaotong为newyuxiaotong: ALTER SERVER ROLE yuxiaotong WITH NAME =newyuxiaotong; . 修改数据库角色名yanni为newyanni: ALTER ROLE yanni WITH NAME =newyanni; . 向数据库角色yanni中添加角色成员masu: ALTER ROLE yanni ADD MEMBER masu; . 删除数据库角色yanni中的角色成员masu: ALTER ROLE yanni DROP MEMBER masu; . 删除服务器角色yuxiaotong: DROP SERVER ROLE yuxiaotong; . 删除服务器角色yanni: DROP ROLE yanni; 除了自己定义的角色外,SQLServer为方便用户使用,还提供了固定的服务器角色 和数据库角色。 固定服务器角色有以下9种。 .sysadmin:可以在SQLServer中执行任何活动。 .serveradmin:可以设置服务器范围的配置选项和关闭服务器。 .setupadmin:可以添加和删除连接服务器。 .securityadmin:可以管理登录及其属性。 . processadmin:可以结束在SQLServer实例中运行的进程。 . dbcreator:可以创建、修改、删除并恢复所有数据库。 . diskadmin:可以管理磁盘文件。 . bulkadmin:可以执行BULKINSERT语句,以用户指定的格式将数据文件加载 到数据表或视图中。 . public:拥有服务器中登录名所有默认权限。 固定数据库角色有以下10种。 . db_owner:可以执行数据库的所有配置和维护活动,还可以删除SQLServer中 的数据库。 第5章 SQLServer数据保护1 15 . db_securityadmin:可以修改自定义角色的角色成员资格和管理权限。 . db_accessadmin:可以为Windows登录名、Windows组和SQLServer登录名添 加或删除数据库访问权限。 . db_backupoperator:可以备份数据库。 . db_ddladmin:可以在数据库中运行任何数据定义语言(DDL)命令。 . db_datawriter:可以在所有用户表中添加、删除或更改数据。 . db_datareader:可以从所有用户表和视图中读取所有数据。 . db_denydatawriter:不能添加、修改或删除数据库内用户表中的任何数据。 . db_denydatareader:不能读取数据库内用户表和视图中的任何数据。 . public:拥有数据库中用户的所有默认权限。 5.3.2 授予权限 SQLServer提供GRANT语句来给角色授予数据库操作权限,GRANT语句的一般 格式为: GRANT <权限>[,<权限>]…[ON <对象名>]TO <角色>[,<角色>][WITH GRANT OPTION]; 这个语句可以赋予给定角色对给定对象的给定操作权限。当此GRANT 语句包含 WITH GRANTOPTION 选项时,在此语句获得权限的角色可以将所获得权限授予其他 角色。对 于不同的操作对象,有不同的操作权限。常见的操作权限如表5-1所示。 表5-1 表常见操作权限 对 象可以授予的权限 表、视图SELECT、INSERT、UPDATE、DELETE REFERENCES 表或视图的字段SELECT、UPDATE 存储过程和函数EXECUTE 接受权限的角色可以是一个或者多个角色,也可以是PUBLIC(全体用户)。 如果指定了WITH GRANT OPTION 子句,则获得某种权限的角色可以把这种权 限再授予其他角色,否则该角色只能使用所获得的权限,而不能将该权限传播给其他 角色。在 以下的例子中,假设在前述考试系统数据库中已创建了uZhang、uChang、uWang、 uYing、uLong和uLiang等角色。 [例5-6] 把查询组卷表erexam 权限授给角色uZhang。 GRANT SELECT ON erexam TO uZhang; 1 16 数据库系统原理与实践 [例5-7] 把对院系表department的查询权限授予所有用户。 GRANT SELECT ON department TO PUBLIC; [例5-8] 把查询department表和更新院系联系电话的权限授给角色uYing。 GRANT UPDATE(dtele), SELECT ON department TO uYing; 授予关于属性列权限时,应当指出相应的属性列名。 [例5-9] 把更新department表的权限授予uLong角色,并允许他再将此权限授予 其他角色。 GRANT UPDATE ON department TO uLong WITH GRANT OPTION; uLong不仅拥有了更新department表的权限,还可以传播此权限,即uLong角色使 用上述GRANT命令给其他角色授权。 [例5-10] uLong可以将此权限授予uLiang。 GRANT UPDATE ON department TO uLiang WITH GRANT OPTION; [例5-11] uLiang还可以将此权限授予uKuang。 GRANT UPDATE ON department TO uKuang; 但是由于uLiang未给uKuang传播的权限,因此uKuang不能再传播此权限。 5.3.3 收回权限 GRANT 语句和REVOKE 语句用于给角色授予或收回数据库操作权限。收回权限 的REVOKE语句一般格式为: REVOKE<权限>[,<权限>][ON<对象名>]FROM<角色>[,<角色>]; 第5章 SQLServer数据保护1 17 [例5-12] 把角色uYing更新院系联系电话的权限收回。 REVOKE UPDATE(dtele) ON department FROM uYing; [例5-13] 把角色uLong更新department表的权限收回。 REVOKE UPDATE ON department FROM uLong CASCADE; 将角色uLong更新department表的权限收回的时候必须级联(CASCADE)收回,即 系统将收回直接或间接从uLong处获得的权限。 SQLServer中的授权和收权语句可以赋予或撤销角色相应的访问权限,数据库管理 系统确保只有获得授权的角色有资格访问数据库对象,从而保护数据保密性和完整性。 .. 5.4 完整性约束 5.4.1 约束含义 假如DBMS足够智能,理想情况下,当数据库中任意数据项值发生变化时,比如执行 插入、删除或更新操作时,DBMS能够判断出数据项的新值是否与其对应的现实世界状 态一致,即数据是否真实正确。当然,这个目标是无法实现的。能做的是,在系统中定义 一些正确数据应该满足的约束条件,系统自动检查数据是否满足这些约束条件,并且只允 许满足这些约束条件的数据进入数据库。换句话说,DBMS无法保证数据始终与其对应 的现实世界状态一致,但可以保证数据始终与系统中明确定义的约束一致。这种约束通 常称为完整性约束。典型的完整性约束包括主键约束(也称为实体完整性)、外键约束(也 称为引用完整性)、值非空、值唯一等。典型的完整性约束包括主键约束、外键约束、值非 空、值唯一等,约束的一般形式就是一个任意谓词。 1.主键 主键约束意味着各元组主键值不能重复,且不能为空。将一个表的一个或几个属性 定义为主键后,插入或对主键列进行修改操作时,系统自动检查主键的各个属性是否为 空,只要有一个为空就拒绝插入或修改;并且检查主键值是否唯一,如果不唯一则拒绝插 入或修改。由于实际上删除操作不会导致违背主键约束,只有插入或对主键列进行修改 时才可能发生违背主键约束,因此,只有对关系进行插入或修改时系统才检验主键约束。 2.外键 外键约束意味着各元组外键值必须来自于引用表或为空。违背外键约束的那些元组