MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
The following sections provide a reference to MySQL Enterprise Firewall elements:
MySQL Enterprise Firewall maintains profile information on a per-account and
per-group basis. It uses tables in the
mysql system database for persistent
storage and INFORMATION_SCHEMA or
Performance Schema tables to provide views into in-memory
cached data. When enabled, the firewall bases operational
decisions on the cached data.
MySQL Enterprise Firewall maintains account profile information using tables in
the mysql system database for persistent
storage and INFORMATION_SCHEMA tables to
provide views into in-memory cached data.
Each mysql system database table is
accessible only by accounts that have the
SELECT privilege for it. The
INFORMATION_SCHEMA tables are accessible by
anyone.
The mysql.firewall_users table lists names
and operational modes of registered firewall account profiles.
The table has the following columns (with the corresponding
INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS
table having similar but not necessarily identical columns):
USERHOST
The account profile name. Each account name has the format
user_name@host_name
MODE
The current operational mode for the profile. Permitted
mode values are OFF,
DETECTING,
PROTECTING,
RECORDING, and
RESET. For details about their
meanings, see Firewall Operational Concepts.
The mysql.firewall_whitelist table lists
allowlist rules of registered firewall account profiles. The
table has the following columns (with the corresponding
INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST
table having similar but not necessarily identical columns):
USERHOST
The account profile name. Each account name has the format
user_name@host_name
RULE
A normalized statement indicating an acceptable statement pattern for the profile. A profile allowlist is the union of its rules.
ID
An integer column that is a primary key for the table. This column was added in MySQL 8.0.12.
As of MySQL 8.0.23, MySQL Enterprise Firewall maintains group profile information
using tables in the mysql system 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 mysql.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):
NAME
The group profile name.
MODE
The current operational mode for the profile. Permitted
mode values are OFF,
DETECTING,
PROTECTING, and
RECORDING. For details about their
meanings, see Firewall Operational Concepts.
USERHOST
The training account for the group profile, to be used
when the profile is in RECORDING mode.
The value is NULL, or a
non-NULL account that has the format
user_name@host_name
If the value is NULL, the firewall
records allowlist rules for statements received from
any account that is a member of the group.
If the value is non-NULL, the
firewall records allowlist rules only for statements
received from the named account (which should be a
member of the group).
The mysql.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):
NAME
The group profile name.
RULE
A normalized statement indicating an acceptable statement pattern for the profile. A profile allowlist is the union of its rules.
ID
An integer column that is a primary key for the table.
The mysql.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):
GROUP_ID
The group profile name.
MEMBER_ID
The name of an account that is a member of the profile.
MySQL Enterprise Firewall 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 user-defined functions (UDFs) that provide an API for lower-level tasks.
Firewall stored procedures are created in the
mysql system database. To invoke a firewall
stored procedure, either do so while mysql
is the default database, or qualify the procedure name with
the database name. For example:
CALL mysql.sp_set_firewall_mode(user,mode);
These stored procedures perform management operations on firewall account profiles:
sp_reload_firewall_rules(
user)
This stored procedure provides control over firewall
operation for individual account profiles. The procedure
uses firewall UDFs to reload the in-memory rules for an
account profile from the rules stored in the
mysql.firewall_whitelist table.
Arguments:
user: The name of the
affected account profile, as a string in
user_name@host_name
Example:
CALL mysql.sp_reload_firewall_rules('fwuser@localhost');
This procedure clears the account profile in-memory
allowlist rules before reloading them from persistent
storage, and sets the profile mode to
OFF. If the profile mode was not
OFF prior to the
sp_reload_firewall_rules() call, use
sp_set_firewall_mode() to restore its
previous mode after reloading the rules. For example, if
the profile was in PROTECTING mode,
that is no longer true after calling
sp_reload_firewall_rules() and you
must set it to PROTECTING again
explicitly.
sp_set_firewall_mode(
user,
mode)
This stored procedure establishes the operational mode for
a firewall account profile, after registering the profile
with the firewall if it was not already registered. The
procedure also invokes firewall UDFs as necessary to
transfer firewall data between the cache and persistent
storage. This procedure may be called even if the
mysql_firewall_mode system variable is
OFF, although setting the mode for a
profile has no operational effect until the firewall is
enabled.
Arguments:
user: The name of the
affected account profile, as a string in
user_name@host_name
mode: The operational mode
for the profile, as a string. Permitted mode values
are OFF,
DETECTING,
PROTECTING,
RECORDING, and
RESET. For details about their
meanings, see Firewall Operational Concepts.
Switching an account profile to any mode but
RECORDING synchronizes its firewall
cache data to the mysql system database
tables that provide persistent underlying storage.
Switching the mode from OFF to
RECORDING reloads the allowlist from
the mysql.firewall_whitelist table into
the cache.
If an account profile has an empty allowlist, its mode
cannot be set to PROTECTING because the
profile would reject every statement, effectively
prohibiting the account from executing statements. In
response to such a mode-setting attempt, the firewall
produces a diagnostic message that is returned as a result
set rather than as an SQL error:
mysql> CALL mysql.sp_set_firewall_mode('a@b','PROTECTING');
+----------------------------------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+----------------------------------------------------------------------+
| ERROR: PROTECTING mode requested for a@b but the whitelist is empty. |
+----------------------------------------------------------------------+
These stored procedures perform management operations on firewall group profiles:
sp_firewall_group_delist(
group,
user)
This stored procedure removes an account from a firewall group profile.
If the call succeeds, the change in group membership is made to both the in-memory cache and persistent storage.
Arguments:
group: The name of the
affected group profile.
user: The account to
remove, as a string in
user_name@host_name
Example:
CALL sp_firewall_group_delist('g', 'fwuser@localhost');
This procedure was added in MySQL 8.0.23.
sp_firewall_group_enlist(
group,
user)
This stored procedure adds an account to a firewall group profile. It is not necessary to register the account itself with the firewall before adding the account to the group.
If the call succeeds, the change in group membership is made to both the in-memory cache and persistent storage.
Arguments:
group: The name of the
affected group profile.
user: The account to add,
as a string in
user_name@host_name
Example:
CALL sp_firewall_group_enlist('g', 'fwuser@localhost');
This procedure was added in MySQL 8.0.23.
sp_reload_firewall_group_rules(
group)
This stored procedure provides control over firewall
operation for individual group profiles. The procedure
uses firewall UDFs to reload the in-memory rules for a
group profile from the rules stored in the
mysql.firewall_group_allowlist table.
Arguments:
group: The name of the
affected group profile.
Example:
CALL sp_reload_firewall_group_rules('myapp');
This procedure clears the group profile in-memory
allowlist rules before reloading them from persistent
storage, and sets the profile mode to
OFF. If the profile mode was not
OFF prior to the
sp_reload_firewall_group_rules()
call, use
sp_set_firewall_group_mode() to
restore its previous mode after reloading the rules. For
example, if the profile was in
PROTECTING mode, that is no longer
true after calling
sp_reload_firewall_group_rules() and
you must set it to PROTECTING again
explicitly.
This procedure was added in MySQL 8.0.23.
sp_set_firewall_group_mode(
group,
mode)
This stored procedure establishes the operational mode for
a firewall group profile, after registering the profile
with the firewall if it was not already registered. The
procedure also invokes firewall UDFs as necessary to
transfer firewall data between the cache and persistent
storage. This procedure may be called even if the
mysql_firewall_mode system variable is
OFF, although setting the mode for a
profile has no operational effect until the firewall is
enabled.
If the profile previously existed, any recording
limitation for it remains unchanged. To set or clear the
limitation, call
sp_set_firewall_group_mode_and_user()
instead.
Arguments:
group: The name of the
affected group profile.
mode: The operational mode
for the profile, as a string. Permitted mode values
are OFF,
DETECTING,
PROTECTING, and
RECORDING. For details about their
meanings, see Firewall Operational Concepts.
Example:
CALL sp_set_firewall_group_mode('myapp', 'PROTECTING');
This procedure was added in MySQL 8.0.23.
sp_set_firewall_group_mode_and_user(
group,
mode,
user)
This stored procedure registers a group with the firewall
and establishes its operational mode, similar to
sp_set_firewall_group_mode(), but also
specifies the training account to be used when the group
is in RECORDING mode.
Arguments:
group: The name of the
affected group profile.
mode: The operational mode
for the profile, as a string. Permitted mode values
are OFF,
DETECTING,
PROTECTING, and
RECORDING. For details about their
meanings, see Firewall Operational Concepts.
user: The training account
for the group profile, to be used when the profile is
in RECORDING mode. The value is
NULL, or a
non-NULL account that has the
format
user_name@host_name
If the value is NULL, the
firewall records allowlist rules for statements
received from any account that is a member of the
group.
If the value is non-NULL, the
firewall records allowlist rules only for
statements received from the named account (which
should be a member of the group).
Example:
CALL sp_set_firewall_group_mode_and_user('myapp', 'RECORDING', 'myapp_user1@localhost');
This procedure was added in MySQL 8.0.23.
MySQL Enterprise Firewall user-defined functions (UDFs) provide an API for lower-level tasks such as synchronizing the firewall cache with the underlying system tables.
Under normal operation, these UDFs are invoked by the firewall stored procedures, not directly by users. For that reason, these UDF descriptions do not include the usual details, such as information about their arguments and return types.
These UDFs perform management operations on firewall account profiles:
read_firewall_users(
user,
mode)
This aggregate UDF updates the firewall account profile
cache through a SELECT statement on the
mysql.firewall_users table. It requires
the FIREWALL_ADMIN
privilege or the deprecated
SUPER privilege.
Example:
SELECT read_firewall_users('fwuser@localhost', 'RECORDING')
FROM mysql.firewall_users;
read_firewall_whitelist(
user,
rule)
This aggregate UDF updates the recorded-statement cache
for the named account profile through a
SELECT statement on the
mysql.firewall_whitelist table. It
requires the FIREWALL_ADMIN
privilege or the deprecated
SUPER privilege.
Example:
SELECT read_firewall_whitelist('fwuser@localhost', fw.rule)
FROM mysql.firewall_whitelist AS fw
WHERE USERHOST = 'fwuser@localhost';
This UDF manages the account profile cache and establishes
the profile operational mode. It requires the
FIREWALL_ADMIN privilege or
the deprecated SUPER
privilege.
Example:
SELECT set_firewall_mode('fwuser@localhost', 'RECORDING');
These UDFs perform management operations on firewall group profiles:
firewall_group_delist(
group,
user)
This UDF removes an account from a group profile. It
requires the FIREWALL_ADMIN
privilege.
Example:
SELECT firewall_group_delist('g', 'fwuser@localhost');
This function was added in MySQL 8.0.23.
firewall_group_enlist(
group,
user)
This UDF adds an account to a group profile. It requires
the FIREWALL_ADMIN
privilege.
It is not necessary to register the account itself with the firewall before adding the account to the group.
Example:
SELECT firewall_group_enlist('g', 'fwuser@localhost');
This function was added in MySQL 8.0.23.
read_firewall_group_allowlist(
group,
rule)
This aggregate UDF updates the recorded-statement cache
for the named group profile through a
SELECT statement on the
mysql.firewall_group_allowlist table.
It requires the
FIREWALL_ADMIN privilege.
Example:
SELECT read_firewall_group_allowlist('my_fw_group', fgw.rule)
FROM mysql.firewall_group_allowlist AS fgw
WHERE NAME = 'my_fw_group';
This function was added in MySQL 8.0.23.
read_firewall_groups(
group,
mode,
user)
This aggregate UDF updates the firewall group profile
cache through a SELECT
statement on the mysql.firewall_groups
table. It requires the
FIREWALL_ADMIN privilege.
Example:
SELECT read_firewall_groups('g', 'RECORDING', 'fwuser@localhost')
FROM mysql.firewall_groups;
This function was added in MySQL 8.0.23.
set_firewall_group_mode(
group,
mode[,
user])
This UDF manages the group profile cache, establishes the
profile operational mode, and optionally specifies the
profile training account. It requires the
FIREWALL_ADMIN privilege.
If the optional user argument
is not given, any previous user
setting for the profile remains unchanged. To change the
setting, call the UDF with a third argument.
If the optional user argument
is given, it specifies the training account for the group
profile, to be used when the profile is in
RECORDING mode. The value is
NULL, or a non-NULL
account that has the format
user_name@host_name
If the value is NULL, the firewall
records allowlist rules for statements received from
any account that is a member of the group.
If the value is non-NULL, the
firewall records allowlist rules only for statements
received from the named account (which should be a
member of the group).
Example:
SELECT set_firewall_group_mode('g', 'DETECTING');
This function was added in MySQL 8.0.23.
These UDFs perform miscellaneous firewall operations:
This UDF resets several firewall status variables to 0:
This UDF requires the
FIREWALL_ADMIN privilege or
the deprecated SUPER
privilege.
Example:
SELECT mysql_firewall_flush_status();
This UDF normalizes an SQL statement into the digest form
used for allowlist rules. It requires the
FIREWALL_ADMIN privilege or
the deprecated SUPER
privilege.
Example:
SELECT normalize_statement('SELECT * FROM t1 WHERE c1 > 2');
The same digest functionality is available outside
firewall context using the
STATEMENT_DIGEST_TEXT()
SQL function.
MySQL Enterprise Firewall supports the following system variables. Use them to configure firewall operation. These variables are unavailable unless the firewall is installed (see Section 6.4.7.2, “Installing or Uninstalling MySQL Enterprise Firewall”).
| Command-Line Format | --mysql-firewall-mode[={OFF|ON}] |
|---|---|
| System Variable | mysql_firewall_mode |
| Scope | Global |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Boolean |
| Default Value | ON |
Whether MySQL Enterprise Firewall is enabled (the default) or disabled.
| Command-Line Format | --mysql-firewall-trace[={OFF|ON}] |
|---|---|
| System Variable | mysql_firewall_trace |
| Scope | Global |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Boolean |
| Default Value | OFF |
Whether the MySQL Enterprise Firewall trace is enabled or disabled (the
default). When
mysql_firewall_trace is
enabled, for PROTECTING mode, the
firewall writes rejected statements to the error log.
MySQL Enterprise Firewall supports the following status variables. Use them to
obtain information about firewall operational status. These
variables are unavailable unless the firewall is installed
(see Section 6.4.7.2, “Installing or Uninstalling MySQL Enterprise Firewall”). Firewall 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()
UDF (see MySQL Enterprise Firewall User-Defined Functions).
The number of statements rejected by MySQL Enterprise Firewall.
The number of statements accepted by MySQL Enterprise Firewall.
The number of statements logged by MySQL Enterprise Firewall as suspicious
for users who are in DETECTING mode.
The number of statements recorded by MySQL Enterprise Firewall, including duplicates.