mysql审计日志-ProxySQL

MySQL审计概述： 出于对数据安全的考虑，很多公司要求对MySQL的操作进行审计，这就要求我们对所有MySQL的操作都进行记录，并且相关信息要齐全（账号，时间，语句等）。   1、general_log：这样虽然可以记录所有的操作日志，但很遗憾，缺少账号等必要信息，而且IO消耗非常大。 2、init-connect：这个其实就是在用户连接的时候，插入一条当前用户，thread id，用户，时间的数据到审计表，结合binlog日志，就可以进行简单的定位。  简单粗暴，性能影响不大。不会记录超级用户。 3、第三方插件：方便好用。对数据库性能有一定的影响。 4、通过在代理或者代理前通过MySQL协议解析，不影响mysql性能，但要多一个中间层，如果代理本身支持最优。推荐方案：ProxySQL，代理maxscale暂不支持。   1.audit_log：MySQL自5.5版本起，实现了audit api，可以专门用于编写审计插件，Oracle官方就实现了一个叫做audit_log的插件，但这个插件既不免费也不开源，而是作为其企业服务的一部分，只供给付费用户使用. 2.安全厂商Macfee也有一个MySQL audit插件，但好久没有更新了，而且实现方式很奇特，在此不做讨论。 3.MariaDB出品的 MariaDB Audit Plugin，经过我的测试，安装和使用都非常简单。 4.percona公司出品的插件。   3种可用的MySQL插件：mariadb、percona、macfee     一、init-connect审计方法：   1。创建用于存放连接信息的表   create database audit_db default charset utf8; use audit_db; create table accesslog (id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,connection_id int(11) DEFAULT NULL,conn_user varchar(30) DEFAULT NULL,priv_match_name varchar(30) DEFAULT NULL,login_time timestamp NULL DEFAULT NULL);    2。保证所有的用户对此表有写权限   insert into db (Host,Db,User,Insert_priv) values ('%','audit_db','','Y'); flush privileges;   3。设置init-connect 在my.cnf 中的 [mysqld] 的block 添加以下配置； init-connect='insert into audit_db.accesslog (connection_id,conn_user,priv_match_name,login_time) values(connection_id(),user(),current_user(),now());' log-bin=bin.log   4。重启数据库生效 service mysqld restart   假设想知道 是谁把tmp_2.tmp这个表的数据全删了。可以用binlog来定位。 /usr/local/mysql/3308/bin/mysqlbinlog /log/binlog/3308/bin.000006 -v | grep 'tmp' -B 10   COMMIT/*!*/; # at 1136 #160720 14:40:03 server id 3308161  end_log_pos 1201 CRC32 0xa779d0c3 GTID last_committed=3 sequence_number=4 SET @@SESSION.GTID_NEXT= 'e08d636f-47de-11e6-af3d-0050569e70f2:29'/*!*/; # at 1201 #160720 14:40:03 server id 3308161  end_log_pos 1274 CRC32 0x45a0e6b7 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1468996803/*!*/; BEGIN /*!*/; # at 1274 #160720 14:40:03 server id 3308161  end_log_pos 1324 CRC32 0xb2514b75 Table_map: `tmp_2`.`tmp` mapped to number 109 # at 1324 #160720 14:40:03 server id 3308161  end_log_pos 1377 CRC32 0xed68bd31 Delete_rows: table id 109 flags: STMT_END_F   BINLOG ' wxyPVxOBejIAMgAAACwFAAAAAG0AAAAAAAEABXRtcF8yAAN0bXAAAgMPAloAA3VLUbI= wxyPVyCBejIANQAAAGEFAAAAAG0AAAAAAAEAAgAC//wBAAAAA3BwcPwBAAAAA3JycjG9aO0= '/*!*/; ### DELETE FROM `tmp_2`.`tmp` ### WHERE ###   @1=1 ###   @2='ppp' ### DELETE FROM `tmp_2`.`tmp`   可以上看，删除tmp_2,tmp表的线程是：thread_id=11   mysql> select * from accesslog where connection_id=11; +----+---------------+----------------+-----------------+---------------------+ | id | connection_id | conn_user      | priv_match_name | login_time          | +----+---------------+----------------+-----------------+---------------------+ |  2 |            11 | test@localhost | test@localhost  | 2016-07-20 14:39:42 | +----+---------------+----------------+-----------------+---------------------+ 1 row in set (0.02 sec)   Q：使用init-connect会影响服务器性能吗？ A：理论上，只会在用户每次连接时往数据库里插入一条记录，不会对数据库产生很大影响。除非连接频率非常高（当然，这个时候需要注意的就是如何进行连接复用和控制，而非是不是要用这种方法的问题了）   Q：access-log表如何维护? A: 由于是一个log系统，推荐使用archive存储引擎，有利于数据厄压缩存放。如果数据库连接数量很大的话，建议一定时间做一次数据导出，然后清表。 Q：表有其他用途么？ A：有！access-log表当然不只用于审计，当然也可以用于对于数据库连接的情况进行数据分析，例如每日连接数分布图等等。 Q：会有遗漏的记录吗？ A：会的，init-connect 是不会在super用户登录时执行的。所以access-log里不会有数据库超级用户的记录，这也是为什么我们不主张多个超级用户，并且多人使用的原因。     二、mcafee出品的MySQL插件 github:https://github.com/mcafee/mysql-audit   二进制版本下载地址: https://bintray.com/mcafee/mysql-audit-plugin/release/1.0.9-585/ 本次下载：audit-plugin-mysql-5.7-1.0.9-585-linux-x86_64.zip   [root@test11 ~]# ls audit-plugin-mysql-5.7-1.0.9-585 COPYING  lib  README.txt  THIRDPARTY.txt [root@test11 ~]# ls audit-plugin-mysql-5.7-1.0.9-585/lib/ libaudit_plugin.so   确定mysql的插件目录。 mysql> select @@version; +------------+ | @@version  | +------------+ | 5.7.13-log | +------------+ 1 row in set (0.00 sec)   mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir'; +---------------+-----------------------------------+ | Variable_name | Value                             | +---------------+-----------------------------------+ | plugin_dir    | /usr/local/mysql/3308/lib/plugin/ | +---------------+-----------------------------------+ 1 row in set (0.00 sec)   cp插件的so文件到mysql的插件目录。 [root@test11 ~]# cp audit-plugin-mysql-5.7-1.0.9-585/lib/libaudit_plugin.so /usr/local/mysql/3308/lib/plugin/   mysql>  mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so'; ERROR 1123 (HY000): Can't initialize function 'AUDIT'; Plugin initialization function failed. 失败。 查看错误日志： 2016-07-20T15:57:54.985752+08:00 18 [Warning] option 'audit-json-file-bufsize': signed value 0 adjusted to 1 2016-07-20T15:57:54.985824+08:00 18 [Warning] option 'plugin-audit-json-file-bufsize': signed value 0 adjusted to 1 2016-07-20T15:57:54.986058+08:00 18 [Note] Audit Plugin: starting up. Version: 1.0.9 , Revision: 585 (64bit). AUDIT plugin interface version: 1025 (0x401). MySQL Server version: 5.7.13-log. 2016-07-20T15:57:54.986084+08:00 18 [Note] Audit Plugin: setup_offsets audit_offsets: (null) validate_checksum: 1 offsets_by_version: 1 2016-07-20T15:57:57.317648+08:00 18 [Note] Audit Plugin: mysqld: /usr/local/mysql/3308/bin/mysqld (1663d3c6234242c71b3ed6a2521194da) 2016-07-20T15:57:57.317731+08:00 18 [Note] Audit Plugin: Couldn't find proper THD offsets for: 5.7.13-log 2016-07-20T15:57:57.317747+08:00 18 [ERROR] Plugin 'AUDIT' init function returned error. 2016-07-20T15:57:57.317759+08:00 18 [ERROR] Plugin 'AUDIT' registration as a AUDIT failed. 2016-07-20T15:57:57.317785+08:00 18 [Note] Shutting down plugin 'AUDIT' 2016-07-20T15:57:57.317802+08:00 18 [Note] Audit Plugin: deinit   Couldn't find proper THD offsets for: 5.7.13-log查不到合适的偏移值。   这个问题，已经有人遇到：https://github.com/mcafee/mysql-audit/issues/2 简单来说就是使用一个脚本，提取偏移量，然后配置在my.cnf中。 目录下offset-extract.sh就是本次脚本。   chmod +x offset-extract.sh   [root@test11 ~]# ./offset-extract.sh /usr/local/mysql/3308/bin/mysqld //offsets for: /usr/local/mysql/3308/bin/mysqld (5.7.13) {"5.7.13","1663d3c6234242c71b3ed6a2521194da", 7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964},   这里需要的偏移量就是7800开始的内容。 [root@test11 ~]# vi /usr/local/mysql/3308/my.cnf plugin-load=AUDIT=libaudit_plugin.so audit_offsets=7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964   mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so'; Query OK, 0 rows affected (0.73 sec)   mysql> SHOW GLOBAL STATUS LIKE 'AUDIT_version'; +---------------+-----------+ | Variable_name | Value     | +---------------+-----------+ | Audit_version | 1.0.9-585 | +---------------+-----------+ 1 row in set (0.00 sec)   安装成功 6.开启audit功能   SET GLOBAL audit_json_file=ON;   7.执行任何语句(默认会记录任何语句)，然后去mysql数据目录查看mysql-audit.json文件(默认为该文件)   mysql> SHOW GLOBAL VARIABLES LIKE '%audi%'; +---------------------------------+-------------------------------------------------------------------------- | Variable_name                   | Value                                                                                                    +---------------------------------+-------------------------------------------------------------------------- | 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          | /tmp/mysql.audit__data_mysql_3308_3308                                    | audit_json_socket_retry         | 10                                                                                                      | audit_offsets                   | 7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964         | audit_offsets_by_version        | ON                                                                                                                                                                                                                                                                    | audit_password_masking_cmds     | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER  | audit_password_masking_regex    |。。。。。。。 | audit_record_cmds               |                                                                   | audit_record_objs               |                                                                                                                                                                                                                                                                | audit_uninstall_plugin          | OFF                                                                                                                                                                                                                                        | audit_validate_offsets_extended | ON                                                                                                                                                                                                                        | audit_whitelist_cmds            | BEGIN,COMMIT                                           +---------------------------------+------------------------------------ 25 rows in set (0.00 sec)   其中我们需要关注的参数有： 1. audit_json_file 是否开启audit功能，on，off 2. audit_json_log_file   记录文件的路径和名称信息。 3. audit_record_cmds audit记录的命令,默认为记录所有命令。 可以设置为任意dml、dcl、ddl的组合， 如：audit_record_cmds=select,insert,delete,update 还可以在线设置set global audit_record_cmds=NULL   (表示记录所有命令)。 4. audit_record_objs    audit记录操作的对象,默认为记录所有对象, 可以用SET GLOBAL audit_record_objs=NULL设置为默认。 也可以指定为下面的格式 audit_record_objs=,test.*,mysql.*,information_schema.* 5. audit_whitelist_users  用户白名单   还有offsets参数的设置,如果开启audit_offsets_by_version=ON，则必须设置audit_offsets。   查看mysql data目录下mysql-audit.json的日志，格式如下： {"msg-type":"header","date":"1469002978327","audit-version":"1.0.9-585","audit-protocol-version":"1.0","hostname":"test11","mysql-version":"5.7.13-log","mysql-program":"/usr/local/mysql/3308/bin/mysqld","mysql-socket":"/usr/local/mysql/3308/mysql.sock","mysql-port":"3308"} {"msg-type":"activity","date":"1469003001877","thread-id":"18","query-id":"182","user":"root","priv_user":"root","host":"localhost","ip":"","cmd":"show_variables","objects":[{"db":"information_schema","name":"/tmp/#sql_63d4_0","obj_type":"TABLE"}],"query":"SHOW GLOBAL VARIABLES LIKE '%audi%'"} {"msg-type":"activity","date":"1469003010787","thread-id":"18","query-id":"183","user":"root","priv_user":"root","host":"localhost","ip":"","cmd":"show_variables","objects":[{"db":"information_schema","name":"/tmp/#sql_63d4_0","obj_type":"TABLE"}],"query":"SHOW GLOBAL VARIABLES LIKE '%audi%'"}   注意这里的时间戳：1469003010787多了3位，描述到了毫秒。 date -d @1469003010 +"%Y-%m-%d %H:%M:%S" 2016-07-20 16:23:30       三、mariadb出品的MySQL插件   mysql> select @@version; +---------------------+ | @@version           | +---------------------+ | 10.0.20-MariaDB-log | +---------------------+ 1 row in set (0.00 sec)   如果你是MySQL官方版本，需要下载这个插件或是直接从mariadb的目录下cp过去（目录下也有server_audit.so 文件）。解压后就是一个server_audit.so，将这个文件拷贝到mysql的plugin目录 [root@test12 ~]# ls /usr/local/mysql/3306/lib/plugin/server_audit.so  /usr/local/mysql/3306/lib/plugin/server_audit.so   mariadb自带该插件，所以直接运行即可： mysql> INSTALL PLUGIN server_audit SONAME 'server_audit'; Query OK, 0 rows affected (0.06 sec)   然后执行： SET GLOBAL server_audit_logging=on; 打开日志记录。   mysql> show variables like '%audit%'; +-------------------------------+-----------------------+ | Variable_name                 | Value                 | +-------------------------------+-----------------------+ | server_audit_events           |                       | | server_audit_excl_users       |                       | | server_audit_file_path        | server_audit.log      | | server_audit_file_rotate_now  | OFF                   | | server_audit_file_rotate_size | 1000000               | | server_audit_file_rotations   | 9                     | | server_audit_incl_users       |                       | | server_audit_logging          | ON                    | | server_audit_mode             | 0                     | | server_audit_output_type      | file                  | | server_audit_query_log_limit  | 1024                  | | server_audit_syslog_facility  | LOG_USER              | | server_audit_syslog_ident     | mysql-server_auditing | | server_audit_syslog_info      |                       | | server_audit_syslog_priority  | LOG_INFO              | +-------------------------------+-----------------------+ 15 rows in set (0.01 sec)   也可以直接在my.cnf中指定： [root@test11 ~]# vi /usr/local/mysql/3308/my.cnf plugin-load=server_audit=server_audit.so   详细参数： https://mariadb.com/kb/en/mariadb/server_audit-system-variables/   默认日志文件在MySQL datadir目录下面的server_audit.log文件。 [root@test12 ~]# cat /data/mysql/3306/server_audit.log 20160720 21:06:51,test12,root,localhost,14,795,QUERY,,'SET GLOBAL server_audit_logging=on',0 20160720 21:07:09,test12,root,localhost,14,796,QUERY,,'show variables like \'%audit%\'',0 20160720 21:20:22,test12,root,localhost,14,797,QUERY,,'show variables like \'%audit%\'',0 20160720 21:20:28,test12,root,localhost,14,0,DISCONNECT,,,0   percona出品插件，详情查看目录下文档。     四、ProxySQL审计日志   ProxySQL 2.0.5 引入了审计日志。此功能允许跟踪某些连接活动。要启用此功能，需要配置变量 mysql-auditlog_filename，也就是审计日志的文件名。此变量的默认值为空，也就是默认情况下不启用日志记录。   启用后，将记录以下事件：   在 MySQL 模块上：成功认证、认证失败、正常断开、封闭连接、更改架构（COMINITDB）   在管理模块上：成功认证、认证失败、正常断开、封闭连接   变量 mysql-auditlog_filename：此变量定义记录审核事件的审计日志的基本名称。日志文件的文件名将是基本名称，后跟一个 8 位数的逐行编号。 默认值为空字符串（）。 mysql-auditlog_filesize ：此变量定义关闭当前文件并创建新文件时审计日志的最大文件大小。 默认值为 104857600（100MB）。   当前实现仅支持一种日志记录格式：JSON。     属性：   client_addr ：连接到 ProxySQL 的客户端的地址（IP：port）   proxy_addr ：ProxySQL 正在侦听的绑定接口的地址（IP：端口）（仅适用于 MySQL 模块）   event：事件类型。当前可能的值：     MySQLClientConnect_OK ：成功连接到 MySQL 模块     MySQLClientConnect_ERR ：与 MySQL 模块的连接失败     MySQLClientClose ：MySQL 会话被关闭     MySQLClientQuit：客户端向 COM_QUITMySQL 模块发送显式信息     MySQLClientInitDB：客户端向 COMINIT_DBMySQL 模块发送显式信息     AdminConnectOK ：成功连接到管理模块     AdminConnectERR ：与管理模块的连接失败     Admin_Close ：管理员会话已关闭     AdminQuit：客户端向 COMQUIT 管理模块发送显式信息   time ：事件发生时的人类可读时间，以毫秒为单位   timestamp ：纪元时间（以毫秒为单位）   ssl ：布尔值，指定是否使用 SSL   schemaname：用于成功建立连接的当前模式   username：客户的用户名   threadid：分配给客户端的 threadid（会话 ID）   creation_time ：创建会话时，信息仅在会话关闭时可用   duration ：创建会话以来的时间（以毫秒为单位），此信息仅在会话关闭时可用   extra_info：提供其他信息的属性。当前仅用于描述会话在代码的哪一部分关闭。   审计日志示例： {\"client_addr\":\"10.0.200.179:51543\",\"event\":\"MySQL_Client_Connect_OK\",\"proxy_addr\":\"0.0.0.0:6033\",\"schemaname\":\"information_schema\",\"ssl\":false,\"thread_id\":8,\"time\":\"2020-08-13 16:08:24.960\",\"timestamp\":1597306104960,\"username\":\"dbmgr\"}   2、通用操作日志 管理员账户登录 ProxySQL：   mysql> SET mysql-eventslog_filename='all_queries.log'; mysql> LOAD MYSQL VARIABLES TO RUNTIME; mysql> SAVE MYSQL VARIABLES TO DISK;   日志存储路径是：/var/lib/proxysql   接下来，创建查询规则以匹配需要记录的日志记录。如果需要记录所有，则一个简单的规则就可以： mysql\> INSERT INTO mysql_query_rules (rule_id, active, match_digest, log,apply) VALUES (1,1,\'.\',1,0);   请注意，并不是所有查询都由查询处理器处理。一些特殊，比如：commit、rollback 和 set autocommit 都是在查询处理器之前被处理的。如果要记录此类查询操作，则需要启用全局记录。   mysql> SET mysql-eventslog_default_log=1; mysql> LOAD MYSQL VARIABLES TO RUNTIME; mysql> SAVE MYSQL VARIABLES TO DISK;   精确记录：记录 web_opr 这个账户的所有操作记录 mysql\> INSERT INTO mysql_query_rules (rule_id, active, username, log, apply) VALUES (1, 1, \'web_opr\', 1, 0);   记录对 T2 表的所有 INSERT 语句： INSERT INTO mysql_query_rules (rule_id, active, match_digest, log, apply) VALUES (1, 1, \'INSERT.\*t2, 1, 0);   记得要让规则生效，需要载入 RUNTIME，要保存就要 SAVE 到磁盘  LOAD MYSQL QUERY RULES TO RUNTIME;  SAVE MYSQL QUERY RULES TO DISK;        在 2.0.6 版中，新变量 mysql-eventslog_format 控制查询日志的记录格式：   变量值：   1：默认值：查询记录在二进制格式文件 请注意，在 2.0.6 版本更好的支持下，引入了一种预处理语句，记录 rowsaffected 和 rowssent。因此，需要使用更新的版本 eventslogreadersample 来读取这些文件。 2 ：查询以 JSON 格式记录。   JSON 格式记录   要启用 JSON 格式的日志记录，需要设置 mysql-eventslog_format=2。   SET mysql-eventslog_format=2; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;   示例： {"client":"127.0.0.1:6966","digest":"0x0CA2979885DD8D2A","duration_us":26126,"endtime":"2020-08-13 15:34:05.674394","endtime_timestamp_us":1597304045674394,"event":"COM_QUERY","hostgroup_id":10,"query":"insert into hrttest.t2 values (123123)","rows_affected":1,"rows_sent":0,"schemaname":"information_schema","server":"10.0.53.210:3307","starttime":"2020-08-13 15:34:05.648268","starttime_timestamp_us":1597304045648268,"thread_id":6,"username":"web_opr"}   {"client":"127.0.0.1:6966","digest":"0x0CA2979885DD8D2A","duration_us":2235,"endtime":"2020-08-13 15:34:15.999281","endtime_timestamp_us":1597304055999281,"event":"COM_QUERY","hostgroup_id":10,"query":"insert into hrttest.t2 values (456456)","rows_affected":1,"rows_sent":0,"schemaname":"information_schema","server":"10.0.53.210:3307","starttime":"2020-08-13 15:34:15.997046","starttime_timestamp_us":1597304055997046,"thread_id":6,"username":"web_opr"}   在目前的版本，这些日志还无法实现保存在数据库表中，但是官方已经计划在后期的版本中实现。但这些日志可以通过脚本，写入ES，Doris，Starrocks中。   操作总结 mysql> SET mysql-eventslog_filename='all_queries.log';  # 可以指定日志存储绝对路径： set mysql-eventslog_filename='/data/ProxySQL/log/sql.log'； mysql> SET mysql-eventslog_default_log=1; mysql> SET mysql-eventslog_format=2;   mysql> LOAD MYSQL VARIABLES TO RUNTIME; mysql> SAVE MYSQL VARIABLES TO DISK;