MySQL8访问限制用户的创建

限制登录(程序ip放开)，更改默认端口 ，访问密码复杂度，添加白名单(防火墙仅开放业务ｉｐ连接)

1.限制登录

远程访问限制ip，即限制登录主机，程序ip放开

1.1.更改用户对应某些库的远程权限语法模板

host字段中不能有*，需用%

use mysql
select host,user from user;
--更改用户对应某些库的远程权限语法模板  with grant option根据情况选择加或不加
create user 'root'@'192.168.40.%' identified with mysql_native_password by 'top@123';
grant all privileges on 库名.表名 to '用户名'@'IP地址'  with grant option;
flush privileges;
1
2
3
4
5
6

1.2.示例

不建议用update。如果没有’root’@'localhost’用户需注意

样例1:只有’root’@'%'用户

新部署环境只有’root’@'localhost’用户

--
use mysql
select host,user from user;  --确保有localhost，按以下步骤添加用户
create user 'root'@'localhost' identified with mysql_native_password by 'admin@2023';
grant all privileges on *.* to 'root'@'localhost' with grant option;
flush privileges;

create user 'root'@'192.168.40.%' identified with mysql_native_password by 'top@123';
grant all privileges on *.* to 'root'@'192.168.40.%'   with grant option;
flush privileges;

--创建授权用户的同时添加远程ip访问权限
create user 'root'@'10.40.21.12' identified with mysql_native_password by 'top@123';
grant all privileges on *.* to 'root'@'10.40.21.12' with grant option;
flush privileges;
或
grant all privileges on testwa.* to 'root'@'192.168.1.105' identified by '123uupp' with grant option;
flush privileges;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

问题

--问题描述
root@localhost :mysql 07:10:22>create user 'root'@'localhost' identified with mysql_native_password by 'admin@2023';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'

--问题原因
先前直接将'root'@'localhost'用户update成了'root'@'%'。不管是update或delete都会提示上述报错。
select user,host from user;中并不显示'root'@'localhost'

--解决办法
drop user 'root'@'localhost';
flush privileges;
create user 'root'@'localhost' identified with mysql_native_password by 'admin@2023';
grant all privileges on *.* to 'root'@'localhost' with grant option;
flush privileges;
select user,host from user;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

样例2:同时有’root’@'%‘和’root’@‘localhost’ 用户

同时有’root’@'%‘和’root’@‘localhost’ 用户

--创建访问用户
use mysql
select host,user from user;

create user 'root'@'172.30.155.39' identified with mysql_native_password by 'topicis';
grant all privileges on *.* to 'root'@'172.30.155.39'   with grant option;
flush privileges;

create user 'root'@'172.30.155.40' identified with mysql_native_password by 'topicis';
grant all privileges on *.* to 'root'@'172.30.155.40'   with grant option;
flush privileges;

create user 'root'@'172.30.155.45' identified with mysql_native_password by 'topicis';
grant all privileges on *.* to 'root'@'172.30.155.45'   with grant option;
flush privileges;

create user 'root'@'172.30.134.%' identified with mysql_native_password by 'topicis';
grant all privileges on *.* to 'root'@'172.30.134.%'   with grant option;
flush privileges;

select host,user from user;


--删除'root'@'%'用户
select user,host from mysql.user where host='%' and user='root';

delete from mysql.user where host='%' and user='root';

--远程访问测试
mysql -h42.284.39.184 -uroot -p123456

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

1.3.取消IP访问限制

--创建授权用户同时添加远程ip访问权限
grant all on *.* to root@192.168.1.105  with grant option;
flush privileges;

--取消IP访问限制
revoke all on *.* from root@192.168.1.105;
flush privileges;
1
2
3
4
5
6
7

1.4.禁止远程用户

如果需要禁止远程用户，删除即可drop user git@%;

drop user root@192.168.1.105;
flush privileges;
1
2

2.更改默认端口

更改配置文件，重启数据库服务

--Centos6
service mysqld stop
service mysqld start

--Centos7
systemctl restart mysqld 
1
2
3
4
5
6

3.访问密码复杂度

更改数据库用户root密码

use mysql
select host,user from user;

flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
flush privileges;

或
mysqladmin -u username -h hostname -p password "newpwd"
1
2
3
4
5
6
7
8
9

4.添加白名单

防火墙配置白名单，仅开放业务ｉｐ连接

--查看防火墙所有策略
firewall-cmd --permanent --list-all 或 firewall-cmd --list-all
firewall-cmd --zone=public --add-source=192.168.16.122/32 --permanent
firewall-cmd --zone=public --add-port=2213/tcp --permanent
firewall-cmd --zone=public --add-port=22/tcp --permanent
firewall-cmd --reload
1
2
3
4
5
6