How to filter mysql audit log by user account

Question

My issue is even i disable the root user from audit logging but still logging for these user. Anyone please help. Here is i did step by step.

[Setp -1] Check the audit log variable.

mysql> SHOW VARIABLES LIKE 'audit_log%';
+-----------------------------+--------------+
| Variable_name               | Value        |
+-----------------------------+--------------+
| audit_log_buffer_size       | 1048576      |
| audit_log_connection_policy | ALL          |
| 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            | ALL          |
| audit_log_rotate_on_size    | 0            |
| audit_log_statement_policy  | ALL          |
| audit_log_strategy          | ASYNCHRONOUS |
+-----------------------------+--------------+
12 rows in set (0.00 sec)

[Setp-2] The following statement is disable audit logging for root account.

-- audit_log_include_accounts to NULL
SET GLOBAL audit_log_include_accounts = NULL;
SET GLOBAL audit_log_exclude_accounts = root@%;

Note: I used the root@% instead root@localhost because of this database server can access from another ip address.

[Setp-3] I call the select statement SELECT * FROM SSVR_AUDIT_LOG from remote PC.

[Step-4] I checked the 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 * FROM `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 FROM `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 is my reference link enter link description here

score 2 · Answer 1 · answered Apr 27 '16 at 03:52

I got the answer for my question. Here is correct answer. When you facing like that issue, you can follow below the steps.

Audit Log Filtering by Account

List all ‘audit log’ configuration items

> mysql -u root -p
> SHOW VARIABLES LIKE ‘audit_log%’;
+-----------------------------+--------------+
| Variable_name               | Value        |
+-----------------------------+--------------+
| audit_log_buffer_size       | 1048576      |
| audit_log_connection_policy | ALL          |
| 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            | ALL          |
| audit_log_rotate_on_size    | 0            |
| audit_log_statement_policy  | ALL          |
| audit_log_strategy          | ASYNCHRONOUS |
+-----------------------------+--------------+

To add the remote application server host name and 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 only for the application database user (root) local host and 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 all ‘audit log’ configuration items and check the audit_log_exclude_account value.

> SHOW VARIABLES LIKE 'audit_log%';
> +-----------------------------+----------------------------+
| Variable_name               | Value                      |
+-----------------------------+----------------------------+
| audit_log_buffer_size       | 1048576                    |
| audit_log_connection_policy | ALL                        |
| 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            | ALL                        |
| audit_log_rotate_on_size    | 0                          |
| audit_log_statement_policy  | ALL                        |
| audit_log_strategy          | ASYNCHRONOUS               |
+-----------------------------+----------------------------+

How to filter mysql audit log by user account

1 Answers1