MySQL审计功能（插件）

审计功能是MySQL数据库的一个弱项 Oracle公司在5.5的企业版增加了mysql-audit plugin，问题是该插件类似于general-log,而且仅仅存在于企业版！

因此使用审计功能需通过加载第三方插件（plugin），当前的插件有以下几种：

①Percona Audit Log Plugin

• Audit Log是Percona server的内置审计插件
  
• 此插件兼容性不高，适用于Percona server使用，安装到MySQL社区版上，筛选过滤语句类型时会出现问题
  

②McAfee MySQL Audit Plugin 

• MySQL Audit是McAfee公司基于percona开发的MySQL审计插件
  
• McAfee的MySQL Audit插件以JSON格式保存，且日志信息比较大
  

③MariaDB Audit Plugin

• MariaDB Audit是MariaDB的内置审计插件
  
• MariaDB_5.5.37版本和MariaDB_10.0.10以后版本的audit插件支持MariaDB, MySQL、Percona Server使用

McAfee MySQL Audit 安装方法

1、下载对应版本的二进制包

下载地址：https://bintray.com/mcafee/mysql-audit-plugin/release

2、查看MySQL的插件目录

1

mysql> show global variables like "plugin_dir";

2

+---------------+------------------------------+

3

| Variable_name | Value                        |

4

+---------------+------------------------------+

5

| plugin_dir    | /usr/local/mysql/lib/plugin/ |

6

+---------------+------------------------------+

7

1 row in set (0.01 sec)

3、复制下载的包中的/lib目录下的.so文件至plugin_dir

1

$ cp libaudit_plugin.so /usr/local/mysql/lib/plugin/

4、对.so文件授权授权

1

$ chmod 755 libaudit_plugin.so

2

$ chown mysql:mysql libaudit_plugin.so

5、加载插件

1

mysql> INSTALL PLUGIN AUDIT SONAME "libaudit_plugin.so";

6、查看版本

1

mysql> show global status like "AUDIT_version";

2

+---------------+-----------+

3

| Variable_name | Value     |

4

+---------------+-----------+

5

| Audit_version | 1.0.9-585 |

6

+---------------+-----------+

7

1 row in set (0.01 sec)

7、开启audit功能

1

SET GLOBAL audit_json_file=ON;

8、审计测试

执行任何语句（默认会记录所有语句），然后去MySQL的datadir下查看mysql-audit.json文件(默认为该文件)

1

$ cat mysql-audit.json

2

{"msg-type":"header","date":"1502882826793","audit-version":"1.0.9-585","audit-protocol-version":"1.0","hostname":"centos65-h1","mysql-version":"5.7.17-log","mysql-program":"/usr/local/mysql/bin/mysqld","mysql-socket":"/data/mysql.sock","mysql-port":"3306"}

3

{"msg-type":"activity","date":"1502882867249","thread-id":"3","query-id":"7","user":"root","priv_user":"root","host":"localhost","ip":"","cmd":"show_plugins","objects":[{"db":"information_schema","name":"/tmp/#sql_1ceb_0","obj_type":"TABLE"}],"query":"show plugins"}

4

{"msg-type":"activity","date":"1502888907808","thread-id":"3","query-id":"8","user":"root","priv_user":"root","host":"localhost","ip":"","cmd":"show_variables","objects":[{"db":"information_schema","name":"/tmp/#sql_1ceb_0","obj_type":"TABLE"}],"query":"show global variables like "plugin_dir""}

安装时需注意的问题

安装插件时报错：

1

mysql> INSTALL PLUGIN AUDIT SONAME "libaudit_plugin.so";

2

ERROR 1123 (HY000): Can"t initialize function "AUDIT"; Plugin initialization function failed.

查看错误日志发现如下报错：

1

2017-07-25T14:26:24.547189Z 4 [ERROR] Plugin "AUDIT" init function returned error.

2

2017-07-25T14:26:24.547193Z 4 [ERROR] Plugin "AUDIT" registration as a AUDIT failed.

报此类型的错误的原因为未设置此版本的偏移量

解决方法：

1、利用插件包中的offset脚本计算出偏移量（执行脚本前需安装gdb包）

1

$ sh offset-extract.sh /usr/local/mysql/bin/mysqld

2

//offsets for: /usr/local/mysql/bin/mysqld (5.7.19)

3

{"5.7.19","b4633eb887552a3bbb5db3a1eea76e48", 7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964, 4352, 3648, 3656, 3660, 6048, 2072, 8, 7032, 7072, 7056},

2、将偏移量添加到/etc/my.cnf中

1

audit_offsets=7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964, 4352, 3648, 3656, 3660, 6048, 2072, 8, 7032, 7072, 7056

如仍安装插件失败错误日志报错如下

1

2017-07-25T14:36:02.676771Z 4 [ERROR] Audit Plugin: Failed parsing audit_offsets: too many offsets specified

2

2017-07-25T14:36:02.676776Z 4 [ERROR] Audit Plugin: Failed parsing audit_offsets: 7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964, 4352, 3648, 3656, 3660, 6048, 2072, 8, 7032, 7072, 7056

3

2017-07-25T14:36:02.676779Z 4 [ERROR] Plugin "AUDIT" init function returned error.

4

2017-07-25T14:36:02.676783Z 4 [ERROR] Plugin "AUDIT" registration as a AUDIT failed.

则将偏移量从后依次删除并测试，发现偏移量设置如下时安装插件成功

1

audit_offsets=7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964

audit相关参数

1

+---------------------------------+-----------------------------+

2

| Variable_name                   | Value                       |

3

+---------------------------------+-----------------------------+

4

| audit_checksum                  |                             |

5

| audit_delay_cmds                |                             |

6

| audit_delay_ms                  | 0                           |

7

| audit_force_record_logins       | OFF                         |

8

| audit_header_msg                | ON                          |

9

| audit_json_file                 | ON                          |

10

| audit_json_file_bufsize         | 1                           |

11

| audit_json_file_flush           | OFF                         |

12

| audit_json_file_retry           | 60                          |

13

| audit_json_file_sync            | 0                           |

14

| audit_json_log_file             | mysql-audit.json            |

15

| audit_json_socket               | OFF                         |

16

| audit_json_socket_name          | /tmp/mysql.audit__data_3306 |

17

| audit_json_socket_retry         | 10                          |

18

| audit_offsets                   |                             |

19

| audit_offsets_by_version        | ON                          |

20

| audit_password_masking_cmds     | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER  |

21

| 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>.*?)(?<!\)["|"]    |

22

| audit_record_cmds               |                             |

23

| audit_record_objs               |                             |

24

| audit_uninstall_plugin          | OFF                         |

25

| audit_validate_checksum         | ON                          |

26

| audit_validate_offsets_extended | ON                          |

27

| audit_whitelist_cmds            | BEGIN,COMMIT                |

28

| audit_whitelist_users           |                             |

29

+---------------------------------+-----------------------------+ 

需要关注的参数

1

1. audit_json_file

2

  是否开启audit功能、

3

2. audit_json_log_file

4

  记录文件的路径和名称信息

5

3. audit_record_cmds

6

  audit记录的命令,默认为记录所有命令

7

  可以设置为任意dml、dcl、ddl的组合

8

  如：audit_record_cmds=select,insert,delete,update

9

  还可以在线设置set global audit_record_cmds=NULL

10

  (表示记录所有命令)

11

4. audit_record_objs

12

   audit记录操作的对象,默认为记录所有对象,在这里的对象是指MySQL的库

13

   可以用SET GLOBAL audit_record_objs=NULL设置为默认

14

   也可以指定为下面的格式

15

   audit_record_objs=,test.*,mysql.*,information_schema.*

16

5. audit_whitelist_users

17

   用户白名单

所有调优参数

1

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.

2

audit_json_file: json log file ON|OFF.

3

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.

4

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.

5

audit_json_socket_name: json UNIX socket name. If audit_json_socket option is enabled will write audit trail to this UNIX socket.

6

audit_json_socket: json UNIX socket ON|OFF.

7

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.

8

audit_validate_checksum: mysqld binary checksum validation ON|OFF. See Troubleshooting section in Installation page.

9

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.

10

audit_record_cmds: Comma separated list of commands to log to the audit trail. For example:insert,update,delete.

11

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).

12

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.

13

audit_whitelist_cmds: Comma separated list of white-listed cmds whose queries are not recorded. Introduced in version 1.0.6.

14

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.

15

audit_header_msg: Header message logging ON|OFF. Default value: ON. Introduced in version 1.0.6.

16

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.

17

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.

18

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.

19

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.

20

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.

21

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.

22

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.

23

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.

24

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.

MariaDB Audit 安装方法

1、下载mariadb-5.5.56-linux-x86_64.tar.gz解压获取server_audit.so插件

插件默认存放在./lib/plugin/目录下

2、查看MySQL的插件目录

1

mysql> show global variables like "plugin_dir";

2

+---------------+------------------------------+

3

| Variable_name | Value                        |

4

+---------------+------------------------------+

5

| plugin_dir    | /usr/local/mysql/lib/plugin/ |

6

+---------------+------------------------------+

7

1 row in set (0.01 sec)

3、复制.so文件至plugin_dir

1

$ cp server_audit.so /usr/local/mysql/lib/plugin/

4、对.so文件授权授权

1

$ chmod 755 server_audit.so

2

$ chown mysql:mysql server_audit.so

5、加载插件

1

mysql> INSTALL PLUGIN server_audit SONAME "server_audit.so";

6、开启审计日志

1

mysql> set global server_audit_logging = ON;

7、审计测试

执行任何语句（默认会记录所有语句），然后去MySQL的datadir下查看server_audit.log文件(默认为该文件)

1

$ cat server_audit.log 

2

20170704 20:57:36,centos6.5h1,root,localhost,5,6,QUERY,,"set global server_audit_logging=on",0

3

20170704 20:57:38,centos6.5h1,root,localhost,5,7,QUERY,,"show variables like "%audit%"",0

相关参数

1

mysql> show variables like "%audit%";

2

+-------------------------------+-----------------------+

3

| Variable_name                 | Value                 |

4

+-------------------------------+-----------------------+

5

| server_audit_events           |                       |

6

| server_audit_excl_users       |                       |

7

| server_audit_file_path        | server_audit.log      |

8

| server_audit_file_rotate_now  | OFF                   |

9

| server_audit_file_rotate_size | 1000000               |

10

| server_audit_file_rotations   | 9                     |

11

| server_audit_incl_users       |                       |

12

| server_audit_loc_info         |                       |

13

| server_audit_logging          | OFF                   |

14

| server_audit_mode             | 1                     |

15