Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb
HTML Download (TGZ) - 231.0Kb
HTML Download (Zip) - 238.3Kb

Security in MySQL  /  ...  /  MySQL Enterprise Firewall Procedures and Functions 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.


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

    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:



    SELECT mysql_firewall_flush_status();
  • normalize_statement(stmt)

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


    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.


    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.


    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.


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