6.4.6.4 MySQL Enterprise Firewall Reference

The following discussion serves as a reference to MySQL Enterprise Firewall components:

MySQL Enterprise Firewall Tables

MySQL Enterprise Firewall maintains account and whitelist information. It uses tables in the mysql system database to store this data in persistent form, and INFORMATION_SCHEMA tables to provide views into cached data. When enabled, the firewall bases its operational decisions on the cached data.

The mysql tables can be accessed only by users with privileges for that database. The INFORMATION_SCHEMA tables are accessible by anyone.

The mysql.firewall_users table lists registered firewall accounts and their operational modes. The table has the following columns (with the corresponding INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS table having similar but not necessarily identical columns):

  • USERHOST

    An account registered with the firewall. Each account has the format user_name@host_name and represents actual user and host names as authenticated by the server. Patterns and netmasks should not be used when registering users.

  • MODE

    The current firewall operational mode for the account. The permitted mode values are OFF, DETECTING, PROTECTING, RECORDING, and RESET. For details about their meanings, see the description of sp_set_firewall_mode() in MySQL Enterprise Firewall Procedures and Functions.

The mysql.firewall_whitelist table lists registered firewall accounts and their whitelists. The table has the following columns (with the corresponding INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST table having similar but not necessarily identical columns):

  • USERHOST

    An account registered with the firewall. The format is the same as for the user account tables.

  • RULE

    A normalized statement indicating an acceptable statement pattern for the account. An account whitelist is the union of its rules.

  • ID

    An integer column that is a primary key for the table. This column was added in MySQL 5.7.23.

MySQL Enterprise Firewall Procedures and Functions

MySQL Enterprise Firewall has stored procedures that perform tasks such as registering MySQL accounts with the firewall, establishing their operational mode, and managing transfer of firewall data between the cache and the underlying system tables. It also has a set of user-defined functions (UDFs) that provides an SQL-level API for lower-level tasks such as synchronizing the cache with the underlying system tables.

Under normal operation, the stored procedures implement the user interface. The UDFs are invoked by the stored procedures, not directly by users.

To invoke a stored procedure when the default database is not the database that contains the procedure, 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 and UDF:

  • sp_reload_firewall_rules(user)

    This stored procedure uses firewall UDFs to reset a registered account and reload the in-memory rules for it from the rules stored in the mysql.firewall_whitelist table. This procedure provides control over firewall operation for individual accounts.

    The user argument names the affected account, as a string in user_name@host_name format.

    Example:

    CALL mysql.sp_reload_firewall_rules('fwuser@localhost');
    Warning

    This procedure sets the account mode to RESET, which clears the account whitelist and sets its mode to OFF. If the account 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 account 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 registers a MySQL account with the firewall and establishes its operational mode. The procedure also invokes firewall UDFs as necessary to transfer firewall data between the cache and the underlying system tables. This procedure may be called even if the mysql_firewall_mode system variable is OFF, although setting the mode for an account has no operational effect while the firewall is disabled.

    The user argument names the affected account, as a string in user_name@host_name format.

    The mode is the operational mode for the user, as a string. These mode values are permitted:

    • OFF: Disable the firewall for the account.

    • DETECTING: Intrusion-detection mode: Write suspicious (nonmatching) statements to the error log but do not deny access.

    • PROTECTING: Protect the account by matching incoming statements against the account whitelist.

    • RECORDING: Training mode: Record acceptable statements for the account. Incoming statements that do not immediately fail with a syntax error are recorded to become part of the account whitelist rules.

    • RESET: Clear the account whitelist and set the account mode to OFF.

    Switching the mode for an account to any mode but RECORDING synchronizes the firewall cache data to the underlying mysql system database tables for persistent storage. Switching the mode from OFF to RECORDING reloads the whitelist from the mysql.firewall_whitelist table into the cache.

    If an account has an empty whitelist, setting its mode to PROTECTING produces an error message that is returned in a result set, but not 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_firewall_flush_status()

    This UDF resets several firewall status variables to 0:

    Firewall_access_denied
    Firewall_access_granted
    Firewall_access_suspicious

    Example:

    SELECT mysql_firewall_flush_status();
  • normalize_statement(stmt)

    This UDF normalizes an SQL statement into the digest form used for whitelist rules.

    Example:

    SELECT normalize_statement('SELECT * FROM t1 WHERE c1 > 2');
  • read_firewall_users(user, mode)

    This aggregate UDF updates the firewall user cache through a SELECT statement on the mysql.firewall_users table.

    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 through a SELECT statement on the mysql.firewall_whitelist table.

    Example:

    SELECT read_firewall_whitelist('fwuser@localhost', 'RECORDING')
    FROM mysql.firewall_whitelist;
  • set_firewall_mode(user, mode)

    This UDF manages the user cache and establishes the user operational mode.

    Example:

    SELECT set_firewall_mode('fwuser@localhost', 'RECORDING');
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.6.2, “Installing or Uninstalling MySQL Enterprise Firewall”).

  • mysql_firewall_mode

    Property Value
    Command-Line Format --mysql-firewall-mode[={OFF|ON}]
    System Variable mysql_firewall_mode
    Scope Global
    Dynamic Yes
    Type Boolean
    Default Value ON

    Whether MySQL Enterprise Firewall is enabled (the default) or disabled.

  • mysql_firewall_trace

    Property Value
    Command-Line Format --mysql-firewall-trace[={OFF|ON}]
    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 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.6.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 Procedures and Functions).