MySQL :: MySQL 8.0 Reference Manual :: 6.4.7.3 Using MySQL Enterprise Firewall

5.7
5.6

MySQL 8.0 Reference Manual / ... / Using MySQL Enterprise Firewall

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

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.

Enabling or Disabling the Firewall

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

After modifying my.cnf, restart the server to cause the new setting to take effect.

Alternatively, to set and persist the firewall setting at runtime:

SET PERSIST mysql_firewall_mode = OFF;
SET PERSIST mysql_firewall_mode = ON;

SET PERSIST sets the value for the running MySQL instance. It also saves the value, causing it to carry over to subsequent server restarts. To change a value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword rather than PERSIST. See Section 13.7.6.1, “SET Syntax for Variable Assignment”.

Assigning Firewall Privileges

With the firewall installed, grant the appropriate privileges to the MySQL account or accounts that will administer it. The privileges depend on which firewall operations an account should be permitted to perform:

Grant the FIREWALL_ADMIN privilege to any account that should have full adminstrative firewall access. (Some user-defined procedures can be invoked by accounts that have FIREWALL_ADMIN or the deprecated SUPER privilege, as indicated in individual UDF descriptions.)
Grant the FIREWALL_USER privilege to any account that should have administrative access only for its own firewall rules.
Grant the EXECUTE privilege for the firewall stored procedures in the mysql system database. These may invoke UDFs, so stored procedure access also requires the privileges indicated earlier that are needed for those UDFs.

Note

The FIREWALL_ADMIN and FIREWALL_USER privileges can be granted only while the firewall is installed because the firewall component defines those privileges.

Firewall Operational Concepts

The MySQL server permits clients to connect and receives from them SQL statements to be executed. The server passes to the firewall each incoming statement that does not immediately fail with a syntax error. Based on whether the firewall accepts the statement, the server executes it or returns an error to the client.

Firewall statement evaluation proceeds according to these principles:

A subject registry lists the entities to which firewall protection can be applied. A given subject corresponds to a client account.
Each subject has rules that define which statements are acceptable to it. This set of rules forms the subject allowlist.
For each client connection, the firewall determines which subject allowlist applies, and accepts only statements the allowlist permits. (If the client matches no subject, the firewall ignores it and accepts all statements.)
Each subject has a current operational mode. Modes enable the allowlist to be trained, used for restricting statement execution or intrusion detection, or disabled.

The firewall supports account-based subjects such that each subject matches a particular client account (client user name and host name combination). For example, you can register one account subject for which the allowlist applies to connections originating from admin@localhost and another account subject for which the allowlist applies to connections originating from myapp@apphost.example.com.

By default, the firewall accepts all statements and has no effect on which statements MySQL accounts can execute. To apply firewall protective capabilities, you must take explicit action:

Register one or more subjects with the firewall. (Required because the firewall ignores clients that match no subject.)
Train the firewall to establish the allowlist for each subject; that is, the types of statements the subject permits clients to execute.
Tell the firewall to protect each subject for which it has been trained; that is, to match incoming statements against the appropriate allowlist when clients connect.

Statement matching performed by the firewall does not use SQL statements as received from clients. Instead, the server converts incoming statements to normalized digest form and firewall operation uses these digests. The benefit of statement normalization is that it enables similar statements to be grouped and recognized using a single pattern. For example, these statements are distinct from each other:

SELECT first_name, last_name FROM customer WHERE customer_id = 1;
select first_name, last_name from customer where customer_id = 99;
SELECT first_name, last_name FROM customer WHERE customer_id = 143;

But all of them have the same normalized digest form:

SELECT `first_name` , `last_name` FROM `customer` WHERE `customer_id` = ?

By using normalization, the firewall can store digests in allowlists that each match many different statements received from clients. For more information about normalization and digests, see Section 26.10, “Performance Schema Statement Digests and Sampling”.

Each subject registered with the firewall has its own operational mode, chosen from these values:

OFF: This mode disables the subject. The firewall considers it inactive and ignores it.
RECORDING: This is the firewall training mode. In RECORDING mode, incoming statements received from a client that matches the subject are considered acceptable for the subject and become part of its “fingerprint.” The firewall records the normalized digest form of each statement to learn the acceptable statement patterns for the subject. Each pattern is a rule, and the union of the rules is the subject allowlist.
PROTECTING: This mode protects the subject by matching incoming statements against its allowlist, accepting only only statements that match and rejecting those that do not. After training the subject in RECORDING mode, switch it to PROTECTING mode to harden MySQL against access by statements that deviate from the allowlist.
DETECTING: This mode detects but not does not block intrusions (statements that are suspicious because they match nothing in the subject allowlist). In DETECTING mode, the firewall writes suspicious statements to the error log but accepts them without denying access.

When any of the preceding mode values is assigned to a subject, the firewall stores the mode as part of the current subject information. Firewall mode-setting operations also permit a mode value of RESET, but this value is not stored. Setting a subject to RESET mode causes the firewall to delete all rules for the subject and set its mode to OFF.

Registering Firewall Account Subjects

MySQL Enterprise Firewall enables subjects to be registered and protected that correspond to individual accounts.

MySQL authenticates each client session for a specific user name and host name combination. This combination is the session account. The firewall matches the session account against registered account subjects to determine which subject applies to handling incoming statements from the session:

The firewall ignores inactive subjects (subjects with a mode of OFF).
The session account matches the active account subject having the same user and host, if there is one. There is at most one such account subject.

In other words, at most one active account subject is applicable to a given session, for which the firewall handles each incoming statement as follows:

If there is no applicable subject, there are no restrictions. The firewall accepts the statement.
If there is an applicable subject, its mode determines statement handling:
- In RECORDING mode, the firewall adds the statement to the subject allowlist rules and accepts it.
- In PROTECTING mode, the firewall compares the statement to the rules in the subject allowlist. The firewall accepts the statement if there is a match, and rejects it otherwise. If the mysql_firewall_trace system variable is enabled, the firewall also writes rejected statements to the error log.
- In DETECTING mode, the firewall detects instrusions without denying access. The firewall accepts the statement, but also matches it to the subject allowlist, as in PROTECTING mode. If the statement is suspicious (nonmatching), the firewall writes it to the error log.

To protect a MySQL account using a firewall account subject, follow these steps:

Register the account subject and put it in RECORDING mode.
Connect to the MySQL server using the account and execute statements to be learned. This trains the corresponding firewall account subject and establishes the rules that form the subject allowlist.
Switch the account subject to PROTECTING mode. When a client connects to the server using the account, the account subject allowlist restricts statement execution.
Should additional training be necessary, switch the account subject to RECORDING mode again, update its allowlist with new statement patterns, then switch it back to PROTECTING mode.

By maintaining an allowlist per registered account subject, the firewall enables implementation of protection strategies such as these:

If an application has unique protection requirements, configure it to use an account not used for any other purpose and set up a corresponding firewall account subject.
If related applications share protection requirements, configure them all to use the same account (and thus the same account subject).

When referring to accounts for use with the firewall, observe these guidelines:

Take note of the context in which account references occur. To name an account for firewall operations, specify it as a single quoted string ('user_name@host_name'). This differs from the usual MySQL convention for statements such as CREATE USER and GRANT, for which you quote the user and host parts of an account name separately ('user_name'@'host_name').
The requirement for naming accounts as a single quoted string for firewall operations means that you cannot use accounts that have embedded @ characters in the user name.
The firewall assesses statements against accounts represented by actual user and host names as authenticated by the server. When registering account subjects, do not use wildcard characters or netmasks:
- Suppose that an account named me@%.example.org exists and a client uses it to connect to the server from the host abc.example.org.
- The account name contains a % wildcard character, but the server authenticates the client as having a user name of me and host name of abc.example.com, and that is what the firewall sees.
- Consequently, the account to use for firewall operations is me@abc.example.org, not me@%.example.org.

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

Use an administrative MySQL account to perform the steps in this procedure, except those steps designated for execution by the fwuser@localhost account corresponding to the account subject registered with the firewall. The default database for statements executed using the account should be sakila. (You can use a different database by adjusting the instructions accordingly.)

If necessary, create the account to be protected (choose an appropriate password) and grant it privileges for the sakila database:
```
CREATE USER 'fwuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON sakila.* TO 'fwuser'@'localhost';
```
Register an account subject with the firewall and place it in RECORDING (training) mode using the sp_set_firewall_mode() stored procedure:
```
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.
To use the registered account subject, connect to the server as fwuser from the server host so that the firewall sees a session account of fwuser@localhost. Then use the account to execute some statements that are legitimate for the account:
```
SELECT first_name, last_name FROM customer WHERE customer_id = 1;
UPDATE rental SET return_date = NOW() WHERE rental_id = 1;
SELECT get_customer_balance(1, NOW());
```
Because the account subject is in RECORDING mode, the firewall records the normalized digest form of the statements as rules in the subject allowlist.
Note

Until the fwuser@localhost account subject receives statements in RECORDING mode, its allowlist is empty, which is equivalent to “deny all.” No statement can match an empty allowlist, which has these implications:
- The account subject cannot be switched to PROTECTING mode because the firewall would reject every statement, effectively prohibiting the account from executing any statement.
- The account subject can be switched to DETECTING mode and the firewall will accept every statement but log it as suspicious.
At this point, the account subject and allowlist 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 using the account corresponding to the account subject.

Important

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 allowlist for the account subject associated with the application.
Invoke sp_set_firewall_mode() again, this time switching the account subject to PROTECTING mode:
```
CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'PROTECTING');
```
Important

Switching the account subject out of RECORDING mode synchronizes its firewall cache data to the mysql system database tables that provide persistent underlying storage. If you do not switch the mode for a subject that is being recorded, the cached data is not written to persistent storage and is lost when the server is restarted.
Test the account against the firewall by using it to execute some acceptable and unacceptable statements. The firewall matches each statement against the account subject allowlist 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 match nothing in the allowlist, so the firewall rejects each with 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
```
- If the mysql_firewall_trace system variable is enabled, the firewall also writes rejected statements to the error log. 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.

The firewall account subject now is trained for the fwuser@localhost account and is protecting it against statements not matched by the subject allowlist.

It is also possible to detect intrusions by logging nonmatching statements as suspicious without denying access. First, put the account subject in DETECTING mode:

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

Then, using the account, execute a statement that does not match the account subject allowlist. In DETECTING mode, the firewall permits the nonmatching statement to execute:

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

In addition, the firewall 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

DETECTING 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 error-logging verbosity is sufficient to log information messages. For example, if you are using priority-based log filtering, as described in Section 5.4.2.5, “Priority-Based Error Log Filtering (log_filter_internal)”, set the log_error_verbosity system variable to a value of 3.

To stop protecting an account, change its subject mode to OFF:

CALL mysql.sp_set_firewall_mode(user, 'OFF');

To forget all training for a subject and disable firewall protection for it, reset it:

CALL mysql.sp_set_firewall_mode(user, 'RESET');

A reset operation causes the firewall to delete all rules for the subject and set its mode to OFF.

Monitoring the Firewall

To assess firewall activity, examine its status variables. For example, after performing the procedure shown earlier to train and protect the fwuser@localhost account, the variables look like this:

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 times you connected using the registered account, plus the SHOW TABLES statement that was not blocked in DETECTING mode.

PREV HOME UP NEXT