Database Experiment 4: view and database security
[purpose and requirements of the experiment]
1. Understand the SQL view and its difference from the basic table;
2. Master the definition, query and update of SQL view;
3. Master the management methods of users and permissions in SQL Server;
4. Master the control function of SQL language;
5. Deepen the understanding of database security.
[experiment content]
1. For the student database, apply SQL statements to achieve the following requirements:
1) Create a Male_Student view with attributes including student number, name, elective course name and grade;
2) Query the student number and name of students whose average score is greater than 80 in the male student view;
3) Modify the data of male student view;
Change the student name of "95001" student number to "Li Yong";
Change the grade of "95001" students' elective course "database" to "95".
4) Count the number of courses and average scores of each student.
2. For the book reader database, apply SQL statement or enterprise manager to realize the following requirements:
1) Create READER_BOOKS, whose attributes include reader No., reader name, borrowed book No., book name, category, publisher, borrowing date and return date;
2) Query the title, category, borrowing date and return date of all books borrowed by Yan Guanxi readers in this view; (Zhang San to be checked here is changed to Yan Guanxi)
3) Count the total number of books borrowed by each reader;
3. Two students (called classmate a and classmate B respectively) cooperate to complete the following experimental requirements.
① Classmate a's computer is the SQL Server database server, and classmate B's machine is the client. You need to access the data in the database "mydb# classmate a student number" on the server.
a. Classmate a creates a new login for classmate B's computer on his own computer (i.e. server), and accesses the database "mydb_; classmate a student number" on the server:
1) Classmate a starts MS SQL Server MANAGEMENT STUDIO on his own computer (i.e. server) and connects to SQL server engine by Winiodows authentication;
2) Create a login in "security", the login name is "L_ classmate B student number", and set the password. The default connected database is the "Mydb_ classmate a student number" created by classmate a in Experiment 2.
b. Classmate a creates a new user for the database "mydb_ classmate a student number" on his own computer (i.e. server) as classmate B: classmate a expands the "mydb_ student number" database in the "database" on his own computer (i.e. server), and creates a new database user with the login name of "L_ classmate B student number" and the user name of "L_ classmate B student number";
c. Classmate B logs in to the server (i.e. classmate a's computer) by means of "SQL Server authentication" on his computer (i.e. client), with the user name "L_ classmate B's student number", and performs the query and insertion of data in the server (i.e. classmate a's computer):
1) Classmate B selects "connect to the network server" on his computer (i.e. client), finds the computer name of classmate a, and connects to the server in the way of "SQL Server authentication". The login name and password are "L_ classmate B student number" and password respectively;
2) After connecting to the server successfully, expand the database folder in the server and open the database "Mydb_; classmate a student number";
3) Create a new query, execute the following SQL statements, observe and record the execution results;
(a) select * from Student；
(b) Insert into Student Values ('95099 ',' Yan Guanxi ',' male ', 20,' computer department ');
Only mydb_20211104227 database can view the tables created under it. The following errors will be reported for other database access.
d. Classmate a authorizes classmate B's user on his own computer (i.e. server) so that he has the authority to query the table student in the database "Mydb_ classmate a's student number": classmate a expands the "Mydb_ student number" database in the "database" on his own computer (i.e. server), creates a new query and authorizes classmate B, Make it have the permission to query the table student in the database "Mydb_; classmate a student number";
e. Classmate B logs in again on his own computer (i.e. client), performs the operations in 3, observes and records the execution results;
Yan Guanxi's data has been inserted. The name constraint is unique, so an error occurred
f. Classmate a re authorizes classmate B's user on his own computer (i.e. server) to have the permission to insert the table student in the database "Mydb_; classmate a student number";
Here I give both insert permission and query permission
The code can also be given manually, as shown in the figure below
g. Classmate B logs in again on his own computer (i.e. client), performs the operation in 3 again, and observes and records the execution results;
h. Classmate a revokes the query permission (i.e. recycling permission) of classmate B's user on his own computer (i.e. server), so that he no longer has the permission to query the table student in the database "Mydb_; classmate a student number";
Here I deleted both the insert permission and the insert permission
The code can also be deleted manually, as shown in the figure below
i. Classmate B logs in again on his computer (i.e. client), performs the operation in 3 again, and observes and records the execution results.
② The roles of the two students are exchanged, that is, classmate B's computer is used as the SQL Server database server, and classmate a's machine is used as the client. It is necessary to access the data in the database "mydb# classmate B student number" on the server. Repeat the experiment contents of ① above
After the role exchange, it is still the above effect picture
[experimental thinking questions]
1. Understand the concept of view deeply through the above experiments. Please summarize the benefits of using view?
Simplicity, trying to make it easier for users to understand the data
Security is improved by trying to intelligently query and modify the data users can see.
2. Can the data of the view be updated? Why?
no, it isn't.
(1) If the fields of the view are from field expressions or constants, INSERT and UPDATE operations are not allowed for this view, and DELETE operations are allowed;
(2) If the fields of the view are from library functions, the view cannot be updated;
(3) If there is a GROUP BY clause or aggregation function in the definition of the view, the view cannot be updated;
(4) If there is any DISTINCT option in the definition of the view, the view cannot be updated;
(5) If there is a nested query in the definition of a view, and the table involved in the FROM clause of the nested query is also the base table for exporting the view, the view cannot be updated;
(6) If the view is exported from more than two base tables, the view cannot be updated;
(7) A view defined on a view that is not allowed to be updated is also not allowed to be updated;
(8) A view defined by a base table can only be updated if it contains the primary key or candidate key of the base table and there are no attributes defined by expressions or functions in the view.
3. For Experiment 3, what should I do if I want to grant the client user "L_ student number" the right to modify the name in the student table and reclaim its insertion permission?
Grant select,update(name) on Student to L_20211104205
[appendix code]
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','Li Yong','male',30,'CS');
INSERT
INTO Student
VALUES('95002','Liu Chen','female','19','IS');
INSERT
INTO Student
VALUES('95003','Wang Min','female','18','MA');
INSERT
INTO Student
VALUES('95004','Zhang Li','male','19','IS');
INSERT
INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('95005','Yan Guanxi','male',20,'CS');
insert
into Course
values(2,'mathematics',null,2);
insert
into Course
values(6,'data processing',null,2);
insert
into Course
values(4,'operating system',6,3);
insert
into Course
values(7,'C language',6,4);
insert
into Course
values(5,'data structure',7,4);
insert
into Course
values(1,'database',5,4);
insert
into Course
values(3,'information system',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 = 'male';
select Sno,Sname
from Male_Student
group by Sno,Sname
having AVG(Grade)>80
update Male_Student
set Sname = 'Li Yong'
where Sno = '95001';
update Male_Student
set Grade = 95
where Sno = '95001' and Cname = 'database';
select Sno Student number,COUNT(*) Number of courses,AVG(Grade) Average score
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','Yan Guanxi','Class 2102, planning section','male',18335850028);
insert
into person
values('20211104225','Xiao Li','Class 2102, planning section','female',12374196301);
insert
into person
values('20211104217','a prodigal','Class 2102, planning section','male',19635741286);
insert
into person
values('20211104221','Xiao Liuzi','Class 2101, planning section','female',15934712486);
insert
into person
values('20211104205','brutal militarist who dictated policy in declining years of the Han dynasty','Class 2102, planning department','male',15642893651);
insert
into person
values('20211104233','Zehua','Class 2102, planning section','male',17634852176);
insert
into person
values('20211104201','Liu 1','Class 2102, planning department','female',15835411496);
insert
into person
values('20211104202','Li 4','Class 2101, planning section','male',13478900123);
insert
into person
values('20211104203','Zhang 3','Class 2101, planning section','female',19336411234);
insert
into person
values('20211104204','Lao Wang next door','Class 2103, planning section','male',13888482153);
insert
into person
values('20211104256','Lao Fan next door','Class 2102, planning section','female',18664722189);
insert
into person
values('20211104206','bottle','Class 2101, planning section','male',17334758194);
insert
into person
values('20211104331','Qiao Yanzu','Class 2102, planning department','female',14334899516);
insert
into person
values('20211104333','Jinchengwu','Class 2103, planning section','male',17684262796);
insert
into person
values('20211104347','Hegu ','Class 2103, planning section','female',14614893346);
insert
into book
values('K565.41 24','novel','Shanghai Translation Publishing House','William·Doyle','French Revolution',58.98,200,100);
insert
into book
values('K231.04 4','history','Shanghai Classics Publishing House ','Liu Xiang','Strategies of the Warring States Period',99.8,20,10);
insert
into book
values('TP3121036','computer','Machinery Industry Press','Dark horse programmer','Java Object oriented programming',78.568,60,32);
insert
into book
values('I216.2 483','literature','Shanghai Translation Publishing House','Lin Huiyin','Selected works of Lin Huiyin's Literature',68.46,400,186);
insert
into book
values('I216.2 484','literature','Shanghai Translation Publishing House','Lin Yin','Selected works of Lin Yin literature',500,400,356);
insert
into book
values('I210.1 1: 16 ','computer','Machinery Industry Press','Lu Xun literature lovers','Complete works of Lu Xun',86.79,500,321);
insert
into book
values('k565.08 6','novel','tsinghua university press ','simple·Austin','Pride and Prejudice',46.27,80,25);
insert
into book
values('K242.09 7','history','New Star Press ','Chen shunchen','Tang Empire',54.31,150,114);
insert
into book
values('J222.49 23','computer','Machinery Industry Press','Gao Yangping','Office software application',125.63,350,289);
insert
into book
values('K835.655.2 10','history','Machinery Industry Press','Ah San','Biography of Napoleon',54.31,150,68);
insert
into book
values('I512.4 175','novel','Machinery Industry Press','Lowie ','chameleon',125.63,350,71);
insert
into book
values('I247.8795','history','Machinery Industry Press','Wang Wei','qianlong emperor ',54.31,150,22);
insert
into book
values('I247.8796','history','Machinery Industry Press','Wang Wei3','Qing Dynasty Qingning',600,260,210);
insert
into book
values('I111.8417 424','novel','Machinery Industry Press','Aromatic pore','New countryside',125.63,350,100);
insert
into book
values('I111.8418 427','novel','Machinery Industry Press','Aromatic pore 1','New World',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 Reader number,name Reader name, book.book_number Book number borrowed,title title,classes category,press press,jiedate Borrowing date,huandate Return date
from book,look,person
where book.book_number = look.book_number and person.Sno = look.Sno;
select title,category,Borrowing date,Return date
from READER_BOOKS
where Reader name = 'Yan Guanxi';
select Reader name,category,COUNT(*) Total borrowing
from READER_BOOKS
group by category,Reader name
============================================================================================
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','Li Yong','male',30,'CS');
INSERT
INTO Student
VALUES('95002','Liu Chen','female','19','IS');
INSERT
INTO Student
VALUES('95003','Wang Min','female','18','MA');
INSERT
INTO Student
VALUES('95004','Zhang Li','male','19','IS');
insert
into Course
values(2,'mathematics',null,2);
insert
into Course
values(6,'data processing',null,2);
insert
into Course
values(4,'operating system',6,3);
insert
into Course
values(7,'C language',6,4);
insert
into Course
values(5,'data structure',7,4);
insert
into Course
values(1,'database',5,4);
insert
into Course
values(3,'information system',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','Yan Guanxi 1','male',20,'Computer Department');
delete
from Student
where Sname = 'Lao Wang' or Sname = 'Yan Guanxi'
grant select
on Student
to L_20211104205
grant insert
on Student
to L_20211104205
revoke select
on Student
from L_20211104205;