Bug #77553 audit_log_exclude_accounts does not work using IP addresses
Submitted: 30 Jun 2015 1:00 Modified: 3 Oct 2016 18:21
Reporter: Jesse Duce Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Audit Severity:S3 (Non-critical)
Version:5.6.22, 5.6.28 OS:CentOS
Assigned to: CPU Architecture:Any

[30 Jun 2015 1:00] Jesse Duce 
Description:
I've tried every possible combination of user@host format, setting the variable in the my.cnf and restarting as well as setting the global variable online - and nothing prevents what I've entered from being logged to the audit.log.

I've tried these combinations with two different users.

set global audit_log_exclude_accounts = 'user1';
set global audit_log_exclude_accounts = 'user1@';
set global audit_log_exclude_accounts = 'user1@%';
set global audit_log_exclude_accounts = 'user1@127.0.0.1';
set global audit_log_exclude_accounts = '\'user1\'@\'127.0.0.1\'';

<AUDIT_RECORD TIMESTAMP="2015-06-30T00:17:48 UTC" RECORD_ID="800130107_2015-06-29T23:53:17" NAME="Connect" CONNECTION_ID="13" STATUS="0" STATUS_CODE="0" USER="user1" OS_LOGIN="" HOST="" IP="127.0.0.1" COMMAND_CLASS="connect" PRIV_USER="user1" PROXY_USER="" DB=""/>

I also can't get the inverse of this to work.  audit_log_include_accounts also does nothing. Yes, the opposite setting I was testing was always set to null.

| Variable_name               | Value           |
| audit_log_buffer_size       | 1048576         |
| audit_log_connection_policy | ALL             |
| audit_log_current_session   | ON              |
| audit_log_exclude_accounts  | user1@127.0.0.1 |
| audit_log_file              | audit.log       |
| audit_log_flush             | OFF             |
| audit_log_format            | OLD             |
| audit_log_include_accounts  |                 |
| audit_log_policy            | LOGINS          |
| audit_log_rotate_on_size    | 1073741824      |
| audit_log_statement_policy  | NONE            |
| audit_log_strategy          | ASYNCHRONOUS    |

I've set audit_log_connection_policy to ALL and audit_log_policy to LOGINS
I've set audit_log_connection_policy to NONE and audit_log_policy to LOGINS
I've set audit_log_connection_policy to ALL and audit_log_policy to NONE

The exclude variable literally does not work.

How to repeat:
Install mysql enterprise.
Install the audit plugin.
Log logins only.
Exclude an account.
tail -f audit.log
connect as the account that should be excluded.
connect as an account that should not be excluded.

The excluded account should not show up in the audit.log.
The account not excluded should show up in the audit log.
[23 Jul 2015 8:42] MySQL Verification Team 
Hello Jesse,

Thank you for the report.
I'm cannot repeat this issue on 5.6.25 GA builds.
Could you please try with 5.6.25 and report us back if you are still having this issue.

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Umesh
[23 Jul 2015 8:43] MySQL Verification Team 
// 5.6.25

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.25: bin/mysql -uroot --port=15000 --protocol=tcp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25-enterprise-commercial-advanced MySQL Enterprise .
.
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
Query OK, 0 rows affected (0.00 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+--------------+-------------+
| Name                       | Status   | Type               | Library      | License     |
+----------------------------+----------+--------------------+--------------+-------------+
.
.
| audit_log                  | ACTIVE   | AUDIT              | audit_log.so | PROPRIETARY |
+----------------------------+----------+--------------------+--------------+-------------+
43 rows in set (0.00 sec)

mysql> 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 |
+-----------------------------+--------------+
12 rows in set (0.00 sec)

mysql> SET GLOBAL audit_log_include_accounts = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = 'ushastry@localhost,mysql@localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> 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  | ushastry@localhost,mysql@localhost |
| 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)

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on db1.* to 'ushastry'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on db2.* to 'mysql'@'localhost';
Query OK, 0 rows affected (0.00 sec)

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.25: bin/mysql -uushastry --port=15000 --protocol=tcp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.25-enterprise-commercial-advanced MySQL Enterprise .
.
mysql> show grants;
+-----------------------------------------------------------+
| Grants for ushastry@localhost                             |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ushastry'@'localhost'              |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'ushastry'@'localhost' |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use db1
Database changed
mysql> create table t select now() from dual;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> \q
Bye

// Confirmed that excluded accounts are not logged

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.25: bin/mysql -uroot --port=15000 --protocol=tcp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.25-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();
+---------------------------------------+
| version()                             |
+---------------------------------------+
| 5.6.25-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@audit_log_exclude_accounts;
+------------------------------------+
| @@audit_log_exclude_accounts       |
+------------------------------------+
| ushastry@localhost,mysql@localhost |
+------------------------------------+
1 row in set (0.00 sec)

mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.25: more 77553/audit.log |grep root
  <AUDIT_RECORD TIMESTAMP="2015-07-23T08:25:46 UTC" RECORD_ID="2_2015-07-23T08:25:46" NAME="Query" CONNECTION_ID="1" STATUS="0" STATUS_CODE="0" USER="root[root] @ localhost []" OS_LOGIN="" HOST="localhost" IP="" COMMAND_CLASS="install_plugin" SQLTEXT="INSTALL PLUGIN audit_log SONAME 'audit_log.so'"/>
  <AUDIT_RECORD TIMESTAMP="2015-07-23T08:26:25 UTC" RECORD_ID="3_2015-07-23T08:25:46" NAME="Query" CONNECTION_ID="1" STATUS="0" STATUS_CODE="0" USER="root[root] @ localhost []" OS_LOGIN="" HOST="localhost" IP="" COMMAND_CLASS="show_plugins" SQLTEXT="show plugins"/>
  <AUDIT_RECORD TIMESTAMP="2015-07-23T08:26:35 UTC" RECORD_ID="4_2015-07-23T08:25:46" NAME="Query" CONNECTION_ID="1" STATUS="0" STATUS_CODE="0" USER="root[root] @ localhost []" OS_LOGIN="" HOST="localhost" IP="" COMMAND_CLASS="show_variables" SQLTEXT="show variables like 'audit_log%'"/>
  <AUDIT_RECORD TIMESTAMP="2015-07-23T08:29:21 UTC" RECORD_ID="5_2015-07-23T08:25:46" NAME="Query" CONNECTION_ID="1" STATUS="0" STATUS_CODE="0" USER="root[root] @ localhost []" OS_LOGIN="" HOST="localhost" IP="" COMMAND_CLASS="set_option" SQLTEXT="SET GLOBAL audit_log_include_accounts = NULL"/>
  <AUDIT_RECORD TIMESTAMP="2015-07-23T08:40:59 UTC" RECORD_ID="6_2015-07-23T08:25:46" NAME="Connect" CONNECTION_ID="3" STATUS="0" STATUS_CODE="0" USER="root" OS_LOGIN="" HOST="localhost" IP="::1" COMMAND_CLASS="connect" PRIV_USER="root" PROXY_USER="" DB=""/>
  <AUDIT_RECORD TIMESTAMP="2015-07-23T08:40:59 UTC" RECORD_ID="7_2015-07-23T08:25:46" NAME="Query" CONNECTION_ID="3" STATUS="0" STATUS_CODE="0" USER="root[root] @ localhost [::1]" OS_LOGIN="" HOST="localhost" IP="::1" COMMAND_CLASS="select" SQLTEXT="select @@version_comment limit 1"/>
  <AUDIT_RECORD TIMESTAMP="2015-07-23T08:41:06 UTC" RECORD_ID="8_2015-07-23T08:25:46" NAME="Query" CONNECTION_ID="3" STATUS="0" STATUS_CODE="0" USER="root[root] @ localhost [::1]" OS_LOGIN="" HOST="localhost" IP="::1" COMMAND_CLASS="select" SQLTEXT="select version()"/>
  <AUDIT_RECORD TIMESTAMP="2015-07-23T08:41:18 UTC" RECORD_ID="9_2015-07-23T08:25:46" NAME="Query" CONNECTION_ID="3" STATUS="0" STATUS_CODE="0" USER="root[root] @ localhost [::1]" OS_LOGIN="" HOST="localhost" IP="::1" COMMAND_CLASS="select" SQLTEXT="SELECT @@audit_log_exclude_accounts"/>
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.25:
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.25: more 77553/audit.log |grep ushastry
[23 Dec 2015 4:17] MySQL Verification Team 
- with MySQL 5.6.28 ( reproducible when IP is used), marked verified
[4 Jan 2016 20:53] Jesse Duce 
Correcting the title - as the bug is limited to specifying IP address.
[28 Sep 2016 6:10] MySQL Verification Team 
I confirm that this issue is no longer reproducible on latest GA 5.6.33(Not sure which internal bug fixed, may be BUG#19509471?)
[3 Oct 2016 18:21] Paul Dubois 
Posted by developer:
 
Noted in 5.6.30, 5.7.12 changelogs.

Account filtering performed by the audit_log plugin incorrectly used
the account named by the USER() function rather than the
CURRENT_USER() function (the latter being the account used for
authentication).