Movatterモバイル変換

8.4.8.2 The MySQL Enterprise Firewall Component

Before runninguninstall_component_firewall.sql, you must insure that there are no other connections to the server. UseSHOW PROCESSLIST or query the Performance Schemaprocesslist table to help you determine that this is the case.

• Migrates any plugin account profiles to group profiles with single users.

• Drops the plugin's stored procedures.

• Uninstalls all firewall plugins.

• Drops all tables not used by the component.

• Alters those tables remaining after the others are dropped to conform with the table definitions accepted by the firewall component.

• Translates plugin system variables to those used by the component and persists them. For example, the value ofmysql_firewall_database is copied tocomponent_firewall.database.

• Installs the firewall component.

• Creates the stored procedures used by the component.

If the firewall plugin was loaded using--plugin-load-add, you must remove it from the list of plugins specified for that option prior to runningfirewall_plugin_to_component.sql.

• Preparation for the plugin and uninstallation of the firewall component

  This is performed by executing the SQL scriptfirewall_component_to_plugin.sql in amysql client session.

• Installation of the firewall plugin

  This is accomplished by runninglinux_install_firewall.sql orwin_install_firewall.sql (also in themysql client), depending on the platform.

• firewall_group_allowlist

• 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 theEXECUTE privilege for the stored procedures in the firewall database (seeMySQL Enterprise Firewall Component Stored Procedures). These may invoke administrative functions, so stored procedure access also requires the privileges indicated earlier that are needed for those functions.

TheFIREWALL_EXEMPT andFIREWALL_ADMIN privileges can be granted only while the firewall is installed because those privileges are defined bycomponent_firewall.

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

• The firewall supports group profiles which can have multiple accounts as members, with the profile allowlist applying equally to all members.

• 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 inPROTECTING mode to harden MySQL against unauthorized statement execution. It does this first by associating each client session with a specific user name and host name combination, known as thesession account. Then, for each client connection, the firewall uses the session account to determine which profile or profiles apply to incoming statements from this client, accepting only those statements which are permitted by the applicable profile allowlists.

The firewall component does not support account profiles. For assistance with converting existing account profiles prior to upgrading from the firewall plugin, seeMigrating Account Profiles to Group 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 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.

Settingmax_digest_length to0 disables digest production, which also disables server functionality that requires digests, such as MySQL Enterprise Firewall.

• OFF: Disables the profile. The firewall considers the profile inactive and ignores it.

• RECORDING: 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; the profile allowlist consists of the union of all such rules.

• PROTECTING: 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 thecomponent_firewall.trace system variable is enabled, the firewall also writes any rejected statements to the error log.

• DETECTING: 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)”, setlog_error_verbosity 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 profile and put it inRECORDING mode.

2. Add a member account to the profile.

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

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

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

6. Should additional training be necessary, switch the 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 any account whose user name contains the@ character.

• 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. The reasons for this are described here:

  • 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 the user nameme and host nameabc.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 the stored procedureset_firewall_group_mode() to register the group profile with the firewall and place the profile inRECORDING (training) mode, as shown here:

   CALLfirewall-database.set_firewall_group_mode('fwgrp', 'RECORDING');

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

   CALLfirewall-database.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” and means that no statement can match. This has the following implications:

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

   • 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 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 group profile associated with the application.

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

   CALLfirewall-database.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.

7. Add any other accounts that should be members of this group profile, like this:

   CALLfirewall-database.firewall_group_enlist('fwgrp', 'member2@localhost');CALLfirewall-database.firewall_group_enlist('fwgrp', 'member3@localhost');CALLfirewall-database.firewall_group_enlist('fwgrp', 'member4@localhost');

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

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 this account against the profile allowlist and accepts or rejects it based on the result, as described here:

   • This statement is not identical to any of the training statements 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 one 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

   • Ifcomponent_firewall.trace is enabled, the firewall also writes rejected statements to the error 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 procedurefirewall_group_delist(), like this:

    CALLfirewall-database.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.

• NAME

  The group profile name.

• MODE

  The current operational mode for the profile. Permitted mode values areOFF,DETECTING,PROTECTING, andRECORDING. For details 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 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).

• 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

  An integer column that is a primary key for the table.

• GROUP_ID

  The group profile name.

• MEMBER_ID

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

• 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_firewall_group_remove(name)

  Removes the the group having the supplied name.

• sp_firewall_group_rename(oldname,newname)

  Renames the group namedoldname tonewname.

• 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');

• 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');

• mysql_firewall_flush_status()

  This function resets several firewall status variables to 0:

  • 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();

• 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 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:

  SELECT set_firewall_group_mode('g', 'DETECTING');

• component_firewall.database

  System Variable	component_firewall.database
  Scope	Global
  Dynamic	No
  SET_VAR Hint Applies	No
  Type	String
  Default Value	mysql

  The name of the database used for MySQL Enterprise Firewall component tables. For more information about these tables, seeMySQL Enterprise Firewall Component Tables.

• component_firewall.enabled

  System Variable	component_firewall.enabled
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Boolean
  Default Value	ON

  Whether the MySQL Enterprise Firewall component is enabled.

• component_firewall.reload_interval_seconds

  System Variable	component_firewall.reload_interval_seconds
  Scope	Global
  Dynamic	No
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	0
  Minimum Value	60 (0 = no reload)
  Maximum Value	INT_MAX
  Unit	seconds

  Time in seconds between reloads of the MySQL Enterprise Firewall internal cache. Set to 0 to disable. Values from 1 to 59 inclusive are rounded up to 60.

• component_firewall.trace

  System Variable	component_firewall.trace
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Boolean
  Default Value	OFF

  Whether the firewall component trace is enabled or disabled (the default). When the 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 component.

• firewall_access_granted

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

• firewall_access_suspicious

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

• firewall_cached_entries

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

• install_component_firewall.sql

  This script installs all elements of the MySQL Enterprise Firewall component, performing the steps listed here:

  1. Checks whether the firewall plugin is installed; if so, stops with an error.

  2. Creates the component tables (seeMySQL Enterprise Firewall Component Tables).

  3. Installs the component.

  4. Creates the component's stored procedures (seeMySQL Enterprise Firewall Component Stored Procedures.

  SeeInstalling the MySQL Enterprise Firewall Component, for usage instructions.

• firewall_plugin_to_component.sql

  This script upgrades an existing firewall plugin installation to an installation of the firewall component. It performs the steps listed here:

  1. Runsfirewall_profile_migration.sql (provided by the firewall plugin) to migrate account profiles to group profiles. (The firewall component does not support account profiles.)

  2. Uninstalls the firewall plugin usinguninstall_firewall.sql (also provided by the firewall plugin).

  3. Drops the plugin's stored procedures and functions.

  4. Drops thefirewall_whitelist andfirewall_users tables.

  5. Installs the firewall component usinginstall_component_firewall.sql, skipping the check for the plugin.

  Note

  If the firewall plugin was loaded using--plugin-load-add, you must remove it from that option prior to running the script.

  SeeUpgrading to the MySQL Enterprise Firewall Component, for additional information and instructions.

• firewall_component_to_plugin.sql

  This script can be used to perform a downgrade from the MySQL Enterprise Firewall component to the firewall plugin.firewall_component_to_plugin.sql performs the following actions:

  1. Uninstalls the firewall component usinguninstall_component_firewall.sql.

  2. Drops the component's stored procedures and functions.

  3. Creates thefirewall_whitelist andfirewall_users tables.

  4. Creates the plugin's stored procedures and functions.

  SeeDowngrading the MySQL Enterprise Firewall Component, for usage and other information.

• uninstall_component_firewall.sql

  Run this script to remove an installation of the MySQL Enterprise Firewall component. The script performs the steps listed here:

  1. Uninstalls the firewall component tables.

  2. Drops the component's stored procedures and functions.

  3. Uninstalls the firewall component.

  For usage information, seeUninstalling the MySQL Enterprise Firewall Component.