数据库作业十二-第四章课后题

最新推荐文章于 2023-04-23 20:40:30 发布

原创最新推荐文章于 2023-04-23 20:40:30 发布 · 1.5k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

本文详细介绍了如何在SQL数据库中创建表、定义用户权限，包括授予和撤销不同类型的权限，如SELECT、INSERT、UPDATE、DELETE等，以及如何通过视图限制数据访问，确保数据的安全性和合规性。

6、

先创建要用到的两张表

CREATE TABLE Class
(
  ClassNum int PRIMARY KEY ,
  ClassName nvarchar(5) UNIQUE NOT NULL,
  HeadTeacher nvarchar(5) CHECK(len(HeadTeacher)>1) NOT NULL,
  Monitor nvarchar(11) UNIQUE  
);

CREATE TABLE Students
(
  ID varchar(11) PRIMARY KEY,
  SName nvarchar(5) CHECK(len(SName)>1) NOT NULL,
  Age SMALLINT CHECK(Age>0 AND Age<120) NOT NULL,
  Gender nvarchar(1) CHECK(Gender=N'男' OR Gender=N'女'),
  HomeAddress nvarchar(50),
  ClassNum int FOREIGN KEY REFERENCES Class (ClassNum) ON DELETE CASCADE
);

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

GRANT ALL PRIVILEGES
ON Students
To U1
WITH GRANT OPTION

GRANT ALL PRIVILEGES
ON Class
To U1
WITH GRANT OPTION

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

GRANT SELECT,UPDATE(HomeAddress)
ON Students
TO U2

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

GRANT SELECT
ON Students
TO PUBLIC

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

GRANT SELECT,UPDATE
ON Students
TO R1

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

ALTER ROLE R1 ADD MEMBER U1

7、

仍先创建要用到的表

CREATE TABLE Department
(
	ID varchar(7) PRIMARY KEY,
	DName nvarchar(5) CHECK(len(DName)>2) UNIQUE NOT NULL,
	Manager nvarchar(5) NOT NULL,
	DeAddress nvarchar(8),
	PhoneNumber varchar(11)
)

CREATE Table Employee
(
	ID varchar(11) PRIMARY KEY,
	EName nvarchar(5) CHECK(len(EName)>1) NOT NULL,
	Age SMALLINT CHECK(Age>0 ANd Age<120),
	Salary Money,
	DepartmentID varchar(7) REFERENCES Department(ID) ON DELETE CASCADE
)

（1）用户U3对两个表有SELECT权限

GRANT SELECT
ON Department
TO U3
GRANT  SELECT
ON Employee
To U3

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

GRANT INSERT,DELETE
ON Department
To U4
GRANT INSERT,DELETE
ON Employee
TO U4

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

--先创建相应的视图，并且把员工ID作为员工的用户名
CREATE VIEW VW_User
AS
SELECT *
FROM Employee
WHERE Employee.ID=USER

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

插入两条数据
在这里插入图片描述

使用U1登录
在这里插入图片描述

在U1中执行查询
(画着红线不是报错，是SSMS没还没反应过来，)
在这里插入图片描述
换成U2登录

并查询
这时不像上一个画着红线警告了。

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

GRANT SELECT,UPDATE(Salary)
ON Employee
TO U5

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

GRANT ALTER
ON Employee
TO U6
GRANT ALTER
ON Department
TO U6

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

GRANT SELECT,UPDATE,DELETE,INSERT
ON Department
To U7 
WITH GRANT OPTION

GRANT SELECT,UPDATE,DELETE,INSERT
ON Employee
To U7 
WITH GRANT OPTION

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

CREATE VIEW VW_Salary_Level
AS 
SELECT MAX(Salary) as N'最高工资',MIN(Salary) as N'最低工资',AVG(Salary) as N'平均工资'
FROM Employee
GROUP BY DepartmentID

GRANT SELECT
ON VW_Salary_Level
TO U7

针对习题7中（1）~（7）的每一种情况，撤销各用户所授予的权限
（1）收回用户U3对两个表的SELECT权限。

REVOKE SELECT
ON Department
FROM U3
REVOKE  SELECT
ON Employee
FROM U3

（2）收回用户U4对两个表的INSERT和DELETE权限。

REVOKE INSERT,DELETE
ON Department
FROM U4
REVOKE INSERT,DELETE
ON Employee
FROM U4

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

REVOKE SELECT
ON VW_User
FROM PUBLIC
--同时删除视图
DROP VIEW VW_User

（4）收回用户U5对职工表的SELECT权限，对工资字段的更新权限。

REVOKE SELECT,UPDATE(Salary)
ON Employee
FROM U5

（5）收回用户U6的修改这两个表的结构的权限。

REVOKE ALTER
ON Employee
FROM U6
REVOKE ALTER
ON Department
FROM U6

（6）收回用户U7对两个表的所有权限

REVOKE SELECT,UPDATE,DELETE,INSERT
ON Department
FROM U7 
REVOKE SELECT,UPDATE,DELETE,INSERT
ON Employee
FROM U7

（7）收回用户U7的从每个部门职工中 SELECT 最高工资、最低工资、平均工资的权限

REVOKE SELECT
ON VW_Salary_Level
FROM U7
DROP VIEW VW_Salary_Level