MySQL安全插件-数据库审计
一、数据库审计介绍
数据库审计（简称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":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"10483","_client_version":"5.7.31","_platform":"x86_64","program_name":"mysql","pid":"10483","os_user":"root","appname":"mysql","rows":"5","status":"0","cmd":"select","objects":[{"db":"mysql","name":"user","obj_type":"TABLE"}],"query":"select * from user"}作者：UStarGao
链接：https://www.starcto.com/mysql/179.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
UCloud云平台推荐
随便看看
- 2021-04-12MySQL删除数据空间没有释放-碎片
- 2021-01-28Docker安装部署教程
- 2022-06-22MySQL如何创建全文索引
- 2021-07-21MySQL Binlog日志解析方法
- 2021-08-09MongoDB副本集搭建教程
