【数据库系统概论】作业4 第四章 习题6|7 、第五章 习题6

要求：

1.请完成第四章习题6、7题，第五章习题6题。

题目：

第四章 数据库安全性
习题6.对下列两个关系模式∶
学生（学号，姓名，年龄，性别，家庭住址，班级号）
班级（班级号，班级名，班主任，班长）
使用GRANT语句完成下列授权功能∶
（1）授予用户U1对两个表的所有权限，并可给其他用户授权。
（2》授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。
（3）将对班级表查看权限授予所有用户。
（4）将对学生表的查询、更新权限授予角色R1。
（5）将角色R1授予用户U1，并且 UI可继续授权给其他角色。
习题7.今有以下两个关系模式∶
职工（职工号，姓名，年龄，职务，工资，部门号）
部门（部门号，名称，经理名，地址，电话号）
请用 SQL的GRANT 和REVOKE 语句（加上视图机制）完成以下授权定义或存取控制功能∶
（1）用户主明对两个表有SELECT权限。
（2）用户李勇对两个表有 INSERT和DELETE权限。
（3）每个职工只对自己的记录有SELECT权限。
（4）用户刘星对职工表有SELECT权限，对工资字段具有更新权限。
（5）用户张新具有修改这两个表的结构的权限。
（6）用户周平具有对两个表的所有权限（读、插、改、删数据）。并具有给其他用户授权的权限。
（7）用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看
每个人的工资。
第五章 数据库完整性
习题6.假设有下面两个关系模式∶
职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码∶
部门（部门号，名称，经理名，电话），其中部门号为主码。
用SQL 语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义∶
（1）定义每个模式的主码;
（2）定义参照完整性;
（3）定义职工年龄不得超过60岁。

实现内容及记录

第四章 数据库安全性

习题6.对下列两个关系模式∶

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

-- 创建班级表
CREATE TABLE C(
  cid CHAR(20) PRIMARY KEY,
  cname VARCHAR(30) NOT NULL,
  tc VARCHAR(30),
  mc VARCHAR(30)
);
-- 创建学生表
CREATE TABLE S(
  id CHAR(20) PRIMARY KEY,
  sname VARCHAR(30) NOT NULL,
  age INT,
  sex CHAR(2),
  address VARCHAR(100),
  cid CHAR(20),
  FOREIGN KEY (cid) REFERENCES C(cid)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE USER U1 FOR LOGIN U_1; -- 新建用户U1
CREATE USER U2 FOR LOGIN U_2; -- 新建用户U2
CREATE ROLE R1; -- 新建角色R1
1
2
3

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

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

GRANT ALL PRIVILEGES 
ON C,S
TO U1
WITH GRANT OPTION;
1
2
3
4

一次授权在SqlServer2019中无法实现，通过分开执行实现：

-- 授权学生表给用户U1并允许给其他用户授权
GRANT ALL PRIVILEGES 
ON C
TO U1
WITH GRANT OPTION;
-- 授权学生表给用户U1并允许给其他用户授权
GRANT ALL PRIVILEGES 
ON S
TO U1
WITH GRANT OPTION;
1
2
3
4
5
6
7
8
9
10

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

GRANT SELECT,UPDATE(address)
ON S
TO U2;
1
2
3

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

GRANT SELECT
ON C
TO PUBLIC;
1
2
3

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

GRANT SELECT,UPDATE
ON S
TO R1;
1
2
3

（5）将角色R1授予用户U1，并且 UI可继续授权给其他角色。

课本上写的本应该实现的语句：

GRANT R1
TO U1
WITH GRANT OPTION;
1
2
3

在SQLserver2019中无法实现，使用下面的语句：

EXEC sp_addrolemember 'R1','U1';
1

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

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

CREATE TABLE Department(
  sdeptnum CHAR(6) PRIMARY KEY,
  dname CHAR(5) NOT NULL,
  manager CHAR(8) NOT NULL,
  address CHAR(15),
  tel CHAR(11)
);
CREATE TABLE Staff(
  sno CHAR(11) PRIMARY KEY,
  sname CHAR(10) UNIQUE NOT NULL,
  sage INT,
  sjob CHAR(5),
  salary INT,
  sdeptnum CHAR(6) REFERENCES  Department(sdeptnum)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

请用 SQL的GRANT 和REVOKE 语句（加上视图机制）完成以下授权定义或存取控制功能∶

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

GRANT SELECT 
ON Staff
TO "王明";

GRANT SELECT
ON Department
TO "王明";
1
2
3
4
5
6
7

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

GRANT INSERT,DELETE
ON Staff
TO "李勇";

GRANT INSERT,DELETE
ON Department
TO "李勇";
1
2
3
4
5
6
7

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

-- 建立可查询的视图
CREATE VIEW USERS
AS
SELECT *
FROM Staff
WHERE Staff.Sno=USER;
-- 授权用户查询视图
GRANT SELECT
ON USERS
TO PUBLIC;
1
2
3
4
5
6
7
8
9
10

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

GRANT SELECT,UPDATE(salary)
ON Staff
TO "刘星";
1
2
3

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

GRANT ALTER
ON Staff
TO "张新";
GRANT ALTER
ON Department
TO "张新";
1
2
3
4
5
6

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

GRANT ALL
ON Staff
TO "周平"
WITH GRANT OPTION;

GRANT ALL
ON Department
TO "周平"
WITH GRANT OPTION;
1
2
3
4
5
6
7
8
9

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

-- 创建视图
CREATE VIEW Salary_D(dname,maxsal,minsal,avgsal)
AS
SELECT Department.dname,MAX(Staff.salary),MIN(Staff.salary),AVG(Staff.salary)
FROM Staff,Department
WHERE Staff.sno=Department.sdeptnum
GROUP BY Staff.sno,Department.dname;
-- 查询视图
SELECT * FROM Salary_D;
-- 授权视图的SELECT给杨兰
GRANT SELECT
ON Salary_D
TO "杨兰";
1
2
3
4
5
6
7
8
9
10
11
12
13

第五章 数据库完整性

习题6.假设有下面两个关系模式∶

职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码∶
部门（部门号，名称，经理名，电话），其中部门号为主码。

CREATE TABLE Departments(
  sdeptnum CHAR(6),
  dname CHAR(5),
  manager CHAR(8),
  tel CHAR(11)
);
CREATE TABLE Staffs(
  sno CHAR(11),
  sname CHAR(10),
  sage INT,
  sjob CHAR(5),
  salary INT
);
1
2
3
4
5
6
7
8
9
10
11
12
13

用SQL 语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义∶

（1）定义每个模式的主码;（2）定义参照完整性;（3）定义职工年龄不得超过60岁。

CREATE TABLE DEPARTMENT(
  ID CHAR(15) PRIMARY KEY,
  Name CHAR(10) UNIQUE NOT NULL,
  Manager CHAR(10) NOT NULL,
  Location SMALLINT,
  PhoneNumber CHAR(11),
);
CREATE TABLE EMPIOYEE(
  ID CHAR(15) PRIMARY KEY,
  Name CHAR(10) NOT NULL,
  Age SMALLINT CHECK(Age<=60),
  Salary Money,
  Job CHAR(20),
  DepartmentId CHAR(15) FOREIGN KEY
  REFERENCES Department(ID) ON DELETE CASCADE
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17