数据库概论实验指导书(4)

FROM IS_S1_88 WHERE Grade>=92;

思考：如何查询信息系选修了1号课程且成绩在85分以上的学生的学号、姓名、性别、年龄和成绩？

练习6：在S_G视图（参见练习3）中查询平均成绩在88分以上的学生学号和平均成绩。

SELECT * FROM S_G

WHERE Gavg>=88;

思考：对于此视图查询语句，SQL Server2005能否正确完成视图消解？试根据实际执行结果的正确与否加以判断，并写出视图消解转换后的SQL语句。（参考教材p122-123）

（3）更新视图

练习7：向信息系学生视图IS_Student中插入一个新的学生元组，其中学号为200215185，姓名为周捷轮，性别为男，年龄为23岁。

INSERT

INTO IS_Student

VALUES('200215185','周捷轮','男',23); 思考：执行此语句会出现什么问题？如何修改？ 试试：

①首先，使用如下语句新建一个信息系学生视图IS_Student2：

CREATE VIEW IS_Student2 AS

SELECT Sno,Sname,Ssex,Sage FROM Student

WHERE isnull(Sdept,'IS')= 'IS ' /*判断Sdept是否为空，若为空则用'IS'代替 */ WITH CHECK OPTION;

思考：与练习1中建立的IS_Student视图有何区别？

②然后，执行如下SQL语句，对视图进行插入操作，观察视图和基本表中的结果是否正确？

INSERT

INTO IS_Student2

VALUES('200215185','周捷轮','男',23);

练习8：将信息系学生视图IS_Student中学号为200215132的学生姓名修改为樊虹宇。

UPDATE IS_Student SET Sname='樊虹宇' WHERE Sno='200215132';

观察：视图IS_Student、IS_Student2以及基本表Student中的数据的变化情况。

练习9：删除信息系学生视图IS_Student中学号为200215135的记录。

DELETE

FROM IS_Student

WHERE Sno='200215135';

观察：视图IS_Student、IS_Student2以及基本表Student中的数据的变化情况。

（4）删除视图（即删除视图的定义）

练习8：删除视图S_G和视图IS_Student2。

DROP VIEW S_G;

DROP VIEW IS_Student2;

思考：如何删除视图IS_Student及其导出的其他视图？（提示：SQL Server的DROP VIEW 语句中不支持CASCADE）

扩展练习

首先执行S_T.sql，重建示例数据库S_T。

然后写出并执行SQL语句完成以下各种操作，记录查询结果。

创建计算机系学生的视图CS_Student，包括学号、姓名、年龄、性别、系别等属性列。

CREATE VIEWCS_Student AS

SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student WHERE Sdept='CS' WITH CHECK OPTION;

通过视图CS_Student查询计算机系男生的详细信息。

通过视图CS_Student将计算机系的一个新生元组（学号为200215120，姓名为龙语嫣，性别为女，年龄17岁）插入表中。

INSERT

INTO CS_Student

VALUES('200215120','龙语嫣','女',17,'CS');

如果换成如下语句：（系别改为信息系） INSERT

INTO CS_Student

VALUES('200215120','龙语嫣','女',17,'IS'); 会出现什么问题？

通过视图CS_Student将计算机系某个男生（学号为200215121）的年龄修改为19岁。 将信息系所有女生的学号及她的平均成绩定义为一个视图S_F_G。

通过视图S_F_G查询信息系所有女生中平均成绩在85-95之间的学号及平均成绩。 （7）删除视图IS_Student和视图S_F_G。 创建表的视图

例如：建立信息系学生的视图。 CREATE VIEW IS_Student AS

SELECT Sno，Sname，Sage FROM Student WHERE Sdept= 'IS' 利用视图完成表的查询

例如：查询信息系选修了1号课程的学生

SELECT Sno，Sname

FROM IS_Student，SC

WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1' 删除表的视图

例如：DROP VIEW IS_Student

实验五SQL的数据控制（2学时）

1、实验目的

(1) 理解SQL Server 2005数据库的安全性相关的概念。 (2) 掌握SQL Server 2005的验证模式、登录管理、用户管理、角色管理、权限管理等

操作。

2、实验内容

(1) SQL Server的安全认证模式 (2) 管理数据库用户 (3) 管理数据库角色 (4) 权限管理

实验要求

(1) 掌握SQL Server的安全认证模式的概念及设置方法 (2) 掌握使用SSMS和T-SQL两种方式进行SQL Server登录账号、数据库用户、数据库

角色的添加、修改、删除等操作，以及对数据库用户、角色进行权限授予和回收等操作。

(3) 理解架构(Schema)、用户(User)、角色(Role)和登录(Login)等基本概念及其关系。 (4) 认真记录实验过程，完成实验报告。

相关知识

4.1 数据库安全性控制基础

用户标识与鉴别

通过用户名和口令来鉴别用户。只有在DBMS成功注册了的人员才是该数据库的用户(拥有属于自己的惟一的标识符)，才能访问数据库。任何数据库用户要访问数据库时，都须声明自己的用户标识符。由系统检查该用户标识符的用户是否存在，如果存在还要进一步鉴别该声明者是否确实是具有此用户标识符的那个用户。只有通过鉴别的人才能进入系统。鉴别的方法多种多样，口令是最广泛使用的用户鉴别方法。

存取控制

由DBMS授权给有资格的用户访问数据库的权限。存取控制机制有两部分组成：①定义用户权限，并将用户权限登记到数据字典中；②合法性检查，当用户提出操作请求时，DBMS查找数据字典，进行合法性检查，如果用户的操作请求超出了所定义的权限，系统将拒绝执行此操作。最常使用的存取控制方法是自主存取控制（DAC），能够通过授权机制有效地控制其他用户对敏感数据的存取，而且用户对数据的存取权限是“自主”的，用户可以自由地将数据的存取权限授予他人、决定是否也将“授权”的权限再授予别人，而系统对此无法控制。

自主存取控制主要通过SQL的GRANT和REVOKE语句来实现。另一种存取控制方法是强制存取控制（MAC），主要用于高安全级别的场合，如军事领域。

视图机制

通过视图机制，可以为不同的用户定义不同的视图，使得要保密的数据对无权存取的用户隐藏起来，从而在一定程序上达到对数据的安全保护的目的。

审计

通过审计可以把用户对数据库的所有操作自动记录下来放入审计日记中，DBA可以利用审计跟踪的信息，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容。审计通常是很费时间和空间的，一般用于安全性较高的部门，而且可由DBA决定是否采用、何时采用。

数据加密

通过对敏感数据进行加密存储能有效地防止数据泄密。通常由用户决定是否选用数据加密功能。

4.2 SQL Server 2005实现数据库安全性

（1）一台计算机上可以安装多个SQL Server，使得不同的应用程序可以拥有自己的SQL Server。SQL Server的每一个安装称为一个实例（instance），每个实例必须有属于自己的唯一的名字。

（2）在SQL Server中，有很多种方法来保证安全性。可以通过Windows验证来让Windows自己设置安全性；可以通过视图来限制用户对敏感数据的访问；还可以它通过特定的创建登录名、用户、角色以及授权来明确访问的许可权限。

（3）登录帐号与身份验证。如果用户准备建立与SQL Server的连接，就必须拥有相应的登录帐号。SQL Server提供两种身份验证模式：一种是Windows身份验证模式，由SQL Server系统管理员将Windows帐号或Windows组定义为合法的SQL Server登录帐号，只进行Windows身份验证，用户不必提供SQL Server登录帐号的用户名和密码。另一种是混合模式，如果用户准备建立与SQL Server的连接，那么既可以使用Windows身份验证，也可以使用SQL Server身份验证，对于后者，用户必须提供SQL Server登录帐号的用户名和密码（由SQL Server系统管理员预先定义登录帐号）。

（4）数据库用户帐号和角色。如果用户通过Windows或SQL Server身份验证，即可登录（连接）到SQL Server，但这并不意味着就能够访问SQL Server中的所有数据库，要想访问某个数据库，用户必须在该数据库中拥有用户帐号。数据库用户帐号和角色用来标识数据库中的用户，并控制数据库对象的所有权和执行语句的权限。角色允许将多个用户归类到某个单元中，之后便可对此单元实施一定的许可权限。SQL Server为常用的管理功能提供了预定义（或固定）的服务器角色和数据库角色，固定服务器角色是指在服务器级别提供的管理特权的分组，与用户数据库无关。固定数据库角色是指在数据库级别提供管理特权的分组。用户还可以创建自定义的数据库角色，来表达组织内部分组的工作，这样用户就不必为其中的各个成员分别授予或废除许可权限。如果某角色的职能发生了改变，那么也能非常容易地改变角色的许可权限，这种变更将自动应用于每个角色成员。

（5）许可权限验证。在每个数据库内部，可以为用户帐号和角色分配执行（或限制）某种动作的许可权限。然后按如下步骤进行许可权限验证：在用户执行某个动作之后，客户端将发送T-SQL语句给SQL Server；SQL Server接收到T-SQL语句后将检查用户是否具有执行该语句的许可权限；如果是，SQL Server执行相应的操作；否则，SQL Server返回错误信息。

（6）SQL Server 2005中的几个相关概念：架构（Schema）、用户（User）、角色（Role）

和登录（Login）。其中，架构是一个独立数据库用户的非重复命名空间，可以被视为数据库对象的容器。

（7）SQL Server 2005 中数据库对象的引用形式为：

[DatabaseServer].[DatabaseName].[DatabaseSchema].[DatabaseObject]

5、实验步骤 5.1 实验准备：

通过执行S_T.sql，在SQL Server 2005中创建一个建立示例数据库S_T。 5.2 利用SQL Server Management Studio进行安全性控制 5.2.1 使用系统管理员账号（如sa）设置SQL Server的安全认证模式，并添加SQL Server登录账户。

设置SQL Server的安全认证模式。在对象资源管理器中展开服务器组，右击需要设置的SQL服务器，从弹出的快捷菜单中选择“属性”命令。在弹出的窗口中，单击左上角“安全性”选项，右边显示安全性相关设置项目。选中“Windows身份验证模式”或“SQL Server和Windows身份验证模式”单选按钮。设置改变后，必须停止并重新启动SQL Server服务，新设置才能生效。

添加SQL Server登录帐号。打开SQL服务器，选择“安全性”下的“登录名”文件夹，右击“登录名”文件夹，在弹出的快捷菜单中选择“新建登录名”命令，在出现的窗口的“登录名”文本框中输入一个不带反斜杠的用户名（如tempuser），选中“SQL Server身份验证”单选按钮，并在“密码”与“确认密码”文本框中输入相同的口令（如qq），默认数据库为master（或改为S_T），默认语言为English，单击“确定”按钮完成创建。

修改登录账号的属性。即双击要修改属性的登录账号，在其属性对话框中修改SQL Server账号temp的口令（如将口令修改为tempqq）

删除登录账号（如tempuser）。（注意：在实际操作时，先不要执行此项操作！）

使用上述方法，创建4个登录帐号：tempuser,temp1,temp2,temp3; 密码分别为：tempqq,qq1,qq2,qq3。

5.2.2 管理数据库用户 （1）添加数据库用户。

展开要添加用户的某数据库（如S_T数据库），展开安全性，右击用户目录，从弹出的快捷菜单中选择“新建数据库用户”命令；打开“数据库用户”对话框；单击“登录名”文本框右边的按钮来选择一个登录账号（如tempuser）；在“用户名”文本框中输入用户名，默认情况下它被设置为登录账号名；若需要可以指定数据库用户拥有的架构、数据库角色成员身份等；单击“确定”按钮完成数据库用户的创建。 （2）删除数据库用户。

使用上述方法，为S_T数据库添加4个用户：tempuser,temp1,temp2,temp3，与SQL Server登录帐号一一对应（映射）。

5.2.3 管理数据库角色

添加数据库角色。展开要添加用户的某数据库（如S_T数据库），展开安全性，右击角色目录，从弹出的快捷菜单中选择“新建数据库角色”命令；打开“数据库角色”对话框；单击“所有者”文本框右边的按钮来选择一个数据库用户（如dbo）；在“角色名称”文本框中输入角色名（如db_operator），若需要可以指定数据库角色拥有的架构、此数据库角

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库数据库概论实验指导书(4)在线全文阅读。