数据库题目整理及详解（二）

¶前言

— 吴文英: 风入松·听风听雨过清明

听风听雨过清明，愁草瘗花铭。楼前绿暗分携路，一丝柳，一寸柔情。料峭春寒中酒，交加晓梦啼莺。

西园日日扫林亭，依旧赏新晴。黄蜂频扑秋千索，有当时、纤手香凝。惆怅双鸳不到，幽阶一夜苔生。

今早晚起，来到实验室大门紧闭，暗光尤凉啊，窗帘落幕，显得格外清幽；于是乎，接着昨天的数据库题目继续整理，享受这片刻安宁…

¶说明

这部分和前一片博文内容一样，在书写上的格式、规范及环境配置等，详见：数据库题目整理及详解（一），说明部分。

这一部分主要内容为：mysql数据库权限相关的SQL语句。

¶题目解析

2.考虑如下关系模式:

Employee(Eno, Ename, Birthday, Title, Salary, Dno)
Department(Dno, Dname, MgrNo, Address, Phone)

使用 SQL 提供的功能完成如下授权:

(1) 允许 WangLan 对两个关系进行任何操作,并可以将他的权限转授他人。
(2) 允许所有用户查询 Department 关系。
(3) 允许所有用户查询 Employee 关系的 Salary 属性进行修改。
(4) 允许 LiYong 对 Employee 关系的 Salary 属性进行修改。
(5) 允许 ShangHua 查询每个部门的最低、最高和平均工资。
(6) 定义一个角色 Secretary,可以对 Department 进行任何操作,对 Employee
除了不能修改 Salary 属性值之外,可以进行任何操作。
(7) LiHua 是秘书,拥有角色 Secretary 的权限。
(8) 回收(1)-(4)的权限。
(9) 不允许角色 Secretary 修改 Employee 的 Title 属性值。

¶初始化: 创建数据库、建表、插入数据

1，创建数据库：

1 2	create database emp; ## 创建名为：emp的数据库 use emp; ## 使用emp数据库

2，创建department数据表：

create table department (    ## 创建department表
dno char(8) primary key,
dname char(16) not null,
mgrno int, 
address char(36),
phone char(11));

注意到，在创建department表时，最后一个属性phone当时错误写为：phont了（详见配图），修改表属性使用如下操作：

## 修改写错的表属性：phone
alter table department change phont phone char(11);  

## 查看表结构
desc department;

3，在department表中插入数据：

insert into department (dno, dname, mgrno, address, phone) values ('d0001', 'dname001', 'mgrno01', 'add001', '18839952431');

insert into department (dno, dname, mgrno, address, phone) values ('d0002', 'dname002', 'mgrno02', 'add002', '18238952440');

insert into department (dno, dname, mgrno, address, phone) values ('d0003', 'dname003', 'mgrno03', 'add003', '18738992450');

select * from department;     ## 查看一下插入的数据

4，创建employee数据表

create table employee (
eno char(8) not null,
ename char(16) not null,
birthday date,
title char(22),
salary int,
dno char(8),
primary key (eno),
FOREIGN KEY (dno) REFERENCES department(dno));

5，在employee表中插入数据：

insert into employee (eno, ename, birthday, title, salary, dno) values ('e0001', 'wanglan', '1992-02-04', 'jingli', 8000, 'd0001');

insert into employee (eno, ename, birthday, title, salary, dno) values ('e0002', 'liyong', '1989-03-14', 'guanliyuan', 10000, 'd0002');

insert into employee (eno, ename, birthday, title, salary, dno) values ('e0003', 'shanghua', '1988-10-18', 'cto', 20000, 'd0003');

select * from employee;     ## 查看一下插入的数据

6，添加用到的用户：

## 添加数据库用户：wanglan
insert into mysql.user(host, user, password) values('localhost', 'wanglan', password('wanglan123'));

## 添加数据库用户：liyong
insert into mysql.user(host, user, password) values('localhost', 'liyong', password('liyong123'));

## 添加数据库用户：shanghua
insert into mysql.user(host, user, password) values('localhost', 'shanghua', password('shanghua123'));

## 注意mysql.user表的host字段，若值为：localhost，表示本地用户；
若值为：%，表示外网用户；

到这里先前的准备工作就做完了…

¶题目解答

这里再次贴一下题目内容吧:

(1) 允许 WangLan 对两个关系进行任何操作, 并可以将他的权限转授他人。

1
2
3

grant all privileges on emp.* to wanglan@localhost identified by 'wanglan123' with grant option;

## 注意mysql中要严格添加完整的操作哪个数据库的哪个表, 并且用户需要缀上host主机号, 如上:wanglan@localhost 或 wanglan@'%'外网用户.

(2) 允许所有用户查询 Department 关系。

1	grant select on emp.department to public;

(3) 允许所有用户查询 Employee 关系的 Salary 属性进行修改。

## 我们需要在 employee 上创建一个视图 emp_view:
create view emp_view as select ename, title, dno from employee;

## 然后,将emp_view上的查询权限授予所有用户:
grant select on emp_view to public;

(4) 允许 LiYong 对 Employee 关系的 Salary 属性进行修改。

## 注意, 更新用户权限后,应该刷新一下权限列表:
flush privileges; 

grant update(salary) on table emp.employee to liyong@localhost;

(5) 允许 ShangHua 查询每个部门的最低、最高和平均工资。

## 我们需要在 employee 上创建一个视图 deptsalary:
create view deptsalary as select dname, min(salary) minsalary, max(salary) maxsalary, avg(salary) avgsalary from employee e, department d where e.dno=d.dno group by dname;

## 然后,将deptsalary上的查询权限授予所有用户:
grant select on deptsalary to shanghua@localhost;

(6) 定义一个角色 Secretary,可以对 Department 进行任何操作,对 Employee
除了不能修改 Salary 属性值之外,可以进行任何操作。

## 首先创建一个角色 Secretary:
CREATE ROLE Secretary;

## 然后,对角色 Secretary 授权:
GRANT ALL PRIVILIGES ON Department TO Secretary;
GRANT SELECT, DELETE ON Employee TO Secretary;
GRANT UPDATE(Eno, Ename, Birthday, Title, Dno)
ON TABLE Employee TO Secretary;

## 注意,mysql数据库并不需要配置**角色**, 一般都是通过创建用户分配权限完成, 这里给出的时SQL SERVICE版的写法.

(7) LiHua 是秘书,拥有角色 Secretary 的权限。

1	GRANT Secretary TO Lihua;

(8) 回收(1) - (4)的权限。

revoke all privileges on emp.* from wanglan@localhost;

revoke select on table emp.department from public;

revoke select on table emp_view from public;

revoke update(salary) on table emp.employee from liyong@localhost;

## 注意所有的内容都要书写完整.

(9) 不允许角色 Secretary 修改 Employee 的 Title 属性值。

1 2	## SQL SERVICE版写法 REVOKE UPDATE(Title) ON TABLE Employee FROM Secretary;

¶参考资料

[1]. http://www.blogjava.net/etlan/archive/2007/07/12/129794.html
[2]. http://blog.163.com/xychenbaihu@yeah/blog/static/13222965520113353229427/
[3]. http://www.linuxidc.com/Linux/2014-09/107422.htm
[4]. http://www.cnblogs.com/fslnet/p/3143344.html
[5]. http://my.oschina.net/wojibuzhu/blog/229094
[6]. http://www.cnblogs.com/wanghetao/p/3806888.html
[7]. http://www.2cto.com/database/201506/409608.html
[8]. http://database.51cto.com/art/201011/234543.htm
[9]. http://blog.csdn.net/andy_yf/article/details/7487519

@@@@@@ 吃饭喽, 未完待续…