CentOS 7.2 mysql-5.7.17 Audit plugin installation, opening and setting

Recently, because of some things happened, there appeared a very benign page, into an empty page. Turn head development colleague to question me, is not what I moved, later after investigation found that the platform operation of colleagues mistakenly deleted data caused.

So if the operation or development department has a lot of people assigned to the database and other operations permissions, such as the deletion of the table or the field is lost can not find who did it, so this pot can only be transported to the back. Therefore, it is necessary to keep the database operation record, but the cost is that the performance of the server will be worse, but for security reasons, it is recommended to open.

In some operational restrictions, it is inevitable that people with super privileges to do what the user did, so or the root user also audit together, can avoid unnecessary trouble

1. We use mysql-5.7.17 as an example to place a third-party plug-in for auditing. Knowing the MySQL AUDIT plugin is a MySQL security audit plugin, provided by McAfee, designed to emphasize security and audit capabilities. Can be used as a standalone audit solution, or configured for data transfer to external monitoring tools.

Audit

Https://bintray.com/mcafee/mysql-audit-plugin/release

2. Preparing to install the audit plugin

[[Email Protected]~]unzip audit-plugin-mysql-5.7-1.1.2-694-linux-x86_64.zip

Archive:audit-plugin-mysql-5.7-1.1.2-694-linux-x86_64.zip

creating:audit-plugin-mysql-5.7-1.1.2-694/

creating:audit-plugin-mysql-5.7-1.1.2-694/lib/

Inflating:audit-plugin-mysql-5.7-1.1.2-694/lib/libaudit_plugin.so

Inflating:audit-plugin-mysql-5.7-1.1.2-694/copying

Inflating:audit-plugin-mysql-5.7-1.1.2-694/thirdparty.txt

Inflating:audit-plugin-mysql-5.7-1.1.2-694/readme.txt

creating:audit-plugin-mysql-5.7-1.1.2-694/utils/

inflating:audit-plugin-mysql-5.7-1.1.2-694/utils/offset-extract.sh

3. View MySQL as the path to the placement of the audit plugin

MySQL > Show global variables like ' plugin_dir ';

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

| variable_name | Value |

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

| Plugin_dir |/data0/mysql/lib/plugin/|

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

1 row in Set (0.00 sec)

4. Copy the plugin that needs to be installed to the specified path above and give the executable permission to

[[Email PROTECTED]~]CD audit-plugin-mysql-5.7-1.1.2-694/lib

[[Email PROTECTED]]CP libaudit_plugin.so/data0/mysql/lib/plugin/

[[Email protected]~]cd/data0/mysql/lib/plugin/

[Email protected]]chmod a+x libaudit_plugin.so

5. After 4 steps, you can load the audit plugin on the database

Mysql> Install plugin audit soname ' libaudit_plugin.so ';

Query OK, 0 rows affected (1.78 sec)

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

6. Once the plugin is loaded, check that the audit plugin is plugged into the database and view the relevant plug-in version

Mysql> show global status like '%audit% ';

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

| variable_name | Value |

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

| audit_protocol_version | 1.0 |

| audit_version | 1.1.2-694 |

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

2 rows in Set (0.00 sec)

7. Open the audit function of the database

mysql> set global audit_json_file=on;

Query OK, 0 rows Affected (0.00 sec)

8. Review the database for relevant audit related information

Mysql> show global 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__data0_mysql-data_3306 |

| Audit_json_socket_retry | 10 |

|      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) *? (?:p assword)? (?:/ \*.*?\*/|\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 | |

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

Rows in Set (0.00 sec)

9. You can change the path where you want to store the log so that you will remember where to look for the log

Audit_json_log_file is the database in the audit log mysql-audit.json storage path, the general audit log is placed in the database data storage path. Like my mysql-audit.json is stored in the/data0/mysql-data. Of course if you want to change to the path you specify can also, with MySQL > set global audit_json_log_file= ' you want to place the path ';

10. Reduce Log Content

After reviewing the audit log that has been turned on, you will find that the log volume is too large, as long as the site is visited, there must be a log record of Select, because by default audit_record_cmds=null, all operation instructions are logged, But we open the audit log for the purpose of auditing, not to remember Journal, so only record insert, delete, update on it. This is more in line with our original intention, so you can do as follows:

MySQL > Set global audit_record_cmds = ' insert,delete,update ';

11. Limit to the database you want to audit

By default Audit_record_objs=null, that is, all objects in the database are listed as record objects. But for security reasons, I audit. Of course, you can also specify which database tables require audit records, as in the following example:

MySQL > Set global audit_record_objs = ' mysql.*,test.* ';

The 12.mysql audit still has the specialization, is the white list this kind of thing, in short, can designate who is exempt from the audit the suffering.

Settings are as follows:

MySQL > Set global audit_whitelist_users = ' z ';

13. View Audit Log Content

Cat/data0/mysql-data/mysql-audit.json

Probably through the above steps, the audit can be said to be briefly completed.

This article is from the "10793382" blog, please be sure to keep this source http://10803382.blog.51cto.com/10793382/1925000

CentOS 7.2 mysql-5.7.17 Audit plugin installation, opening and setting