If the MySQL Enterprise Firewall plugin from an older version of MySQL is already installed, uninstall it using the instructions given later in this section, then restart the server before installing the current version. In this case, it is also necessary to re-register the configuration.

On Windows, you can use Section 2.3.2, “Configuration: Using MySQL Configurator” to install the MySQL Enterprise Firewall plugin by checking the Enable MySQL Enterprise Firewall check box from the Type and Networking tab. (Open Firewall port for network access has a different purpose. It refers to Windows Firewall and controls whether Windows blocks the TCP/IP port on which the MySQL server listens for client connections.)

To install the MySQL Enterprise Firewall plugin using MySQL Workbench, see MySQL Enterprise Firewall Interface.

To install the firewall plugin manually, look in the share directory of your MySQL installation and choose the script that is appropriate for your platform from those listed here:

The installation script creates stored procedures and tables in the firewall database you specify when you run the script. The mysql system database is the default location, but we recommend that you create and use a custom schema specifically for this purpose.

To use the mysql system database, run the script as follows from the command line. The example here uses the Linux installation script. Make any substitutions appropriate for your system.

$> mysql -u root -p -D mysql < linux_install_firewall.sql
Enter password: (enter root password here)

To create and use a custom schema with the script, do the following:

Installing MySQL Enterprise Firewall either using a graphical interface or manually should enable the firewall. To verify this, connect to the server and execute the statement shonw here:

mysql> SHOW GLOBAL VARIABLES LIKE 'mysql_firewall_mode';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| mysql_firewall_mode | ON    |
+---------------------+-------+

If the plugin fails to initialize, check the server error log for diagnostic messages.

The MySQL Enterprise Firewall plugin can be uninstalled using MySQL Workbench, or manually.

To uninstall the MySQL Enterprise Firewall plugin using MySQL Workbench 6.3.4 or later, see MySQL Enterprise Firewall Interface, in Chapter 33, MySQL Workbench.

To uninstall the firewall plugin from the command line, run the uninstall script located in the share directory of your MySQL installation. This example specifies mysql as the firewall database:

$> mysql -u root -p -D mysql < uninstall_firewall.sql
Enter password: (enter root password here)

If you created a custom schema when you installed the firewall plugin, run the uninstall script as shown here, substituting the schema name for database-name:

$> mysql -u root -p -D database-name < uninstall_firewall.sql
Enter password: (enter root password here)

uninstall_firewall.sql removes all firewall plugins, tables, functions, and stored procedures associated with the MySQL Enterprise Firewall plugin.

To enable or disable the firewall plugin, set the mysql_firewall_mode system variable. By default, this is ON when the firewall plugin 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]
mysql_firewall_mode=ON

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

Alternatively, to set and persist the firewall setting at runtime, run the SQL statements shown here:

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 into its internal cache from the tables listed here:

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 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, execute the following SHOW VARIABLES statement:

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

With the firewall plugin installed, set mysql_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] --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 this setting at startup, precede the variable name with the PERSIST_ONLY keyword or the @@PERSIST_ONLY. qualifier, like this:

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

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

After the firewall plugin 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:

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

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

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 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, call sp_set_firewall_group_mode_and_user() as shown here:

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

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 associated status variables. For example, after performing the procedure shown earlier to train and protect the fwgrp group profile, 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. The Firewall_access_granted count is 4 because of 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 plugin supports account profiles, each of which applies to a single account as well as group profiles which can each apply to multiple accounts. A group profile simplifies 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 of those profiles, you can create a single group profile and make the accounts members of it. The group allowlist then applies to all of 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. (In addition, account profiles are not supported by the MySQL Enterprise Firewall component.) For upgrades from firewall installations which use account profiles, the MySQL Enterprise Firewall plugin 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 information about sp_migrate_firewall_user_to_group(), see Firewall Plugin Miscellaneous Stored Procedures.

The MySQL Enterprise Firewall plugin maintains profile information on a per-group and per-account 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 during installation (see Installing the MySQL Enterprise Firewall Plugin).

Tables in the firewall database are covered in this section. For information about MySQL Enterprise Firewall plugin Information Schema tables, see Section 28.7, “INFORMATION_SCHEMA MySQL Enterprise Firewall Plugin Tables”; for information about MySQL Enterprise Firewall Performance Schema tables, see Section 29.12.17, “Performance Schema Firewall Tables”.

The MySQL Enterprise Firewall plugin maintains group profile information using tables in the firewall database (mysql or custom) for persistent storage and Performance Schema tables to provide views into in-memory cached data.

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

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

MySQL Enterprise Firewall maintains account profile information using tables in the firewall database for persistent storage and INFORMATION_SCHEMA tables to provide views into in-memory cached data. The firewall database can be the mysql system database or a custom schema (see Installing the MySQL Enterprise Firewall Plugin).

Each default database table is accessible only by accounts that have the SELECT privilege for it. The INFORMATION_SCHEMA tables are accessible by anyone.

These tables are deprecated, and subject to removal in a future MySQL version. See Migrating Account Profiles to Group Profiles.

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

The firewall-database.firewall_whitelist table lists allowlist rules of registered firewall account profiles. The table has the following columns (with the corresponding MYSQL_FIREWALL_WHITELIST table having similar but not necessarily identical columns):

MySQL Enterprise Firewall plugin stored procedures perform tasks such as registering profiles with the firewall, establishing their operational mode, and managing transfer of firewall data between the cache and persistent storage. These procedures invoke administrative functions that provide an API for lower-level tasks.

Firewall stored procedures are created in the firewall database, which can be the mysql or other database (see Installing the MySQL Enterprise Firewall Plugin).

To invoke a firewall stored procedure, either do so while the specified firewall database is the default database, or qualify the procedure name with the database name. For example, if mysql is the firewall database:

CALL mysql.sp_set_firewall_group_mode(group, mode);

Firewall stored procedures are transactional; if an error occurs during execution of a firewall stored procedure, all changes made by it up to that point are rolled back, and an error is reported.

CALL fwdb.sp_set_firewall_group_mode(group, mode);

The stored procedures listed here perform management operations on firewall group profiles:

The stored procedures listed here perform management operations on firewall account profiles:

The stored procedures listed perform miscellaneous firewall management operations.

MySQL Enterprise Firewall plugin 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 functions listed here perform management operations on firewall account profiles:

The functions listed here perform miscellaneous firewall operations:

The MySQL Enterprise Firewall plugin supports the following system variables for controlling various aspects of firewall operation. These variables are unavailable unless the firewall plugin is installed (see Section 8.4.8.1.2, “Installing or Uninstalling the MySQL Enterprise Firewall Plugin”).

The MySQL Enterprise Firewall plugin supports the following status variables which provide information about the firewall's operational status. These variables are unavailable unless the firewall plugin is installed (see Section 8.4.8.1.2, “Installing or Uninstalling the MySQL Enterprise Firewall Plugin”). Firewall plugin status variables are set to 0 whenever the MYSQL_FIREWALL plugin is installed or the server is started. Many of them are reset to zero by the mysql_firewall_flush_status() function (see MySQL Enterprise Firewall Plugin Administrative Functions).