SQL firewall

Background information

SQL firewall is a software system designed specifically to protect database security. It defends against SQL injection attacks and other malicious activities targeting the database by monitoring and filtering SQL statements entering and leaving the database. SQL injection is a common network attack method; attackers insert malicious SQL code into query statements to trick the database into performing unauthorized operations, such as stealing data, modifying data, or destroying the database structure.

By formulating SQL firewall rules, ApsaraDB for OceanBase can monitor and intercept database SQL statements in real time, which significantly improves the security capabilities of the database and protects the database from such attacks.

Prerequisites

The database proxy version of the instance is V4.3.2 or later. For more information about the database proxy, see Overview of database proxy services.

Considerations

Pay attention to the following rules:

1. SQL firewall takes effect with hot loading, without the need to restart the database proxy service, and it takes effect in real time for all newly started transactions in all sessions. Please proceed with caution to avoid mistakenly affecting online business traffic.

2. When SQL firewall is enabled, it will affect database performance by about 10%.

Manage the SQL firewall

1. Log on to the ApsaraDB for OceanBase console.

2. In the left-side navigation pane, click Instances.

3. In the instance list, click the name of the target cluster instance to go to the Cluster Instance Workspace page.

4. In the left-side navigation pane, click Security Settings.

5. Click the SQL Firewall tab on the Security Settings page to view the list of existing SQL firewall rules.

6. You can manage the SQL firewall rules.

   • Create a SQL firewall rule

   • Enable a SQL firewall rule

   • Delete a SQL firewall rule

Create a SQL firewall rule

1. On the SQL Firewall tab, click Create Rule.

   

2. In the dialog box that appears, configure the following parameters:

   Parameter	Description

   Parameter	Description
   Rule Name	The name of the SQL firewall rule. It is 2 to 32 characters long and can only contain Chinese characters, numbers, English letters, underscores, and hyphens.
   Rule Description	The brief description of the rule for future reference.
   Tenant	The tenant for which the rule will take effect.
   Database Proxy	The database proxy information.
   Database Account	The account for which the rule will take effect. If it is left blank, it means that the rule will take effect for all database accounts.
   Rule Type	Block Rule
   Mode	Inspection Mode: Log SQL types, but do not actually block Protection Mode: Block SQL and log
   Firewall Rule	Block Specific Types of SQL: You can choose SELECT, UPDATE, INSERT, DELETE, CREATE, DROP, ALTER, TRUNCATE, RENAME, or REPLACE. Block SQL without WHERE Conditions: You can choose SELECT, UPDATE, or DELETE. Block Custom SQL: You can customize the SQL statements that need to be intercepted. For example, if you want to intercept SQL statements using ORDER BY or GROUP BY, enter the following into the text box: ORDER\s+BY|GROUP\s+BY
   Enable Rule	If the check box is selected, the rule will take effect immediately after being saved successfully. If the check box is not selected, the rule configuration will be retained and can be edited and enabled later.

3. Click OK.

Enable a SQL firewall rule

1. On the SQL Firewall tab, find the target rule and click Edit in the Actions column.

2. In the dialog box that appears, select the Enable Rule check box.

Edit a SQL firewall rule

1. On the SQL Firewall tab, find the target rule and click Edit in the Actions column.

   

2. In the dialog box that appears, reconfigure the following parameters:

   Parameter	Description

   Parameter	Description
   Rule Name	The name of the SQL firewall rule. It is 2 to 32 characters long and can only contain Chinese characters, numbers, English letters, underscores, and hyphens.
   Rule Description	The brief description of the rule for future reference.
   Tenant	The tenant for which the rule will take effect.
   Database Proxy	The database proxy information.
   Database Account	The account for which the rule will take effect. If it is left blank, it means that the rule will take effect for all database accounts.
   Rule Type	Block Rule
   Mode	Inspection Mode: Log SQL types, but do not actually block Protection Mode: Block SQL and log
   Firewall Rule	Block Specific Types of SQL: You can choose SELECT, UPDATE, INSERT, DELETE, CREATE, DROP, ALTER, TRUNCATE, RENAME, or REPLACE. Block SQL without WHERE Conditions: You can choose SELECT, UPDATE, or DELETE. Block Custom SQL: You can customize the SQL statements that need to be intercepted. For example, if you want to intercept SQL statements using ORDER BY or GROUP BY, enter the following into the text box: ORDER\s+BY|GROUP\s+BY
   Enable Rule	If the check box is selected, the rule will take effect immediately after being saved successfully. If the check box is not selected, the rule configuration will be retained and can be edited and enabled later.

3. Click OK.

Delete a SQL firewall rule

1. On the SQL Firewall tab, find the target rule and click Delete in the Actions column.

   

2. Confirm the deletion in the pop-up dialog box.

   

3. Click Delete.