数据库 ----- 实验四：视图和数据库安全
[实验目的和要求]
1.理解SQL 的视图以及与基本表的区别；
2.掌握SQL 视图的定义、查询、更新；
3.掌握SQL Server中有关用户、权限的管理方法；
4.掌握SQL 语言的控制功能；
5.加深对数据库安全性的理解。
[实验内容]
1.对学生数据库，应用SQL 语句实现以下要求：
1）建立男学生的视图（Male_Student），属性包括学号、姓名、选修课程名和成绩；
2）在男学生视图中查询平均成绩大于80分的学生学号与姓名；
3）对男学生视图的数据进行修改；
将“95001”学号的学生姓名改为“李咏”；
将“95001”学生选修“数据库”的成绩改为“95”。
4）统计每个学生有成绩的课程门数、平均成绩。
2.对图书读者数据库，应用SQL 语句或企业管理器实现以下要求：
1）建立读者借阅视图（READER_BOOKS），属性包括读者编号、读者姓名、所借书号、书名、类别、出版社、借阅日期、归还日期）；
2）在该视图中查询闫冠希读者所借阅的全部书籍的书名、类别以及借阅日期和归还日期；(这里将要查的张三改为查闫冠希)
3）统计每个读者各类别书籍的借阅总数；
3.两个同学（分别称为同学甲和同学乙）合作, 完成下述实验要求。
①同学甲的计算机作为SQL Server数据库服务器，同学乙的机器作为客户端，需要访问该服务器上的数据库“mydb_同学甲学号”中的数据。
a．同学甲在自己的计算机（即服务器）上为同学乙的计算机新建一个登录，访问服务器上的数据库“mydb_同学甲学号”：
1）同学甲在自己的计算机（即服务器）上启动MS SQL Server MANAGEMENT STUDIO，以Winiodows 身份验证的方式连接到SQL Server引擎；
2）在“安全性”中创建一个登录，登录名为：“L_同学乙学号”，并设置密码，默认连接的数据库是同学甲在实验二创建的“Mydb_同学甲学号”。
b．同学甲在自己的计算机（即服务器）上对数据库“mydb_同学甲学号”新建一个用户为同学乙：同学甲在自己的计算机（即服务器）上展开“数据库”中“Mydb_学号”数据库，新建一数据库用户，登录名为“L_同学乙学号”，用户名为“L_同学乙学号”；
c．同学乙在自己的计算机（即客户端）上以“SQL Server 身份验证”方式登录服务器（即同学甲计算机），用户名为“L_同学乙学号” ，并执行对服务器（即同学甲计算机）中数据的查询和插入操作：
1）同学乙在自己的计算机（即客户端）上选择“连接网络服务器”，找到同学甲计算机名，以“SQL Server 身份验证”方式连接到服务器，登录名和密码分别是“L_同学乙学号”和密码；
2）连接服务器成功后，展开该服务器中的数据库文件夹，打开数据库“Mydb_同学甲学号”；
3）新建查询，执行如下SQL 语句，观察并记录执行结果；
（a） select * from Student；
（b） Insert into Student Values （‘95099’，‘闫冠希’，‘男’，20，‘计算机系’）；
只有mydb_20211104227数据库可以看其下创建的表，其他数据库访问会报如下错误。
d．同学甲在自己的计算机（即服务器）上对同学乙用户进行授权，使其具有对数据库“Mydb_同学甲学号”中的表student 进行查询的权限：同学甲在自己的计算机（即服务器）上展开“数据库”中“Mydb_学号”数据库，新建查询，进行对同学乙的授权，使其具有对数据库“Mydb_同学甲学号”中的表student 进行查询的权限；
e．同学乙在自己的计算机（即客户端）重新登录，执行3中的操作，观察并记录执行结果；
已经插入闫冠希这条数据了，名字约束为唯一，所以出错
f．同学甲在自己的计算机（即服务器）上对同学乙用户进行再次授权，使其具有对数据库“Mydb_同学甲学号”中的表student 进行插入的权限；
这里我既给了插入权限也给了查询权限
手动给了，代码也可以如下图
g．同学乙在自己的计算机（即客户端）重新登录，再次执行3中的操作，观察并记录执行结果；
h．同学甲在自己的计算机（即服务器）上对同学乙用户的查询权限进行撤销（即回收权限），使其不再具有对数据库“Mydb_同学甲学号”中的表student 进行查询的权限；
这里我既删除了插入权限也删除了插入权限
手动删除，代码也可以如下图
i．同学乙在自己的计算机（即客户端）重新登录，再次执行3中的操作，观察并记录执行结果。
②两同学角色互换，即同学乙的计算机作为SQL Server数据库服务器，同学甲的机器作为客户端，需要访问该服务器上的数据库“mydb_同学乙学号”中的数据。重复上述①的实验内容
角色互换之后，仍然是上述的效果图
[实验思考题]
1． 通过以上实验深入理解视图概念，请总结使用视图的好处？
简单性，试图可以让用户对数据理解更加方便
安全性提高，通过试图用户智能查询和修改他们所能见到的数据。
2． 对视图的数据都可以更新吗？为什么？
不是的。
（1）若视图的字段是来自字段表达式或常数，则不允许对此视图执行INSERT、UPDATE操作，允许执行DELETE操作；
（2）若视图的字段是来自库函数，则此视图不允许更新；
（3）若视图的定义中有GROUP BY子句或聚集函数时，则此视图不允许更新；
（4）若视图的定义中有DISTINCT任选项，则此视图不允许更新；
（5）若视图的定义中有嵌套查询，并且嵌套查询的FROM子句中涉及的表也是导出该视图的基表，则此视图不允许更新；
（6）若视图是由两个以上的基表导出的，此视图不允许更新；
（7）一个不允许更新的视图上定义的视图也不允许更新；
（8）由一个基表定义的视图，只含有基表的主键或候补键，并且视图中没有用表达式或函数定义的属性，才允许更新。
3．针对实验内容三，如果想要授予客户端用户“L_学号”对student 表中姓名的修改权限，并回收其插入权限，应该如何操作？
Grant select,update(name) on Student to L_20211104205
【附录代码】
create database MyDb2
create table Student
(Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sage smallint,
Sdept char(20)
);
create table Course
(
Cno char(4) primary key,
Cname char(40) not null,
Cpno char(4),
Ccredit smallint,
foreign key(Cpno) References Course(cno)
);
create table SC
(
Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
);
INSERT
INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('95001','李勇','男',30,'CS');
INSERT
INTO Student
VALUES('95002','刘晨','女','19','IS');
INSERT
INTO Student
VALUES('95003','王敏','女','18','MA');
INSERT
INTO Student
VALUES('95004','张立','男','19','IS');
INSERT
INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('95005','闫冠希','男',20,'CS');
insert
into Course
values(2,'数学',null,2);
insert
into Course
values(6,'数据处理',null,2);
insert
into Course
values(4,'操作系统',6,3);
insert
into Course
values(7,'C语言',6,4);
insert
into Course
values(5,'数据结构',7,4);
insert
into Course
values(1,'数据库',5,4);
insert
into Course
values(3,'信息系统',1,4);
insert
into SC(Sno,Cno,Grade)
values('95001',1,32);
insert
into SC
values('95001',2,85);
insert
into SC
values('95001',3,65)
insert
into SC(Sno,Cno,Grade)
values('95001',4,46);
insert
into SC
values('95001',5,58);
insert
into SC
values('95001',6,98)
insert
into SC
values('95001',7,22)
insert
into SC
values('95002',2,90)
insert
into SC
values('95002',3,80)
insert
into SC
values('95002',6,75)
insert
into SC
values('95002',7,56)
insert
into SC
values('95002',5,97)
insert
into SC(Sno,Cno,Grade)
values('95003',1,32);
insert
into SC
values('95003',2,85);
insert
into SC
values('95003',3,88)
insert
into SC(Sno,Cno,Grade)
values('95004',1,32);
insert
into SC(Sno,Cno,Grade)
values('95004',2,99);
insert
into SC(Sno,Cno,Grade)
values('95004',4,98);
insert
into SC(Sno,Cno,Grade)
values('95004',5,97);
insert
into SC(Sno,Cno,Grade)
values('95004',6,96);
insert
into SC
values('95005',2,88);
insert
into SC
values('95004',3,86)
insert
into SC(Sno,Cno,Grade)
values('95005',1,67);
insert
into SC
values('95005',7,62);
insert
into SC
values('95005',6,74)
create view Male_Student
as
select Student.Sno,Sname,Cname,Grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno= SC.Cno and Ssex = '男';
select Sno,Sname
from Male_Student
group by Sno,Sname
having AVG(Grade)>80
update Male_Student
set Sname = '李咏'
where Sno = '95001';
update Male_Student
set Grade = 95
where Sno = '95001' and Cname = '数据库';
select Sno 学号,COUNT(*) 课程门数,AVG(Grade) 平均成绩
from Male_Student
where Grade is not null
group by Sno
=============================================================================================
create database Library4
create table book
(
book_number char(20) primary key,
classes char(20),
press char(30),
writer char(20),
title char(30),
pricing money,
number bigint,
sold bigint
);
create table person
(
Sno char(11) primary key,
name char(20),
work_address char(30),
Ssex char(2),
phone bigint
);
create table look
(
book_number char(20),
Sno char(11),
jiedate date,
huandate date,
primary key(book_number,Sno),
foreign key(book_number) references book(book_number),
foreign key(Sno) references person(Sno)
);
insert
into person
values('20211104227','闫冠希','计科2102班','男',18335850028);
insert
into person
values('20211104225','小丽','计科2102班','女',12374196301);
insert
into person
values('20211104217','凯子','计科2102班','男',19635741286);
insert
into person
values('20211104221','小刘子','计科2101班','女',15934712486);
insert
into person
values('20211104205','董卓','计科2102班','男',15642893651);
insert
into person
values('20211104233','泽华','计科2102班','男',17634852176);
insert
into person
values('20211104201','刘1','计科2102班','女',15835411496);
insert
into person
values('20211104202','李4','计科2101班','男',13478900123);
insert
into person
values('20211104203','张3','计科2101班','女',19336411234);
insert
into person
values('20211104204','隔壁老王','计科2103班','男',13888482153);
insert
into person
values('20211104256','隔壁老樊','计科2102班','女',18664722189);
insert
into person
values('20211104206','瓶子','计科2101班','男',17334758194);
insert
into person
values('20211104331','乔彦祖','计科2102班','女',14334899516);
insert
into person
values('20211104333','金城武','计科2103班','男',17684262796);
insert
into person
values('20211104347','阿广','计科2103班','女',14614893346);
insert
into book
values('K565.41 24','小说','上海译文出版社','威廉·多伊尔','法国大革命',58.98,200,100);
insert
into book
values('K231.04 4','历史','上海古籍出版社','刘向','战国策',99.8,20,10);
insert
into book
values('TP3121036','计算机','机械工业出版社','黑马程序员','Java面向对象程序设计',78.568,60,32);
insert
into book
values('I216.2 483','文学','上海译文出版社','林徽因','林徽因文学精品选',68.46,400,186);
insert
into book
values('I216.2 484','文学','上海译文出版社','林因','林因文学选',500,400,356);
insert
into book
values('I210.1 1：16 ','计算机','机械工业出版社','鲁迅文学爱好者','鲁迅全集',86.79,500,321);
insert
into book
values('k565.08 6','小说','清华大学出版社','简·奥斯汀','傲慢与偏见',46.27,80,25);
insert
into book
values('K242.09 7','历史','新星出版社','陈舜臣','大唐帝国',54.31,150,114);
insert
into book
values('J222.49 23','计算机','机械工业出版社','高样萍','办公软件应用',125.63,350,289);
insert
into book
values('K835.655.2 10','历史','机械工业出版社','阿三','拿破仑传',54.31,150,68);
insert
into book
values('I512.4 175','小说','机械工业出版社','李武','变色龙',125.63,350,71);
insert
into book
values('I247.8795','历史','机械工业出版社','王伟','乾隆皇帝',54.31,150,22);
insert
into book
values('I247.8796','历史','机械工业出版社','王伟3','清朝清宁',600,260,210);
insert
into book
values('I111.8417 424','小说','机械工业出版社','芳孔','新农村',125.63,350,100);
insert
into book
values('I111.8418 427','小说','机械工业出版社','芳孔1','新世界',600,350,50);
insert
into look
values('K565.41 24',20211104221,'2021-11-10','2021-11-20');
insert
into look
values('J222.49 23',20211104221,'2021-11-11','2021-11-20');
insert
into look
values('K231.04 4',20211104227,'2021-10-06','2021-10-15');
insert
into look
values('TP3121036',20211104233,'2021-09-25','2021-09-30');
insert
into look
values('I216.2 483',20211104233,'2021-10-25','2021-11-10');
insert
into look
values('J222.49 23',20211104233,'2021-10-21','2021-11-21');
insert
into look
values('I210.1 1：16',20211104205,'2021-11-16','2021-11-23');
insert
into look
values('k565.08 6',20211104217,'2021-11-18','2021-11-28');
insert
into look
values('TP3121036',20211104217,'2021-11-18','2021-11-28');
insert
into look
values('I210.1 1：16 ',20211104217,'2021-11-18','2021-11-28');
insert
into look
values('K242.09 7',20211104227,'2021-10-14','2021-12-20');
insert
into look
values('I210.1 1：16 ',20211104227,'2021-11-15','2021-12-20');
insert
into look
values('J222.49 23',20211104225,'2021-09-25','2021-10-20');
insert
into look
values('TP3121036',20211104225,'2021-09-25','2021-10-10');
insert
into look
values('I210.1 1：16 ',20211104225,'2021-11-18','2021-11-28');
insert
into look
values('J222.49 23',20211104201,'2021-10-21','2021-11-21');
insert
into look
values('I210.1 1：16',20211104201,'2021-11-16','2021-11-26');
insert
into look
values('k565.08 6',20211104202,'2021-11-18','2021-11-28');
insert
into look
values('TP3121036',20211104201,'2021-11-18','2021-11-29');
insert
into look
values('I210.1 1：16 ',20211104203,'2021-11-18','2021-11-28');
insert
into look
values('K242.09 7',20211104204,'2021-10-14','2021-11-24');
insert
into look
values('I210.1 1：16 ',20211104206,'2021-11-11','2021-11-22');
insert
into look
values('J222.49 23',20211104256,'2021-09-24','2021-11-20');
insert
into look
values('k565.08 6',20211104331,'2021-09-27','2021-11-27');
insert
into look
values('I210.1 1：16 ',20211104331,'2021-11-10','2021-11-20');
insert
into look
values('J222.49 23',20211104333,'2021-09-14','2021-11-20');
insert
into look
values('TP3121036',20211104204,'2021-09-15','2021-10-12');
insert
into look
values('I210.1 1：16 ',20211104204,'2021-11-11','2021-11-21');
create view READER_BOOKS
as
select look.Sno 读者编号,name 读者姓名, book.book_number 所借书号,title 书名,classes 类别,press 出版社,jiedate 借阅日期,huandate 归还日期
from book,look,person
where book.book_number = look.book_number and person.Sno = look.Sno;
select 书名,类别,借阅日期,归还日期
from READER_BOOKS
where 读者姓名 = '闫冠希';
select 读者姓名,类别,COUNT(*) 借阅总数
from READER_BOOKS
group by 类别,读者姓名
============================================================================================
create table Student
(Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sage smallint,
Sdept char(20)
);
create table Course
(
Cno char(4) primary key,
Cname char(40) not null,
Cpno char(4),
Ccredit smallint,
foreign key(Cpno) References Course(cno)
);
create table SC
(
Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
);
INSERT
INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('95001','李勇','男',30,'CS');
INSERT
INTO Student
VALUES('95002','刘晨','女','19','IS');
INSERT
INTO Student
VALUES('95003','王敏','女','18','MA');
INSERT
INTO Student
VALUES('95004','张立','男','19','IS');
insert
into Course
values(2,'数学',null,2);
insert
into Course
values(6,'数据处理',null,2);
insert
into Course
values(4,'操作系统',6,3);
insert
into Course
values(7,'C语言',6,4);
insert
into Course
values(5,'数据结构',7,4);
insert
into Course
values(1,'数据库',5,4);
insert
into Course
values(3,'信息系统',1,4);
insert
into SC(Sno,Cno,Grade)
values('95001',1,32);
insert
into SC
values('95001',2,85);
insert
into SC
values('95001',3,88)
insert
into SC
values('95002',2,90)
insert
into SC
values('95002',3,80)
select * from Student;
insert
into Student
values('95100','闫冠希1','男',20,'计算机系');
delete
from Student
where Sname = '老王' or Sname = '闫冠希'
grant select
on Student
to L_20211104205
grant insert
on Student
to L_20211104205
revoke select
on Student
from L_20211104205;
2120
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
