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 a 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 15.7.6.1, “SET Syntax for Variable Assignment”.

Each time the MYSQL_FIREWALL server-side plugin initializes, it loads data from these tables to its internal cache:

Without restarting the server or reinstalling the server-side plugin, modification of data outside of the plugin is not reflected internally. The mysql_firewall_reload_interval_seconds system variable makes it possible to force memory cache reloads from tables at specified intervals. By default, the periodic interval value is set to zero, which disables reloads.

To schedule regular cache reloads, first ensure that the scheduler component is installed and enabled (see Section 7.5.5, “Scheduler Component”). To check the status of the component:

SHOW VARIABLES LIKE 'component_scheduler%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------|
| component_scheduler.enabled | On    |
+-----------------------------+-------+

With the firewall installed, set the mysql_firewall_reload_interval_seconds global system variable at server startup to a number between 60 and the INT_MAX macro value of the platform hosting the server. Values between zero and 60 (1 through 59) reset to 60. For example:

$> mysqld [server-options] --mysql-firewall-reload-interval-seconds=40
...
2023-08-31T17:46:35.043468Z 0 [Warning] [MY-015031] [Server] Plugin MYSQL_FIREWALL 
reported: 'Invalid reload interval specified: 40. Valid values are 0 (off) or 
greater than or equal to 60. Adjusting to 60.'
...

Alternatively, to set and persist the firewall setting at startup, precede the read-only variable name by the PERSIST_ONLY keyword or the @@PERSIST_ONLY. qualifier:

SET PERSIST_ONLY mysql_firewall_reload_interval_seconds = 120;
SET @@PERSIST_ONLY.mysql_firewall_reload_interval_seconds = 120;

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

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

The MySQL server permits clients to connect and receives from them SQL statements to be executed. If the firewall is enabled, the server passes to it 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. This section describes how the firewall accomplishes the task of accepting or rejecting statements.

Firewall Profiles

The firewall uses a registry of profiles that determine whether to permit statement execution. Profiles have these attributes:

Initially, no profiles exist, so by default, the firewall accepts all statements and has no effect on which statements MySQL accounts can execute. To apply firewall protective capabilities, explicit action is required:

Most firewall principles apply identically to group profiles and account profiles. The two types of profiles differ in these respects:

The profile-based protection afforded by the firewall enables implementation of strategies such as these:

Firewall Statement Matching

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, firewall allowlists can store digests that each match many different statements received from clients. For more information about normalization and digests, see Section 29.10, “Performance Schema Statement Digests and Sampling”.

Profile Operational Modes

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

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

Firewall Statement Handling When Multiple Profiles Apply

For simplicity, later sections that describe how to set up profiles take the perspective that the firewall matches incoming statements from a client against only a single profile, either a group profile or account profile. But firewall operation can be more complex:

The following description covers the general case of how the firewall operates, when potentially multiple profiles apply to incoming statements.

As previously mentioned, MySQL associates each client session with a specific user name and host name combination known as the session account. The firewall matches the session account against registered profiles to determine which profiles apply to handling incoming statements from the session:

In other words, the session account can match 0 or more active group profiles, and 0 or 1 active account profiles. This means that 0, 1, or multiple firewall profiles are applicable to a given session, for which the firewall handles each incoming statement as follows:

With that description in mind, the next sections revert to the simplicity of the situations when a single group profile or a single account profile apply, and cover how to set up each type of profile.

MySQL Enterprise Firewall supports registration of group profiles. A group profile can have multiple accounts as its members. To use a firewall group profile to protect MySQL against incoming statements from a given account, follow these steps:

Observe these guidelines for firewall-related account references:

The following procedure shows how to register a group profile with the firewall, train the firewall to know the acceptable statements for that profile (its allowlist), use the profile to protect MySQL against execution of unacceptable statements, and add and remove group members. The example uses a group profile name of fwgrp. The example profile is presumed for use by clients of 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 member accounts of the firewall group profile. For statements executed by member accounts, the default database should be sakila. (You can use a different database by adjusting the instructions accordingly.)

The firewall group profile now is trained for member accounts. When clients connect using any account in the group and attempt to execute statements, the profile protects MySQL against statements not matched by the profile allowlist.

The procedure just shown added only one member to the group profile before training its allowlist. Doing so provides better control over the training period by limiting which accounts can add new acceptable statements to the allowlist. Should additional training be necessary, you can switch the profile back to RECORDING mode:

CALL mysql.sp_set_firewall_group_mode('fwgrp', 'RECORDING');

However, that enables any member of the group to execute statements and add them to the allowlist. To limit the additional training to a single group member, call sp_set_firewall_group_mode_and_user(), which is like sp_set_firewall_group_mode() but takes one more argument specifying which account is permitted to train the profile in RECORDING mode. For example, to enable training only by member4@localhost, do this:

CALL mysql.sp_set_firewall_group_mode_and_user('fwgrp', 'RECORDING', 'member4@localhost');

That enables additional training by the specified account without having to remove the other group members. They can execute statements, but the statements are not added to the allowlist. (Remember, however, that in RECORDING mode the other members can execute any statement.)

After the additional training, set the group profile back to PROTECTING mode:

CALL mysql.sp_set_firewall_group_mode('fwgrp', 'PROTECTING');

The training account established by sp_set_firewall_group_mode_and_user() is saved in the group profile, so the firewall remembers it in case more training is needed later. Thus, if you call sp_set_firewall_group_mode() (which takes no training account argument), the current profile training account, member4@localhost, remains unchanged.

To clear the training account if it actually is desired to enable all group members to perform training in RECORDING mode, call sp_set_firewall_group_mode_and_user() and pass a NULL value for the account argument:

CALL mysql.sp_set_firewall_group_mode_and_user('fwgrp', 'RECORDING', NULL);

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

CALL mysql.sp_set_firewall_group_mode('fwgrp', 'DETECTING');

Then, using a member account, execute a statement that does not match the group profile 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 'member1@localhost'. Reason: No match in allowlist.
Statement: SHOW TABLES LIKE ?'

To disable a group profile, change its mode to OFF:

CALL mysql.sp_set_firewall_group_mode(group, 'OFF');

To forget all training for a profile and disable it, reset it:

CALL mysql.sp_set_firewall_group_mode(group, 'RESET');

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

MySQL Enterprise Firewall enables profiles to be registered that correspond to individual accounts. To use a firewall account profile to protect MySQL against incoming statements from a given account, follow these steps:

Observe these guidelines for firewall-related account references:

The following procedure shows how to register an account profile with the firewall, train the firewall to know the acceptable statements for that profile (its allowlist), and use the profile to protect MySQL against execution of unacceptable statements by the account. The example account, fwuser@localhost, is presumed 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 that corresponds to the account profile registered with the firewall. For statements executed using this account, the default database should be sakila. (You can use a different database by adjusting the instructions accordingly.)

The firewall account profile now is trained for the fwuser@localhost account. When clients connect using that account and attempt to execute statements, the profile protects MySQL against statements not matched by the profile allowlist.

It is possible to detect intrusions by logging nonmatching statements as suspicious without denying access. First, put the account profile 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 profile 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 allowlist.
Statement: SHOW TABLES LIKE ?'

To disable an account profile, change its mode to OFF:

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

To forget all training for a profile and disable it, reset it:

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

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

To assess firewall activity, examine its status variables. For example, after performing the procedure shown earlier to train and protect the fwgrp group profile, 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.

MySQL Enterprise Firewall supports account profiles that each apply to a single account and also group profiles that each can apply to multiple accounts. A group profile enables easier administration when the same allowlist is to be applied to multiple accounts: instead of creating one account profile per account and duplicating the allowlist across all those profiles, create a single group profile and make the accounts members of it. The group allowlist then applies to all the accounts.

A group profile with a single member account is logically equivalent to an account profile for that account, so it is possible to administer the firewall using group profiles exclusively, rather than a mix of account and group profiles. For new firewall installations, that is accomplished by uniformly creating new profiles as group profiles and avoiding account profiles.

Due to the greater flexibility offered by group profiles, it is recommended that all new firewall profiles be created as group profiles. Account profiles are deprecated, and subject to removal in a future MySQL version. For upgrades from firewall installations that already contain account profiles, MySQL Enterprise Firewall includes a stored procedure named sp_migrate_firewall_user_to_group() to help you convert account profiles to group profiles. To use it, perform the following procedure as a user who has the FIREWALL_ADMIN privilege:

For additional details about sp_migrate_firewall_user_to_group(), see Firewall Miscellaneous Stored Procedures.