MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

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

Firewall Account Profile Tables

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.

Firewall Group Profile Tables

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

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

    Example:

    CALL mysql.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 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 format.

    • 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. |
    +----------------------------------------------------------------------+
    
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 user_name@host_name format.

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

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

    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

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.

Firewall Account Profile User-Defined Functions

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';
    
  • set_firewall_mode(user, mode)

    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');
    
Firewall Group Profile User-Defined Functions

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.

Firewall Miscellaneous User-Defined Functions

These UDFs perform miscellaneous firewall operations:

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 (see Section 6.4.7.2, “Installing or Uninstalling MySQL Enterprise Firewall”).

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

MySQL Enterprise Firewall Status Variables

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