6.4.7.3 Using MySQL Enterprise Firewall

Before using MySQL Enterprise Firewall, install it according to the instructions provided in Section 6.4.7.2, “Installing or Uninstalling MySQL Enterprise Firewall”. Also, MySQL Enterprise Firewall does not work together with the query cache; disable the query cache if it is enabled (see Query Cache Configuration).

This section describes how to configure MySQL Enterprise Firewall using SQL statements. Alternatively, MySQL Workbench 6.3.4 or higher provides a graphical interface for firewall control. See MySQL Enterprise Firewall Interface.

To enable or disable the firewall, set the mysql_firewall_mode system variable. By default, this variable is enabled when the firewall is installed. To control the initial firewall state explicitly, you can set the variable at server startup. For example, to enable the firewall in an option file, use these lines:

[mysqld]
mysql_firewall_mode=ON

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

mysql> SET GLOBAL mysql_firewall_mode = OFF;
mysql> SET GLOBAL mysql_firewall_mode = ON;

In addition to the global on/off firewall mode, each account registered with the firewall has its own operational mode. For an account in recording mode, the firewall learns an application's “fingerprint,” that is, the acceptable statement patterns that, taken together, form a whitelist. After training, switch the firewall to protecting mode to harden MySQL against access by statements that deviate from the fingerprint. For additional training, switch the firewall back to recording mode as necessary to update the whitelist with new statement patterns. An intrusion-detection mode is available that writes suspicious statements to the error log but does not deny access.

The firewall maintains whitelist rules on a per-account basis, enabling implementation of protection strategies such as these:

• For an application that has unique protection requirements, configure it to use an account that is not used for any other purpose.

• For applications that are related and share protection requirements, configure them as a group to use the same account.

Firewall operation is based on conversion of SQL statements to normalized digest form. Firewall digests are like the statement digests used by the Performance Schema (see Section 26.10, “Performance Schema Statement Digests and Sampling”). However, unlike the Performance Schema, the relevant digest-related system variable is max_digest_length.

For a connection from a registered account, the firewall converts each incoming statement to normalized form and processes it according to the account mode:

• In recording mode, the firewall adds the normalized statement to the account whitelist rules.

• In protecting mode, 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.

• In detecting mode, the firewall matches statements as in protecting mode, but writes nonmatching statements to the error log without denying access.

Accounts that have a mode of OFF or are not registered with the firewall are ignored by it.

To protect an account using MySQL Enterprise Firewall, follow these steps:

1. Register the account and put it in recording mode.

2. Connect to the MySQL server using the registered account and execute statements to be learned. This establishes the account's whitelist of accepted statements.

3. Switch the registered account to protecting mode.

The following example shows how to register an account with the firewall, use the firewall to learn acceptable statements for that account, and protect the account against execution of unacceptable statements. The example account, 'fwuser'@'localhost', is for use by an application that accesses tables in the sakila database. (This database is available at https://dev.mysql.com/doc/index-other.html.)

Note

The user and host parts of the account name are quoted separately for statements such as CREATE USER and GRANT, whereas to specify an account for use with a firewall component, name it as a single quoted string 'fwuser@localhost'.

The convention for naming accounts as a single quoted string for firewall components means that you cannot use accounts that have embedded @ characters in the user name.

Perform the steps in the following procedure using an administrative MySQL account, except those designated for execution by the account registered with the firewall. The default database should be sakila for statements executed using the registered account.

1. If necessary, create the account to be protected (choose an appropriate password) and grant it privileges for the sakila database:

   mysql> CREATE USER 'fwuser'@'localhost' IDENTIFIED BY 'fWp@3sw0rd';
   mysql> GRANT ALL ON sakila.* TO 'fwuser'@'localhost';

2. Use the sp_set_firewall_mode() stored procedure to register the account with the firewall and place it in recording mode (if the procedure is located in a database other than mysql, adjust the statement accordingly):

   mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING');

   During the course of its execution, the stored procedure invokes firewall user-defined functions, which may produce output of their own.

3. Using the registered account, connect to the server, then execute some statements that are legitimate for it:

   mysql> SELECT first_name, last_name FROM customer WHERE customer_id = 1;
   mysql> UPDATE rental SET return_date = NOW() WHERE rental_id = 1;
   mysql> SELECT get_customer_balance(1, NOW());

   The firewall converts the statements to digest form and records them in the account whitelist.

   Note

   Until the account executes statements in recording mode, its whitelist is empty, which is equivalent to “deny all.” If switched to protecting mode, the account will be effectively prohibited from executing statements.

4. At this point, the user and whitelist information is cached and can be seen in the firewall INFORMATION_SCHEMA tables:

   mysql> SELECT MODE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS
          WHERE USERHOST = 'fwuser@localhost';
   +-----------+
   | MODE      |
   +-----------+
   | RECORDING |
   +-----------+
   mysql> SELECT RULE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST
          WHERE USERHOST = 'fwuser@localhost';
   +----------------------------------------------------------------------------+
   | RULE                                                                       |
   +----------------------------------------------------------------------------+
   | SELECT `first_name` , `last_name` FROM `customer` WHERE `customer_id` = ?  |
   | SELECT `get_customer_balance` ( ? , NOW ( ) )                              |
   | UPDATE `rental` SET `return_date` = NOW ( ) WHERE `rental_id` = ?          |
   | SELECT @@`version_comment` LIMIT ?                                         |
   +----------------------------------------------------------------------------+

   Note

   The @@version_comment rule comes from a statement sent automatically by the mysql client when you connect to the server as the registered user.

   It is important to train the firewall under conditions matching application use. For example, a given MySQL connector might send statements to the server at the beginning of a connection to determine server characteristics and capabilities. If an application normally is used through that connector, train the firewall that way, too. That enables those initial statements to become part of the whitelist for the account associated with the application.

5. Use the stored procedure to switch the registered user to protecting mode:

   mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'PROTECTING');

   Important

   Switching the account out of RECORDING mode synchronizes its firewall cache data to the underlying mysql system database tables for persistent storage. If you do not switch the mode for a user who is being recorded, the cached whitelist data is not written to the system tables and will be lost when the server is restarted.

6. Using the registered account, execute some acceptable and unacceptable statements. The firewall matches each one against the account whitelist and accepts or rejects it.

   This statement is not identical to a training statement but produces the same normalized statement as one of them, so the firewall accepts it:

   mysql> SELECT first_name, last_name FROM customer WHERE customer_id = '48';
   +------------+-----------+
   | first_name | last_name |
   +------------+-----------+
   | ANN        | EVANS     |
   +------------+-----------+

   These statements do not match anything in the whitelist and each results in an error:

   mysql> SELECT first_name, last_name FROM customer WHERE customer_id = 1 OR TRUE;
   ERROR 1045 (28000): Statement was blocked by Firewall
   mysql> SHOW TABLES LIKE 'customer%';
   ERROR 1045 (28000): Statement was blocked by Firewall
   mysql> TRUNCATE TABLE mysql.slow_log;
   ERROR 1045 (28000): Statement was blocked by Firewall

   The firewall also writes the rejected statements to the error log if the mysql_firewall_trace system variable is enabled. For example:

   [Note] Plugin MYSQL_FIREWALL reported:
   'ACCESS DENIED for fwuser@localhost. Reason: No match in whitelist.
   Statement: TRUNCATE TABLE `mysql` . `slow_log` '

   You can use these log messages in your efforts to identify the source of attacks.

7. You can log nonmatching statements as suspicious without denying access. To do this, put the account in intrusion-detecting mode:

   mysql> CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'DETECTING');

8. Using the registered account, connect to the server, then execute a statement that does not match the whitelist:

   mysql> SHOW TABLES LIKE 'customer%';
   +------------------------------+
   | Tables_in_sakila (customer%) |
   +------------------------------+
   | customer                     |
   | customer_list                |
   +------------------------------+

   In detecting mode, the firewall permits the nonmatching statement to execute but writes a message to the error log:

   [Note] Plugin MYSQL_FIREWALL reported:
   'SUSPICIOUS STATEMENT from 'fwuser@localhost'. Reason: No match in whitelist.
   Statement: SHOW TABLES LIKE ? '

   Note

   Detection mode writes messages as Notes, which are information messages. To ensure that such messages appear in the error log and are not discarded, make sure that the log_error_verbosity system variable is set to a value of 3.

9. To assess firewall activity, examine its status variables:

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

   The variables indicate the number of statements rejected, accepted, logged as suspicious, and added to the cache, respectively. The Firewall_access_granted count is 4 because of the @@version_comment statement sent by the mysql client each of the three time you used it to connect as the registered user, plus the SHOW TABLES statement that was not blocked in DETECTING mode.

Should additional training for an account be necessary, switch it to recording mode again, then back to protecting mode after executing statements to be added to the whitelist.