自建 MySQL 数据库安全配置建议

数据库关系型数据库技术服务知识库
问题描述

安装完 MySQL 数据库后,如何进行安全性方面的配置?

问题分析

数据库作为存储应用数据的系统,安全性非常重要。首先要保证系统本身的安全,可以删除无效用户、空密码用户,密码加密,禁止本地文件读取等方面进行一些安全方面的配置。

解决方案

1. 将root用户的口令修改为复杂口令,如大小写字母、特殊字符、数字、12位

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'xxxx';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

2. 删除默认数据库和用户

将测试创建的用户 test 删除,如下:

mysql> delete from user where user='test';
Query OK, 0 rows affected (0.00 sec)

删除空密码的 root,如下:

mysql> delete from user where user='root' and authentication_string='';
Query OK, 0 rows affected (0.00 sec)

查看目前数据库用户,如下:

mysql> select user,host,authentication_string  from user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | *C039D10921BCFBE67405F907A64DCE5BC1E565EC                              |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

3. 修改管理员用户名称

将 root 修改为 toor,如下:

mysql> update user set user='toor' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

进行登录测试,如下:

[root@iv-7gra07d5mdig9u8djm4x ~]# mysql -u toor -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

4. 将涉及密码的字段进行加密存储。

将 users 表中的密码字段加密存储,如下:

mysql> insert into users values (1,'test',SHA1(123456));
Query OK, 1 row affected (0.01 sec)

5. 不用使用root运行mysql,使用独立的mysql用户运行

[root@iv-7gra07d5mdig9u8djm4x ~]# ps -eo pid,user,group,euser,egroup,cmd | grep mysql
 1905 mysql    mysql    mysql    mysql    /usr/sbin/mysqld

6. 禁止 MySQL 本地文件读取

load data local infile 命令可以泄露敏感信息。 示例:

mysql> load data local infile '/root/sqlfile' into table users fields terminated by ',';
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

修改 /etc/my.cnf 文件,如下:

[mysqld]
local_infile=0

再进行测试,如下:


mysql> load data local infile '/root/sqlfile' into table users fields terminated by ',';
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

如果您有其他问题,欢迎您联系火山引擎技术支持服务

31
0
0
0
相关产品
评论
未登录
看完啦,登录分享一下感受吧~
暂无评论