【数据库系统原理作业】十二、第四章作业题

第四章作业题

2021.4.18

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

学生（学号、班级、年龄、性别、家庭住址、班级号）
  班级（班级号，班级名，班主任，班长）。

使用GRANT语句完成以下授权功能

根域题意，先在数据库中建立两个新的关系模式学生(Students)和班级(Class)

create table Class--课程表
(ClassNum int primary key,
ClassName char(5) unique not null,
HeadTeacher char(5) not null,
Monitor char(5)
);
go--下一个批处理
create table Students--学生表
(Id char(11)primary key,
Name char(5) not null,
Age smallint check(age>0 and age<100) not null,
Sex char(2) check(sex='男' or sex='女'),
Address char(20),
ClassNum int foreign key
references Class(ClassNum) on delete cascade);

以及建立用户:

create login U_1 with password='123456';--创建登录名
create user U1 for login U_1; --创建用户名
create login U_2 with password='123456';
create user U2 for login U_2; 

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

grant all privileges
on table Class
to U1
with grant option;
grant all privileges
on table Students
to U1
with grant option;

TSQL:

grant all privileges
on Class
to U1
with grant option;
grant all privileges
on Students
to U1
with grant option;

执行结果:

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

grant select,update(Address)
on table Students
to U2;

TSQL:

grant select,update(Address)
on Students
to U2;

执行结果:


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

标准SQL:

grant select
on table Class
to public;

TSQL:

grant select
on Class
to public;
--对于所有用户

执行结果:

(4）将对学生表的查询、更新权限授予角色R1.
标准SQL:

grant select,update
on table Students
to R1;

TSQL：

grant select,update
on Students
to R1;

(5）将角色R1授予用户U1,并且U1可继续授权给其他角色。
标准SQL:

grant R1
to U1
with admin option;

TSQL:

EXEC sp_addrolemember 'R1','U1';

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

职工（职工号，姓名，年龄，职务，工资，部门号）
部门（部门号，名称，经理名，地址，电话号）

用SQL的GRANT和REVOKE语句（加上视图机制）完成以下授权定义或存取控制功能
先建立实验需要的部门表(Department)和职工表(Employee)

create table Department(
Id char(11) primary key,
Name char(5) unique not null,
Manager char(5) not null,
PhoneNumber char(11)
);
create table Employee(Id char(11) primary key,
					  Name char(5) not null,
					  Age smallint check(Age>0 and Age<100),
					  Salary Money,
					  DepartmentId char(11) references Department(Id)
					  on delete cascade);

建立用户:

create login 王明1 with password='123456';
create user 王明 for login 王明1;
create login 李勇1 with password='123456';
create user 李勇 for login 李勇1;
create login 刘星1 with password='123456';
create user 刘星 for login 刘星1;
create login 张新1 with password='123456';
create user 张新 for login 张新1;
create login 周平1 with password='123456';
create user 周平 for login 周平1;
create login 杨兰1 with password='123456';
create user 杨兰 for login 杨兰1;

(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 Users
as
select *
from Employee
where Employee.Id=USER

创建好试图后，员工使用自己的用户名登录，直接从视图中查询，就只能看到自己的信息。

grant select
on Users
to public;

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

grant select,update(Salary)
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;

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

create view demoney(Id,Max_salary,Min_salary,Avg_salary)--先创建一个视图
as select Department.Id,max(Salary),min(Salary),Avg(Salary)
from Department,Employee
where Department.Id = Employee.DepartmentId
group by Department.Id;
go
grant select
on demoney
to 杨兰;

执行结果:

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

下面的用的都是TSQL，标准SQL在on 后面 加一个table 就好了

--(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 Users
from public;
drop view Users;
--(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 demoney
from 杨兰;
drop view demoney;

最近学的句子有点多，很多认为以及掌握的句子还是会忘，还要加强记忆