【作业】开启MySQL数据库的远程连接、创建指定权限的远程用户以及如何访问远程数据库

简介：本篇主要讲述如何在本机（Windows）开启数据库的远程连接、创建指定权限的远程用户以及如何访问远程数据库。

文章目录 作业题目：作业思路：作业过程：u1 :1、创建数据库及表结构1.1、创建数据库epidemicdb1.2、创建表(Person、NucleicTest）1.3、向表中插入相关数据 2、为指定数据库添加特定权限的用户2.1、创建u2、u3、u4用户2.2、切换到指定数据库'mysql'下，查看已创建的用户 3、设置远程访问—开放3306端口 u2:1、远程访问u1数据库并测试连接1.1、新建连接 2、验证权限的有效性2.1、显示数据库2.2、测试权限范围内的SQL2.3、测试不在权限范围内的SQL u3：1、远程访问u1数据库并测试连接2、验证权限的有效性2.1、测试权限范围内的SQL2.2、测试不在权限范围内的SQL 3、为用户4添加特定权限 u4：1、远程访问u1数据库并测试连接2、验证权限的有效性2.1、测试权限范围内的SQL2.2、测试不在权限范围内的SQL 作业总结：

作业题目：

假设某核酸检测数据库系统中有四类用户u1、u2、u3和u4，分别表示系统管理员、普通人、疫情管理人员和检测机构。

下面是对这四类用户的相关描述：用户u1创建了数据库epidemicdb，其中包含两张表：

Person( ID, name, sex, address, workunit)，各属性含义为：身份z号、性名、性别、住址和工作单位；

NucleicTest(ID, testdate, ifnegative, testorganization)，各属性含义为：身份z号、检测日期、是否阴性、检测单位，其中(ID和testdate)是主键。

用户u1在数据库epidemicdb中执行了下面的 *** 作：

grant select, insert,update on person to u2;

grant select on NucleicTest to u2;

grant all privileges on person to u3 with grant option;

grant all privileges on NucleicTest to u3 with grant option;

用户u3在数据库epidemicdb中执行了下面的 *** 作：

grant select on person to u4;

grant select, insert on NucleicTest to u4;

针对上面的描述，按下面的要求完成小组作业：

1）3-4人一组，至少每人模拟其中一个用户；

2）用户u1在自己电脑上创建数据库epidemicdb，并在该数据库下建立u2, u3和u4用户；并按题目要求执行相应授权语句；

3）用户u2、u3、u4在自己电脑上远程连接用户u1电脑上的数据库epidemicdb，并自由编写该用户权限下可以执行的 *** 作，并验证权限的有效性。

4）每个用户有多个权限，验证权限的有效性时，要求为每个权限至少编写一条SQL *** 作，其中有一条是不在权限范围内的。

5）每组提交一份作业，其中写明作业题目，并用文字和截图的形式详细展示作业过程和结果。

作业思路：

用户u1在自己电脑上创建数据库epidemicdb，并在该数据库下建立u2, u3和u4用户，并按题目要求执行相应授权 *** 作（用户u2可以对person表进行select, insert,update *** 作；用户u2可以对NucleicTest表进行selec *** 作；u3可以对person、NucleicTest表进行全部 *** 作并且可以给其它用户授权自己拥有的权限），最后设置远程连接，好让其他用户远程连接u1电脑的数据库。

用户u2在自己电脑上通过navicat连接用户u1电脑上的数据库epidemicdb，登录u2用户，验证权限的有效性。

用户u3在自己电脑上通过navicat连接用户u1电脑上的数据库epidemicdb，登录u3用户，验证权限的有效性，并且用户u3需要执行相应授权 *** 作（用户u4可以对person表进行select *** 作，用户u4可以对NucleicTest表进行select、insert *** 作）。

用户u4在自己电脑上通过navicat连接用户u1电脑上的数据库epidemicdb，登录u4用户，验证权限的有效性。

作业过程： u1 : 1、创建数据库及表结构 1.1、创建数据库epidemicdb

create database epidemicdb;

1.2、创建表(Person、NucleicTest）

create table Person (
	ID varchar(20) primary key,
	name varchar(10) not null,
	sex varchar(3) not null,
	address varchar(25) not null,
	workunit varchar(25) not null
)

create table NucleicTest (
	ID varchar(20),
	testdate date,
	ifnegative varchar(3) not null,
	testorganization varchar(25) not null,
	primary key(ID,testdate)
)

1.3、向表中插入相关数据

INSERT INTO `person` VALUES ('20401', '孙铭', '男', '山东济南', '山东建筑大学');
INSERT INTO `person` VALUES ('20402', '韩sir', '男', '山东济南', '山东建筑大学');
INSERT INTO `person` VALUES ('20403', '王sir', '男', '山东济南', '山东建筑大学');
INSERT INTO `person` VALUES ('20404', '孟sir', '男', '山东济南', '山东建筑大学');

INSERT INTO `nucleictest` (`ID`, `testdate`, `ifnegative`, `testorganization`) VALUES ('2040', '2022-04-23', '是', '建大花园');
INSERT INTO `nucleictest` (`ID`, `testdate`, `ifnegative`, `testorganization`) VALUES ('20401', '2022-04-20', '是', '建大花园');
INSERT INTO `nucleictest` (`ID`, `testdate`, `ifnegative`, `testorganization`) VALUES ('20401', '2022-04-22', '是', '建大花园');
INSERT INTO `nucleictest` (`ID`, `testdate`, `ifnegative`, `testorganization`) VALUES ('20401', '2022-04-24', '是', '建大花园');
INSERT INTO `nucleictest` (`ID`, `testdate`, `ifnegative`, `testorganization`) VALUES ('20402', '2022-04-20', '是', '建大花园');
INSERT INTO `nucleictest` (`ID`, `testdate`, `ifnegative`, `testorganization`) VALUES ('20402', '2022-04-22', '是', '建大花园');
INSERT INTO `nucleictest` (`ID`, `testdate`, `ifnegative`, `testorganization`) VALUES ('20402', '2022-04-24', '是', '建大花园');
INSERT INTO `nucleictest` (`ID`, `testdate`, `ifnegative`, `testorganization`) VALUES ('20403', '2022-04-21', '是', '建大花园');
INSERT INTO `nucleictest` (`ID`, `testdate`, `ifnegative`, `testorganization`) VALUES ('20403', '2022-04-23', '是', '建大花园');
INSERT INTO `nucleictest` (`ID`, `testdate`, `ifnegative`, `testorganization`) VALUES ('20404', '2022-04-21', '是', '建大花园');

2、为指定数据库添加特定权限的用户

# 创建用户，用户可以通过特定ip的客户端访问数据库

CREATE USER '用户名'@'ip' IDENTIFIED BY '密码';

# 赋予用户权限，让其拥有权限1和权限2去 *** 作特定数据库的所有表

GRANT 权限1, 权限2 ON '数据库名'.* TO '用户名'@'ip';

#为指定数据库添加特定权限的用户

GRANT SELECT, INSERT, UPDATE, DELETE ON 指定数据库.*  TO  '用户名'  @ 'localhost' IDENTIFIED BY '密码';

2.1、创建u2、u3、u4用户

# 创建用户u2、u3、u4，便于测试密码皆是123456。
# @'%' 表示用户可以通过任意ip的客户端访问数据库
CREATE USER 'u2'@'%' IDENTIFIED BY '123456';
CREATE USER 'u3'@'%' IDENTIFIED BY '123456';
CREATE USER 'u4'@'%' IDENTIFIED BY '123456';

# 分别赋予权限给用户u2、u3、u4，去 *** 作数据库epidemicdb中的表

grant select, insert,update on epidemicdb.person to  'u2'@'%';

grant select on epidemicdb.NucleicTest to  'u2'@'%';

grant all privileges on epidemicdb.person to 'u3' @'%' with grant option;

grant all privileges on epidemicdb.NucleicTest to 'u3' @'%' with grant option;

2.2、切换到指定数据库’mysql’下，查看已创建的用户

use mysql
select host,user from user;

3、设置远程访问—开放3306端口

控制面板—系统和安全—windows防火墙—高级设置—入站规则

第一步：打开电脑的【控制面板】选择【系统和安全】

第二步：选择【防火墙】

第三步：选择【高级设置】

第四步：选择【入站规则】

第五步：点击【 *** 作】里【入站规则】的【新建规则】，选【端口】，然后【下一步】

第六步：依次选择【TCP】，【特定本地端口】，输入【3306】，点击【下一步】

第六步：点击允许连接，一直点下一步即可，最后我们可以在入站规则找到我们开放的3306端口。

u2: 1、远程访问u1数据库并测试连接

远程访问u1 的数据库，登录u2 用户进行测试连接

1.1、新建连接

打开电脑中的navicat连接用户u1的数据库，结果如下图所示，说明为用户u2远程连接成功。

2、验证权限的有效性 2.1、显示数据库

show databases;

2.2、测试权限范围内的SQL

2.3、测试不在权限范围内的SQL

u3： 1、远程访问u1数据库并测试连接

打开电脑中的navicat连接用户u1的数据库，结果如下图所示，说明为用户u3远程连接成功。

2、验证权限的有效性 2.1、测试权限范围内的SQL

2.2、测试不在权限范围内的SQL

create table pn;

3、为用户4添加特定权限

grant select on epidemicdb.person to 'u4' @'%' ;

grant select, insert on epidemicdb.NucleicTest to 'u4' @'%' ;

u4： 1、远程访问u1数据库并测试连接

打开电脑中的navicat连接用户u1的数据库，结果如下图所示，说明为用户u4远程连接成功。

2、验证权限的有效性 2.1、测试权限范围内的SQL

selevt testdate from nucleictest where id='20401'

2.2、测试不在权限范围内的SQL

create table pn;

作业总结：

本文主要讲述如何在本机（Windows）开启数据库的远程连接、创建指定权限的远程用户以及如何访问远程数据库。

注意：实际开发工作中，数据库是安装在服务器当中的，如果要使用数据库，也是必须通过远程连接，或者团队中的几个人做本地项目的过程中，项目成员之间需要互相连接访问对方的MySQL数据库也是需要了解如何。一般步骤为：1、为指定数据库添加特定权限的用户 。2、设置远程访问并开放端口号（关闭防火墙等）。

1、作为服务器端的电脑，需要创建数据库并添加特定权限的用户，开放3306端口号（或关闭防火墙）。

#创建用户，用户可以通过特定ip的客户端访问数据库

CREATE USER '用户名'@'ip' IDENTIFIED BY '密码';

#赋予用户权限，让其拥有权限1和权限2去 *** 作特定数据库的所有表

GRANT 权限1, 权限2 ON '数据库名'.* TO '用户名'@'ip';

2、作为需要远程连接的电脑端需要获取两个信息：服务器的IP地址，数据库的账号和密码

3、当两台电脑在同一网段下，我们就可以在自己电脑上使用数据库工具（例如navicat)，新建连接，登录用户远程连接数据库。

附：如何查看自己电脑的IP

打开运行（WIN+R键），输入“CMD”后回车，进入命令行模式下，输入输入ipconfig /all就可以显示本机IP地址，找到IPv4地址。

附：如何查看两台电脑在同一网段下

如果网络环境比较简单，只有1台路由器，且两台电脑通过网线或无线连接在同一台路由器上，那么这种情况下，两台电脑的IP地址是属于同一个网络的。

如果网络环境比较复杂，有多台路由器，多台交换设备，线路特别复杂的情况下，如何判断两个电脑是不是在同一个网络呢？

解决办法：

在两台电脑上，打开运行（WIN+R键），输入“CMD”后回车，再输入ipconfig /all，并且截图IP地址、物理地址（MAC地址）、子网掩码、网关地址。这里可以初判断，一般子网掩码一样，网关地址一样的话，基本是属于同一个网络。

在用户1的电脑上，使用ping命令，ping一下用户2的电脑，看是否出现数据包（没有是不正常的)。

最后祝大家五一快乐鸭🥳🥳🥳