dasini.net – Diary of a MySQL expert

When thinking about security within a MySQL installation, you should consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools / features / plugins in order to protect your data including some advanced features like Audit, TDE, Data Masking & De-Identification, Password Management, Password Validation Plugin, User Account Locking, etc…

In this seventh episode of the MySQL Security series, we will see how MySQL Enterprise Firewall can help you to strengthen the protection of your data, in real-time, against cyber security threats including SQL Injection attacks by monitoring, alerting, and blocking unauthorized database activity without any changes to your applications.

Installing the MySQL Enterprise Firewall Plugin

MySQL Enterprise Firewall installation is an easy one-time operation that involves running a script (e.g. linux_install_firewall.sql in this blog post (Linux and similar systems that use .so as the file name suffix); win_install_firewall.sql for Windows systems that use .dll as the file name suffix) located in the share directory of your MySQL installation.

I’m using MySQL 5.7.21 Enterprise Edition :

mysql> 
SELECT version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.7.21-enterprise-commercial-advanced-log |
+-------------------------------------------+

Updated on 22nd of August 2018
Note: MySQL Enterprise Firewall works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+

MySQL Enterprise Firewall does not work together with the MySQL Query Cache. Fortunately the query cache is disabled by default.

mysql> 
SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

Note
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

Note
For a great query cache tuning advice from Domas Mituzas : click here 🙂

Now we can installed the Firewall

mysql> 
SOURCE /usr/share/mysql/linux_install_firewall.sql

Database changed
Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

And check if it has been launched with the system variable mysql_firewall_mode :

mysql> 
SHOW GLOBAL VARIABLES LIKE 'mysql_firewall_mode';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| mysql_firewall_mode | ON    |
+---------------------+-------+

Alternatively, we can also add mysql_firewall_mode under the [mysqld] option group in the MySQL configuration file :

[mysqld]
mysql_firewall_mode=ON

It is also possible to disable or enable the firewall at runtime :

mysql> 
SET GLOBAL mysql_firewall_mode = OFF;

SET GLOBAL mysql_firewall_mode = ON;

Playtime

The MySQL Firewall is installed!

Let’s assume now we have an application that uses the schema sakila in this instance. This application has a dedicated user account (myApp@localhost) and all the privileges on sakila :

mysql> 
CREATE USER 'myApp'@'localhost' IDENTIFIED BY 'AppU$3rPwd';

GRANT ALL ON sakila.* TO 'myApp'@'localhost';

Note

The firewall maintains whitelist rules on a per-account basis.

Regular queries from this hypothetical application are :

• UPDATE rental SET return_date = <date> WHERE rental_id = <id>;
• SELECT get_customer_balance(<id>, <date>);

But first, users are authenticated with :

• SELECT staff_id, first_name, email, last_name, username, password FROM staff WHERE username = ‘<userName>’ AND password=sha1(<userPassword>);

Warning
Query above is not safe nor optimal for production.

Firewall allows 3 modes :

• recording, the firewall adds the normalized statement to the account whitelist rules.
• protecting, the firewall compares the normalized statement to the account whitelist rules. If there is a match, the statement passes and the server continues to process it. Otherwise, the server rejects the statement and returns an error to the client. The firewall also writes the rejected statement to the error log if the mysql_firewall_trace system variable is enabled.
• detecting, the firewall matches statements as in protecting mode, but writes nonmatching statements to the error log without denying access.

Recording mode

Ok now we know our queries, let’s go back to the Firewall.

The basic and powerful idea of the MySQL Firewall is to deny SQL statement execution based on matching against a whitelist. In other words the Firewall learns acceptable statement patterns.

In order to create this whitelist, we’ll switch the Firewall in the RECORDING mode using sp_set_firewall_mode stored procedure :

mysql> 
-- register the account with the firewall and place it in recording mode
CALL mysql.sp_set_firewall_mode('myApp@localhost', 'RECORDING');

+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0
Imported rules: 0
          |
+-----------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

We can know see what is the status of the Firewall for any user with INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS table :

mysql> 
-- Check firewall status
SELECT MODE 
FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS 
WHERE USERHOST = 'myApp@localhost';
+-----------+
| MODE      |
+-----------+
| RECORDING |
+-----------+

During the recording mode, we can run the application. The queries generated by the application will be recorded in the Firewall’s whitelist :

# Login failed (Mike / 0000)
mysql_myApp> 
SELECT staff_id, first_name, email, last_name, username, password 
FROM sakila.staff 
WHERE username = 'Mike' AND password=sha1(0000)\G
Empty set (0.00 sec)


# Login succeed (Mike / 12345)
SELECT staff_id, first_name, email, last_name, username, password 
FROM sakila.staff 
WHERE username = 'Mike' AND password=sha1(12345)\G
*************************** 1. row ***************************
  staff_id: 1
first_name: Mike
     email: Mike.Hillyer@sakilastaff.com
 last_name: Hillyer
  username: Mike
  password: 8cb2237d0679ca88db6464eac60da96345513964

Other queries…

mysql_myApp>
UPDATE rental SET return_date = NOW() WHERE rental_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT get_customer_balance(1, NOW());
+--------------------------------+
| get_customer_balance(1, NOW()) |
+--------------------------------+
|                           0.00 |
+--------------------------------+

And so on…

When the training is done switch the Firewall to protecting mode.

Protecting mode

Use the sp_set_firewall_mode stored procedure to switch the registered user to protecting mode:

mysql> 
-- Switch the Firewall in protecting mode
CALL mysql.sp_set_firewall_mode('myApp@localhost', 'PROTECTING');
Query OK, 3 rows affected (0.00 sec)


-- Check
SELECT * FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS;
+------------------+------------+
| USERHOST         | MODE       |
+------------------+------------+
| myApp@localhost  | PROTECTING |
+------------------+------------+

Firewall stores SQL statements on a normalized digest form. You can check the whitelist with INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST table :

mysql>
-- Check whitelist content 
SELECT USERHOST, RULE 
FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST 
WHERE USERHOST = 'myApp@localhost';

+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| USERHOST        | RULE                                                                                                                                                     |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| myApp@localhost | SELECT `get_customer_balance` ( ? , NOW ( ) )                                                                                                            |
| myApp@localhost | UPDATE `rental` SET `return_date` = NOW ( ) WHERE `rental_id` = ?                                                                                        |
| myApp@localhost | SELECT `staff_id` , `first_name` , `email` , `last_name` , `username` , PASSWORD FROM `sakila` . `staff` WHERE `username` = ? AND PASSWORD = `sha1` (?)  |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Note
For additional training you can switch back recording mode or even update (that is an UPDATE query) this table if necessary using the normalize_statement UDF.

In protecting mode, there are 2 kind of queries for the application point of view :

• Acceptable

mysql_app> 
UPDATE rental SET return_date = NOW() WHERE rental_id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0


UPDATE rental SET return_date = NOW() WHERE rental_id = 42;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0


SELECT get_customer_balance(5, NOW());
+--------------------------------+
| get_customer_balance(5, NOW()) |
+--------------------------------+
|                           0.00 |
+--------------------------------+
1 row in set (0.01 sec)

• Unacceptable

mysql_app>
DROP TABLE rental;
ERROR 1045 (28000): Statement was blocked by Firewall


UPDATE rental SET return_date = NOW();
ERROR 1045 (28000): Statement was blocked by Firewall

SQL injection

One of the big advantage of the MySQL Firewall is that it can help protect against SQL Injection attacks. In this post, I will not go into details of what is an SQL injection. However below a simplistic example to illustrate the overall principle.

User name and password are needed for authentication :

mysql_app> 
-- user = Mike : OK
-- password = 12345 : OK
-- Login OK

SELECT staff_id, first_name, email, last_name, username, password 
FROM sakila.staff 
WHERE username = 'Mike' AND password=sha1(12345)\G
*************************** 1. row ***************************
  staff_id: 1
first_name: Mike
     email: Mike.Hillyer@sakilastaff.com
 last_name: Hillyer
  username: Mike
  password: 8cb2237d0679ca88db6464eac60da96345513964

Low quality code can generate unsafe queries :

mysql>
-- user = Mike' AND '1'='1'\G --  : Not OK
-- password = <whatever>
-- SQL injection /!\

SELECT staff_id, first_name, email, last_name, username, password 
FROM sakila.staff 
WHERE username = 'Mike' AND '1'='1'\G --   AND password=sha1(<whatever>)\G
*************************** 1. row ***************************
  staff_id: 1
first_name: Mike
     email: Mike.Hillyer@sakilastaff.com
 last_name: Hillyer
  username: Mike
  password: 8cb2237d0679ca88db6464eac60da96345513964

Fortunately they are blocked by the MySQL Firewall :

mysql_app>
-- user = Mike' AND '1'='1'\G --  : Not OK
-- password = <whatever>
-- Blocked by Firewall because not matching whitelist

SELECT staff_id, first_name, email, last_name, username, password 
FROM sakila.staff 
WHERE username = 'Mike' AND '1'='1'\G --   AND password=sha1(12345)\G

ERROR 1045 (28000): Statement was blocked by Firewall

Rejected queries can be seen in the MySQL error log if the mysql_firewall_trace system variable is enabled.

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


SHOW VARIABLES LIKE 'mysql_firewall_trace';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| mysql_firewall_trace | ON    |
+----------------------+-------+

Unacceptable queries :

mysql_app> 
SELECT * FROM sakila.staff;
ERROR 1045 (28000): Statement was blocked by Firewall

DELETE FROM rental;
ERROR 1045 (28000): Statement was blocked by Firewall

TRUNCATE mysql.user;
ERROR 1045 (28000): Statement was blocked by Firewall

are available in the MySQL error log :

mysql> 
system tail -n3 /var/log/mysqld.log

2018-04-16T08:46:09.353950Z 1966 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for 'myApp@localhost'. Reason: No match in whitelist. Statement: SELECT * FROM `sakila` . `staff` '
2018-04-16T08:46:09.354198Z 1966 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for 'myApp@localhost'. Reason: No match in whitelist. Statement: DELETE FROM `rental` '
2018-04-16T08:46:09.354403Z 1966 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for 'myApp@localhost'. Reason: No match in whitelist. Statement: TRUNCATE `mysql` . `user`

Detecting mode

MySQL Enterprise Firewall can also be used into a intrusion-detecting mode that writes suspicious statements to the error log but does not deny access.

mysql> 
CALL mysql.sp_set_firewall_mode('myApp@localhost', 'DETECTING');
Query OK, 3 rows affected (0.06 sec)


SELECT * FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS;
+-----------------+-----------+
| USERHOST        | MODE      |
+-----------------+-----------+
| myApp@localhost | DETECTING |
+-----------------+-----------+

Now using the application user account, suspicious queries will not be blocked :

mysql_app> 
-- Suspicious query
SHOW TABLES LIKE 'customer%';
+------------------------------+
| Tables_in_sakila (customer%) |
+------------------------------+
| customer                     |
| customer_list                |
+------------------------------+
2 rows in set (0.00 sec)

however a message is written into the MySQL error log :

mysql> 
system tail -n1 /var/log/mysqld.log
2018-04-16T09:01:47.133398Z 1966 [Note] Plugin MYSQL_FIREWALL reported: 'SUSPICIOUS STATEMENT from 'myApp@localhost'. Reason: No match in whitelist. Statement: SHOW TABLES LIKE ? '

Monitor the Firewall

MySQL Enterprise Firewall provides the following status variables :

• Firewall_access_denied : The number of statements rejected by MySQL Enterprise Firewall.
• Firewall_access_granted : The number of statements accepted by MySQL Enterprise Firewall.
• Firewall_access_suspicious : The number of statements logged by MySQL Enterprise Firewall as suspicious for users who are in DETECTING mode.
• Firewall_cached_entries : The number of statements recorded by MySQL Enterprise Firewall, including duplicates.

mysql> 
SHOW GLOBAL STATUS LIKE 'Firewall%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Firewall_access_denied     | 9     |
| Firewall_access_granted    | 8     |
| Firewall_access_suspicious | 1     |
| Firewall_cached_entries    | 3     |
+----------------------------+-------+

Uninstall the Firewall

To remove MySQL Enterprise Firewall, execute the following statements :

mysql> 
USE mysql;

DROP TABLE mysql.firewall_whitelist;
DROP TABLE mysql.firewall_users;
UNINSTALL PLUGIN mysql_firewall;
UNINSTALL PLUGIN mysql_firewall_whitelist;
UNINSTALL PLUGIN mysql_firewall_users;
DROP FUNCTION set_firewall_mode;
DROP FUNCTION normalize_statement;
DROP FUNCTION read_firewall_whitelist;
DROP FUNCTION read_firewall_users;
DROP FUNCTION mysql_firewall_flush_status;
DROP PROCEDURE mysql.sp_set_firewall_mode;
DROP PROCEDURE mysql.sp_reload_firewall_rules;

Note
You may have to kill the application remaining connections (e.g. KILL CONNECTION) or reconnect the application user (e.g. mysql> connect)

MySQL Enterprise Edition

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.

It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.

MySQL Enterprise Edition server Trial Download (Note – Select Product Pack: MySQL Database).

In order to go further

MySQL Security Series

1. Password Validation Plugin
2. Password Management
3. User Account Locking
4. The Connection-Control Plugins
5. Enterprise Audit
6. Enterprise Transparent Data Encryption (TDE)
7. Enterprise Firewall
8. Enterprise Data Masking and De-Identification

Reference Manual

• MySQL Enterprise Firewall High lights
• Installing the MySQL Enterprise Firewall Plugin
• MySQL Enterprise Firewall

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
MySQL, Security, Tuto by Olivier DASINI