原地址:http://dev.mysql.com/doc/mysql-security-excerpt/5.6/en/firewall-usage.html
Before using MySQL Enterprise Firewall, install it according to the instructions at Section 6.4.2, “Installing or Uninstalling MySQL Enterprise Firewall”. Also, MySQL Enterprise Firewall does not work together with the query cache; disable the query cache if it is enabled (see Query Cache Configuration).
This section describes how to configure MySQL Enterprise Firewall using SQL statements. Alternatively, MySQL Workbench 6.3.4 or higher provides a graphical interface for firewall control. See MySQL Enterprise Firewall Interface.
To enable or disable the firewall, set the mysql_firewall_mode
system variable. By default, this variable is enabled when the firewall is installed. To control the initial firewall state explicitly, you can set the variable at server startup. For example, to enable the firewall in an option file, use these lines:
[mysqld] mysql_firewall_mode=ON
It is also possible to disable or enable the firewall at runtime:
mysql>SET GLOBAL mysql_firewall_mode = OFF;
mysql>SET GLOBAL mysql_firewall_mode = ON;
In addition to the global on/off firewall mode, each account registered with the firewall has its own operational mode. For an account in recording mode, the firewall learns an application's “fingerprint,” that is, the acceptable statement patterns that, taken together, form a whitelist. After training, switch the firewall to protecting mode to harden MySQL against access by statements that deviate from the fingerprint. For additional training, switch the firewall back to recording mode as necessary to update the whitelist with new statement patterns. As of MySQL 5.6.26, an intrusion-detection mode is available that writes suspicious statements to the error log but does not deny access.
The firewall maintains whitelist rules on a per-account basis, enabling implementation of protection strategies such as these:
-
For an application that has unique protection requirements, configure it to use an account that is not used for any other purpose.
-
For applications that are related and share protection requirements, configure them as a group to use the same account.
Firewall operation is based on conversion of SQL statements to normalized digest form. Firewall digests are like the statement digests used by the Performance Schema (see Performance Schema Statement Digests). However, unlike the Performance Schema, the relevant digest-related system variables are max_digest_length
and mysql_firewall_max_query_size
.
For a connection from a registered account, the firewall converts each incoming statement to normalized form and processes it according to the account mode:
-
In recording mode, the firewall adds the normalized statement to the account whitelist rules.
-
In protecting mode, the firewall compares the normalized statement to the account whitelist rules. If there is a match, the statement passes and the server continues to process it. Otherwise, the server rejects the statement and returns an error to the client. As of MySQL 5.6.25, the firewall also writes the rejected statement to the error log if the
mysql_firewall_trace
system variable is enabled. -
In detecting mode, the firewall matches statements as in protecting mode, but writes nonmatching statements to the error log without denying access.
Accounts that have a mode of OFF
or are not registered with the firewall are ignored by it.
Before MySQL 5.6.25, MySQL Enterprise Firewall records prepared statements as they are received by the server, not as normalized digests. Thus, spaces, tabs, and lettercase are signficant for comparison of whitelist rules against incoming statements.
To protect an account using MySQL Enterprise Firewall, follow these steps:
-
Register the account and put it in recording mode.
-
Connect to the MySQL server using the registered account and execute statements to be learned. This establishes the account's whitelist of accepted statements.
-
Switch the registered account to protecting mode.
The following example shows how to register an account with the firewall, use the firewall to learn acceptable statements for that account, and protect the account against execution of unacceptable statements. The example account, 'fwuser'@'localhost'
, is for use by an application that accesses tables in the sakila
database. (This database is available at http://dev.mysql.com/doc/index-other.html.)
The user and host parts of the account name are quoted separately for statements such as CREATE USER
andGRANT
, whereas to specify an account for use with a firewall component, name it as a single quoted string'fwuser@localhost'
.
The convention for naming accounts as a single quoted string for firewall components means that you cannot use accounts that have embedded @
characters in the user name.
Perform the steps in the following procedure using an administrative MySQL account, except those designated for execution by the account registered with the firewall. The default database should be sakila
for statements executed using the registered account.
-
If necessary, create the account to be protected (choose an appropriate password) and grant it privileges for the
sakila
database:mysql>
CREATE USER 'fwuser'@'localhost' IDENTIFIED BY 'fWp@3sw0rd';
mysql>GRANT ALL ON sakila.* TO 'fwuser'@'localhost';
-
Use the
sp_set_firewall_mode()
stored procedure to register the account with the firewall and place it in recording mode (if the procedure is located in a database other thanmysql
, adjust the statement accordingly):mysql>
CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'RECORDING');
During the course of its execution, the stored procedure invokes firewall user-defined functions, which may produce output of their own.
-
Using the registered account, connect to the server, then execute some statements that are legitimate for it:
mysql>
SELECT first_name, last_name FROM customer WHERE customer_id = 1;
mysql>UPDATE rental SET return_date = NOW() WHERE rental_id = 1;
mysql>SELECT get_customer_balance(1, NOW());
The firewall converts the statements to digest form and records them in the account whitelist.
NoteUntil the account executes statements in recording mode, its whitelist is empty, which is equivalent to “deny all.” If switched to protecting mode, the account will be effectively prohibited from executing statements.
-
At this point, the user and whitelist information is cached and can be seen in the firewall
INFORMATION_SCHEMA
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 ? | +----------------------------------------------------------------------------+NoteThe
@@version_comment
rule comes from a statement sent automatically by the mysql client when you connect to the server as the registered user.It is important to train the firewall under conditions matching application use. For example, a given MySQL connector might send statements to the server at the beginning of a connection to determine server characteristics and capabilities. If an application normally is used through that connector, train the firewall that way, too. That enables those initial statements to become part of the whitelist for the account associated with the application.
-
Use the stored procedure to switch the registered user to protecting mode:
mysql>
CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'PROTECTING');
ImportantSwitching the account out of
RECORDING
mode synchronizes its firewall cache data to the underlyingmysql
system database tables for persistent storage. If you do not switch the mode for a user who is being recorded, the cached whitelist data is not written to the system tables and will be lost when the server is restarted. -
Using the registered account, execute some acceptable and unacceptable statements. The firewall matches each one against the account whitelist 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 first_name, last_name FROM customer WHERE customer_id = '48';
+------------+-----------+ | first_name | last_name | +------------+-----------+ | ANN | EVANS | +------------+-----------+These statements do not match anything in the whitelist and each results in an error:
mysql>
SELECT first_name, last_name FROM customer WHERE customer_id = 1 OR TRUE;
ERROR 1045 (28000): Statement was blocked by Firewall mysql>SHOW TABLES LIKE 'customer%';
ERROR 1045 (28000): Statement was blocked by Firewall mysql>TRUNCATE TABLE mysql.slow_log;
ERROR 1045 (28000): Statement was blocked by FirewallAs of MySQL 5.6.25, the firewall also writes the rejected statements to the error log if the
mysql_firewall_trace
system variable is enabled. For example:[Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for fwuser@localhost. Reason: No match in whitelist. Statement: TRUNCATE TABLE `mysql` . `slow_log` '
You can use these log messages in your efforts to identify the source of attacks.
-
As of MySQL 5.6.26, you can log nonmatching statements as suspicious without denying access. To do this, put the account in intrusion-detecting mode:
mysql>
CALL mysql.sp_set_firewall_mode('fwuser@localhost', 'DETECTING');
-
Using the registered account, connect to the server, then execute some statement does not match the whitelist:
mysql>
SHOW TABLES LIKE 'customer%';
+------------------------------+ | Tables_in_sakila (customer%) | +------------------------------+ | customer | | customer_list | +------------------------------+In detecting mode, the firewall permits the nonmatching statement to execute but writes a message to the error log:
[Note] Plugin MYSQL_FIREWALL reported: 'SUSPICIOUS STATEMENT from 'fwuser@localhost'. Reason: No match in whitelist. Statement: SHOW TABLES LIKE ? '
-
To assess firewall activity, examine its status variables:
mysql>
SHOW GLOBAL STATUS LIKE 'Firewall%';
+----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Firewall_access_denied | 3 | | Firewall_access_granted | 4 | | Firewall_access_suspicious | 1 | | Firewall_cached_entries | 4 | +----------------------------+-------+The variables indicate the number of statements rejected, accepted, logged as suspicious, and added to the cache, respectively. The
Firewall_access_granted
count is 4 because of the@@version_comment
statement sent by the mysql client each of the three time you used it to connect as the registered user, plus theSHOW TABLES
statement that was not blocked inDETECTING
mode.
Should additional training for an account be necessary, switch it to recording mode again, then back to protecting mode after executing statements to be added to the whitelist.