6. For the following two relationship models:
Student (student number, class, age, gender, home address, class number)
Class (class number, class name, head teacher, monitor).
(1) Grant user U1 all permissions on the two tables and authorize other users
(2) Grant user U2 permission to view the student table and update the home address.
(3) Grant viewing permission to class table to all users.
(4) Grant the right to query and update the student table to role R1.
(5) The role RI is granted to user U1, and U1 can continue to authorize to other roles.
answer:
(1)

grant all privileges
on Class
to U1
with grant option;

grant all privileges
on Student
to U1
with grant option;

(2)

grant select, update(Saddress)
on Student
to U2;

(3)

grant select
on Class
to public;

(4)

grant select,update
on Student
to R1;

(5)

grant R1
to U1
with grant option;

7. There are two relationship models:
Employee (employee number, name, age, position, salary, department number)
Department (department number, name, manager name, address, telephone number)
Complete the following authorization definition or access control functions with GRANT and REVOKE statements (plus view mechanism) of SQL
Build table
(1) User Wang Ming has SELECT permission on two tables.
(2) User Li Yong has INSERT and DELETE permissions on the two tables.
(3) Each employee only has the right to SELECT their own records. (not realized)
(4) User Liu Xing has SELECT permission on the employee table and update permission on the salary field.
(5) User Zhang Xin has permission to modify the structure of these two tables.
(6) User Zhou Ping has all permissions (read, insert, modify and delete data) on these two tables and has the permission to authorize other users.
(7) User Yang Lan has the permission to SELECT the maximum wage, minimum wage and average wage from employees in each department. He cannot view everyone's salary.
answer:
(1)

grant select
on Staff
to Wang Ming;

grant select
on Department
to Wang Ming;

(2)

grant insert,delete
on Staff
to Li Yong;

grant insert,delete
on Department
to Li Yong;

(3)

grant select
on Staff
when user()=Sname
to all;

(4)

grant select,update(salary)
on Staff
to Liu Xing;

(5)

grant alter
on Staff
to Zhang Xin;

grant alter
on Department
to Zhang Xin;

(6)

grant all privileges
on Staff
to Zhou Ping
with grant option;

grant all privileges
on Department
to Zhou Ping
with grant option;

(7)

create view staff(Dname,max_salary,min_salary,avg_salary)
as
select Department.Dname,max(salary),min(salary),avg(salary)
from Staff,Deparment
where Staff.Depa_num=Department.Depa_num 
group by Staff.Depa_num;

grant select
on staff
to Yang Lan;

8. Revoke the permission granted by the user for each case (1) ~ (7) in exercise 7.
answer:
(1)

revoke select
on Staff
from Wang Ming;

revoke select
on Department
from Wang Ming;

(2)

revoke insert,delete
on Staff
from Li Yong;

revoke insert,delete
on Department
from Li Yong;

(3)

revoke select
on Staff
when user()=Sname
from all;

(4)

revoke select,update(salary)
on Staff
from Liu Xing;

(5)

revoke alter
on Staff
from Zhang Xin;

revoke alter
on Department
from Zhang Xin;

(6)

revoke all privileges
on Staff
from Zhou Ping cascade;

revoke all privileges
on Department
from Zhou Ping cascade;

(7)

revoke select
on staff
from Yang Lan;

Um. Some of them are still not very familiar with the investigation. They looked at the specific implementation vaguely and didn't particularly understand it.. Ask later