来自Mcafee的MySQL审计插件

项目地址

https://github.com/mcafee/mysql-audit/

安装

将安装包libaudit_plugin.so文件复制到 plugin_dir 目录

mysql> show global variables like 'plugin_dir';+---------------+------------------------------+| Variable_name | Value                        |+---------------+------------------------------+| plugin_dir    | /usr/local/mysql/lib/plugin/ |+---------------+------------------------------+1 row in set (0.01 sec)

可以使用两种方式安装，

方法1：[mysqld] plugin-load=AUDIT=libaudit_plugin.so 不需要重启，生产环境建议使用这种方式方法2：mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

验证

mysql>  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%';+-------------+---------------+| PLUGIN_NAME | PLUGIN_STATUS |+-------------+---------------+| AUDIT       | ACTIVE        |+-------------+---------------+1 row in set (0.00 sec)

开启审计

默认不开启

mysql> set global audit_json_file=on;Query OK, 0 rows affected (0.00 sec查看输出位置mysql> show global variables like 'audit_json_log_file';+---------------------+------------------+| Variable_name       | Value            |+---------------------+------------------+| audit_json_log_file | mysql-audit.json |+---------------------+------------------+1 row in set (0.00 sec)##测试session1# mysql> select version();+------------+| version()  |+------------+| 5.7.18-log |+------------+1 row in set (0.00 sec)session2# tail -f mysql-audit.json {"msg-type":"activity","date":"1494127901889","thread-id":"7","query-id":"36","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.5","_client_name":"libmysql","_pid":"8116","_client_version":"5.7.18","_platform":"x86_64","program_name":"mysql"},"pid":"8116","os_user":"root","appname":"mysql","rows":"1","cmd":"select","query":"select version()"}

常用参数

1. audit_json_file=on|off
是否开启audit功能
 
2. audit_json_log_file
审计文件的路径和名称信息
 
3. audit_record_cmds
audit记录的命令,默认为记录所有命令
可以设置为任意dml、dcl、ddl的组合
如：audit_record_cmds="select,insert,delete,update"

 
4. audit_record_objs
audit记录操作的对象或表,默认为记录所有对象,
也可以指定为下面的格式
audit_record_objs="*.mytable,mydb.*,mydb.mytable"

5. audit_whitelist_users
白名单
如set global audit_whitelist_users="root,rpl";

更多参数详见文章最后。

 

所有调优参数

• audit_json_log_file: json log file name. If audit_json_file option is enabled will write audit trail to this file. Value may be either an absolute path or relative to the MySQL datadir. Default value: mysql-audit.json.
• audit_json_file: json log file ON|OFF.
• audit_json_file_sync: json log file sync period. If the value of this variable is greater than 0, audit log will sync to disk after every audit_json_file_sync writes. Default value: 0.
• audit_json_file_flush: Calling set global audit_json_file_flush=on will cause a flush of the log file (close and reopen of the log). This can be used to rotate logs similarly to how MySQL manages its log files. See: http://dev.mysql.com/doc/refman/5.5/en/log-file-maintenance.html. For further clarification, see issue #140.
• audit_json_socket_name: json UNIX socket name. If audit_json_socket option is enabled will write audit trail to this UNIX socket.
• audit_json_socket: json UNIX socket ON|OFF.
• audit_uninstall_plugin: AUDIT uninstall plugin ON|OFF (command line/conf file only). If disabled attempts to uninstall the AUDIT plugin via the sql UNINSTALL command will fail. Provides added security from uninstalling the plugin. Also protection from CVE-2010-1621affecting versions up to 5.1.46.
• audit_validate_checksum: mysqld binary checksum validation ON|OFF. See Troubleshooting section in Installation page.
• audit_checksum: Checksum for mysqld to validate (command line/conf file only). Used when audit_offsets are present. The plugin will not load if the specified checksum doesn't match the calculated one. This is useful when offsets have been set manually and you want to avoid using the manual offsets without validation after an upgrade of mysqld.
• audit_record_cmds: Comma separated list of commands to log to the audit trail. For example:insert,update,delete.
• audit_record_objs: Comma separated list of objects (tables) to log to the audit trail. Table name should be specified as: database.table. Wild cards are supported and it is possible to specify: *.mytable or mydb.*. Specify: {} as part of the list to include the empty set to catch also cases where an activity has no objects (for example connect and quit).
• audit_whitelist_users: Comma separated list of white-listed users whose queries are not recorded. Specify: {} as part of the list to include the empty user.
• audit_whitelist_cmds: Comma separated list of white-listed cmds whose queries are not recorded. Introduced in version 1.0.6.
• audit_force_record_logins: Force logging: Connect, Quit and Failed Login commands, regardless of the settings in audit_record_cmds and audit_record_objs variables. ON|OFF. Default value: OFF. Introduced in version 1.0.8.
• audit_header_msg: Header message logging ON|OFF. Default value: ON. Introduced in version 1.0.6.
• audit_password_masking_cmds: Comma separated list of commands for which the password masking regex will be applied. Default value includes MySQL commands that may include a password clause:CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER. Introduced in version 1.0.6.
• audit_password_masking_regex: PCRE compliant regular expression used for password masking. Regex will be applied only to statements with command type as specified at:audit_password_masking_cmds. Introduced at version 1.0.6.
• audit_json_file_retry: json log file retry interval. If the plugin fails to open/write to the json log file, will retry to open every specified interval in seconds. Set for 0 to disable retrying. Defaults to 60 seconds. Introduced in version 1.0.6.
• audit_json_socket_retry: json socket retry interval. If the plugin fails to connect/write to the json audit socket, will retry to connect every specified interval in seconds. Set for 0 to disable retrying. Defaults to 10 seconds. Introduced in version 1.0.6.
• audit_json_file_bufsize: json file buffer size in bytes used for logging. Value of 0 means default size, value of 1 means no buffering. Max value: 262144 (256KB). A larger value may improve performance when logging large statements (log entries larger than 4KB). Defaults to 0. If changed during run-time need, to perform a flush for the new value to take affect. Default is ON. Introduced in version 1.0.8.
• audit_client_capabilities: If enabled, the plugin sends the value of client capabilities bit map as an unsigned 64-bit value. Default is OFF. Introduced in version 1.1.1.
• audit_sess_connect_attrs: If enabled, the plugin sends session connection attributes. Default is ON. Currently supported only on MySQL 5.6 and 5.7. Introduced in version 1.1.1. Note: logging of connection attributes requires also a client of MySQL 5.6 and up to connect to the server. The connection attributes where added in 5.6 as part of the communication protocol. When enabled a json entry of the form will be added to each log entry: "connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"11450","_client_version":"5.6.20-68.0","_platform":"x86_64","program_name":"mysql"}. For further info on connection attributes in MySQL see: https://dev.mysql.com/doc/refman/5.6/en/performance-schema-connection-attribute-tables.html.
• audit_socket_creds: If enabled, the plugin sends information about the client process, such as the PID, application name and user name who owns it. Default is ON. Introduced in version 1.1.2.
• audit_before_after: Controls whether the plugin writes its log records before or after execution of the current SQL statement, or both. Possible values are 'before', 'after', or 'both'. Default is 'after'. Introduced in version 1.1.2.