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.
grant all privileges on Class to U1 with grant option; grant all privileges on Student to U1 with grant option;
grant select, update(Saddress) on Student to U2;
grant select on Class to public;
grant select,update on Student to R1;
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
(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.
grant select on Staff to Wang Ming; grant select on Department to Wang Ming;
grant insert,delete on Staff to Li Yong; grant insert,delete on Department to Li Yong;
grant select on Staff when user()=Sname to all;
grant select,update(salary) on Staff to Liu Xing;
grant alter on Staff to Zhang Xin; grant alter on Department to Zhang Xin;
grant all privileges on Staff to Zhou Ping with grant option; grant all privileges on Department to Zhou Ping with grant option;
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.
revoke select on Staff from Wang Ming; revoke select on Department from Wang Ming;
revoke insert,delete on Staff from Li Yong; revoke insert,delete on Department from Li Yong;
revoke select on Staff when user()=Sname from all;
revoke select,update(salary) on Staff from Liu Xing;
revoke alter on Staff from Zhang Xin; revoke alter on Department from Zhang Xin;
revoke all privileges on Staff from Zhou Ping cascade; revoke all privileges on Department from Zhou Ping cascade;
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