Senior told me that the giant MySQL is through SSH connection

大家好,我是咔咔 不期速成,日拱一卒

一、背景

Several companies I have worked with before,数据库、Server permissions are directly filled for all backends,But there are also cases where employees leave,The database password needs to be changed every time someone leaves、服务器密码.

After each password change, all developers must be informed to change the local password,But this kind of thing doesn't happen often,The company is small but stable.

Suppose the company you are working for is a company with a lot of development,It is possible that you have been here for a year and haven't met him yet,The movement of people is also very fast,At this time, the implementation cost of the above scheme is very high.

If this question is thrown to you at this time,Let you figure out what plan you have？

Next, we will introduce two options,One traditional scheme the other is throughSSH来实现的.

二、传统方案

MySQL版本：8.0.26

The simplest solution is to add an account to the database for each person,具体步骤如下：

创建新用户

create user "kaka"@"%" identified by 'qwerty123456';

其中kakais a custom user name;%for the login domain name,host为'%'时表示为 任意IP,为localhostindicates this unit,Or fill in the specifiedIP地址;qwerty123456为密码

为用户授权

grant all privileges on kaka.* to "kaka"@"%" with grant option;

或

grant all on *.* to "kaka"@"%";

其中kaka.*,kakadata name,*为所有表,If you want to authorize all tables, just do itkaka.*写成*.*,Of course, this is based on the development library,All permissions have to be given.Partial permissions can also currently be granted.

刷新权限

flush privileges;

使用用户名：kaka进行登录

发现kakaThe user has only two libraries,kakaA library is an authorized library,When switching the system library, it is found that there is no permission.

在切到kaka库,It can be done normallycurd操作的

Give some permissions

grant update on kaka.* to "kaka"@"%";

flush privileges;

If you want to give multiple permissions,Separate them with commas,update,select,insert ....,Remember to refresh permissions after execution,否则不会生效

撤销全部权限

This one is a bit of a hiatus,Such an error was reported after executing the undo command

Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

Check out the official documentation,原因是由于root用户没有SYSTEM_USER权限,把权限加入后即可解决

grant system_user on *.* to 'root';

revoke all privileges ,grant option from kaka;

或

revoke all privileges on kaka.* from kaka;

flush privileges;

Plug some permissions

revoke select on kaka.* from kaka;

kaka.*为表名,kaka为用户名

When an employee leaves, the user can be deleted

drop from kaka;

kaka 为用户名

嗯,成功的把MySQLPermissions were reviewed again.....

三、通过SSH隧道连接MySQL数据库

准备工作

修改MySQLThe host only allows remote server connections

use mysql;

update user set host='8.142.40.202' where user = "root";

此时在MySQLThe host server cannot log in directly

配置SSH连接MySQL主机

在远程主机执行

ssh -fN -L33888:47.93.12.204:3306 [email protected]

在使用SSHWhen using the connection, I found that some people said that the connection has been disconnected,Affects normal development,Just add the following parameters and re-execute it,This parameter is per60秒发送一个KeepAlive请求,保证终端不会因为超时空闲而断开连接

ssh -o ServerAliveInterval=60 -fN -L33888:47.93.12.204:3306 [email protected]

Note that the front is the remote server Behind it is the server account of the remote host、服务器地址

命令执行完成后,可以通过命令

mysql -h 127.0.0.1 -P 33888 -uroot -p

密码是MySQL服务器的 MySQL密码

四、Local development connection

The above is demonstrated by two servers,Next, let's see how developers connect to the development database

Also execute the command locally

ssh -fN -L3306:47.93.12.204:3306 [email protected]

在hostDomain name mapping is performed in the configuration file

// 127.0.0.1 MySQL服务器地址
127.0.0.1 8.142.40.202

使用Navicat进行连接

You can see that it is connected

This forces all developers to passSSH来连接MySQL,When a developer leaves,Just delete the corresponding server account

五、限制Linux用户登录

你肯定也想到了,通过SSHThe server account password used for connection,Then it means that all developers can log in to the server with their own account and password.

上有政策,下有对策,Next, let's see how to restrict users from logging into the server.

For example the user is now addedniuniu,At this point, the user can definitely connect to the server

You can see that the current user has passedXsheel连接上了服务器,It is better to open server permissions to some people,The next step is to restrict the user from logging in to the server

执行命令

usermod -s /sbin/nologin niuniu 

用户niuniu通过Xsheel登录服务器,You can see that returning to the current account is unavailable,It means that the result we want is already there

再看看本地Navicat连接是否正常

All safety measures have been completed so far.

六、扩展一：WITH GRANT OPTION

这个参数是可选的,如果不加,That sentence ends here,This user is the first class,He can no longer go to create sub-users,如果给了,It means you can create a sub-account,Of course, the permissions that sub-users can assign are limited to their own permissions

注意一点,The operations here can only be assigned to existing accounts,Creating new accounts requires additional permissions 并且,还得有GRANT权限,Otherwise, even if you have this permission, you do not have the permission to execute this permission

七、扩展二：Linux用户操作

添加用户

useradd {username}

删除用户

vipw

After entering, delete the corresponding user name

groupdel {username}

rm -rf /home/{username}

设置密码

passwd {username}

需要输入两遍,注意

八、总结

This article will introduce two ways to deal with developers after they leave,The scheme of database privilege revocation.一种是通过MySQLPermissions for its own field、另一种是通过SSH来连接,At present, the company where Kaka works is throughSSH进行连接的.

In the introduction of these two schemes, a lot of knowledge points that can be expanded are found,Also written out,When you read this article, you don't have to do the research again.

“

坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念.愿文章在偌大的互联网上能给你带来一点帮助,我是咔咔,下期见.

”