Movatterモバイル変換

8.4.8.1 The MySQL Enterprise Firewall Plugin

• win_install_firewall.sql

• linux_install_firewall.sql

1. Start the server with the--loose-mysql-firewall-database=database-name option. Insert the name of the custom schema to be used as the firewall database.

   Prefixing the option name with--loose, causes the server to issue a warning rather than to terminate with an error due to there being (as yet) no such database.

2. Invoke the MySQL client program and create the custom schema on the server, like this:

   mysql> CREATE DATABASE IF NOT EXISTSdatabase-name;

3. Run the installation script, specifying by name the custom schema just created as the firewall database:

   $> mysql -u root -p -Ddatabase-name < linux_install_firewall.sqlEnter password:(enter root password here)

To use the MySQL Enterprise Firewall plugin in the context of source/replica replication, Group Replication, or InnoDB Cluster, you must prepare any replica nodes prior to running the installation script on the source node. This is necessary because theINSTALL PLUGIN statements in the script are not replicated.

• firewall_whitelist

• firewall_group_allowlist

• firewall_users

• firewall_groups

• firewall_membership

• Grant theFIREWALL_EXEMPT privilege to any account that should be exempt from firewall restrictions. This is useful, for example, for a database administrator who configures the firewall, to avoid the possibility of a misconfiguration causing even the administrator to be locked out and unable to execute statements.

• Grant theFIREWALL_ADMIN privilege to any account that should have full administrative firewall access. (Some administrative firewall functions can be invoked by accounts that haveFIREWALL_ADMINor the deprecatedSUPER privilege, as indicated in the individual function descriptions.)

• Grant theFIREWALL_USER privilege (deprecated) to any account that should have administrative access only for its own firewall rules.

  Note

  FIREWALL_USER is not supported by the MySQL Enterprise Firewall component.

• Grant theEXECUTE privilege for the stored procedures in the firewall database. These may invoke administrative functions, so stored procedure access also requires the privileges indicated earlier that are needed for those functions. The firewall database can be themysql system database or a custom schema (seeInstalling the MySQL Enterprise Firewall Plugin).

TheFIREWALL_EXEMPT,FIREWALL_ADMIN, andFIREWALL_USER privileges can be granted only while the firewall is installed because theMYSQL_FIREWALL plugin defines those privileges.

• Firewall Profiles

• Firewall Statement Matching

• Profile Operational Modes

• Firewall Statement Handling When Multiple Profiles Apply

• An allowlist. The allowlist is the set of rules that defines which statements are acceptable to the profile.

• A current operational mode. The mode enables the profile to be used in different ways. For example: the profile can be placed in training mode to establish the allowlist; the allowlist can be used for restricting statement execution or intrusion detection; the profile can be disabled entirely.

• A scope of applicability. The scope indicates which client connections the profile applies to:

  • The firewall supports account-based profiles such that each profile matches a particular client account (client user name and host name combination). For example, you can register one account profile for which the allowlist applies to connections originating fromadmin@localhost and another account profile for which the allowlist applies to connections originating frommyapp@apphost.example.com.

    Note

    Account-based profiles are deprecated as of MySQL 9.4.0, and are not supported by the MySQL Enterprise Firewall component. If you are using account profiles with the firewall plugin, you can migrate them to group profiles as described inMigrating Account Profiles to Group Profiles. This is also done when you runupgrade_firewall_to_component.sql (seeMySQL Enterprise Firewall Component Scripts) or migrate from the firewall plugin to the component using MySQL Configurator (seeSection 2.3.2.1, “MySQL Server Configuration with MySQL Configurator”).

  • The firewall supports group profiles that can have multiple accounts as members, with the profile allowlist applying equally to all members. Group profiles enable easier administration and greater flexibility for deployments that require applying a given set of allowlist rules to multiple accounts.

• Register one or more profiles with the firewall.

• Train the firewall by establishing the allowlist for each profile; that is, the types of statements the profile permits clients to execute.

• Place the trained profiles in protecting mode to harden MySQL against unauthorized statement execution:

  • MySQL associates each client session with a specific user name and host name combination. This combination is thesession account.

  • For each client connection, the firewall uses the session account to determine which profiles apply to handling incoming statements from the client.

    The firewall accepts only statements permitted by the applicable profile allowlists.

• An account profile allowlist applies only to a single account. A group profile allowlist applies when the session account matches any account that is a member of the group.

• To apply an allowlist to multiple accounts using account profiles, it is necessary to register one profile per account and duplicate the allowlist across each profile. This entails training each account profile individually because each one must be trained using the single account to which it applies.

  A group profile allowlist applies to multiple accounts, with no need to duplicate it for each account. A group profile can be trained using any or all of the group member accounts, or training can be limited to any single member. Either way, the allowlist applies to all members.

• Account profile names are based on specific user name and host name combinations that depend on which clients connect to the MySQL server. Group profile names are chosen by the firewall administrator with no constraints other than that their length must be from 1 to 288 characters.

Due to the advantages of group profiles over account profiles, and because a group profile with a single member account is logically equivalent to an account profile for that account, it is recommended that all new firewall profiles be created as group profiles. Account profiles are deprecated, and subject to removal in a future MySQL version. For assistance converting existing account profiles, seeMigrating Account Profiles to Group Profiles. The firewall component does not support account profiles.

• If an application has unique protection requirements, have it use an account not used for any other purpose, and set up a group profile or account profile for that account.

• If related applications share protection requirements, associate each application with its own account, then add these application accounts as members of the same group profile. Alternatively, have all of the applications use the same account and associate them with an account profile for that account.

Setting themax_digest_length system variable to0 disables digest production, which also disables server functionality that requires digests, such as MySQL Enterprise Firewall.

• OFF: This mode disables the profile. The firewall considers it inactive and ignores it.

• RECORDING: This is the firewall training mode. Incoming statements received from a client that matches the profile are considered acceptable for the profile and become part of its“fingerprint.” The firewall records the normalized digest form of each statement to learn the acceptable statement patterns for the profile. Each pattern is a rule, and the union of the rules is the profile allowlist.

  Group and account profiles differ in that statement recording for a group profile can be limited to statements received from a single group member (the training member).

• PROTECTING: In this mode, the profile allows or prevents statement execution. The firewall matches incoming statements against the profile allowlist, accepting only statements that match and rejecting those that do not. After training a profile inRECORDING mode, switch it toPROTECTING mode to harden MySQL against access by statements that deviate from the allowlist. If themysql_firewall_trace system variable is enabled, the firewall also writes rejected statements to the error log.

• DETECTING: This mode detects but not does not block intrusions (statements that are suspicious because they match nothing in the profile allowlist). InDETECTING mode, the firewall writes suspicious statements to the error log but accepts them without denying access.

Messages written to the error log inDETECTING mode or becausemysql_firewall_trace is enabled are written as Notes, which are information messages. To ensure that such messages appear in the error log and are not discarded, make sure that error-logging verbosity is sufficient to include information messages. For example, if you are using priority-based log filtering, as described inSection 7.4.2.5, “Priority-Based Error Log Filtering (log_filter_internal)”, set thelog_error_verbosity system variable to3.

• A group profile can include multiple accounts as members.

• An account can be a member of multiple group profiles.

• Multiple profiles can match a given client.

• The firewall ignores inactive profiles (profiles with a mode ofOFF).

• The session account matches every active group profile that includes a member having the same user and host. There can be more than one such group profile.

• The session account matches an active account profile having the same user and host, if there is one. There is at most one such account profile.

• If there is no applicable profile, the firewall imposes no restrictions and accepts the statement.

• If there are applicable profiles, their modes determine statement handling:

  • The firewall records the statement in the allowlist of each applicable profile that is inRECORDING mode.

  • The firewall writes the statement to the error log for each applicable profile inDETECTING mode for which the statement is suspicious (does not match the profile allowlist).

  • The firewall accepts the statement if at least one applicable profile is inRECORDING orDETECTING mode (those modes accept all statements), or if the statement matches the allowlist of at least one applicable profile inPROTECTING mode. Otherwise, the firewall rejects the statement (and writes it to the error log if themysql_firewall_trace system variable is enabled).

1. Register the group profile and put it inRECORDING mode.

2. Add a member account to the group profile.

3. Connect to the MySQL server using the member account and execute statements to be learned. This trains the group profile and establishes the rules that form the profile allowlist.

4. Add to the group profile any other accounts that are to be group members.

5. Switch the group profile toPROTECTING mode. When a client connects to the server using any account that is a member of the group profile, the profile allowlist restricts statement execution.

6. Should additional training be necessary, switch the group profile toRECORDING mode again, update its allowlist with new statement patterns, then switch it back toPROTECTING mode.

• Take note of the context in which account references occur. To name an account for firewall operations, specify it as a single quoted string ('user_name@host_name'). This differs from the usual MySQL convention for statements such asCREATE USER andGRANT, for which you quote the user and host parts of an account name separately ('user_name'@'host_name').

  The requirement for naming accounts as a single quoted string for firewall operations means that you cannot use accounts that have embedded@ characters in the user name.

• The firewall assesses statements against accounts represented by actual user and host names as authenticated by the server. When registering accounts in profiles, do not use wildcard characters or netmasks:

  • Suppose that an account namedme@%.example.org exists and a client uses it to connect to the server from the hostabc.example.org.

  • The account name contains a% wildcard character, but the server authenticates the client as having a user name ofme and host name ofabc.example.com, and that is what the firewall sees.

  • Consequently, the account name to use for firewall operations isme@abc.example.org rather thanme@%.example.org.

1. If necessary, create the accounts that are to be members of thefwgrp group profile and grant them appropriate access privileges. Statements for one member are shown here (choose an appropriate password):

   CREATE USER 'member1'@'localhost' IDENTIFIED BY 'password';GRANT ALL ON sakila.* TO 'member1'@'localhost';

2. Use thesp_set_firewall_group_mode() stored procedure to register the group profile with the firewall and place the profile inRECORDING (training) mode, as shown here:

   CALL mysql.sp_set_firewall_group_mode('fwgrp', 'RECORDING');

   Note

   If you have installed MySQL Enterprise Firewall in a custom schema, substitute its name formysql. For example, if the firewall is installed in thefwdb schema, execute the stored procedure like this:

   CALL fwdb.sp_set_firewall_group_mode('fwgrp', 'RECORDING');

3. Use thesp_firewall_group_enlist() stored procedure to add an initial member account for use in training the group profile allowlist:

   CALL mysql.sp_firewall_group_enlist('fwgrp', 'member1@localhost');

4. To train the group profile using the initial member account, connect to the server asmember1 from the server host so that the firewall sees a session account formember1@localhost. Then execute some statements to be considered legitimate for the profile. For example:

   SELECT title, release_year FROM film WHERE film_id = 1;UPDATE actor SET last_update = NOW() WHERE actor_id = 1;SELECT store_id, COUNT(*) FROM inventory GROUP BY store_id;

   The firewall receives the statements from themember1@localhost account. Because that account is a member of thefwgrp profile, which is inRECORDING mode, the firewall interprets the statements as applicable tofwgrp and records the normalized digest form of the statements as rules in thefwgrp allowlist. Those rules then apply to all accounts that are members offwgrp.

   Note

   Until thefwgrp group profile receives statements inRECORDING mode, its allowlist is empty, which is equivalent to“deny all.” No statement can match an empty allowlist, which has these implications:

   • The group profile cannot be switched toPROTECTING mode. It would reject every statement, effectively prohibiting the accounts that are group members from executing any statement.

   • The group profile can be switched toDETECTING mode. In this case, the profile accepts every statement but logs it as suspicious.

5. At this point, the group profile information is cached, including its name, membership, and allowlist. To see this information, query the Performance Schema firewall tables, like this:

   mysql> SELECT MODE FROM performance_schema.firewall_groups       WHERE NAME = 'fwgrp';+-----------+| MODE      |+-----------+| RECORDING |+-----------+mysql> SELECT * FROM performance_schema.firewall_membership       WHERE GROUP_ID = 'fwgrp' ORDER BY MEMBER_ID;+----------+-------------------+| GROUP_ID | MEMBER_ID         |+----------+-------------------+| fwgrp    | member1@localhost |+----------+-------------------+mysql> SELECT RULE FROM performance_schema.firewall_group_allowlist       WHERE NAME = 'fwgrp';+----------------------------------------------------------------------+| RULE                                                                 |+----------------------------------------------------------------------+| SELECT @@`version_comment` LIMIT ?                                   || UPDATE `actor` SET `last_update` = NOW ( ) WHERE `actor_id` = ?      || SELECT `title` , `release_year` FROM `film` WHERE `film_id` = ?      || SELECT `store_id` , COUNT ( * ) FROM `inventory` GROUP BY `store_id` |+----------------------------------------------------------------------+

   SeeSection 29.12.17, “Performance Schema Firewall Tables”, for more information about these tables.

   Note

   The@@version_comment rule comes from a statement sent automatically by themysql client when you connect to the server.

   Important

   Train the firewall under conditions matching application use. For example, to determine server characteristics and capabilities, a given MySQL connector might send statements to the server at the beginning of each session. If an application normally is used through that connector, train the firewall using the connector, too. That enables those initial statements to become part of the allowlist for the group profile associated with the application.

6. Invokesp_set_firewall_group_mode() again to switch the group profile toPROTECTING mode:

   CALL mysql.sp_set_firewall_group_mode('fwgrp', 'PROTECTING');

   Important

   Switching the group profile out ofRECORDING mode synchronizes its cached data to the firewall database tables that provide persistent underlying storage. If you do not switch the mode for a profile that is being recorded, the cached data is not written to persistent storage and is lost when the server is restarted. The firewall database can be themysql system database or a custom schema (seeInstalling the MySQL Enterprise Firewall Plugin).

7. Add to the group profile any other accounts that should be members:

   CALL mysql.sp_firewall_group_enlist('fwgrp', 'member2@localhost');CALL mysql.sp_firewall_group_enlist('fwgrp', 'member3@localhost');CALL mysql.sp_firewall_group_enlist('fwgrp', 'member4@localhost');

   The profile allowlist trained using themember1@localhost account now also applies to the additional accounts.

8. To verify the updated group membership, query thefirewall_membership table again:

   mysql> SELECT * FROM performance_schema.firewall_membership       WHERE GROUP_ID = 'fwgrp' ORDER BY MEMBER_ID;+----------+-------------------+| GROUP_ID | MEMBER_ID         |+----------+-------------------+| fwgrp    | member1@localhost || fwgrp    | member2@localhost || fwgrp    | member3@localhost || fwgrp    | member4@localhost |+----------+-------------------+

9. Test the group profile against the firewall by using any account in the group to execute some acceptable and unacceptable statements. The firewall matches each statement from the account against the profile allowlist and accepts or rejects it:

   • This statement is not identical to a training statement but produces the same normalized statement as one of them, so the firewall accepts it:

     mysql> SELECT title, release_year FROM film WHERE film_id = 98;+-------------------+--------------+| title             | release_year |+-------------------+--------------+| BRIGHT ENCOUNTERS |         2006 |+-------------------+--------------+

   • These statements match nothing in the allowlist, so the firewall rejects each with an error:

     mysql> SELECT title, release_year FROM film WHERE film_id = 98 OR TRUE;ERROR 1045 (28000): Statement was blocked by Firewallmysql> SHOW TABLES LIKE 'customer%';ERROR 1045 (28000): Statement was blocked by Firewallmysql> TRUNCATE TABLE mysql.slow_log;ERROR 1045 (28000): Statement was blocked by Firewall

   • If themysql_firewall_trace system variable is enabled, the firewall also writes rejected statements to the error log. For example:

     [Note] Plugin MYSQL_FIREWALL reported:'ACCESS DENIED for 'member1@localhost'. Reason: No match in allowlist.Statement: TRUNCATE TABLE `mysql` . `slow_log`'

     These log messages may be helpful in identifying the source of attacks, should that be necessary.

10. Should members need to be removed from the group profile, use the stored proceduresp_firewall_group_delist(), like this:

    CALL mysql.sp_firewall_group_delist('fwgrp', 'member3@localhost');

To avoid unexpected behavior when a particular account is specified as the training account for a group profile, always ensure that account is a member of the group.

1. Register the account profile and put it inRECORDING mode.

2. Connect to the MySQL server using the account and execute statements to be learned. This trains the account profile and establishes the rules that form the profile allowlist.

3. Switch the account profile toPROTECTING mode. When a client connects to the server using the account, the account profile allowlist restricts statement execution.

4. Should additional training be necessary, switch the account profile toRECORDING mode again, update its allowlist with new statement patterns, then switch it back toPROTECTING mode.

• Take note of the context in which account references occur. To name an account for firewall operations, specify it as a single quoted string ('user_name@host_name'). This differs from the usual MySQL convention for statements such asCREATE USER andGRANT, for which you quote the user and host parts of an account name separately ('user_name'@'host_name').

  The requirement for naming accounts as a single quoted string for firewall operations means that you cannot use accounts that have embedded@ characters in the user name.

• The firewall assesses statements against accounts represented by actual user and host names as authenticated by the server. When registering accounts in profiles, do not use wildcard characters or netmasks:

  • Suppose that an account namedme@%.example.org exists and a client uses it to connect to the server from the hostabc.example.org.

  • The account name contains a% wildcard character, but the server authenticates the client as having a user name ofme and host name ofabc.example.com, and that is what the firewall sees.

  • Consequently, the account name to use for firewall operations isme@abc.example.org rather thanme@%.example.org.

1. If necessary, create the account to use for executing statements (choose an appropriate password) and grant it privileges for thesakila database, like this:

   CREATE USER 'fwuser'@'localhost' IDENTIFIED BY 'password';GRANT ALL ON sakila.* TO 'fwuser'@'localhost';

2. Use thesp_set_firewall_mode() stored procedure to register the account profile with the firewall and place the profile inRECORDING (training) mode, as shown here:

   CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING');

   Note

   If you have installed the MySQL Enterprise Firewall using a custom schema, substitute its name formysql in the preceding statement. For example, if the firewall is installed in thefwdb schema, execute the stored procedure like this:

   CALL fwdb.sp_set_firewall_mode('fwuser@localhost', 'RECORDING');

3. To train the registered account profile, connect to the server asfwuser from the server host so that the firewall sees a session account forfwuser@localhost. Then use the account to execute some statements to be considered legitimate for the profile. For example:

   SELECT first_name, last_name FROM customer WHERE customer_id = 1;UPDATE rental SET return_date = NOW() WHERE rental_id = 1;SELECT get_customer_balance(1, NOW());

   Because the profile is inRECORDING mode, the firewall records the normalized digest form of the statements as rules in the profile allowlist.

   Note

   Until thefwuser@localhost account profile receives statements inRECORDING mode, its allowlist is empty, which is equivalent to“deny all.” No statement can match an empty allowlist, which has these implications:

   • The account profile cannot be switched toPROTECTING mode. It would reject every statement, effectively prohibiting the account from executing any statement.

   • The account profile can be switched toDETECTING mode. In this case, the profile accepts every statement but logs it as suspicious.

4. At this point, the account profile information is cached. To see this information, query theINFORMATION_SCHEMA firewall tables:

   mysql> SELECT MODE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS       WHERE USERHOST = 'fwuser@localhost';+-----------+| MODE      |+-----------+| RECORDING |+-----------+mysql> SELECT RULE FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST       WHERE USERHOST = 'fwuser@localhost';+----------------------------------------------------------------------------+| RULE                                                                       |+----------------------------------------------------------------------------+| SELECT `first_name` , `last_name` FROM `customer` WHERE `customer_id` = ?  || SELECT `get_customer_balance` ( ? , NOW ( ) )                              || UPDATE `rental` SET `return_date` = NOW ( ) WHERE `rental_id` = ?          || SELECT @@`version_comment` LIMIT ?                                         |+----------------------------------------------------------------------------+

   SeeSection 28.7, “INFORMATION_SCHEMA MySQL Enterprise Firewall Plugin Tables”, for more information about these tables.

   Note

   The@@version_comment rule comes from a statement sent automatically by themysql client when it connects to the server.

   Important

   Train the firewall under conditions matching application use. For example, to determine server characteristics and capabilities, a given MySQL connector might send statements to the server at the beginning of each session. If an application normally is used through that connector, train the firewall using the connector, too. That enables those initial statements to become part of the allowlist for the account profile associated with the application.

5. Invokesp_set_firewall_mode() again, this time switching the account profile toPROTECTING mode, as shown here:

   CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'PROTECTING');

   Important

   Switching the account profile out ofRECORDING mode synchronizes its cached data to the firewall database tables that provide persistent underlying storage. If you do not switch the mode for a profile that is being recorded, the cached data is not written to persistent storage and is lost when the server is restarted. The firewall database can be themysql system database or a custom schema (seeInstalling the MySQL Enterprise Firewall Plugin).

6. Test the account profile by using the account to execute some acceptable and unacceptable statements. The firewall matches each statement from the account against the profile allowlist and accepts or rejects it. This list provides some examples:

   • This statement is not identical to a training statement but produces the same normalized statement as one of them, so the firewall accepts it:

     mysql> SELECT first_name, last_name FROM customer WHERE customer_id = '48';+------------+-----------+| first_name | last_name |+------------+-----------+| ANN        | EVANS     |+------------+-----------+

   • These statements match nothing in the allowlist, so the firewall rejects each with an error:

     mysql> SELECT first_name, last_name FROM customer WHERE customer_id = 1 OR TRUE;ERROR 1045 (28000): Statement was blocked by Firewallmysql> SHOW TABLES LIKE 'customer%';ERROR 1045 (28000): Statement was blocked by Firewallmysql> TRUNCATE TABLE mysql.slow_log;ERROR 1045 (28000): Statement was blocked by Firewall

   • Ifmysql_firewall_trace is enabled, the firewall also writes rejected statements to the error log. For example:

     [Note] Plugin MYSQL_FIREWALL reported:'ACCESS DENIED for fwuser@localhost. Reason: No match in allowlist.Statement: TRUNCATE TABLE `mysql` . `slow_log`'

     Such log messages may be helpful in identifying the source of attacks, should that be necessary.

1. Run thefirewall_profile_migration.sql script to install thesp_migrate_firewall_user_to_group() stored procedure. The script is located in theshare directory of the MySQL installation.

   Specify the same firewall database name on the command line that you previously defined for your firewall installation. The example here specifies the system database,mysql.

   $> mysql -u root -p -D mysql < firewall_profile_migration.sqlEnter password:(enter root password here)

   If you installed the MySQL Enterprise Firewall plugin using a custom schema, make the appropriate substitution for your system.

2. Identify which account profiles exist by querying the Information SchemaMYSQL_FIREWALL_USERS table, like this:

   mysql> SELECT USERHOST FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS;+-------------------------------+| USERHOST                      |+-------------------------------+| admin@localhost               || local_client@localhost        || remote_client@abc.example.com |+-------------------------------+

3. For each account profile identified by the previous step, convert it to a group profile. Replace themysql prefix with the actual firewall database name, if necessary:

   CALL mysql.sp_migrate_firewall_user_to_group('admin@localhost', 'admins');CALL mysql.sp_migrate_firewall_user_to_group('local_client@localhost', 'local_clients');CALL mysql.sp_migrate_firewall_user_to_group('remote_client@localhost', 'remote_clients');

   In each case, the account profile must exist and must not currently be inRECORDING mode, and the group profile must not already exist. The resulting group profile has the named account as its single enlisted member, which is also set as the group training account. The group profile operational mode is taken from the account profile operational mode.

4. (Optional:) Removesp_migrate_firewall_user_to_group():

   DROP PROCEDURE IF EXISTS mysql.sp_migrate_firewall_user_to_group;

   If you installed the MySQL Enterprise Firewall plugin using a custom schema, use it sname in place ofmysql in the preceding statement.

• Firewall Group Profile Tables

• Firewall Account Profile Tables

• NAME

  The group profile name.

• MODE

  The current operational mode for the profile. Permitted mode values areOFF,DETECTING,PROTECTING, andRECORDING. For information about their meanings, seeFirewall Concepts.

• USERHOST

  The training account for the group profile, to be used when the profile is inRECORDING mode. The value isNULL, or an account in the formatuser_name@host_name:

  • If the value isNULL, the firewall records allowlist rules for statements received from any account that is a member of the group.

  • If the value is notNULL, the firewall records allowlist rules only for statements received from the named account (which should be a member of the group).

• 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

  A unique integer; the table's primary key.

• GROUP_ID

  The group profile name.

• MEMBER_ID

  The name of an account that is a member of the profile.

• USERHOST

  The account profile, in the formatuser_name@host_name.

• MODE

  The profile's current operational mode. Permitted mode values areOFF,DETECTING,PROTECTING,RECORDING, andRESET. For information about their meanings, seeFirewall Concepts.

• USERHOST

  The account profile name, using the formatuser_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

  Unique identifier (integer); this table's primary key.

If you have installed MySQL Enterprise Firewall in a custom schema, use its name in place ofmysql when invoking firewall plugin stored procedures. For example, if the firewall is installed in thefwdb schema, then execute the stored proceduresp_set_firewall_group_mode like this:

CALL fwdb.sp_set_firewall_group_mode(group,mode);

• Firewall Group Profile Stored Procedures

• Firewall Plugin Account Profile Stored Procedures

• Firewall Plugin Miscellaneous Stored Procedures

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

  Example:

  CALL mysql.sp_firewall_group_delist('g', 'fwuser@localhost');

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

  Example:

  CALL mysql.sp_firewall_group_enlist('g', 'fwuser@localhost');

• sp_reload_firewall_group_rules(group)

  This stored procedure provides control over firewall operation for individual group profiles. The procedure uses firewall administrative functions to reload the in-memory rules for a group profile from the rules stored in thefirewall-database.firewall_group_allowlist table.

  Arguments:

  • group: The name of the affected group profile.

  Example:

  CALL mysql.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 toOFF. If the profile mode was notOFF prior to thesp_reload_firewall_group_rules() call, usesp_set_firewall_group_mode() to restore its previous mode after reloading the rules. For example, if the profile was inPROTECTING mode, that is no longer true after callingsp_reload_firewall_group_rules() and you must set it toPROTECTING again explicitly.

• 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 administrative functions as necessary to transfer firewall data between the cache and persistent storage. This procedure may be called even if themysql_firewall_mode system variable isOFF, 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, callsp_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 areOFF,DETECTING,PROTECTING, andRECORDING. For details about their meanings, seeFirewall Concepts.

  Example:

  CALL mysql.sp_set_firewall_group_mode('myapp', 'PROTECTING');

• 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 tosp_set_firewall_group_mode(), but also specifies the training account to be used when the group is inRECORDING mode.

  Arguments:

  • group: The name of the affected group profile.

  • mode: The operational mode for the profile, as a string. Permitted mode values areOFF,DETECTING,PROTECTING, andRECORDING. For details about their meanings, seeFirewall Concepts.

  • user: The training account for the group profile, to be used when the profile is inRECORDING mode. The value isNULL, or a non-NULL account that has the formatuser_name@host_name:

    • If the value isNULL, 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 mysql.sp_set_firewall_group_mode_and_user('myapp', 'RECORDING', 'myapp_user1@localhost');

• sp_reload_firewall_rules(user)

  This stored procedure provides control over firewall operation for individual account profiles. The procedure uses firewall administrative functions to reload the in-memory rules for an account profile from the rules stored in thefirewall-database.firewall_whitelist table.

  Arguments:

  • user: The name of the affected account profile, as a string inuser_name@host_name format.

  Example:

  CALL 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 toOFF. If the profile mode was notOFF prior to thesp_reload_firewall_rules() call, usesp_set_firewall_mode() to restore its previous mode after reloading the rules. For example, if the profile was inPROTECTING mode, that is no longer true after callingsp_reload_firewall_rules() and you must set it toPROTECTING again explicitly.

  This procedure is deprecated, and subject to removal in a future MySQL version. SeeMigrating Account Profiles to Group Profiles.

• 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 administrative functions as necessary to transfer firewall data between the cache and persistent storage. This procedure may be called even if themysql_firewall_mode system variable isOFF, 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 inuser_name@host_name format.

  • mode: The operational mode for the profile, as a string. Permitted mode values areOFF,DETECTING,PROTECTING,RECORDING, andRESET. For details about their meanings, seeFirewall Concepts.

  Switching an account profile to any mode butRECORDING synchronizes its firewall cache data to the firewall database tables that provide persistent underlying storage (mysql or custom). Switching the mode fromOFF toRECORDING reloads the allowlist from thefirewall-database.firewall_whitelist table into the cache.

  If an account profile has an empty allowlist, its mode cannot be set toPROTECTING 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 sp_set_firewall_mode('a@b','PROTECTING');+----------------------------------------------------------------------+| set_firewall_mode(arg_userhost, arg_mode)                            |+----------------------------------------------------------------------+| ERROR: PROTECTING mode requested for a@b but the allowlist is empty. |+----------------------------------------------------------------------+

  This procedure is deprecated, and subject to removal in a future MySQL version. SeeMigrating Account Profiles to Group Profiles.

• sp_migrate_firewall_user_to_group(user,group)

  Thesp_migrate_firewall_user_to_group() stored procedure converts a firewall account profile to a group profile with the account as its single enlisted member. Run thefirewall_profile_migration.sql script to install it. The conversion procedure is discussed inMigrating Account Profiles to Group Profiles.

  This routine requires theFIREWALL_ADMIN privilege.

  Arguments:

  • user: The name of the account profile to convert to a group profile, as a string inuser_name@host_name format. The account profile must exist, and must not currently be inRECORDING mode.

  • group: The name of the new group profile, which must not already exist. The new group profile has the named account as its single enlisted member, and that member is set as the group training account. The group profile operational mode is taken from the account profile operational mode.

  Example:

  CALL sp_migrate_firewall_user_to_group('fwuser@localhost', 'mygroup);

• Firewall Group Profile Functions

• Firewall Plugin Account Profile Functions

• Firewall Plugin Miscellaneous Functions

• firewall_group_delist(group,user)

  This function removes an account from a group profile. It requires theFIREWALL_ADMIN privilege.

  Example:

  SELECT firewall_group_delist('g', 'fwuser@localhost');

• firewall_group_enlist(group,user)

  This function adds an account to a group profile. It requires theFIREWALL_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');

• read_firewall_group_allowlist(group,rule)

  This aggregate function updates the recorded-statement cache for the named group profile through aSELECT statement on thefirewall-database.firewall_group_allowlist table. It requires theFIREWALL_ADMIN privilege.

  Example:

  SELECT read_firewall_group_allowlist('my_fw_group', fgw.rule)FROM mysql.firewall_group_allowlist AS fgwWHERE NAME = 'my_fw_group';

• read_firewall_groups(group,mode,user)

  This aggregate function updates the firewall group profile cache through aSELECT statement on thefirewall-database.firewall_groups table. It requires theFIREWALL_ADMIN privilege.

  Example:

  SELECT read_firewall_groups('g', 'RECORDING', 'fwuser@localhost')FROM mysql.firewall_groups;

• set_firewall_group_mode(group,mode[,user])

  This function manages the group profile cache, establishes the profile operational mode, and optionally specifies the profile training account. It requires theFIREWALL_ADMIN privilege.

  If the optionaluser argument is not given, any previoususer setting for the profile remains unchanged. To change the setting, call the function with a third argument.

  If the optionaluser argument is given, it specifies the training account for the group profile, to be used when the profile is inRECORDING mode. The value isNULL, or an account name in the formatuser_name@host_name:

  • If the value isNULL, the firewall records allowlist rules for statements received from any account that is a member of the group.

  • If the value is notNULL, 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');

• read_firewall_users(user,mode)

  This aggregate function updates the firewall account profile cache using aSELECT statement on thefirewall-database.firewall_users table. It requires theFIREWALL_ADMIN privilege or the deprecatedSUPER privilege.

  Example:

  SELECT read_firewall_users('fwuser@localhost', 'RECORDING')FROM mysql.firewall_users;

  This function is deprecated, and subject to removal in a future MySQL version. SeeMigrating Account Profiles to Group Profiles.

• read_firewall_whitelist(user,rule)

  This aggregate function updates the recorded statement cache for the named account profile through aSELECT statement on thefirewall-database.firewall_whitelist table. It requires theFIREWALL_ADMIN privilege or the deprecatedSUPER privilege.

  Example:

  SELECT read_firewall_whitelist('fwuser@localhost', fw.rule)FROM mysql.firewall_whitelist AS fwWHERE USERHOST = 'fwuser@localhost';

  This function is deprecated, and subject to removal in a future MySQL version. SeeMigrating Account Profiles to Group Profiles.

• set_firewall_mode(user,mode)

  This function manages the account profile cache and establishes the profile operational mode. It requires theFIREWALL_ADMIN privilege or the deprecatedSUPER privilege.

  Example:

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

  This function is deprecated, and subject to removal in a future MySQL version. SeeMigrating Account Profiles to Group Profiles.

• mysql_firewall_flush_status()

  This function resets the following firewall plugin status variables to0:

  • Firewall_access_denied

  • Firewall_access_granted

  • Firewall_access_suspicious

  This function requires theFIREWALL_ADMIN privilege or the deprecatedSUPER privilege.

  Example:

  SELECT mysql_firewall_flush_status();

• normalize_statement(stmt)

  This function normalizes an SQL statement into the digest form used for allowlist rules. It requires theFIREWALL_ADMIN privilege or the deprecatedSUPER privilege.

  Example:

  SELECT normalize_statement('SELECT * FROM t1 WHERE c1 > 2');

  Note

  The same digest functionality is available from theSTATEMENT_DIGEST_TEXT() SQL function.

• mysql_firewall_database

  Command-Line Format	--mysql-firewall-database[=value]
  Deprecated	Yes
  System Variable	mysql_firewall_database
  Scope	Global
  Dynamic	No
  SET_VAR Hint Applies	No
  Type	String
  Default Value	mysql

  Specifies the database from which the MySQL Enterprise Firewall plugin reads data. Typically, theMYSQL_FIREWALL server-side plugin stores its internal data (tables, stored procedures, and functions) in themysql system database, but you can create and use a custom schema instead (seeInstalling the MySQL Enterprise Firewall Plugin). This variable permits specifying an alternative database name at startup.

• mysql_firewall_mode

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

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

• mysql_firewall_reload_interval_seconds

  Command-Line Format	--mysql-firewall-reload-interval-seconds[=value]
  Deprecated	Yes
  System Variable	mysql_firewall_reload_interval_seconds
  Scope	Global
  Dynamic	No
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	0
  Minimum Value	60 (unless 0: OFF)
  Maximum Value	INT_MAX
  Unit	seconds

  Specifies the interval (in seconds) that the server-side plugin uses to reload its internal cache from firewall tables. Whenmysql_firewall_reload_interval_seconds has a value of zero (the default), no periodic reloading of data from tables occurs at runtime. Values between0 and60 (1 to 59) are not acknowledged by the plugin. Instead, these values adjust to60 automatically.

  This variable requires that thescheduler component be enabled (ON). For more information, seeScheduling Firewall Cache Reloads.

• mysql_firewall_trace

  Command-Line Format	--mysql-firewall-trace[={OFF|ON}]
  Deprecated	Yes
  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). Whenmysql_firewall_trace is enabled, forPROTECTING mode, the firewall writes rejected statements to the error log.

• Firewall_access_denied

  The number of statements rejected by the MySQL Enterprise Firewall plugin.

  Deprecated in MySQL 9.4.0.

• Firewall_access_granted

  The number of statements accepted by the MySQL Enterprise Firewall plugin.

  Deprecated in MySQL 9.4.0.

• Firewall_access_suspicious

  The number of statements logged by the MySQL Enterprise Firewall plugin as suspicious for users who are inDETECTING mode.

  Deprecated in MySQL 9.4.0.

• Firewall_cached_entries

  The number of statements recorded by the MySQL Enterprise Firewall plugin, including duplicates.

  Deprecated in MySQL 9.4.0.