MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The following sections provide a reference to MySQL Enterprise Firewall elements:
MySQL Enterprise Firewall maintains profile information using tables in the
mysql
system 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.
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
(as of MySQL 5.6.26),
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.
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
);
The following list describes each firewall stored procedure:
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
format.
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
format.
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. |
+----------------------------------------------------------------------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
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 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 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
SUPER
privilege.
Example:
SELECT set_firewall_mode('fwuser@localhost', 'RECORDING');
These UDFs perform miscellaneous firewall operations:
This UDF resets several firewall status variables to 0:
This UDF requires the 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
SUPER
privilege.
Example:
SELECT normalize_statement('SELECT * FROM t1 WHERE c1 > 2');
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.5.2, “Installing or Uninstalling MySQL Enterprise Firewall”).
Command-Line Format | --mysql-firewall-max-query-size=# |
---|---|
Introduced | 5.6.24 |
Removed | 5.6.26 |
System Variable | mysql_firewall_max_query_size |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | 4096 |
Minimum Value | 0 |
Maximum Value | 4294967295 |
The maximum size of a normalized statement that can be
inserted in the MySQL Enterprise Firewall cache. Normalized statements longer
than this size are truncated. Truncated statements are
discarded if the firewall mode for the current user is
RECORDING
and rejected if the mode is
PROTECTING
.
mysql_firewall_max_query_size
was removed in MySQL 5.6.26.
max_digest_length
should
be set large enough to avoid statement truncation.
Command-Line Format | --mysql-firewall-mode[={OFF|ON}] |
---|---|
Introduced | 5.6.24 |
System Variable | mysql_firewall_mode |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
Whether MySQL Enterprise Firewall is enabled (the default) or disabled.
Command-Line Format | --mysql-firewall-trace[={OFF|ON}] |
---|---|
Introduced | 5.6.24 |
System Variable | mysql_firewall_trace |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Whether the MySQL Enterprise Firewall trace is enabled or disabled (the
default). When enabled,
mysql_firewall_trace
has
this effect:
In MySQL 5.6.24, the firewall writes a file named
firewall_trace.txt
in the data
directory.
In MySQL 5.6.25 and higher, 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.5.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.