南京邮电大学数据库实验二（DBMS的数据库保护）_南邮数据库实验二约束,师徒

一、实验目的和要求

(1) 巩固事务的概念
(2) 正确理解并发及锁机制
(3) 熟悉DBMS的安全控制

二、实验环境(实验设备)

硬件：微型计算机
软件：DBMS产品任选

三、实验原理及内容

实验原理基于第4章、第10章以及第11章的相关内容。

(1) DBMS的数据库保护功能

DBMS的数据库保护功能包括：安全控制、完整性控制、并发控制、备份与恢复等。 后两项都属于事务管理。安全控制防止非法用户对数据库的访问和破坏；完整性控制防止合法用户错误地操作数据库；事务是DBMS的最小执行单位，由有限的操作序列组成，事务的执行和调度要满足ACID（原子性、一致性、隔离性和持久性）准则；并发控制使得ACID在多事务并发时仍得到满足；备份与恢复使得ACID在故障时仍得到满足。完整性控制已在实验一中体现，本实验重点体会安全控制和并发控制。

(2) 安全控制中的访问控制机制

用户和权限是访问控制机制的两个主要概念。
三类用户：一般数据库用户（connect）、具有支配部分数据库资源特权的数据库用户（resource）和具有数据库管理员特权的数据库用户（DBA），只有DBA具有创建用户的权力。
两大类权限：系统权限和对象权限。系统权限由DBA授予；对象权限是对数据对象的操作权，由DBA或对象的拥有者授予。
授权语句：权限的授予（GRANT）、转授（WITH GRANT OPTION）、回收（REVOKE）。

(3) 事务的提交与回滚

事务中的提交（COMMIT）是提交事务的所有操作。具体说就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，事务正常结束。
事务中的回滚（ROLLBACK）是数据库回到事务开始时的状态。具体地说就是，
实 验 报 告
在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的更新操作全部撤消，使数据库回滚到事务开始时的状态。

(4) 并发控制的锁机制

并发控制的锁机制：先加锁，后操作；常用的锁类型：X（排他型）、S（共享型）。
在一个事务拥有某个数据的X锁期间，其它事务不能对此数据做任何操作。
当某事务拥有某个数据的S锁期间，允许其它事务读(select)此数据，不允许任何更新。

三、实验内容

1.以root账户登录数据库管理系统，创建用户U1和U2，密码自定。

CREATE USER U1 IDENTIFIED BY 'U1';
CREATE USER U2 IDENTIFIED BY 'U2';

SELECT USER FROM mysql.USER;
1
2
3
4

2.创建数据库test2，在test2中创建emp表，并输入一些数据记录，如下图要求所示：

图1 emp表结构
图2 emp表中记录

CREATE TABLE emp(
    EMPNO INT,
    ENAME varchar(10),
    JOB VARCHAR(9),
    MGR DOUBLE,
    HIREDATE DATE,
    SAL DOUBLE,
    COMM DOUBLE,
    DEPTNO INT,
    PRIMARY KEY (EMPNO)
);

INSERT INTO emp VALUES (7369, 'SMITH',  'CLERK',    7902, '1980-12-17', 800,    NULL,   20);
INSERT INTO emp VALUES (7499, 'ALLEN',  'SALESMAN', 7698, '1981-02-20', 1600,   300,    30);
INSERT INTO emp VALUES (7521, 'WARD',   'SALESMAN', 7698, '1981-02-22', 1250,   500,    30);
INSERT INTO emp VALUES (7566, 'JONES',  'MANAGER',  7839, '1981-04-02', 2975,   NULL,   20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250,   1400,   30);

SELECT * FROM emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3.对emp表，用授权机制完成以下存取控制，并验证存取控制是否实现。

(1) 授予用户U1和U2具有对emp表的员工编号empno、姓名ename和所在部门deptno的查询权限。

 # (1) 授予用户 U1 和 U2 具有对 emp表 的员工编号 empno、姓名 ename 和所在部门 deptno 的查询权限。
GRANT SELECT(EMPNO, ENAME, DEPTNO) ON test.emp TO U1;
GRANT SELECT(EMPNO, ENAME, DEPTNO) ON test.emp TO U2;
1
2
3

(2) 使用户U1拥有对emp表的删除权限和对奖金（comm）的修改权限，并具备转授这些权限的权力。

# (2) 使用户 U1 拥有对表 EMP 的删除权限和对奖金（COMM）的修改权限，并具备转授这些权限的权力。
GRANT DELETE, UPDATE(COMM) ON emp TO U1 WITH GRANT OPTION;

1
2
3

(3) 用户U1授予用户U2对emp表的删除权限。

# (3) 用户 U1 授予用户 U2 对 emp表 的删除权限。
GRANT DELETE ON emp TO U2;
1
2

(4) 分别以用户U1和U2身份登录，自行设计操作用例验证(1)- (3)设置的权限。
①验证用户U1具有对emp表的员工编号empno、姓名ename和所在部门deptno的查询权限。

# 验证用户 U1 具有对 emp表 的员工编号 empno、姓名ename 和所在 部门deptno 的查询权限。
SELECT EMPNO, ENAME, DEPTNO FROM emp;
1
2
3

②验证用户U2具有对emp表的员工编号empno、姓名ename和所在部门deptno的查询权限。

# 验证用户 U2 具有对 emp表 的员工编号 empno、姓名ename 和所在 部门deptno 的查询权限。
SELECT EMPNO, ENAME, DEPTNO FROM emp;
1
2
3

③验证U1拥有对emp表的删除权限和对奖金（comm）的修改权限

 # 验证用户U1授予用户U2对emp表的删除权限。
SHOW GRANTS FOR U2;
1
2

④验证用户U1授予用户U2对emp表的删除权限。

# 验证用户U1授予用户U2对emp表的删除权限。
SHOW GRANTS FOR U2;
1
2

(5) 回收用户U1和用户U2对emp表的删除权限。

(6) 分别以用户U1和U2身份登录，自行设计操作用例验证(4)设置的删除权限的回收。
①验证删除权限的回收

3．为用户U1授予对emp表的所有操作权限。

## 为用户U1授予对emp表的所有操作权限。
GRANT ALL PRIVILEGES ON EMP TO U1 WITH GRANT OPTION;
1
2

4．观察多事务并发时的数据库保护，同时查看两个用户窗口。

(1) 在root窗口和U1窗口都设置：关闭自动提交，启用事务。

(2) 在root中将emp表中30号部门所有员工的工资 (sal)增加200元，此即事务1。

(3) 在root中查询7521号员工的sal。

(4) 在U1中将emp表中的工号以75开头的员工工资增加300元，此即事务2。

(5) 在root中确认提交结束事务1。

(6) 在U1中确认提交结束事务2。

(7) 在两个窗口使用select语句查看sal值的变化。

(8) 使用rollback命令进行测试：数据回退，用于反复测试。

四、实验小结（包括问题和解决方法、心得体会、意见与建议等）

（一）实验中遇到的主要问题及解决方法

在创建用户U1，U2时出错，切换root后重新创建。

（二）实验心得

学会了多用户修改数据库

（三）意见与建议（没有可省略）