数据库实验七——视图、索引及数据安全

实验七：视图、索引及数据安全

一、实验目的

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

二、实验学时

2学时

三、实验要求

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

四、实验内容

1.以实验数据库为基础数据，利用对象资源管理器创建以下视图：
1）创建所有学生学号、姓名及年龄的信息视图stu_info

create view stu_info
as
select Sno,Sname,Sage
from student

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

create view stu_cs
as
select *
from student
where Sdept='CS'

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

create view stu_80
as
select student.Sno,Sname,Sage,Sdept,Grade 
from student,SC
where student.Sno=SC.Sno and SC.grade>80

2. 以实验数据库为基础数据，请使用T-SQL语句完成以下内容，并将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)创建一个视图stu_cg，用于查看学生学号、姓名、课程和成绩信息，并用WITH ENCRYPTION加密。

create view stu_cg
with encryption
as
select Student.Sno,Sname,Cname,Grade
from student,Course,SC

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

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

5)利用视图stu_CS修改学号为200515001的学生姓名为“赵青青”；

update stu_cs
set Sname='赵青青'
where Sno='200515001'

6)利用视图stu_CS删除CS系学号为200515003的记录；

delete from stu_cs
where Sno='200515003'

7)删除视图CS_age_20；

drop view CS_age_20

3.在对象资源管理器中，利用图形化的方法创建和管理下列索引：
1)对数据表student中字段sname建立非聚集索引idx_name；

create nonclustered index idx_name on student(Sname)

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

  create  index idx_no_name
  on student(Sname,Sno)

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

use student 
drop index idx_name on student

4. 利用T-SQL语句创建和管理以下索引：
   1)对course表的cname列创建非聚集索引idx_cname;

 create nonclustered index idx_cname on course(cname)

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

 create index idx_sno_cno on SC(Sno,Cno)

3)利用系统存储过程sp_helpidex查看索引idx_cname信息；

 Exec sp_helpindex 'course'

4)删除索引idx_cname。

drop index idx_cname on course

5. 数据安全性。以系统管理员身份登录到SQL Server服务器，在SQL Server2008界面中实现以下操作，并写出相应程序代码。
   1)在当前计算机中增加一个用户zhang，密码为secret。使此用户通过windows模式下登录SQL Server服务器，登录名为zhang；

create login zhang with password='secret'
create user zhang for login zhang

2)新建以混合模式登录SQL Server服务器的用户登录名分别为stu1、stu2和stu3登录密码为secret，默认登录数据库为stu；
将登录帐号stu1、stu2和stu3加入到服务器角色sysadmin中；

create login stu1 with password='secret',
DEFAULT_DATABASE=student
GO
create login stu2 with password='secret',
DEFAULT_DATABASE=student
GO
create login stu3 with password='secret',
DEFAULT_DATABASE=student
GO

EXEC sp_addsrvrolemember 'stu1','sysadmin'
EXEC sp_addsrvrolemember 'stu2','sysadmin'
EXEC sp_addsrvrolemember 'stu3','sysadmin'

3)删除服务器角色sysadmin的成员stu2和stu3；

EXEC sp_dropsrvrolemember 'stu1','sysadmin'
EXEC sp_dropsrvrolemember 'stu2','sysadmin'
EXEC sp_dropsrvrolemember 'stu3','sysadmin'

4)在数据库stu中创建用户zhang，登录帐号为zhang；

create user zhang for login zhang 

5)在数据库stu中创建用户stu1、stu2和stu3，登录帐号为stu1、stu2和stu3;

create user stu1 for login stu1
create user stu2 for login stu2
create user stu3 for login stu3

6)给数据库用户zhang赋予创建数据表的权限；

Grant create table to zhang

7)给数据库用户stu1赋予对sc表进行插入、修改、删除操作权限；

Grant insert,update,delete on SC to zhang

8)给数据库用户stu2和stu3赋予对student表、course表所有操作权限及查询sc的操作权限，并允许再授权给其他用户；

GRANT ALL 
ON student
TO stu2,stu3
WITH GRANT OPTION;

GRANT ALL
ON course
TO stu2,stu3
WITH GRANT OPTION;

9)回收数据库用户stu2对student表和course表的删除操作的权限；

REVOKE DELETE
on student
TO stu2,stu3 CASCADE

REVOKE DELETE
on course
TO stu2,stu3 CASCADE