MySQL系列（七）：SQL课后作业（以教务系统数据库为例）

大学教务数据库中有如下五张表：

student (s_no, name, sex, b_date, phone, d_no);

course (c_no, name, textbook, credit, hours, d_no);

teacher (t_no, name, rank, email, phone, d_no);

department(d_no, name, Address, Telephone, dean_no);

enroll (s_no, c_no, semester, class_no, t_no, grade);

一、 对如下两个SQL语句，判定要执行它时，须要哪些权限？

INSERT INTO enroll(s_no, c_no, semester) (SELECT s_no, ‘H61030008’, ’2018/01’ FROM student WHERE d_no =’590’ AND s_no LIKE ’2016%’)；

enroll表的更新(INSERT)权限；student表的读(SELECT)权限。

SELECT t1.name, t1.t_no, SUM(c.hours) AS sumHours FROM teacher AS t1, teacher AS t2, course AS c, dept AS d WHERE t1.d_no = d.d_no AND t1.t_no = t2.t_no  AND t2.c_no = c.c_no AND d.name = '信息学院' AND t2.semester LIKE ‘2019% ’ GROUP BY t1.name, t1.t_no HAVING sumHours <192;

teacher表的读(SELECT)权限；course表的读(SELECT)权限；dept表的读(SELECT)权限。

二、 用SQL回答如下问题：

1) 创建角色teacher, administrator, student;

CREATE ROLE teacher,administrator,student;

2) 创建用户s1,s2, t1, a1;

CREATE USER s1,s2,t1,a1;

3) 将角色student指派给用户s1和s2;

GRANT student TO s1,s2;

4) 将角色administrator和teacher指派给用户a1;

GRANT administrator, teacher TO a1;

5) 将角色administrator指派给用户t1;

GRANT administrator TO t1;

6) 给角色administrator赋权：对teacher表有添加，修改，删除的权力;

GRANT INSERT,UPDATE,DROP ON teacher TO administrator;

7) 以角色administrator的身份，给角色student赋权：对enroll表具有INSERT权限，添加时限定仅为s_no, c_no, semester这三个字段赋值；

GRANT INSERT(s_no, c_no, semester) ON enroll TO student AS administrator;