MySQL安全插件-数据库审计
原创MySQL安全插件-数据库审计
原创
Power
发布于 2025-03-01 12:57:27
发布于 2025-03-01 12:57:27
代码可运行
运行总次数：0
代码可运行
一、数据库审计介绍
数据库审计（简称DBAudit）能够实时记录网络上的数据库活动，对数据库操作进行细粒度审计的合规性管理，对数据库遭受到的风险行为进行告警，对攻击行为进行阻断。它通过对用户访问数据库行为的记录、分析和汇报，用来帮助用户事后生成合规报告、事故追根溯源，同时加强内外部数据库网络行为记录，提高数据资产安全。
数据库审计是数据库安全技术之一，数据库安全技术主要包括：数据库漏扫、数据库加密、数据库防火墙、数据脱敏、数据库安全审计系统。
二、MySQL审计方案
MySQL服务器自身没有提供审计功能，但是如果想实现MySQL数据库审计，一般有以下几种方法：
（1）使用init-connect + binlog的方法进行mysql的操作审计。官网介绍：http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_init_connect
（2）MySQL audit SQL审计插件or第三方开源审计插件：libaudit_plugin.so 来完成MySQL的审计工作。
（3）基于360开源数据库流量审计MySQL Sniffer。Github地址：https://github.com/Qihoo360/mysql-sniffer
（4）使用ELK处理MySQL数据库审计日志（ELK日志分析功能是很强大的）。
（5）Mysql bin-log日志进行实时存储和行为分析 当触发设定的规则就实现记录和告警。
（6）开启mysql监控，实施监控日志和用户命令的操作 ，这类往往是一个平台或者软件开发结果集。
（7）其它：https://www.percona.com/blog/2014/05/16/introduction-to-the-percona-mysql-audit-log-plugin/
三、MySQL audit SQL审计插件
3.1 MySQL audit SQL开源审计介绍
Github地址介绍：https://github.com/mcafee/mysql-audit
Github版本下载：https://github.com/mcafee/mysql-audit/releases
3.2 部署MySQL audit插件
（1）查看插件所在目录
mysql> show global variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.01 sec)（2）下载插件包
[root@ansible ~]# wget https://github.com/mcafee/mysql-audit/releases/download/v1.1.8/audit-plugin-mysql-5.7-1.1.8-953-linux-x86_64.zip
[root@ansible ~]# unzip audit-plugin-mysql-5.7-1.1.8-953-linux-x86_64.zip
[root@ansible ~]# cd audit-plugin-mysql-5.7-1.1.8-953/
[root@ansible audit-plugin-mysql-5.7-1.1.8-953]# tree
.
├── COPYING
├── lib
│ └── libaudit_plugin.so
├── plugin-name.txt
├── README.txt
├── THIRDPARTY.txt
└── utils
└── offset-extract.sh
2 directories, 6 files（3）添加插件配置
[root@ansible ~]# cd audit-plugin-mysql-5.7-1.1.8-953/
[root@ansible audit-plugin-mysql-5.7-1.1.8-953]# cp lib/libaudit_plugin.so /usr/local/mysql/lib/plugin/
[root@ansible ~]# vim /etc/my.cnf
plugin-load=AUDIT=libaudit_plugin.so（4）安装插件
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Query OK, 0 rows affected (1.34 sec)（5）查看已安装插件列表
mysql> show plugins;
+------------------------------------------+----------+--------------------+-----------------------+---------+
| Name | Status | Type | Library | License |
+------------------------------------------+----------+--------------------+-----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
| CONNECTION_CONTROL | ACTIVE | AUDIT | connection_control.so | GPL |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE | INFORMATION SCHEMA | connection_control.so | GPL |
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
+------------------------------------------+----------+--------------------+-----------------------+---------+
48 rows in set (0.01 sec)（6）查看插件版本
mysql> show global status like 'AUDIT_version';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Audit_version | 1.1.8-953 |
+---------------+-----------+
1 row in set (0.00 sec)（7）查看audit插件配置
mysql> show variables like '%audit%';
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| audit_before_after | after |
| audit_checksum | |
| audit_client_capabilities | OFF |
| audit_delay_cmds | |
| audit_delay_ms | 0 |
| audit_force_record_logins | OFF |
| audit_header_msg | ON |
| audit_json_file | ON |
| audit_json_file_bufsize | 1 |
| audit_json_file_flush | OFF |
| audit_json_file_retry | 60 |
| audit_json_file_sync | 0 |
| audit_json_log_file | mysql-audit.json |
| audit_json_socket | OFF |
| audit_json_socket_name | /var/run/db-audit/mysql.audit__mysql_data_3306 |
| audit_json_socket_retry | 10 |
| audit_json_socket_write_timeout | 1000 |
| audit_offsets | |
| audit_offsets_by_version | ON |
| audit_password_masking_cmds | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE |
| audit_password_masking_regex | identified(?:/\*.*?\*/|\s)*?by(?:/\*.*?\*/|\s)*?(?:password)?(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?\((?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"](?:/\*.*?\*/|\s)*?\)|password(?:/\*.*?\*/|\s)*?(?:for(?:/\*.*?\*/|\s)*?\S+?)?(?:/\*.*?\*/|\s)*?=(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"] |
| audit_record_cmds | |
| audit_record_objs | |
| audit_sess_connect_attrs | ON |
| audit_socket_creds | ON |
| audit_uninstall_plugin | OFF |
| audit_validate_checksum | ON |
| audit_validate_offsets_extended | ON |
| audit_whitelist_cmds | BEGIN,COMMIT,PING |
| audit_whitelist_users | |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
30 rows in set (0.01 sec)3.3 MySQL audit配置
（1）audit_json_file：开启audit插件功能
mysql> SET GLOBAL audit_json_file=ON;
Query OK, 0 rows affected (0.00 sec)（2）audit_json_log_file：记录文件的路径和名称信息(默认放在mysql数据目录下)
mysql> show variables like "audit_json_log_file";
+---------------------+------------------+
| Variable_name | Value |
+---------------------+------------------+
| audit_json_log_file | mysql-audit.json |
+---------------------+------------------+
1 row in set (0.00 sec)（3）audit_record_cmds：audit记录的命令，默认为记录所有命令
mysql> show variables like "audit_record_cmds";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| audit_record_cmds | |
+-------------------+-------+
1 row in set (0.00 sec)可以设置为任意dml、dcl、ddl的组合。如：audit_record_cmds=select,insert,delete,update。还可以在线设置set global audit_record_cmds=NULL。(表示记录所有命令)
（4）audit_record_objs：audit记录操作的对象
mysql> show variables like "audit_record_objs";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| audit_record_objs | |
+-------------------+-------+
1 row in set (0.00 sec)默认为记录所有对象，可以用SET GLOBAL audit_record_objs=NULL设置为默认。也可以指定为下面的格式：audit_record_objs=,test.*,mysql.*,information_schema.*。
（5）audit_whitelist_users：用户白名单。
mysql> show variables like "audit_whitelist_users";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| audit_whitelist_users | |
+-----------------------+-------+
1 row in set (0.01 sec)【注】重要配置要持久化到配置文件中：
[root@ansible ~]# vim /etc/my.cnf
audit_json_file=on # 保证mysql重启后自动启动插件
plugin-load=AUDIT=libaudit_plugin.so # 防止删除了插件，重启后又会加载
audit_record_cmds='insert,delete,update,create,drop,alter,grant,truncate' # 要记录哪些命令语句,因为默认记录所有操作；四、审计验证
mysql> select * from user; # 执行查询SQL
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N | 2021-07-03 16:42:40 | NULL | N |
| localhost | mysql.session | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2021-07-03 16:31:07 | NULL | Y |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2021-07-03 16:31:07 | NULL | Y |
| % | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N | 2021-07-03 16:42:45 | NULL | N |
| localhost | xtrabackup | N | N | N | N | N | N | Y | N | Y | N | N | N | N | N | N | Y | N | Y | N | N | Y | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N | 2021-07-05 15:51:08 | NULL | N |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
5 rows in set (0.00 sec)
[root@ansible ~]# vim /mysql/data/mysql-audit.json # 查看审计信息
{"msg-type":"activity","date":"1626711164664","thread-id":"94","query-id":"36868","user":"root","priv_user":"root","ip":"",原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
