Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.5Mb
PDF (A4) - 31.5Mb
PDF (RPM) - 30.6Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.7Mb
HTML Download (RPM) - 6.6Mb
Man Pages (TGZ) - 187.5Kb
Man Pages (Zip) - 302.1Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  MySQL Enterprise Firewall Reference

6.5.5.4 MySQL Enterprise Firewall Reference

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

6.5.5.4.1 MySQL Enterprise Firewall Tables

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

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

The INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS and mysql.firewall_users tables list registered firewall accounts and their operational modes. The tables have these 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 (as of MySQL 5.6.26), PROTECTING, RECORDING, and RESET. For details about their meanings, see the description of sp_set_firewall_mode() in Section 6.5.5.4.2, “MySQL Enterprise Firewall Procedures and Functions”.

The INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST and mysql.firewall_whitelist tables list registered firewall accounts and their whitelists. The tables have these 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.

6.5.5.4.2 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');
    
6.5.5.4.3 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.5.5.2, “Installing or Uninstalling MySQL Enterprise Firewall”).

  • mysql_firewall_max_query_size

    Introduced5.6.24
    Removed5.6.26
    Command-Line Format--mysql-firewall-max-query-size=size
    System VariableNamemysql_firewall_max_query_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default4096
    Min Value0
    Max Value4294967295

    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.

  • mysql_firewall_mode

    Introduced5.6.24
    Command-Line Format--mysql-firewall-mode={OFF|ON}
    System VariableNamemysql_firewall_mode
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

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

  • mysql_firewall_trace

    Introduced5.6.24
    Command-Line Format--mysql-firewall-trace={OFF|ON}
    System VariableNamemysql_firewall_trace
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    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.

6.5.5.4.4 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.5.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 Section 6.5.5.4.2, “MySQL Enterprise Firewall Procedures and Functions”).


User Comments
Sign Up Login You must be logged in to post a comment.