Rechercher dans le manuel MySQL

6.4.7.4 MySQL Enterprise Firewall Reference

Contents

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

  • ID

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

Contents Haut

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:

  1. 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:

    1. 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:

    1. mysql> CALL mysql.sp_set_firewall_mode('a@b','PROTECTING');
    2. +----------------------------------------------------------------------+
    3. | set_firewall_mode(arg_userhost, arg_mode)                            |
    4. +----------------------------------------------------------------------+
    5. | ERROR: PROTECTING mode requested for a@b but the whitelist is empty. |
    6. +----------------------------------------------------------------------+
    7. 1 row in set (0.02 sec)
    8.  
    9. 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:

    1. SELECT mysql_firewall_flush_status();

  • normalize_statement(stmt)

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

    Example:

    1. 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:

    1. SELECT read_firewall_users('fwuser@localhost', 'RECORDING')
    2. 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:

    1. SELECT read_firewall_whitelist('fwuser@localhost', 'RECORDING')
    2. FROM mysql.firewall_whitelist;

  • set_firewall_mode(user, mode)

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

    Example:

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

Contents Haut

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

    Property Value
    Command-Line Format --mysql-firewall-mode[={OFF|ON}]
    Introduced 8.0.11
    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

    Property Value
    Command-Line Format --mysql-firewall-trace[={OFF|ON}]
    Introduced 8.0.11
    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.

Contents Haut

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 Procedures and Functions).


Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-firewall-reference.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut