sql - How to filter mysql audit log by user account -
my issue disable root user audit logging still logging these user. please help. here did step step.
[setp -1] check audit log variable.
mysql> show variables 'audit_log%'; +-----------------------------+--------------+ | variable_name | value | +-----------------------------+--------------+ | audit_log_buffer_size | 1048576 | | audit_log_connection_policy | | | audit_log_current_session | on | | audit_log_exclude_accounts | | | audit_log_file | audit.log | | audit_log_flush | off | | audit_log_format | old | | audit_log_include_accounts | | | audit_log_policy | | | audit_log_rotate_on_size | 0 | | audit_log_statement_policy | | | audit_log_strategy | asynchronous | +-----------------------------+--------------+ 12 rows in set (0.00 sec)
[setp-2] following statement disable audit logging root account.
-- audit_log_include_accounts null set global audit_log_include_accounts = null; set global audit_log_exclude_accounts = root@%;
note: used root@% instead root@localhost because of database server can access ip address.
[setp-3] call select statement select * ssvr_audit_log
remote pc.
[step-4] checked audit log in db server.
<audit_record timestamp="2016-04-22t03:49:11 utc" record_id="593_2016-04-22t01:28:17" name="query" connection_id="6" status="0" status_code="0" user="root[root] @ [162.16.22.48]" os_login="" host="" ip="162.16.22.48" command_class="show_create_table" sqltext="show create table `ssvr_audit_log`"/> <audit_record timestamp="2016-04-22t03:49:12 utc" record_id="594_2016-04-22t01:28:17" name="query" connection_id="7" status="0" status_code="0" user="root[root] @ [162.16.22.48]" os_login="" host="" ip="162.16.22.48" command_class="select" sqltext="select * `ssvr_audit_log` limit 0, 1000"/> <audit_record timestamp="2016-04-22t03:49:12 utc" record_id="595_2016-04-22t01:28:17" name="query" connection_id="7" status="0" status_code="0" user="root[root] @ [162.16.22.48]" os_login="" host="" ip="162.16.22.48" command_class="show_fields" sqltext="show columns `tldssvr`.`ssvr_audit_log`"/> <audit_record timestamp="2016-04-22t03:49:13 utc" record_id="596_2016-04-22t01:28:17" name="quit" connection_id="7" status="0" status_code="0" user="root" os_login="" host="" ip="162.16.22.48" command_class="connect"/>
here reference link enter link description here
i got answer question. here correct answer. when facing issue, can follow below steps.
audit log filtering account
- list ‘audit log’ configuration items
> mysql -u root -p > show variables ‘audit_log%’; +-----------------------------+--------------+ | variable_name | value | +-----------------------------+--------------+ | audit_log_buffer_size | 1048576 | | audit_log_connection_policy | | | audit_log_current_session | off | | audit_log_exclude_accounts | | | audit_log_file | audit.log | | audit_log_flush | off | | audit_log_format | old | | audit_log_include_accounts | | | audit_log_policy | | | audit_log_rotate_on_size | 0 | | audit_log_statement_policy | | | audit_log_strategy | asynchronous | +-----------------------------+--------------+
to add remote application server host name , ip address in database server.
> cat /etc/hosts > 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 162.16.22.48 app_pc ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
to disable audit logging application database user (root) local host , remote host accounts.
> mysql –u root –p >set global audit_log_include_accounts = null; >set global audit_log_exclude_accounts = 'root@localhost,root@app_pc';
- list ‘audit log’ configuration items , check audit_log_exclude_account value.
> show variables 'audit_log%'; > +-----------------------------+----------------------------+ | variable_name | value | +-----------------------------+----------------------------+ | audit_log_buffer_size | 1048576 | | audit_log_connection_policy | | | audit_log_current_session | off | | audit_log_exclude_accounts | root@localhost,root@app_pc | | audit_log_file | audit.log | | audit_log_flush | off | | audit_log_format | old | | audit_log_include_accounts | | | audit_log_policy | | | audit_log_rotate_on_size | 0 | | audit_log_statement_policy | | | audit_log_strategy | asynchronous | +-----------------------------+----------------------------+
Comments
Post a Comment