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