Bug #84325 connection-control-plugin doesn't work if audit plugin is installed
Submitted: 23 Dec 2016 13:08 Modified: 26 Jan 2017 19:19
Reporter: Shinya Sugiyama Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Security: Audit Severity:S3 (Non-critical)
Version:5.7.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: audit_log, CONNECTION_CONTROL, CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS

[23 Dec 2016 13:08] Shinya Sugiyama 
Description:
If audit_log plugin is already installed on the Instance, CONNECTION_CONTROL plugin doesn't work.

root@localhost [mysql]> SELECT @@hostname,PLUGIN_NAME,PLUGIN_TYPE,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_TYPE = 'AUDIT';
+------------+--------------------+-------------+---------------+
| @@hostname | PLUGIN_NAME        | PLUGIN_TYPE | PLUGIN_STATUS |
+------------+--------------------+-------------+---------------+
| misc01     | audit_log          | AUDIT       | DELETED       |
| misc01     | CONNECTION_CONTROL | AUDIT       | ACTIVE        |
| misc01     | MYSQL_FIREWALL     | AUDIT       | ACTIVE        |
+------------+--------------------+-------------+---------------+
3 rows in set (0.01 sec)

root@localhost [mysql]> 

How to repeat:
1) Status

root@localhost [(none)]> SELECT @@hostname,PLUGIN_NAME,PLUGIN_TYPE,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_TYPE = 'AUDIT';
+------------+--------------------+-------------+---------------+
| @@hostname | PLUGIN_NAME        | PLUGIN_TYPE | PLUGIN_STATUS |
+------------+--------------------+-------------+---------------+
| misc01     | audit_log          | AUDIT       | ACTIVE        |
| misc01     | CONNECTION_CONTROL | AUDIT       | ACTIVE        |
| misc01     | MYSQL_FIREWALL     | AUDIT       | ACTIVE        |
+------------+--------------------+-------------+---------------+
3 rows in set (0.00 sec)

2) Try CONNECTION_CONTROL plugin
[admin@misc01 ~]$ time mysql -u admin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.023s
user    0m0.015s
sys     0m0.006s
[admin@misc01 ~]$ time mysql -u admin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.020s
user    0m0.009s
sys     0m0.008s
[admin@misc01 ~]$ time mysql -u admin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.022s
user    0m0.011s
sys     0m0.008s
[admin@misc01 ~]$ time mysql -u admin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.018s
user    0m0.011s
sys     0m0.005s
[admin@misc01 ~]$ time mysql -u admin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.022s
user    0m0.012s
sys     0m0.006s
[admin@misc01 ~]$ 

root@localhost [(none)]> select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
Empty set (0.00 sec)

root@localhost [(none)]> SHOW GLOBAL STATUS LIKE 'connection_control%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 0     |
+------------------------------------+-------+
1 row in set (0.01 sec)

root@localhost [(none)]> 

3) Even Delete the rule, CONNECTION_CONTROL doesn't work.
   Then Uninstall Audit_log Plugin.

root@localhost [mysql]> select * from audit_log_filter;
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| NAME             | FILTER                                                                                                                                            |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| log_confidential | {"filter": {"class": {"name": "table_access", "event": {"log": {"field": {"name": "table_name.str", "value": "confidential"}}, "name": "read"}}}} |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_user;
+--------------+------+------------------+
| USER         | HOST | FILTERNAME       |
+--------------+------+------------------+
| audit_target | %    | log_confidential |
+--------------+------+------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> 

root@localhost [mysql]> delete from audit_log_filter;delete from audit_log_user;
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@localhost [mysql]> SELECT audit_log_filter_flush();
+--------------------------+
| audit_log_filter_flush() |
+--------------------------+
| OK                       |
+--------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> DROP TABLE IF EXISTS mysql.audit_log_filter;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> DROP TABLE IF EXISTS mysql.audit_log_user;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> UNINSTALL PLUGIN audit_log;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [mysql]> DROP FUNCTION audit_log_filter_set_filter;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> DROP FUNCTION audit_log_filter_remove_filter;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> DROP FUNCTION audit_log_filter_set_user;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> DROP FUNCTION audit_log_filter_remove_user;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> DROP FUNCTION audit_log_filter_flush;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> 

root@localhost [mysql]> SELECT @@hostname,PLUGIN_NAME,PLUGIN_TYPE,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_TYPE = 'AUDIT';
+------------+--------------------+-------------+---------------+
| @@hostname | PLUGIN_NAME        | PLUGIN_TYPE | PLUGIN_STATUS |
+------------+--------------------+-------------+---------------+
| misc01     | audit_log          | AUDIT       | DELETED       |
| misc01     | CONNECTION_CONTROL | AUDIT       | ACTIVE        |
| misc01     | MYSQL_FIREWALL     | AUDIT       | ACTIVE        |
+------------+--------------------+-------------+---------------+
3 rows in set (0.01 sec)

root@localhost [mysql]> 

4) Re-Try CONNECTION_CONTROL Plugin.
   Then I could confirm CONNECTION_CONTROL function successfully.

[admin@misc01 ~]$ time mysql -u admin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.022s
user    0m0.010s
sys     0m0.008s
[admin@misc01 ~]$ time mysql -u admin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.016s
user    0m0.007s
sys     0m0.008s
[admin@misc01 ~]$ time mysql -u admin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.018s
user    0m0.011s
sys     0m0.004s
[admin@misc01 ~]$ time mysql -u admin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m1.017s  <--- + 1000ms
user    0m0.011s
sys     0m0.004s
[admin@misc01 ~]$ time mysql -u admin -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m2.018s   <--- + 1000ms
user    0m0.008s
sys     0m0.008s
[admin@misc01 ~]$ 

root@localhost [mysql]> select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+-------------+-----------------+
| USERHOST    | FAILED_ATTEMPTS |
+-------------+-----------------+
| 'admin'@'%' |               5 | <--- Count Up successfully
+-------------+-----------------+
1 row in set (0.00 sec)

root@localhost [mysql]> 
root@localhost [mysql]> SHOW GLOBAL STATUS LIKE 'connection_control%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 2     | <--- Count Up successfully
+------------------------------------+-------+
1 row in set (0.01 sec)

root@localhost [mysql]> 

Suggested fix:
Please enable CONNECTION_CONTROL Plugin even audit_log plugin is installed.
[23 Dec 2016 13:09] Shinya Sugiyama 
[Additional Information]

root@localhost [(none)]> SELECT @@hostname,@@version,PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%';
+------------+-------------------------------------------+------------------------------------------+---------------+
| @@hostname | @@version                                 | PLUGIN_NAME                              | PLUGIN_STATUS |
+------------+-------------------------------------------+------------------------------------------+---------------+
| misc01     | 5.7.17-enterprise-commercial-advanced-log | CONNECTION_CONTROL                       | ACTIVE        |
| misc01     | 5.7.17-enterprise-commercial-advanced-log | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE        |
+------------+-------------------------------------------+------------------------------------------+---------------+
2 rows in set (0.00 sec)

root@localhost [(none)]> 

root@localhost [(none)]> SHOW GLOBAL VARIABLES LIKE 'connection_control%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3          |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 1000       |
+-------------------------------------------------+------------+
3 rows in set (0.00 sec)

root@localhost [(none)]>
[23 Dec 2016 13:13] Shinya Sugiyama 
I copied wrong status under Description. This is right status when CONNECTION_CONTROL plugin doesn't work.

If audit_log plugin is already installed on the Instance, CONNECTION_CONTROL plugin doesn't work.

root@localhost [(none)]> SELECT @@hostname,PLUGIN_NAME,PLUGIN_TYPE,PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_TYPE = 'AUDIT';
+------------+--------------------+-------------+---------------+
| @@hostname | PLUGIN_NAME        | PLUGIN_TYPE | PLUGIN_STATUS |
+------------+--------------------+-------------+---------------+
| misc01     | audit_log          | AUDIT       | ACTIVE        |
| misc01     | CONNECTION_CONTROL | AUDIT       | ACTIVE        |
| misc01     | MYSQL_FIREWALL     | AUDIT       | ACTIVE        |
+------------+--------------------+-------------+---------------+
3 rows in set (0.00 sec)

root@localhost [(none)]>
[26 Jan 2017 19:19] Erlend Dahl 
Fixed under the duplicate

Bug#82473 firewall plugin stops recording events when audit_log installed