【数据库系统设计】DBMS的数据库保护

DBMS的数据库保护

一、 实验目的和要求

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

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

硬件:微机
软件:MySQL 5.6及以上版本

实验前言

这次实验是建立在第一次实验的数据库及表的基础上:【数据库系统设计】SQL语言实验

如果做过第一次实验的话,直接跳过往下看即可。

如果没有做过第一次实验,请用以下SQL快速完成建表与插入数据的操作。


创建 SPJ 数据库:

CREATE DATABASE SPJ210224;

USE SPJ210224; #进入刚创建的数据库

创建四个基表 S、P、J、SPJ:这里就直接把插入信息后的表的SQL语句放出来了。

创建S表的SQL:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `s210224`;
CREATE TABLE `s210224` (
  `SNO` char(20) NOT NULL,
  `SNAME` char(20) DEFAULT NULL,
  `STATUS` int(20) DEFAULT NULL,
  `CITY` char(20) DEFAULT NULL,
  PRIMARY KEY (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `s210224` VALUES ('S1', '精益', '20', '天津');
INSERT INTO `s210224` VALUES ('S2', '盛锡', '10', '北京');
INSERT INTO `s210224` VALUES ('S3', '东方红', '30', '北京');
INSERT INTO `s210224` VALUES ('S4', '丰泰盛', '20', '天津');
INSERT INTO `s210224` VALUES ('S5', '为民', '30', '上海');

创建P表的SQL:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `p210224`;
CREATE TABLE `p210224` (
  `PNO` char(20) NOT NULL,
  `PNAME` char(20) DEFAULT NULL,
  `COLOR` char(20) DEFAULT NULL,
  `WEIGHT` int(20) DEFAULT NULL,
  PRIMARY KEY (`PNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `p210224` VALUES ('P1', '螺母', '红', '12');
INSERT INTO `p210224` VALUES ('P2', '螺母', '绿', '17');
INSERT INTO `p210224` VALUES ('P3', '螺丝刀', '蓝', '14');
INSERT INTO `p210224` VALUES ('P4', '螺丝刀', '红', '14');
INSERT INTO `p210224` VALUES ('P5', '凸轮', '蓝', '40');
INSERT INTO `p210224` VALUES ('P6', '齿轮', '红', '30');

创建J表的SQL:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `j210224`;
CREATE TABLE `j210224` (
  `JNO` char(20) NOT NULL,
  `JNAME` char(20) DEFAULT NULL,
  `CITY` char(20) DEFAULT NULL,
  PRIMARY KEY (`JNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `j210224` VALUES ('J1', '三建', '北京');
INSERT INTO `j210224` VALUES ('J2', '一汽', '长春');
INSERT INTO `j210224` VALUES ('J3', '弹簧厂', '天津');
INSERT INTO `j210224` VALUES ('J4', '造船厂', '天津');
INSERT INTO `j210224` VALUES ('J5', '机车厂', '唐山');
INSERT INTO `j210224` VALUES ('J6', '无线电厂', '常州');
INSERT INTO `j210224` VALUES ('J7', '半导体厂', '南京');

创建SPJ表的SQL:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `spj210224`;
CREATE TABLE `spj210224` (
  `SNO` char(20) NOT NULL,
  `PNO` char(20) NOT NULL,
  `JNO` char(20) NOT NULL,
  `QTY` int(20) NOT NULL,
  KEY `SNO` (`SNO`),
  KEY `PNO` (`PNO`),
  KEY `JNO` (`JNO`),
  CONSTRAINT `spj210224_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `s210224` (`SNO`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `spj210224_ibfk_2` FOREIGN KEY (`PNO`) REFERENCES `p210224` (`PNO`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `spj210224_ibfk_3` FOREIGN KEY (`JNO`) REFERENCES `j210224` (`JNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `spj210224` VALUES ('S1', 'P1', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S1', 'P1', 'J3', '100');
INSERT INTO `spj210224` VALUES ('S1', 'P1', 'J4', '700');
INSERT INTO `spj210224` VALUES ('S1', 'P2', 'J2', '100');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J1', '400');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J2', '200');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J4', '500');
INSERT INTO `spj210224` VALUES ('S2', 'P3', 'J5', '400');
INSERT INTO `spj210224` VALUES ('S2', 'P5', 'J5', '400');
INSERT INTO `spj210224` VALUES ('S2', 'P5', 'J2', '100');
INSERT INTO `spj210224` VALUES ('S3', 'P1', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S3', 'P3', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S4', 'P5', 'J1', '100');
INSERT INTO `spj210224` VALUES ('S4', 'P6', 'J3', '300');
INSERT INTO `spj210224` VALUES ('S4', 'P6', 'J4', '200');
INSERT INTO `spj210224` VALUES ('S5', 'P2', 'J4', '100');
INSERT INTO `spj210224` VALUES ('S5', 'P3', 'J1', '200');
INSERT INTO `spj210224` VALUES ('S5', 'P6', 'J2', '200');
INSERT INTO `spj210224` VALUES ('S5', 'P6', 'J4', '500');

三、 实验原理及内容

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

简单的前置知识


简单了解一下创建用户的SQL语句:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  • username:你将创建的用户名
  • host:指定该用户在哪个主机上可以登陆,如果是本地用户可用 localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符 %
  • password:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

但是对我们本次实验来说只需要创建一个用户即可,因此命令就像下面这么简单。

CREATE USER 'U1210224';

TIPS:删除用户的SQL。

DROP USER 'U1210224';

然后再教大家一个很实用的指令:如何查看当前用户信息

用户信息是存储在 mysql 数据库下的 user 表中的,我们只需要查询这个表的信息即可。但是这个表中的字段特别多,如果直接 SELECT * FROM mysql.user 有惊喜。。。。

我们只需要查看用户名,用以下语句即可:

SELECT user FROM mysql.user;

可以看见当前只有3个用户,都是 mysql 中默认创建的。
【数据库系统设计】DBMS的数据库保护


登录 MySQL 开始做实验:WIN + R调出运行,输入cmd,调出命令行。

mysql -u root -p
Enter password: 1234

1.创建用户U1和U2

了解了上面的知识以后,我们只需要创建一个用户,密码为空即可。

CREATE USER U1210224;
CREATE USER U2210224;

创建完以后可以查询一下当前用户:

SELECT user FROM mysql.user;

【数据库系统设计】DBMS的数据库保护

2. 对实验1的SPJ(也是代称)表,用授权机制完成以下存取控制

具体原理可以参见:【数据库系统设计】数据库安全性

(1) 使用户U1具有对供应商代码sno、供应数量qty的查询权限。

注意,要先进入SPJ数据库才能授权:

use SPJ210224;

使用户U1具有对供应商代码sno、供应数量qty的查询权限。

GRANT SELECT(sno, qty)
ON TABLE spj210224
TO U1210224;

【数据库系统设计】DBMS的数据库保护

(2) 使用户U1拥有对表SPJ的查询、删除权限和对供应数量qty的修改权限,并具备转授这些权限的权力。

GRANT SELECT, DELETE, UPDATE(qty)
ON TABLE spj210224
TO U1210224
WITH GRANT OPTION; #允许他再将此权限授予其他用户

【数据库系统设计】DBMS的数据库保护

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

在(2)中我们的SQL语句使得U1具备转授权限的能力,来尝试一下。

首先要切换到 U1 用户:由于之前没有设置密码,直接登陆即可。
可以用 exit 退出当前用户,建议新打开一个命令行窗口,同时操作两个用户

登录 U1用户:

mysql -u U1210224 # 这里不要加分号

【数据库系统设计】DBMS的数据库保护
进入 spj 数据库。

use SPJ210224;

用户U1授予用户U2对表SPJ的删除权限。

GRANT DELETE
ON TABLE spj210224
TO U2210224;

【数据库系统设计】DBMS的数据库保护

(4) 回收用户U1和用户U2对SPJ的删除权限。

重新登录回 root 用户:

mysql -u root -p1234

进入 spj 数据库。

use SPJ210224;

回收用户U1和用户U2对SPJ的删除权限。

REVOKE DELETE
ON TABLE spj210224
FROM U1210224, U2210224;

【数据库系统设计】DBMS的数据库保护

3. 观察多事务并发时的数据库保护

简单了解 autocommit

set autocommit 1|0;

1:mysql 默认为1,表示开启自动提交
0:表示没有开启自动提交

如果没有开启自动提交,当前 user1 所连接的 mysql 的所有操作都会当成一个事务直到你输入rollback 或者 commit;当前事务才算结束。

当前事务结束前新的 mysql 连接时无法读取到任何 user1 的操作的结果的。

如果开起了,mysql 会把每个 sql 语句当成一个事务然后自动的 commit

当然无论开启与否,start transaction commit|rollback 都是独立的事务。

(1)分别以root和自己的用户连接MySQL服务器,并分别输入命令SET AUTOCOMMIT=0;

以 root 连接 MySQL 服务器:

mysql -u root -p1234
########################
SET AUTOCOMMIT = 0;

【数据库系统设计】DBMS的数据库保护
以 U1 连接 MySQL 服务器:

mysql -u U1210224
########################
SET AUTOCOMMIT = 0;

【数据库系统设计】DBMS的数据库保护

(2)root将表SPJ*应商s1所供应的零件数量(qty)都增加50,此即事务1。

UPDATE spj210224
SET qty = qty + 50;

【数据库系统设计】DBMS的数据库保护

(3)自己的用户将表SPJ*应商s1所供应的零件数量(qty)都增加30,此即事务2。

UPDATE spj210224
SET qty = qty + 30;

【数据库系统设计】DBMS的数据库保护

(4)观察发生什么现象,分析为什么,找出解决办法。

两个用户分别查询 spj 表 的 qty 字段。

SELECT qty FROM spj210224;

很明显的发现,两个用户查询结果不同。
【数据库系统设计】DBMS的数据库保护
为什么:由于我们设置了 set autocommit 0;,因此数据库的事务不会自动提交,因此我们先执行事务1后,事务1会给spj表加X锁,此时事务2若想对数据进行操作将处于等待状态。

解决方案要么提交 commit、要么回滚 rollback

  • commit:意味着事务1将会提交,并且表中的数据成功 + 50;
  • rollback:意味着事务1将会回滚,表中数据不会有任何变化。

具体看下面的(5)。

(5)root分别输入commit和rollback命令时,观察两个用户界面的信息及变化,并用select语句查看表SPJ中的qty属性值的变化,注意观察在root事务commit(rollback)前后以及自己的用户事务commit后qty属性的值的变化情况。

commimt

root 用户执行提交操作:

commit;

【数据库系统设计】DBMS的数据库保护

rollback

root 用户执行回滚操作:

rollback;

root 用户执行查询,可以发现 spj 的数据回滚了!!!
【数据库系统设计】DBMS的数据库保护
在 U1 用户执行查询的话,会发现数据从未变过。。。因为ROOT用户的事务1从未提交过,而是直接回滚,因此对U1用户来说,ROOT用户相当于没有执行过任何操作。

上一篇:Java Web Services (1) - 第1章 Web服务快速入门


下一篇:2020-11-25