[大数据]实验六：视图及数据库系统安全

删除登录名 ‘sqlLogin’，因为该用户当前正处于登录状态。 (.Net SqlClient Data Provider)
办法：
exec sp_who
kill spid(id)
然后删除即可

实验六：视图及数据库系统安全

一、实验目的
1.掌握视图的使用
2.掌握混合模式下数据库用户帐号的建立与取消方法；
3.掌握数据库用户权限的设置方法；
4.掌握服务器角色的用法；
5.掌握SQL-Server中数据库备份和恢复的方法。
二、实验学时
2学时
三、实验要求
1.能使用图形化界面和T-SQL语言完成视图的相关操作。
2.熟悉数据库用户、服务器角色及数据库角色的用法；
3.了解创建备份设备和进行数据库完全备份操作的方法；
4.了解进行数据库恢复的步骤；
5.完成实验报告。
四、实验内容

#1. 数据安全性。以系统管理员身份登录到SQL Server服务器，在SQL Server2008界面中实现以下操作，并写出相应程序代码。

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 [DESKTOP-A6HMLH7\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 insert,update,delete,select
on XSKC.student
to stu2,stu3
with grant option


grant insert,update,delete,select
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 select 
ON  XSKC.student 
FROM stu2 CASCADE

REVOKE select 
ON  XSKC.course 
FROM stu2 CASCADE

2.以实验数据库为基础数据，利用对象资源管理器创建以下视图：
1）创建所有学生学号、姓名及年龄的信息视图v_stu_info
代码：
create view v_stu_info
as
select sno,sname,sage
from XSKC.student
界面：（注意选择列的顺序）

2）创建CS系学生基本信息视图v_stu_cs
代码：

create view v_stu_cs
as 
select *
from XSKC.student
where sdept='cs'

界面：（注意选择列的顺序）

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

create view v_stu_80
as 
select XSKC.student.*,XSKC.sc.grade
from XSKC.student,XSKC.sc
where grade>=80 and XSKC.student.sno=XSKC.sc.sno

3. 以实验数据库为基础数据，请使用T-SQL语句完成以下内容，并将SQL语句写在实验报告册中：

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

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

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

create view v_CS_age20
as 
select *
from XSKC.student
where sdept='CS' and sage>20
with check option

3. 创建一个视图vstu_cg，用于查看学生学号、姓名、课程和成绩信息，并用WITH ENCRYPTION加密。

create view vstu_cg
with encryption/*注意此语句位置*/
as 
select XSKC.student.sno,XSKC.student.sname,XSKC.sc.cno,XSKC.sc.grade 
from  XSKC.student,XSKC.sc
where XSKC.student.sno=XSKC.sc.sno

4. 试着向视图v_CS中插入一个新的学生记录，（‘200515026’，‘赵红平’，‘21’），是否能插入成功，原因是什么？如何修改视图才可以插入成功？

消息 550，级别 16，状态 1，第 9 行
试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。
语句已终止。



alter view v_CS
as 
select sno,sname,sage
from XSKC.student
where sdept='CS'

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

update v_CS
set sname='赵青青'
where sno='200515001'

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

delete 
from v_CS
where sno='200515003'

7. 删除视图v_CS_age20；

drop view v_CS_age20

阅读世界，共赴山海

423全民读书节，邀你共读