数据库实验： 安全性语言及多用户事务管理

一、实验内容
1、创建用户user_1和user_2，密码都为1234
2、将用户user_2的名称修改为user_3，并将其密码修改为123456
3、以user_1身份登陆数据库
4、授予用户user_1对DBEM数据库中employee表的查询、插入、修改、删除等权限
5、授予用户user_1对salary表的查询权限，并允许其将权限授予其他用户，然后用user_1登陆数据库并将salary表的查询权限授予user_3
6、回收user_1的employee表上的select权限
7、实验准备
给salary表增加一条记录
insert into salary(employeeID,income,outcome)
values(1,1000,0);
创建两个超级用户clientA和clientB，分别以clientA和clientB登录，将隔离级别改为“读已提交”，取消自动提交事务的功能，改为显示事务提交，即需要执行commit命令提交事务
8、在“读已提交”隔离级别下，验证避免丢失修改的情况
① 修改该记录的income值为1200
② 分别在客户端A和客户端B执行SQL语句查询该员工的income值
③ 在客户端A执行命令修改salary中记录的income值减少500，然后查看修改后的值
④ 在客户端B执行命令修改salary中记录的income值减少500，然后查看修改后的值
⑤ 在客户端A提交事务
⑥ 在客户端B提交事务
⑦ 在客户端A和B分别执行查看命令，检查income的值
9、在“读已提交”隔离级别下，验证避免脏读的情况
① 修改该记录的income值为0
② 分别在客户端A和客户端B执行SQL语句查询员工的income值
③ 在客户端A执行命令修改salary中记录的income值为1200，然后查看修改后的值
④ 在客户端B执行命令查看income的值
⑤ 在客户端A提交事务
⑥ 在客户端B执行查看命令，检查income的值
10、在“读已提交”隔离级别下，验证不可重复读的情况
① 修改该记录的income值为1200
② 在客户端A执行命令查看salary中记录的income值
③ 在客户端B执行命令修改income的值为1300
④ 在客户端A再次读取指定员工的income值
⑤ 客户端B提交事务
⑥ 在客户端A再次执行查看命令，检查income的值
⑦ 客户端A提交事务
11、在“读已提交”隔离级别下，验证出现“幻读”的情况
① 在客户端A执行命令查看所有员工的收入记录
② 在客户端B执行命令插入一个新员工的收入记录
③ 在客户端A再次读取所有员工的收入记录
④ 客户端B提交事务
⑤ 在客户端A再次执行查看命令，读取所有员工的收入记录
⑥ 客户端A提交事务

二、实验过程
1、create user ‘user_1’@‘127.0.0.1’ identified by ‘1234’;
create user ‘user_2’@‘127.0.0.1’ identified by ‘1234’;

2、rename user ‘user_2’@‘127.0.0.1’ to’user_3’@‘127.0.0.1’;
set password for ‘user_3’@‘127.0.0.1’=password(‘123456’);

3、

4、grant all on employee to ‘user_1’@‘127.0.0.1’;

5、grant select on salary to ‘user_1’@‘127.0.0.1’
with grant option;
grant select on salary to ‘user_3’@‘127.0.0.1’;

6、revoke select on employee from ‘user_1’@‘127.0.0.1’;

7、insert into salary(employeeID,income,outcome)
values(‘1’,1000,0);
create user ‘clientA’@‘127.0.0.1’ identified by ‘123’;
grant all on dbem.* to ‘clientA’@‘127.0.0.1’;
set session transaction isolation level read committed;
set @@autocommit=0;
create user ‘clientB’@‘127.0.0.1’ identified by ‘123’;
grant all on dbem.* to ‘clientB’@‘127.0.0.1’;
set session transaction isolation level read committed;
set @@autocommit=0;

8、update salary
set income=1200
where employeeID=‘1’;
clientA：

clientB：

update salary
set income=income-500
where employeeID=‘1’;
clientA：

clientB：
无法执行修改命令，最终直接失去与数据库的连接

Commit之后
ClientA：

clientB：

9、update salary
set income=0
where employeeID=‘1’;

ClientA:

clientB：

update salary
set income=1200
where employeeID=‘1’;

clientA：

clientB：

commit；
clientB：

10、update salary
set income=1200
where employeeID=‘1’;
ClientA：

update salary
set income=1300
where employeeID=‘1’;
ClientA：

Commit；

clientA：

11、select income
from salary
commit;

insert into salary
set employeeID=000002,
income=2000,
outcome=100;

Commit；

三、结果分析
1、修改数据前要先执行SET SQL_SAFE_UPDATES = 0;
2、在验证避免丢失修改时，执行过程无法进行，一开始以为是哪里有错误，后来发现是正常现象
3、一开始没有以clientA，clientB的身份连接客户端，导致后续命令运行不正常