MySQL Security – MySQL Enterprise Firewall

April 16, 2018

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 AuditTDE, Password Management, Password Validation Plugin, User Account Locking, etc…

MySQL Security

 

In this seventh episode of the MySQL 5.7 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 Enterprise Firewall does not work together with the MySQL Query Cache. Fortunately the query cache is disabled by default.

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

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

 

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

 

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

 

 

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 :

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.

 

MySQL Enterprise Firewall

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 :

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

 

 

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

Other queries…

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:

 

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

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

  • Unacceptable

 

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 :

Low quality code can generate unsafe queries :

Fortunately they are blocked by the MySQL Firewall :

 

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

Unacceptable queries :

are available in the MySQL error log :

 

 

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.

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

however a message is written into the MySQL error log :

 

 

Monitor the Firewall

MySQL Enterprise Firewall provides the following status variables :

 

 

Uninstall the Firewall

To remove MySQL Enterprise Firewall, execute the following statements :

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

 

MySQL Enterprise Edition

 

 

In order to go further

MySQL 5.7 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

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

 

Leave a Reply