This section provides information about performing a new installation of the MySQL Enterprise Firewall component. If, instead, you wish to upgrade an existing plugin-based installation of MySQL Enterprise Firewall to use the firewall component, see Upgrading to the MySQL Enterprise Firewall Component.

Prior to beginning the installation, you must choose a location for the firewall database. While it is possible to use the mysql system database, we recommend that you use a separate, dedicated database for this purpose. For example, to install MySQL Enterprise Firewall to a new, not previously existing, database named myfwdb, execute the statements shown here in the mysql client:

mysql> CREATE DATABASE IF NOT EXISTS myfwdb;
Query OK, 1 row affected (0.01 sec)

mysql> USE myfwdb;
Database changed

To perform the installation, use install_component_firewall.sql from the share directory of your MySQL installation. This script installs the firewall database to the current database, so you should make sure that this is the case before proceeding, like this:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| myfwdb     |
+------------+
1 row in set (0.00 sec)

This script requires (and accepts) no arguments; simply run it using the mysql client source command, as shown here:

mysql> source ../share/install_component_firewall.sql

Adjust the path to the share directory as needed to match your installation layout. See Section 6.5.1.2, “mysql Client Commands”, and Section 6.5.1.5, “Executing SQL Statements from a Text File”, for more information about using the source command.

install_component_firewall.sql creates all tables, stored procedures, and server variables needed by the MySQL Enterprise Firewall component. The component_firewall.database server system variable is set to the name of the current database (and persisted), and the firewall is enabled, as you can see by checking the values of component_firewall.database and component_firewall.enabled, like this:

SELECT component_firewall.database, component_firewall.enabled;
+-----------------------------+----------------------------+
| component_firewall.database | component_firewall.enabled |
+-----------------------------+----------------------------+
|                      myfwdb |                         ON |
+-----------------------------+----------------------------+
1 row in set (0.00 sec)

This section provides information about performing a complete removal of the MySQL Enterprise Firewall component and its related elements. For information about downgrading the component to the firewall plugin (deprecated), see Downgrading the MySQL Enterprise Firewall Component.

You can remove the MySQL Enterprise Firewall component from your MySQL installation using the supplied script uninstall_component_firewall.sql which can be found in the share directory.

After ensuring the the current database is the firewall database, execute this script from a mysql client session. In this example, we assume that the firewall database is named myfwdb:

SELECT @@component_firewall.database;
+-------------------------------+
| @@component_firewall.database |
+-------------------------------+
|                        myfwdb |
+-------------------------------+
1 row in set (0.00 sec)

mysql> USE myfwdb;
Database changed

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| myfwdb     |
+------------+
1 row in set (0.00 sec)

mysql> source ../share/uninstall_component_firewall.sql

You may need to adjust the path to the share directory to match the layout of your installation. For more information, see Section 6.5.1.2, “mysql Client Commands”, as well as Section 6.5.1.5, “Executing SQL Statements from a Text File”.

This section describes how to upgrade an existing MySQL Enterprise Firewall plugin installation to the firewall component introduced in MySQL 9.4.0.

You can perform an upgrade from the firewall plugin to the firewall component using the script firewall_plugin_to_component.sql, in the MySQL installation share directory. This script performs the following tasks:

To perform the upgrade, start a mysql client session and ensure that the current database is the firewall database (mysql_firewall_database). After this, simply run the script. This is shown here:

mysql> SELECT @@mysql_firewall_database;
+---------------------------+
| @@mysql_firewall.database |
+---------------------------+
|                    myfwdb |
+---------------------------+
1 row in set (0.00 sec)

mysql> USE myfwdb;
Database changed

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| myfwdb     |
+------------+
1 row in set (0.00 sec)

mysql> source ../share/firewall_plugin_to_component.sql

We use myfwdb in the preceding example as the name of the firewall database; this value is almost certain to be different on your system. In addition, you may also need to adjust the path to the share directory.

This section describes how to downgrade an existing MySQL Enterprise Firewall component installation to the legacy firewall plugin (deprecated in MySQL 9.4.0 and later).

A downgrade from the firewall component to the firewall plugin consists of two parts:

firewall_component_to_plugin.sql must be run with the firewall database as the current database. You can ensure that this is the case, and then execute the script using the mysql client source command, as shown here:

SELECT @@component_firewall.database;
+-------------------------------+
| @@component_firewall.database |
+-------------------------------+
|                        myfwdb |
+-------------------------------+
1 row in set (0.00 sec)

mysql> USE myfwdb;
Database changed

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| myfwdb     |
+------------+
1 row in set (0.00 sec)

mysql> source ../share/firewall_component_to_plugin.sql

The example shows myfwdb as the name of the firewall database; this is likely to be different on your system. Adjust the path shown here to the share directory as necessary to conform to the installation layout on your system.

firewall_component_to_plugin.sql terminates with the output shown here:

Restart mysqld with the following options:
--loose-mysql-firewall-database=database,
--loose-mysql-firewall-mode=mode,
--loose-mysql-firewall-reload-interval-seconds=seconds,
--loose-mysql-firewall-trace=trace,
and run win_install_firewall.sql or linux_install_firewall.sql on schema database.

Restart the MySQL server using the indicated options and values, then start a mysql client session and run linux_install_firewall.sql (Linux and other Unix platforms) or win_install_firewall.sql (Windows platforms). See Installing the MySQL Enterprise Firewall Plugin for more information about using these scripts to install the firewall plugin.

To enable or disable the firewall plugin, set the component_firewall.enabled system variable. By default, this is ON when the firewall component is installed. To set the initial firewall state explicitly at server startup, you can set the variable in an option file such as my.cnf, like this:

[mysqld]
component_firewall.enabled=ON

After modifying my.cnf, you must restart the server to cause the new setting to take effect. See Section 6.2.2.2, “Using Option Files”.

Alternatively, you can set and persist the firewall setting at runtime by executing either of the SQL statements shown here:

SET PERSIST component_firewall.enabled = OFF;
SET PERSIST component_firewall.enabled = ON;

SET PERSIST sets a value for the running MySQL instance, and 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”, for more information.

Each time the firewall component initializes, it loads data into its internal cache from the following tables (see MySQL Enterprise Firewall Component Tables):

Without restarting the server or reinstalling the server-side plugin, modification of data outside of the plugin is not reflected internally. The component_firewall.reload_interval_seconds system variable makes it possible to force memory cache reloads from tables at specified intervals. By default, the interval value is 0, which disables such 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, use a SHOW VARIABLES statement similar to this one:

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

With the firewall plugin installed, set component_firewall.reload_interval_seconds at server startup to a number between 60 and INT_MAX, whose value is platform-specific. Values in the range 1 to 59 (inclusive) are reset to 60, with a warning, as shown here:

$> mysqld [server-options] --component_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 this value at startup, precede the variable name with the PERSIST_ONLY keyword or the @@PERSIST_ONLY. qualifier, like this:

SET PERSIST_ONLY component_firewall.reload_interval_seconds = 120;
SET @@PERSIST_ONLY.component_firewall.reload_interval_seconds = 120;

After performing this modification, restart the server to cause the new setting to take effect.

After the firewall component has been installed and configured, you should grant appropriate privileges to the MySQL account or accounts to be used for administering it. The assignment of privileges depends on which firewall operations an account should be permitted to perform, as listed here:

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.

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. This includes the following steps:

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

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, shown here:

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

Each profile registered with the firewall has its own operational mode, chosen from the following 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 the mode value RESET, but this value is not stored: setting a given profile to RESET mode causes the firewall to delete all rules for this profile, and then set its mode to OFF.

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 account references relating to the MySQL Enterprise Firewall plugin:

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 the group profile name 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, like this:

CALL firewall-database.set_firewall_group_mode('fwgrp', 'RECORDING');

You should keep in mind that this enables any member of the group to execute statements and to add them to the allowlist. To limit the additional training to a single group member, call set_firewall_group_mode_and_user() instead. This is like set_firewall_group_mode() but takes an additional argument specifying which account is permitted to train the profile in RECORDING mode. For example, to enable training only by member4@localhost, call set_firewall_group_mode_and_user() as shown here:

CALL firewall-database.set_firewall_group_mode_and_user('fwgrp', 'RECORDING', 'member4@localhost');

This 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.) You should also keep in mind that, in RECORDING mode, the other members can execute any statement.

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

CALL firewall-database.set_firewall_group_mode('fwgrp', 'PROTECTING');

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

If desired, you can clear the training account, and enable all group members to perform training in RECORDING mode, by calling set_firewall_group_mode_and_user() and passing NULL for the account name, as shown here:

CALL firewall-database.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, like this:

CALL firewall-database.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, as shown here:

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

In addition, the firewall writes a message to the error log.

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

CALL firewall-database.set_firewall_group_mode('fwgrp', 'OFF');

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

CALL firewall-database.sp_set_firewall_group_mode('fwgrp', 'RESET');

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

You can assess firewall activity by examine its associated status variables. For example, after performing the procedure shown earlier to train and protect the fwgrp group profile (see Registering Firewall Profiles), these variables have the values shown in the output of this SHOW GLOBAL STATUS statement:

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     |
+----------------------------+-------+

These variables indicate the numbers of statements rejected, accepted, logged as suspicious, and added to the cache, respectively. firewall_access_granted is 4 due to the @@version_comment statement sent by the mysql client each of the three times the registered account connected to the server, plus the SHOW TABLES statement that was not blocked in DETECTING mode.

the MySQL Enterprise Firewall component maintains profile information on a per-group basis, using tables in the firewall database for persistent storage and Information Schema tables to provide views into in-memory cached data. When enabled, the firewall bases operational decisions on the cached data. The firewall database can be the mysql system database or one determined when installing the component (see Installing the MySQL Enterprise Firewall Component).

Tables in the firewall database are covered in this section. For information about MySQL Enterprise Firewall Performance Schema tables, see Section 29.12.17, “Performance Schema Firewall Tables”.

The MySQL Enterprise Firewall component maintains group profile information using tables in the firewall database for persistent storage, and Performance Schema tables to provide views into in-memory, cached data.

Each system and Performance Schema table is accessible only by accounts that have the SELECT privilege for it.

The firewall-database.firewall_groups table lists names and operational modes of registered firewall group profiles. The table has the following columns (with the corresponding Performance Schema firewall_groups table having similar but not necessarily identical columns):

The firewall-database.firewall_group_allowlist table lists allowlist rules of registered firewall group profiles. The table has the following columns (with the corresponding Performance Schema firewall_group_allowlist table having similar but not necessarily identical columns):

The firewall-database.firewall_membership table lists the members (accounts) of registered firewall group profiles. The table has the following columns (with the corresponding Performance Schema firewall_membership table having similar but not necessarily identical columns):

The MySQL Enterprise Firewall component provides the following stored procedures for performing management operations on firewall group profiles:

MySQL Enterprise Firewall administrative functions provide an API for lower-level tasks such as synchronizing the firewall cache with the underlying system tables.

Under normal operation, these functions are invoked by the firewall stored procedures, not directly by users. For that reason, these function descriptions do not include details such as information about their arguments and return types.

These functions perform management operations on firewall group profiles:

The MySQL Enterprise Firewall component provides the system variables listed in this section. These variables are unavailable unless the component is installed (see Section 8.4.8.1.2, “Installing or Uninstalling the MySQL Enterprise Firewall Plugin”).

The MySQL Enterprise Firewall component provides the status variables listed in this section; you can them to obtain information about the firewall component's operational status.

Firewall component status variables are set to 0 whenever the component is installed or the server is started.

This section contains information about SQL scripts provided by the MySQL Enterprise Firewall component.