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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.