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.
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”.
With the firewall installed, grant the appropriate privileges to the MySQL account or accounts that are expected to 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 haveFIREWALL_ADMIN
or the deprecatedSUPER
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 themysql
system database. These may invoke UDFs, so stored procedure access also requires the privileges indicated earlier that are needed for those UDFs.
The FIREWALL_ADMIN
and
FIREWALL_USER
privileges can
be granted only while the firewall is installed because the
firewall component defines those privileges.
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 operation is based on a registry of profiles that enable statement execution protection to be applied. A profile has these attributes:
Rules that define which statements are acceptable to the profile. This set of rules forms the profile allowlist.
The current operational mode. The mode enables the profile to be used in different ways. For example: the profile can be placed in training mode to establish the allowlist; the allowlist can be used for restricting statement execution or intrusion detection; the profile can be disabled entirely.
The scope of applicability, indicating which client connections the profile applies to.
The firewall supports account-based profiles such that each profile matches a particular client account (client user name and host name combination). For example, you can register one account profile for which the allowlist applies to connections originating from
admin@localhost
and another account profile for which the allowlist applies to connections originating frommyapp@apphost.example.com
.As of MySQL 8.0.23, the firewall also supports group profiles that can have multiple accounts as members. Group profiles enable easier administration and greater flexibility for deployments that require a given set of allowlist rules to apply to multiple accounts:
Using account profiles, it is necessary to register one profile per account and duplicate the allowlist across each profile.
As a simpler alternative, a group profile can be used that has all the accounts as members. The group profile allowlist applies to all member accounts, with no need to duplicate it for each account.
For each client connection, the firewall determines which profile applies, and accepts only statements the profile allowlist permits. (If the client matches no profile, the firewall ignores it and accepts all statements.)
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 profiles with the firewall. (This is required because the firewall ignores clients that match no profile.)
Train the firewall to establish the allowlist for each profile; that is, the types of statements the profile permits clients to execute.
Tell the firewall to protect MySQL using each profile 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 in allowlists digests that each match many different statements received from clients. For more information about normalization and digests, see Section 27.10, “Performance Schema Statement Digests and Sampling”.
Setting the
max_digest_length
system
variable to zero disables digest production, which also
disables server functionality that requires digests, such as
MySQL Enterprise Firewall.
Each profile registered with the firewall has its own operational mode, chosen from these values:
OFF
: This mode disables the profile. The firewall considers it inactive and ignores it.RECORDING
: This is the firewall training mode. Incoming statements received from a client that matches the profile are considered acceptable for the profile and become part of its “fingerprint.” The firewall records the normalized digest form of each statement to learn the acceptable statement patterns for the profile. Each pattern is a rule, and the union of the rules is the profile allowlist.A difference between account and group profiles is that statement recording for a group profile can be limited to statements received from a single group member.
PROTECTING
: In this mode, the profile allows or prevents statement execution. The firewall matches incoming statements against the profile allowlist, accepting only only statements that match and rejecting those that do not. After training the profile inRECORDING
mode, switch it toPROTECTING
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 profile allowlist). InDETECTING
mode, the firewall writes suspicious statements to the error log but accepts them without denying access.
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
.
The following sections describe how to use firewall profiles. For simplicity, the discussion covers setting up a single account profile and a single group profile, and then moves moving on to the more complex case of how the firewall operates when multiple profiles apply simultaneously.
MySQL Enterprise Firewall enables profiles to be registered 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 profiles to determine which profile applies to handling incoming statements from the session:
The firewall ignores inactive profiles (profiles with a mode of
OFF
).The session account matches the active account profile having the same user and host, if there is one. There is at most one such account profile.
In other words, at most one active account profile is applicable to a given session, for which the firewall handles each incoming statement as follows:
If there is no applicable profile, there are no restrictions. The firewall accepts the statement.
If there is an applicable profile, its mode determines statement handling:
In
RECORDING
mode, the firewall adds the statement to the profile allowlist rules and accepts it.In
PROTECTING
mode, the firewall compares the statement to the rules in the profile allowlist. The firewall accepts the statement if there is a match, and rejects it otherwise. If themysql_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 profile allowlist, as inPROTECTING
mode. If the statement is suspicious (nonmatching), the firewall writes it to the error log.
This discussion is a simplification because it assumes the session account matches at most one applicable account profile. For discussion of the multiple-profile case, see Firewall Operation for Multiple Applicable Profiles.
To use a firewall account profile to protect MySQL against incoming statements from a given account, follow these steps:
Register the account profile and put it in
RECORDING
mode.Connect to the MySQL server using the account and execute statements to be learned. This trains the corresponding account profile and establishes the rules that form the profile allowlist.
Switch the account profile to
PROTECTING
mode. When a client connects to the server using the account, the account profile allowlist restricts statement execution.Should additional training be necessary, switch the account profile to
RECORDING
mode again, update its allowlist with new statement patterns, then switch it back toPROTECTING
mode.
By maintaining profiles, 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 account profile.
If related applications share protection requirements, configure them all to use the same account (and thus the same account profile).
Alternatively, associate each application with its own acccount, then add these accounts to the same group profile. See Registering Firewall Group Profiles.
Observe these guidelines for firewall-related account references:
Take note of the context in which account references occur. To name an account for firewall operations, specify it as a single quoted string (
'
). This differs from the usual MySQL convention for statements such asuser_name
@host_name
'CREATE USER
andGRANT
, 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 profiles, 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 hostabc.example.org
.The account name contains a
%
wildcard character, but the server authenticates the client as having a user name ofme
and host name ofabc.example.com
, and that is what the firewall sees.Consequently, the account name to use for firewall operations is
me@abc.example.org
rather thanme@%.example.org
.
The following example shows how to register an account profile
with the firewall, teach the firewall the acceptable
statements for that profile, 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 corresponding
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.)
If necessary, create the account to use for executing statements (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';
Use the
sp_set_firewall_mode()
stored procedure to register an account profile with the firewall and place the profile inRECORDING
(training) mode:CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING');
As it executes, the stored procedure invokes firewall user-defined functions, which may produce output of their own.
To train the registered account profile, connect to the server as
fwuser
from the server host so that the firewall sees a session account offwuser@localhost
. Then use the account to execute some statements to be considered legitimate for the profile. For example: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 profile is in
RECORDING
mode, the firewall records the normalized digest form of the statements as rules in the profile allowlist.NoteUntil the
fwuser@localhost
account profile receives statements inRECORDING
mode, its allowlist is empty, which is equivalent to “deny all.” No statement can match an empty allowlist, which has these implications:The account profile cannot be switched to
PROTECTING
mode. It would reject every statement, effectively prohibiting the account from executing any statement.The account profile can be switched to
DETECTING
mode. In this case, the profile accepts every statement but logs it as suspicious.
At this point, the account profile information is cached. To see this information, query 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 ? | +----------------------------------------------------------------------------+
NoteThe
@@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 profile.ImportantTrain the firewall under conditions matching application use. For example, to determine server characteristics and capabilities, a given MySQL connector might send statements to the server at the beginning of each session. If an application normally is used through that connector, train the firewall using the connector, too. That enables those initial statements to become part of the allowlist for the account profile associated with the application.
Invoke
sp_set_firewall_mode()
again, this time switching the account profile toPROTECTING
mode:CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'PROTECTING');
ImportantSwitching the account profile out of
RECORDING
mode synchronizes its cached data to themysql
system database tables that provide persistent underlying storage. If you do not switch the mode for a profile that is being recorded, the cached data is not written to persistent storage and is lost when the server is restarted.Test the account profile by using the account to execute some acceptable and unacceptable statements. The firewall matches each statement against the profile 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` '
These log messages may be helpful in identifying the source of attacks, should that be necessary.
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 also 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 whitelist.
Statement: SHOW TABLES LIKE ? '
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 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
.
In addition to supporting profiles that correspond to individual accounts, MySQL Enterprise Firewall supports group profiles as of MySQL 8.0.23. A group profile can have multiple accounts as its members.
A group profile differs from an account profile in these ways:
For a group profile, its allowlist applies when the session account matches any member of the group. Group profiles enable you to apply a given allowlist to multiple accounts, without having to duplicate each rule in its allowlist for each account.
An account profile can be trained only using the single account to which it applies. A group profile can be trained using any or all of the group member accounts, or limited to any single one of those accounts.
Account profile names are based on specific user name and host name combinations that depend on which clients connect to the MySQL server. Group profile names are chosen by you with no constraints other than that their length must be from 1 to 288 characters.
In other respects, the principles involved with using account profiles also apply to using group profiles. Familiarity with those principles is assumed here; see Registering Firewall Account Profiles.
This discussion is a simplification because it assumes the session account matches at most one applicable group profile. For discussion of the multiple-profile case, see Firewall Operation for Multiple Applicable Profiles.
The following example shows how to register a group profile
with the firewall, train its allowlist, use it to protect
MySQL against execution of unacceptable statements, and add
and remove members. The example uses a group profile name of
mygrp
.
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.
If necessary, create the accounts that are to be members of the
mygrp
group profile and grant them appropriate access privileges. Statements for one member are shown here (choose an appropriate password):CREATE USER 'member1'@'localhost' IDENTIFIED BY 'password'; GRANT ALL ON sakila.* TO 'member1'@'localhost';
Use the
sp_set_firewall_group_mode()
stored procedure to register a group profile with the firewall and place the profile inRECORDING
(training) mode:CALL mysql.sp_set_firewall_group_mode('mygrp', 'RECORDING');
Use the
sp_firewall_group_enlist()
stored procedure to add an initial member account for use in training the group profile allowlist:CALL mysql.sp_firewall_group_enlist('mygrp', 'member1@localhost');
To use the the initial member account for training the group profile, connect to the server as
member1
from the server host so that the firewall sees a session account ofmember1@localhost
. Then execute some statements to be considered legitimate for the profile. For example:SELECT title, description FROM film WHERE film_id = 1; UPDATE actor SET last_update = NOW() WHERE actor_id = 1; SELECT store_id, COUNT(*) FROM inventory GROUP BY store_id;
The firewall receives the statements from the
member1@localhost
account. Because that account is a member of themygrp
profile, which is inRECORDING
mode, the firewall interprets the statements as applicable tomygrp
and records the normalized digest form of the statements as rules in themygrp
allowlist. Those rules then apply to all accounts that are members ofmygrp
.At this point, the group profile information is cached, including its name, membership, and allowlist. To see this information, query the firewall Performance Schema tables:
mysql> SELECT MODE FROM performance_schema.firewall_groups WHERE NAME = 'mygrp'; +-----------+ | MODE | +-----------+ | RECORDING | +-----------+ mysql> SELECT * FROM performance_schema.firewall_membership ORDER BY USERHOST; +------------+-------------------+ | GROUP_NAME | USERHOST | +------------+-------------------+ | mygrp | member1@localhost | +------------+-------------------+ mysql> SELECT RULE FROM performance_schema.firewall_group_allowlist WHERE NAME = 'mygrp'; +----------------------------------------------------------------------+ | RULE | +----------------------------------------------------------------------+ | SELECT @@`version_comment` LIMIT ? | | SELECT `title` , DESCRIPTION FROM `film` WHERE `film_id` = ? | | UPDATE `actor` SET `last_update` = NOW ( ) WHERE `actor_id` = ? | | SELECT `store_id` , COUNT ( * ) FROM `inventory` GROUP BY `store_id` | +----------------------------------------------------------------------+
Invoke
sp_set_firewall_group_mode()
again to switch the group profile toPROTECTING
mode:CALL mysql.sp_set_firewall_group_mode('mygrp', 'PROTECTING');
Add to the group profile any other accounts that should be members:
CALL mysql.sp_firewall_group_enlist('mygrp', 'member2@localhost'); CALL mysql.sp_firewall_group_enlist('mygrp', 'member3@localhost'); CALL mysql.sp_firewall_group_enlist('mygrp', 'member4@localhost');
The profile allowlist trained using the
member1@localhost
account now also applies to the additional accounts.To verify the updated group membership, query the
firewall_membership
table again:mysql> SELECT * FROM performance_schema.firewall_membership ORDER BY USERHOST; +------------+-------------------+ | GROUP_NAME | USERHOST | +------------+-------------------+ | mygrp | member1@localhost | | mygrp | member2@localhost | | mygrp | member3@localhost | | mygrp | member4@localhost | +------------+-------------------+
Test the group profile against the firewall by using any account in the group to execute some acceptable and unacceptable statements. The firewall matches each statement from the account against the profile allowlist and accepts or rejects it.
Should members need to be removed from the group profile, use the
sp_firewall_group_delist()
stored procedure rather thansp_firewall_group_enlist()
:CALL mysql.sp_firewall_group_delist('mygrp', 'member3@localhost');
The procedure just shown added only one member to the group
profile before training its allowlist. Doing so provides
better control over the 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('mygrp', '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('mygrp', '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('mygrp', '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('mygrp', 'RECORDING', NULL);
To avoid unexpected behavior when a particular account is specified as the training account for a group profile, always ensure that account is a member of the group.
Just like account profiles, group profiles can be assigned a
mode of DETECTING
for intrusion detection,
OFF
to disable the profile, or
RESET
to forget the profile rules and set
its mode to OFF
.
For simplicity, the preceding sections take the perspective that the firewall matches incoming statements from a client against only a single profile, either an account profile or group profile. But firewall operation can be more complex:
A group profile can include multiple accounts as members.
An account can be a member of multiple group profiles.
Multiple profiles can match a given client.
This section describes how the firewall operates when multiple profiles apply to incoming statements. The description covers the general case, including the cases discussed previously of a single applicable account or group profile.
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 profiles to determine which profiles apply to handling incoming statements from the session:
The firewall ignores inactive profiles (profiles with a mode of
OFF
).The session account matches the active account profile having the same user and host, if there is one. There is at most one such account profile.
The session account matches every active group profile that includes a member having the same user and host. There can be more than one such group profile.
In other words, the session account can match 0 or 1 active account profiles, and 0 or more active group 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:
If there is no applicable profile, there are no restrictions. The firewall accepts the statement.
If there are applicable profiles, their modes determine statement handling:
The firewall records the statement in the allowlist of each applicable profile that is in
RECORDING
mode.The firewall writes the statement to the error log for each applicable profile that is in
DETECTING
mode.The firewall accepts the statement if at least one applicable profile is in
RECORDING
orDETECTING
mode (those modes accept all statements), or if the statement matches the allowlist of at least one applicable profile inPROTECTING
mode. Otherwise, the firewall rejects the statement (and writes it to the error log if themysql_firewall_trace
system variable is enabled).
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.
Prior to MySQL 8.0.23, MySQL Enterprise Firewall supports only account profiles that each apply to a single account. As of MySQL 8.0.23, the firewall also supports 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,
account profiles are deprecated as of MySQL 8.0.25 and subject
to removal in a future MySQL version. For upgrades from
firewall installations that already contain account profiles,
MySQL Enterprise Firewall in MySQL 8.0.25 and higher includes a stored procedure
named sp_migrate_firewall_user_to_group()
for converting account profiles to group profiles.
To convert account profiles to group profiles, perform the
following procedure as a user who has the
FIREWALL_ADMIN
privilege:
Identify which account profiles exist by querying the
INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS
table. For example:mysql> SELECT USERHOST FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS; +-------------------------------+ | USERHOST | +-------------------------------+ | admin@localhost | | local_client@localhost | | remote_client@abc.example.com | +-------------------------------+
For each account profile identified by the previous step, convert it to a group profile:
CALL mysql.sp_migrate_firewall_user_to_group('admin@localhost', 'admins'); CALL mysql.sp_migrate_firewall_user_to_group('local_client@localhost', 'local_clients'); CALL mysql.sp_migrate_firewall_user_to_group('remote_client@localhost', 'remote_clients');
In each case, the account profile must exist and must not currently be in
RECORDING
mode, and the group profile must not already exist. The resulting group profile has the named account as its single enlisted member, which is also set as the group training account. The group profile operational mode is taken from the account profile operational mode.
For additional details about
sp_migrate_firewall_user_to_group()
, see
Firewall Miscellaneous Stored Procedures.