数据库系统概论第四章第7题

数据库系统概论第四章第7题

---

今有以下两个关系模式:
    职工(职工号，姓名，年龄，职务，工资，部门号)
    部门(部门号，名称，经理名，地址，电话号)
请用SQL的GRANT和REVOKE语句（加上视图机制）完成以下授权定义或存储控制功能:
(1)用户王明对两个表有SELECT权限。
(2)用户李勇对两个表有NSERT和DELETE权限。
(3)每个职工只对自己的记录有SELECT权限。
(4)用户刘星对职工表有SELECT权限，对工资字段具有更新权限。
(5)用户张新具有修改这两个表的结构的权限。
(6)用户周平具有对两个表的所有权限(读、插、改、删数据)，并具有给其他用户授权的权限。
(7)用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，她不能查看每个人的工资。

---

一些准备工作

建表:

CREATE TABLE 部门 (
    部门号 CHAR(10) PRIMARY KEY,
    名称 CHAR(16),
    经理名 CHAR(10),
    地址 CHAR(50),
    电话号 CHAR(11)
);

CREATE TABLE 职工 (
    职工号 CHAR(10) PRIMARY KEY,
    姓名 CHAR(10),
    年龄 SMALLINT,
    职务 CHAR(12),
    工资 NUMERIC(8,2),
    部门号 CHAR(10),
    FOREIGN KEY ( 部门号 ) REFERENCES 部门(部门号)
);

创建习题中涉及用户:

CREATE USER 王明 IDENTIFIED BY '123456';
CREATE USER 李勇 IDENTIFIED BY '123456';
CREATE USER 刘星 IDENTIFIED BY '123456';
CREATE USER 张新 IDENTIFIED BY '123456';
CREATE USER 周平 IDENTIFIED BY '123456';
CREATE USER 杨兰 IDENTIFIED BY '123456';

插入一些测试数据:

INSERT INTO 部门 VALUES('BM001','部门一','经理一','地址一','12345678901');
INSERT INTO 部门 VALUES('BM002','部门二','经理二','地址二','12345678902');
INSERT INTO 部门 VALUES('BM003','部门三','经理三','地址三','12345678903');

INSERT INTO 职工 VALUES('ZG001','王明',31,'职位一',11000,'BM001');
INSERT INTO 职工 VALUES('ZG002','李勇',32,'职位二',12000,'BM002');
INSERT INTO 职工 VALUES('ZG003','刘星',33,'职位三',13000,'BM003');
INSERT INTO 职工 VALUES('ZG004','张新',34,'职位四',14000,'BM002');
INSERT INTO 职工 VALUES('ZG005','周平',35,'职位五',15000,'BM003');
INSERT INTO 职工 VALUES('ZG006','杨兰',36,'职位六',16000,'BM001');

1. 用户王明对两个表有SELECT权限

GRANT SELECT 
ON TABLE 职工
TO 王明;
GRANT SELECT 
ON TABLE 部门
TO 王明;

2. 用户李勇对两个表有INSERT和DELETE权限

GRANT INSERT, DELETE
ON TABLE 职工
TO 李勇;
GRANT INSERT, DELETE
ON TABLE 部门
TO 李勇;

3. 每个职工只对自己的记录有SELECT权利

CREATE VIEW 职工视图 
AS
SELECT * 
FROM 职工 
WHERE concat(姓名,'@localhost') = USER();

GRANT SELECT 
ON 职工视图
TO 王明,李勇,刘星, 张新,周平,杨兰;

4. 用户刘星对职工表有SELECT权限, 对工资字段有更新权限

GRANT SELECT, UPDATE(工资)
ON TABLE 职工
TO 刘星;

5. 用户张新具有修改两个表结构权限

GRANT ALTER 
ON TABLE 职工
TO 张新;
GRANT ALTER
ON TABLE 部门
TO 张新;

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

GRANT ALL PRIVILEGES
ON TABLE 职工
TO 周平
WITH GRANT OPTION;
GRANT ALL PRIVILEGES
ON TABLE 部门
TO 周平
WITH GRANT OPTION;

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

CREATE VIEW 部门工资(名称,最高工资,最低工资,平均工资)
AS
SELECT `部门`.`名称`, MAX(`工资`), MIN(`工资`), AVG(`工资`)
FROM `职工`, `部门`
WHERE `职工`.`部门号` = `部门`.`部门号`
GROUP BY `职工`.`部门号`;
GRANT SELECT
ON 部门工资
TO 杨兰;