数据库实验7

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

实验内容

注：以下所有用表都基于XSKC模式
1.以实验数据库为基础数据，利用对象资源管理器创建以下视图：
1）创建所有学生学号、姓名及年龄的信息视图stu_info
2）创建CS系学生基本信息视图stu_cs
3）创建选修课成绩在80分以上的学生的信息视图stu_80,包括学生基本情况及成绩。

SQL Server 2019界面添加视图方法：

选中要添加的属性，然后右键点击空白处，选择执行SQL

右键单击箭头所指，然后保存，在对视图命名

2. 以实验数据库为基础数据，请使用T-SQL语句完成以下内容，并将SQL语句写在实验报告册中：
   1）创建CS_age视图，包括CS系各学生的学号、姓名及年龄，要求进行修改和插入操作时仍需保证该视图只有CS系的学生;

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

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

create view CS_age_20  
as  
select *  
from XSKC.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 XSKC.student,XSKC.sc,XSKC.course  
where student.sno=sc.sno and sc.cno=course.cno

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

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

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；
2）针对student表的sno和sname列创建复合索引idx_no_name;
3）删除数据表student的索引idx_name；、

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

create index idx_cname  
on XSKC.course(cname)  

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

create index idx_sno_cno  
on XSKC.sc(sno,cno) 

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

 exec sp_helpindex 'XSKC.course' 

4）删除索引idx_cname。

 drop index XSKC.course.idx_cname 

5. 数据安全性。以系统管理员身份登录到SQL Server服务器，在SQL Server2008界面中实现以下操作，并写出相应程序代码。
   注：以Windows身份验证方式登录就可以
   1）在当前计算机中增加一个用户zhang，密码为secret。使此用户通过windows模式下登录SQL Server服务器，登录名为zhang；
   2）新建以混合模式登录SQL Server服务器的用户登录名分别为stu1、stu2和stu3登录密码为secret，默认登录数据库为stu；将登录帐号stu1、stu2和stu3加入到服务器角色sysadmin中；
   
   
   

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

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 XSKC.sc  
to stu1 

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

grant all privileges  
on XSKC.student  
to stu2,stu3  
with grant option 

grant all privileges  
on XSKC.course  
to stu2,stu3  
with grant option 

grant select  
on XSKC.sc  
to stu2,stu3  
with grant option 

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

revoke delete  
on XSKC.student  
from stu2  
cascade 

revoke delete  
on XSKC.course 
from stu2  
cascade