第四章习题作业

  该章一共三道题，都是偏向于动手实践的，主要是针对一些用户进行权限的授予和收回等操作，就是一些存取控制，然后又进一步可以通过角色进行对用户权限的授予和收回，更加方便。

6.对下列两个关系模式：

  学生(学号，姓名，年龄，性别，家庭住址，班级号)

  班级(班级号，班级名，班主任，班长)

首先，我们应该在一个数据库下面建立对应的这两个基本表，然后把题目中的用户和角色建立出来。用户和角色在之前的实验建立过，不嫌麻烦的话我们可以新建一个！

create table Student
       (
	   Sno char(15) primary key,
	   Sname char(20) not null,
	   Ssex char(2) check(Ssex in('男','女')), --用户定义完整性（第五章刚学的）
	   Saddress char(50),
	   Sclass int
	   --foreign key (Sclass) references Class(Cno)
	   );

create table Class
	   (
	   Cno int primary key,
	   Cname char(10) not null,
	   Chead char(10) not null,
	   Cmonitor char(20) not null
	   );

alter table Student add foreign key (Sclass) references Class(Cno);
alter table Student add foreign key (Cmonitor) references Student(Sname);

  这两个表建成功啦，由于想建立两个互相参照的外码，即学生表中的班级号参照班级表中的班级号和班级中的班长名字参照学生表中的名字，所以在建完两个表之后使用 alter 语句添加完整性约束，但是第二个有一点小问题。

  然后把用户建立一下，这里不再详细阐述。

(1）授予用户U1对两个表的所有权限，并可给其他用户授权。

grant all privileges
on Class
to U1
with grant option;
grant all privileges
on Student
to U1
with grant option;--因为题目要求可以对其他用户授权

(2）授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。

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

(3）将对班级表查看权限授予所有用户。

grant select
on Class
to public;

(4）将对学生表的查询、更新权限授予角色R1。

先使用前面学过的语句建立出R1这个角色

create role R1;

grant select,update
on Student
to R1;

(5）将角色RI授予用户U1,并且U1可继续授权给其他角色。

grant R1
to U1
with admin option;--标准SQL语句

alter role R1
add member U1;--T-SQL语句

zaiTSQL中使用上面的语句会出错，如上图，改成下面的语句就好啦。

7.今有以下两个关系模式

  职工（职工号，姓名，年龄，职务，工资，部门号）

  部门（部门号，名称，经理名，地址，电话号）

同理，先把这两个基本表定义出来。

create table Employee
		(
		 Eno char(15) primary key,
		 Ename char(10) not null,
		 Eage int,
		 Ework char(10),
		 Esalary int,
		 Edep char(10)
		);
create table Department
		(
		Dno char(10) primary key,
		Dname char(15),
		Dmanager char(6),
		Daddress char(20),
		Dphonenum char(11)
		);

alter table Employee add foreign key (Edep) references Department(Dno);
--建立完表之后加上SQL语句，或者在建表的时候就加上列级完整性约束，但是注意建表的顺序

  我们可以在建立完表之后加上SQL语句，也可以在建表的时候就加上列级完整性约束，但是注意建表的顺序就行。然后开始实现例题。

(1）用户王明对两个表有SELECT权限。

grant select 
on Employee
to 王明;
grant select 
on Department
to 王明;--注意不要把两个表同时对王明授权

(2）用户李勇对两个表有INSERT和DELETE权限。

grant insert,delete
on Employee
to 李勇;
grant insert,delete
on Department
to 李勇; --该题同理

(3）每个职工只对自己的记录有SELECT权限。

create view v1
as
select *
from Employee
where Employee.Eno=user;

grant select
on v1
to public;

这个有点小难。

(4）用户刘星对职工表有SELECT权限，对工资字段具有更新权限。

grant select,update(Esalary)
on Employee
to 刘星;

(5）用户张新具有修改这两个表的结构的权限。

grant alter
on Employee
to 张新;
grant alter
on Department
to 张新; --同理只能一个表一个表授权

(6）用户周平具有对两个表的所有权限(读、插、改、删数据)，并具有给其他用户授权的权限。

grant select,update,delete,insert
on Employee
to 周平
with grant option;
grant select,update,delete,insert
on Department
to 周平
with grant option;

(7）用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。

create view v2
as
select Department.Esalary,max(Esalary),min(Esalary),avg(Esalary)
from Employee,Department
where Department.Dno=Employee.Edep
group by Employee.Edep;

grant select
on v2
to 杨兰;

先建立视图，再把这个视图授权给杨兰。

8.针对习题7中（1）~（7）的每一种情况，撤销个用户所授予的权限。

（1）

revoke select
on Department
from 王明;
revoke select
on Employee
from 王明;

(2)

revoke insert,delete
on Department
from 李勇;
revoke insert,delete
on Employee
from 李勇;

(3)

revoke select
on user
from public;
drop view v1;

(4)

revoke select,update(Salary)
on Employee
from 刘星;

(5)

revoke alter
on Employee
from 张新;
revoke alter
on Department
from 张新;

(6)

revoke select,update,delete,insert
on Employee
from 周平;
revoke select,update,delete,insert
on Department
from 周平;

(7)

revoke select
on v2
from 杨兰;
drop view v2;

总结:

  这两章的题差不多都是一些语句操作的题目，实践性很强，既夯实啦自己学的知识，也提高啦动手能力，完结。