实验六：视图、索引和数据安全性

一、实验目的

1. 掌握视图特点；
2. 掌握使用SQL Server创建及管理视图的方法；
3. 掌握在对象资源管理器和T-SQL语句中创建和管理索引；

二、实验学时

2学时

三、实验要求

1. 掌握使用界面方式和命令方式创建及管理视图；
2. 利用对象资源管理器和T-SQL语句创建、修改、删除和使用索引。

四、实验内容

1.以实验数据库为基础数据，利用对象资源管理器创建以下视图：

 1）创建所有学生学号、姓名及年龄的信息视图stu_info

select 
    student表.Sno
    student表.Sname
    student表.Sage
from
    dbo.student表

 2）创建CS系学生基本信息视图stu_cs

 

select 
    student表.Sno
    student表.Ssex
    student表.Sname
    student表.Sage
    student表.Sdept
from
    dbo.student表
where
    student表.Sdept = 'CS'

3）创建选修课成绩在80分以上的学生的信息视图stu_80,包括学生基本情况及成绩。

select 
    student表.Sno,
    student表.Sname,
    student表.Ssex,
    student表.Sage,
    student表.Sdept,
    sc表.Sno,
    sc表.Cno,
    sc表.Grade
from
    dbo.sc表
    inner join
    dbo.student表
    on
        sc表.Sno = student表.Sno
where
    sc表.Grade > '80'

2. 以实验数据库为基础数据，请使用T-SQL语句完成以下内容：

1）创建CS_age视图，包括CS系各学生的学号、姓名及年龄，要求进行修改和插入操作时仍需保证该视图只有CS系的学生;

create view CS_age
as
select sno,sname,sage
from student表
where sdept = 'CS'
with check option

2）创建CS_age_20视图，包括CS系学生年龄在20岁以上的基本信息；并保证对视图文本的修改都要符合年龄大于20这个条件。

 

create view CS_age_20
as
select *
from student表
where sdept = 'CS' and sage > 20
with check option

 3）向CS系学生视图stu_cs中插入一个新的学生记录，学号为200515026，姓名为“赵红平”，性别为“男”，年龄21岁；

insert
into stu_cs
values('200515026','男','赵红平','21','CS');

4）利用视图stu_cs修改学号为200515001的学生姓名为“赵青青”；

update stu_cs
set sname='赵青青'
where sno='200515001';

5）利用视图stu_cs删除CS系学号为200515003的记录；

delete
from stu_cs
where Sno='200515003'

6）删除视图CS_age_20；

delete view CS_age_20;

3.在对象资源管理器中，利用图形化的方法创建和管理下列索引：

1）对数据表student中字段sname建立非聚集索引idx_name；

2）针对student表的sno和sname列创建复合索引idx_no_name;

3） 删除数据表student的索引idx_name；

 

4. 利用T-SQL语句创建和管理以下索引：

1）对course表的cname列创建非聚集索引idx_cname;

create
index idx_cname
on course表(cname)

2）对sc表的sno和cno列创建复合索引idx_sno_cno；

create
index idx_sno_cno
on sc表(sno,cno)

3）删除索引idx_cname。 

drop index course表.idx_cname;

5.数据库安全性练习（以下内容不需要在实验作业中提交）

1. 创建数据库用户

任务：创建数据库用户test1。

因SQL Server中数据库新用户需关联登录名，故建议先创建SQL Server登录名。

操作提示：界面方式。以Windows身份验证连接SSMS→对象资源管理器→安全性→登录名→新建登录名。在“登录名-新建”窗口中进行相关设置，如输入登录名“test1”，SQL Server身份验证，输入密码，选择默认数据库stu，如图1所示。

图1 新建登录名

然后在“用户映射”选项中选择映射数据库stu，同时产生用户test1，如图2所示。单击“确定”即可创建登录名test1和数据库用户test1。

图2 用户映射

SQL命令方式：

CREATE USER user_name [FOR LOGIN <login_name> ]

（2）用户授权

任务：授予用户test1查询、更新数据表student的权限。

用户具有登录名后，可以成功登录数据库服务器，但并不具备访问任何用户数据库的权限。只有成为数据库的合法用户后，才能访问该数据库，故需要赋予其相应权限才可展开工作。

操作提示：界面方式。以Windows身份验证连接SSMS→对象资源管理器→数据库→stu→安全性→用户，双击用户test1即可打开“数据库用户”窗口，依次单击所需权限，如图3所示。单击“确定”即可完成用户授权。

图3 授予数据库用户权限

SQL命令方式：

GRANT <权限>[,<权限>]...  

ON <对象类型> [,<对象名>]...

TO <用户>[,<用户>]...

[WITH GRANT OPTION];

（3）课外自主实验（选做）

建议依次登录验证新用户身份和权限。

1. 在数据库Stu中创建用户stu1、stu2和stu3，登录名分别为stu1、stu2和stu3；
2. 给数据库用户stu1赋予对sc表进行INSERT、UPDATE、DELETE操作权限；
3. 给数据库用户stu2和stu3赋予对student表、course表所有操作权限及查询sc的操作权限，并允许再授权给其他用户；
4. 收回数据库用户stu2对student表和course表的DELETE操作的权限。

五、实验分析与思考

1. 本次实验过程中易出错的地方有哪些，如何解决。
2. 试分析并总结数据库视图的优缺点。
3. 试分析并总结使用索引的注意事项。
4. 本次实验总结。

如有错误望批评指正！谢谢各位！