1、实 验 四 数据安全控制一、实验目的:熟练掌握SQL SERVER数据库中使用SQL命令方式对视图的创建、修改与删除，熟练掌握SQL SERVER数据库中用户和角色的创建、修改和删除，在第一次实验的基础上对用户和角色对表和视图的访问进行权限控制，为后继学习创建数据库项目进行安全控制作准备。二、 实验属性（综合性）1.了解并掌握命令方式对视图的创建、修改与删除；2.掌握命令方式对用户和角色的创建、修改与删除；3掌握通过安全控制语句GRANT、REVOKE对视图和表的访问进行安全控制；三、实验仪器设备及器材1安装了SQL SERVER 2008的计算机。四、实验要求(预习、实验前、实验中、实验后等
2、要求)1复习教材第3章、第4章，掌握SQL语句和安全控制语句；2熟悉SQL Server 2008环境；3掌握SQL语句下视图的创建、修改与删除；4掌握SQL语句下用户和角色的创建、修改与删除；5掌握SQL语句下安全控制语句GRANT、REVOKE；6、完成所有实验要求的SQL语句。五、 实验原理SQL语言综合应用。六、实验内容：(1)启动SQL查询分析器；(2) 选择“学生选课管理数据库”；1 建立视图1.1 建立数学系学生的视图math_stu，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno，Sname，Sage，Sdept。CREATE VIEW math
3、_stu(Sno,Sname,Sage,Sdept) as select Sno,Sname,Sage,Sdept from student where Sdept=201708;1.2 建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图grade_stu。create view grade_stu(Sno,Sname,Cname,Grade) as select student.Sno,Student.Sname,course.Cname,sc.gradefrom student,sc,course where student.Sno=sc.Sn
4、o and course.Cno=sc.Cno;1.3 定义一个反映学生出生年份的视图Student_birth。CREATE VIEW birth(birth) as select (2020-Sage) from student;1.4 定义一个视图AVGG，视图列中有课程号、这门课程的平均成绩，且平均成绩大于80分。CREATE VIEW avgg(Cno,avg_course) as select Cno,avg(grade) from sc group by Cno having avg(grade)80;2 删除视图2.1 删除1.3中建的视图。DROP VIEW IF EXIST
5、S birth;3 查询视图3.1 在数学系的学生视图math_stu中找出年龄(Sage)小于20岁的学生姓名(Sname)和年龄(Sage)。select Sname Sage from math_stu where Sage85;4 更新视图4.1 将数学系学生视图math_stu中学号为200215123的学生姓名改为“黄海”。update math_stu set Sname=黄海 where Sno=200215123;4.2 向数学系学生视图math_stu中插入一个新的学生记录，其中学号为“S09”，姓名为“王海”，年龄为20岁。insert into student(Sno,
6、Sname,Sage,Sdept) values(201708034219,王海,20,201708);5 创建用户5.1创建两个用户，一个以你的名字（汉语拼音）命名的用户，登陆名也为你的名字（汉语拼音），另一个为你同学（汉语拼音）的名字命名的一个用户，登录名为你同学的名字（汉语拼音）。create user linsanlocalhost identified by linsan;create user lpplocalhost identified by lpp;5.2在学生管理数据库上创建两个角色R1和R2，把你这个用户放入R1和把你同学的用户放入角色R2中。create ROLE R1
7、localhost; create ROLE R2localhost; grant R1 to zhangyubinlocalhost; grant R2 to wuyouxiulocalhost;5.3给角色R1授权，使其可以查询学生管理数据库中的student表以及查询视图math_stu。grant select on stu.math_stu to R1; grant select on stu.student to R1; flush privileges; exits; mysql -u zhangyubin -p set default role all to zhangyubi
8、nlocalhost;5.4给角色R2授权，使其可以插入和更新学生管理数据库中的student表。grant insert,update on stu.student to R2;flush privileges;exits;mysql -u wuyouxiu -pset default role all to zhangyubinlocalhost;insert into student(Sno,Sname,Sage,Sdept) values (201708034118,李远,20,201709);update student set Sage=19 where Sno=201708034118;5.5请分别以你自己名字命名的用户和你同学（汉语拼音）的名字命名的用户分别登录学生管理数据库对student表查询，再对student表进行插入和更新，看看分别是什么结果。 5.6 把角色R1的中对student表查询的权限收回。revoke select on stu.student from R1;5.7再次以你自己名字命名的用户登录学生管理数据库对student表查询看是什么结果。
