8.4.7.4 MySQL Enterprise Firewall Reference (original) (raw)

8.4.7.4 MySQL Enterprise Firewall Reference

The following sections provide a reference to MySQL Enterprise Firewall elements:

• MySQL Enterprise Firewall Tables
• MySQL Enterprise Firewall Stored Procedures
• MySQL Enterprise Firewall Administrative Functions
• MySQL Enterprise Firewall System Variables
• MySQL Enterprise Firewall Status Variables

MySQL Enterprise Firewall Tables

MySQL Enterprise Firewall maintains profile information on a per-group and per-account basis. It uses tables in the firewall 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. The firewall database can be themysql system database or a custom schema (see Installing MySQL Enterprise Firewall).

• Firewall Group Profile Tables
• Firewall Account Profile Tables

Firewall Group Profile Tables

MySQL Enterprise Firewall 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 system and Performance Schema table is accessible only by accounts that have the SELECT privilege for it.

The_`default-database`_.firewall_groups table lists names and operational modes of registered firewall group profiles. The table has the following columns (with the corresponding Performance Schemafirewall_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, andRECORDING. For details about their meanings, see Firewall 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_`username`_@_`hostname`_:
  • 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_`default-database`_.firewall_group_allowlist table lists allowlist rules of registered firewall group profiles. The table has the following columns (with the corresponding Performance Schemafirewall_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_`default-database`_.firewall_membership table lists the members (accounts) of registered firewall group profiles. The table has the following columns (with the corresponding Performance Schemafirewall_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.

Firewall Account Profile Tables

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

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. SeeMigrating Account Profiles to Group Profiles.

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

• USERHOST
  The account profile name. Each account name has the format_`username`_@_`hostname`_.
• MODE
  The current operational mode for the profile. Permitted mode values are OFF,DETECTING,PROTECTING,RECORDING, andRESET. For details about their meanings, see Firewall Concepts.

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

• USERHOST
  The account profile name. Each account name has the format_`username`_@_`hostname`_.
• 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.

MySQL Enterprise Firewall Stored Procedures

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 administrative functions that provide an API for lower-level tasks.

Firewall stored procedures are created in the firewall database. The firewall database can be themysql system database or a custom schema (see Installing MySQL Enterprise Firewall).

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

In MySQL 8.4, 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.

Note

If you have installed MySQL Enterprise Firewall in a custom schema, then make appropriate substitution for your system. For example, if the firewall is installed in the fwdb schema, then execute the stored procedures like this:

CALL fwdb.sp_set_firewall_group_mode(group, mode);

• Firewall Group Profile Stored Procedures
• Firewall Account Profile Stored Procedures
• Firewall Miscellaneous Stored Procedures

Firewall Group Profile Stored Procedures

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_`username`_@_`hostname`_ format.
    Example:

CALL mysql.sp_firewall_group_delist('g', 'fwuser@localhost');  

• 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_`username`_@_`hostname`_ format.
    Example:

CALL mysql.sp_firewall_group_enlist('g', 'fwuser@localhost');  

• sp_reload_firewall_group_rules(_`group`_)
  This stored procedure provides control over firewall operation for individual group profiles. The procedure uses firewall administrative functions to reload the in-memory rules for a group profile from the rules stored in the_`firewall-database`_.firewall_group_allowlist table.
  Arguments:
  • group: The name of the affected group profile.
    Example:

CALL mysql.sp_reload_firewall_group_rules('myapp');  

Warning
This procedure clears the group profile in-memory allowlist rules before reloading them from persistent storage, and sets the profile mode toOFF. If the profile mode was notOFF prior to thesp_reload_firewall_group_rules() call, usesp_set_firewall_group_mode() to restore its previous mode after reloading the rules. For example, if the profile was inPROTECTING mode, that is no longer true after callingsp_reload_firewall_group_rules() and you must set it to PROTECTING again explicitly.

• 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 administrative functions 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, callsp_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, andRECORDING. For details about their meanings, see Firewall Concepts.
    Example:

CALL mysql.sp_set_firewall_group_mode('myapp', 'PROTECTING');  

• 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 tosp_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, andRECORDING. For details about their meanings, see Firewall Concepts.
  • user: The training account for the group profile, to be used when the profile is in RECORDING mode. The value isNULL, or a non-NULL account that has the format_`username`_@_`hostname`_:
    * 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 mysql.sp_set_firewall_group_mode_and_user('myapp', 'RECORDING', 'myapp_user1@localhost');  

Firewall Account Profile Stored Procedures

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 administrative functions to reload the in-memory rules for an account profile from the rules stored in the_`firewall-database`_.firewall_whitelist table.
  Arguments:
  • user: The name of the affected account profile, as a string in_`username`_@_`hostname`_ format.
    Example:

CALL sp_reload_firewall_rules('fwuser@localhost');  

Warning
This procedure clears the account profile in-memory allowlist rules before reloading them from persistent storage, and sets the profile mode toOFF. If the profile mode was notOFF prior to thesp_reload_firewall_rules() call, usesp_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 callingsp_reload_firewall_rules() and you must set it to PROTECTING again explicitly.
This procedure is deprecated, and subject to removal in a future MySQL version. SeeMigrating Account Profiles to Group Profiles.

• 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 administrative functions 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_`username`_@_`hostname`_ format.
  • mode: The operational mode for the profile, as a string. Permitted mode values are OFF,DETECTING,PROTECTING,RECORDING, andRESET. For details about their meanings, see Firewall Concepts.
    Switching an account profile to any mode butRECORDING synchronizes its firewall cache data to the firewall database tables that provide persistent underlying storage (mysql or custom). Switching the mode from OFF toRECORDING reloads the allowlist from the_`firewall-database`_.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 sp_set_firewall_mode('a@b','PROTECTING');  
+----------------------------------------------------------------------+  
| set_firewall_mode(arg_userhost, arg_mode)                            |  
+----------------------------------------------------------------------+  
| ERROR: PROTECTING mode requested for a@b but the allowlist is empty. |  
+----------------------------------------------------------------------+  

This procedure is deprecated, and subject to removal in a future MySQL version. SeeMigrating Account Profiles to Group Profiles.

Firewall Miscellaneous Stored Procedures

These stored procedures perform miscellaneous firewall management operations.

• sp_migrate_firewall_user_to_group(_`user`_,_`group`_)
  The sp_migrate_firewall_user_to_group() stored procedure converts a firewall account profile to a group profile with the account as its single enlisted member. Run thefirewall_profile_migration.sql script to install it. The conversion procedure is discussed inMigrating Account Profiles to Group Profiles.
  This routine requires theFIREWALL_ADMIN privilege.
  Arguments:
  • user: The name of the account profile to convert to a group profile, as a string in_`username`_@_`hostname`_ format. The account profile must exist, and must not currently be in RECORDING mode.
  • group: The name of the new group profile, which must not already exist. The new group profile has the named account as its single enlisted member, and that member is set as the group training account. The group profile operational mode is taken from the account profile operational mode.
    Example:

CALL sp_migrate_firewall_user_to_group('fwuser@localhost', 'mygroup);  

MySQL Enterprise Firewall Administrative Functions

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.

• Firewall Group Profile Functions
• Firewall Account Profile Functions
• Firewall Miscellaneous Functions

Firewall Group Profile Functions

These functions perform management operations on firewall group profiles:

• firewall_group_delist(group,user)
  This function removes an account from a group profile. It requires the FIREWALL_ADMIN privilege.
  Example:

SELECT firewall_group_delist('g', 'fwuser@localhost');  

• firewall_group_enlist(group,user)
  This function 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');  

• read_firewall_group_allowlist(group,rule)
  This aggregate function updates the recorded-statement cache for the named group profile through aSELECT statement on the_`firewall-database`_.firewall_group_allowlist table. It requires theFIREWALL_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';  

• read_firewall_groups(group,mode,user)
  This aggregate function updates the firewall group profile cache through a SELECT statement on the_`firewall-database`_.firewall_groups table. It requires theFIREWALL_ADMIN privilege.
  Example:

SELECT read_firewall_groups('g', 'RECORDING', 'fwuser@localhost')  
FROM mysql.firewall_groups;  

• set_firewall_group_mode(group,mode[,user])
  This function manages the group profile cache, establishes the profile operational mode, and optionally specifies the profile training account. It requires theFIREWALL_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 function 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 inRECORDING mode. The value isNULL, or a non-NULL account that has the format_`username`_@_`hostname`_:
  • 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');  

Firewall Account Profile Functions

These functions perform management operations on firewall account profiles:

• read_firewall_users(user,mode)
  This aggregate function updates the firewall account profile cache through a SELECT statement on the_`firewall-database`_.firewall_users table. It requires theFIREWALL_ADMIN privilege or the deprecated SUPER privilege.
  Example:

SELECT read_firewall_users('fwuser@localhost', 'RECORDING')  
FROM mysql.firewall_users;  

This function is deprecated, and subject to removal in a future MySQL version. SeeMigrating Account Profiles to Group Profiles.

• read_firewall_whitelist(user,rule)
  This aggregate function updates the recorded-statement cache for the named account profile through aSELECT statement on the_`firewall-database`_.firewall_whitelist table. It requires theFIREWALL_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 function is deprecated, and subject to removal in a future MySQL version. SeeMigrating Account Profiles to Group Profiles.

• set_firewall_mode(user,mode)
  This function manages the account profile cache and establishes the profile operational mode. It requires theFIREWALL_ADMIN privilege or the deprecated SUPER privilege.
  Example:

SELECT set_firewall_mode('fwuser@localhost', 'RECORDING');  

This function is deprecated, and subject to removal in a future MySQL version. SeeMigrating Account Profiles to Group Profiles.

Firewall Miscellaneous Functions

These functions perform miscellaneous firewall operations:

• mysql_firewall_flush_status()
  This function resets several firewall status variables to 0:
  • Firewall_access_denied
  • Firewall_access_granted
  • Firewall_access_suspicious
    This function requires theFIREWALL_ADMIN privilege or the deprecated SUPER privilege.
    Example:

SELECT mysql_firewall_flush_status();  

• normalize_statement(stmt)
  This function normalizes an SQL statement into the digest form used for allowlist rules. It requires theFIREWALL_ADMIN privilege or the deprecated SUPER privilege.
  Example:

SELECT normalize_statement('SELECT * FROM t1 WHERE c1 > 2');  

Note
The same digest functionality is available outside firewall context using theSTATEMENT_DIGEST_TEXT() SQL function.

MySQL Enterprise Firewall System Variables

MySQL Enterprise Firewall supports the following system variables. Use them to configure firewall operation. These variables are unavailable unless the firewall is installed (seeSection 8.4.7.2, “Installing or Uninstalling MySQL Enterprise Firewall”).

• mysql_firewall_database

  Command-Line Format	--mysql-firewall-database[=value]
  System Variable	mysql_firewall_database
  Scope	Global
  Dynamic	No
  SET_VAR Hint Applies	No
  Type	String
  Default Value	mysql
  Specifies the database from which MySQL Enterprise Firewall reads data. Typically, the MYSQL_FIREWALL server-side plugin stores its internal data (tables, stored procedures, and functions) in themysql system database, but you can create and use a custom schema instead (seeInstalling MySQL Enterprise Firewall). This variable permits specifying an alternative database name at startup.

• mysql_firewall_mode

  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.

• mysql_firewall_reload_interval_seconds

  Command-Line Format	--mysql-firewall-reload-interval-seconds[=value]
  System Variable	mysql_firewall_reload_interval_seconds
  Scope	Global
  Dynamic	No
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	0
  Minimum Value	60 (unless 0: OFF)
  Maximum Value	INT_MAX
  Unit	seconds
  Specifies the interval (in seconds) that the server-side plugin uses to reload its internal cache from firewall tables. Whenmysql_firewall_reload_interval_seconds has a value of zero (the default), no periodic reloading of data from tables occurs at runtime. Values between0 and 60 (1 to 59) are not acknowledged by the plugin. Instead, these values adjust to 60 automatically.
  This variable requires that thescheduler component be enabled (ON). For more information, seeScheduling Firewall Cache Reloads.

• mysql_firewall_trace

  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). Whenmysql_firewall_trace is enabled, for PROTECTING mode, the firewall writes rejected statements to the error log.