Bug #76914 non-existent users are added to firewall_users table despite giving error
Submitted: 3 May 2015 8:55 Modified: 26 May 2015 12:52
Reporter: Mario Beck Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Firewall Severity:S3 (Non-critical)
Version:5.6.24, 5.6.25, 5.7.8, 8.0.0 OS:Linux (OL6)
Assigned to: CPU Architecture:Any

[3 May 2015 8:55] Mario Beck
Description:
When calling sp_set_firewall_mode with an unknown or even illegal username, the stored procedures returns an error but nevertheless adds the user to the firewall_users table.

How to repeat:
mysql> call mysql.sp_set_firewall_mode(" illegal ","RECORDING");
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0
Imported rules: 0
          |
+-----------------------------------------------+
1 row in set (0,00 sec)

+--------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode)  |
+--------------------------------------------+
| ERROR: Failed to create the user  illegal  |
+--------------------------------------------+
1 row in set (0,00 sec)

Query OK, 1 row affected (0,00 sec)

mysql> select * from firewall_users;
+-----------+-----------+
| USERHOST  | MODE      |
+-----------+-----------+
| 32        | OFF       |
| app       | OFF       |
| app@%     | OFF       |
|  illegal  | RECORDING |
+-----------+-----------+
4 rows in set (0,00 sec)

Suggested fix:
If the user is unknown nothing should be changed.
[3 May 2015 12:33] MySQL Verification Team
Hello Mario,

Thank you for the report.
Observed this with 5.6.25 builds.

Thanks,
Umesh
[3 May 2015 12:34] MySQL Verification Team
// 5.6.25

mysql> call mysql.sp_set_firewall_mode(" illegal ","RECORDING");
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0
Imported rules: 0
          |
+-----------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode)  |
+--------------------------------------------+
| ERROR: Failed to create the user  illegal  |
+--------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.firewall_users;
+-----------+-----------+
| USERHOST  | MODE      |
+-----------+-----------+
|  illegal  | RECORDING |
+-----------+-----------+
1 row in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.25                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.25-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
[3 May 2015 12:36] MySQL Verification Team
// 5.7.8

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8:  bin/mysql -uroot -p -S/tmp/mysql_ushastry.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.8-rc-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> call mysql.sp_set_firewall_mode(" illegal ","RECORDING");
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0
Imported rules: 0
          |
+-----------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode)  |
+--------------------------------------------+
| ERROR: Failed to create the user  illegal  |
+--------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.firewall_users;
+-----------+-----------+
| USERHOST  | MODE      |
+-----------+-----------+
|  illegal  | RECORDING |
+-----------+-----------+
1 row in set (0.00 sec)
[3 May 2015 12:39] MySQL Verification Team
// 5.8.0

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.8.0:  bin/mysql -uroot -p -S/tmp/mysql_ushastry.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.8.0-m17-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> call mysql.sp_set_firewall_mode(" illegal ","RECORDING");
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0
Imported rules: 0
          |
+-----------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode)  |
+--------------------------------------------+
| ERROR: Failed to create the user  illegal  |
+--------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.firewall_users;
+-----------+-----------+
| USERHOST  | MODE      |
+-----------+-----------+
|  illegal  | RECORDING |
+-----------+-----------+
1 row in set (0.00 sec)
[26 May 2015 12:52] Paul DuBois
Noted in 5.6.26 changelog.

A call to the MySQL Enterprise Firewall sp_set_firewall_mode() stored
procedure with an invalid user name produced an error but added the
user to the firewall_users table anyway.
[18 Jun 2016 21:26] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0