Configure blacklist rules - PolarDB for MySQL - Alibaba Cloud Documentation Center

Blacklist rules

PolarProxy allows you to configure blacklist rules to block specified types of SQL statements or specific SQL statements.

You can configure blacklist rules in the following ways:

Fixed rule mode: You can configure common blacklist rules in the console. Each rule can be effective for an account or a cluster. For more information about common rules, see Table 1.
Custom parameterized SQL mode: You can parameterize all variables in SQL statements that you execute in a database, generate a parameterized template, and record the template in the database. PolarProxy blocks SQL statements that meet the parameterized template.
Custom SQL mode: You can specify SQL statements to be blocked without parameterizing their variables. SQL statements that use other parameters are not blocked.

Add a blacklist rule

Log on to the PolarDB console.
In the upper-left corner of the console, select the region where the cluster that you want to manage is deployed.
Find the cluster you want to manage and click the cluster ID.
In the left-side navigation pane, choose Settings and Management > Security Management.
On the SQL Firewall tab, click Add in the upper-left corner.

In the Create a Rule dialog box, set the parameters based on the mode that you select.

Fixed rule mode.

If you select the fixed rule mode, set the following parameters.

Table 1. Parameters for a blacklist rule
Parameter		Required	Description
Basic Information	Rule Name	Yes	The name of the rule. The name must meet the following requirements: The name can contain digits and letters. The name can be up to 30 characters in length.
	Description	No	The description of the rule. Note The description can be up to 64 characters in length.
	Endpoint	Yes	The endpoint to which the current rule is applied.
Configurations	Rule Type	Yes	The type of the rule. Select Blacklist Rule.
	Current Mode	No	The mode of the rule. Set the value to Protection Mode. PolarProxy blocks SQL statements that meet the blacklist rule.
	Database Account Name	No	The name of the database account to which the rule is applied. Valid values: All Accounts: indicates that the rule applies to all database accounts in the cluster. The text box on the right must be left empty. Include: indicates that the rule applies only to specified database accounts. You must specify at least one database account name in the text box on the right. Separate multiple accounts with commas (,). Exclude: indicates that the rule applies only to database accounts that are not specified here. You must specify at least one database account name in the text box on the right. Separate multiple accounts with commas (,). Note The database account name can be in one of the following formats: `Username`. Example: `user`. `Username@IP address`. Example: `user@10.0.0.0`.
	Block SQLs With Asterisks (*)	No	Specifies whether to block SQL statements that contain asterisk signs (``). Valid values: Enable: blocks SQL statements that contain asterisk signs (``). Disable: does not block SQL statements that contain asterisk signs (`*`).
	Block SQLs of Specific Types	No	Specifies whether to block SQL statements of specific types. Valid values: Enable: blocks SQL statements of specific types. If you select Enable, select at least one type. The following types are supported: `CREATE` `DROP` `ALTER` `TRUNCATE` `RENAME` `INSERT` `UPDATE` `SELECT` `DELETE` Disable: does not block SQL statements of specific types.
	Block SQLs Without WHERE	No	Specifies whether to block SQL statements that do not contain WHERE clauses. Valid values: Enable: blocks SQL statements of specific types that do not contain WHERE clauses. If you select Enable, select at least one type. The following types are supported: `UPDATE` `SELECT` `DELETE` Disable: does not block SQL statements of specific types that do not contain WHERE clauses. Note This parameter is valid only for the `SELECT`, `UPDATE`, and `DELETE` statements that contain at least one table name. The `SELECT 1;` statement is not blocked by PolarProxy.
	Block SQLs With Specific Columns	No	Specifies whether to block SQL statements that contain specific column names. Valid values: Enable: blocks SQL statements that contain specific column names. If you select Enable, the following options are supported: All: indicates that the rule applies to all column names in the cluster. The text box on the right must be left empty. Include: indicates that the rule applies only to specified column names. You must specify at least one database column name in the text box on the right. Separate multiple column names with commas (,). Exclude: indicates that the rule applies only to column names that are not specified here. You must specify at least one column name in the text box on the right. Separate multiple column names with commas (,). Disable: does not block SQL statements that contain specific column names.
	Block SQLs With Specific Functions	No	Specifies whether to block SQL statements that contain specific functions. Valid values: Enable: blocks SQL statements that contain specific functions. If you select Enable, the following options are supported: All: indicates that the rule applies to all functions in the cluster. The text box on the right must be left empty. Include: indicates that the rule applies only to specified functions. You must specify at least one function in the text box on the right. Separate multiple functions with commas (,). Exclude: indicates that the rule applies only to functions that are not specified here. You must specify at least one function in the text box on the right. Separate multiple functions with commas (,). Disable: does not block SQL statements that contain specific functions.
	Block SQLs With Specific Columns and Specific Functions	No	Specifies whether to block SQL statements that contain specific functions and specific column names. Valid values: Enable: blocks SQL statements that contain specific functions and specific column names. If you select Enable, you must specify at least one function and one column name in the text box on the right. If you set Function Name to Include and Column Name to Include, the rule is effective for SQL statements that contain specific functions and specific column names. If you set Function Name to Include and Column Name to Exclude, the rule is effective for SQL statements that contain specific functions and that do not contain specific column names. If you set Function Name to Exclude and Column Name to Include, the rule is effective for SQL statements that do not contain specific functions and that contain specific column names. If you set Function Name to Exclude and Column Name to Exclude, the rule is effective for SQL statements that do not contain specific functions or specific column names. Disable: does not block SQL statements that contain specific functions and specific column names.

Click OK.

Custom parameterized SQL mode
1. If you select custom parameterized SQL mode, set the required parameters. For more information about the parameters, see Table 1.
  
  Note When you select custom parameterized SQL mode, you can disable all blacklist rules in the Configurations section.
2. Click OK.
3. Connect to the specified database endpoint by using the previously defined database account name. You can specify a SQL statement to be blocked by adding the following hint command before the SQL statement: hint(/* store_to_blacklist */). For example, to block the select id from sqlblack_test where id = 1; statement, run the following command:
```
/* store_to_blacklist */ select id from sqlblack_test where id = 1;
```
  The parameterized template:
```
select id from sqlblack_test where id = ? 
```
  ? indicates any value.
  Wait for five seconds. When the account executes a SQL statement that meets the preceding parameterized template on the specified cluster, PolarProxy blocks the statement. The following information is displayed after a SQL statement is blocked:
```
ERROR 1141 (HY000): This SQL is rejected by SQL Firewall. Access denied for user 'xxx'@'x.x.x.x' to database 'xzh': This SQL is in blacklist bl_test.
```
  bl_test is the name of the blacklist rule table.
Note
- If you use the MySQL command line, you must add the -c option. Otherwise, the hint command does not take effect.
- The parameterized SQL statement takes effect after five seconds.
Custom SQL mode
1. If you select custom SQL mode, set the required parameters. For more information about the parameters, see Table 1.
  
  Note When you select custom SQL mode, you can disable all options in the Configurations section.
2. Click OK.
3. Connect to the specified database endpoint by using the previously defined database account name. You can specify a SQL statement to be blocked by adding the following hint command before the SQL statement: hint(/* orginal_store_to_blacklist */). For example, to block the update t set k = 2 where id = 2; statement, run the following command:
```
/* orginal_store_to_blacklist */ update t set k = 2 where id = 2;
```
  Wait for five seconds. When the account executes the update t set k = 2 where id = 2; statement on the specified cluster, PolarProxy blocks the statement. However, SQL statements that use other parameters are not blocked. The following information is displayed after a SQL statement is blocked:
```
ERROR 1141 (HY000): This SQL is rejected by SQL Firewall. Access denied for user 'xxx'@'x.x.x.x' to database 'xzh': This SQL is in blacklist bl_test.
```
  bl_test is the name of the blacklist rule table.
Note
- If you use the MySQL command line, you must add the -c option. Otherwise, the hint command does not take effect.
- The parameterized SQL statement takes effect after five seconds.

Enable or disable a blacklist rule

Log on to the PolarDB console.
In the upper-left corner of the console, select the region where the cluster that you want to manage is deployed.
Find the cluster you want to manage and click the cluster ID.
In the left-side navigation pane, choose Settings and Management > Security Management.
On the SQL Firewall tab, find the rule and turn on or off Enable/Disable.

Note You can select multiple rules in the rule table and then click Enable or Disable to batch enable or disable the rules.
In the Enable or Disable message, click OK.

Note When you disabled a blacklist rule created in custom parameterized SQL mode or custom SQL mode, the SQL statements in the blacklist rule table in the database are retained even if the blacklist rule is disabled. If the blacklist rule is enabled again, the rule is still applicable to the same accounts. If you want to completely disable the blacklist rule, you can connect to the primary node in the cluster by using the super administrator account and delete the SQL statements from the proxy_auditing.sql_list table. When you delete the SQL statements this way, the SQL statements will not be blocked after five seconds. When you delete the SQL statements from the proxy_auditing.sql_list table, do not execute the DROP statement to delete the table.

Modify a blacklist rule

Log on to the PolarDB console.
In the upper-left corner of the console, select the region where the cluster that you want to manage is deployed.
Find the cluster you want to manage and click the cluster ID.
In the left-side navigation pane, choose Settings and Management > Security Management.
On the SQL Firewall tab, find the rule and click Modify in the Actions column. In the Modify a Rule dialog box, modify the parameters based on your business requirements. For more information about the parameters, see Table 1.

Note When you modify a rule, you cannot modify the rule name.
Click OK.

Note The parameterized SQL statements in a rule created in custom parameterized SQL mode rule and the SQL statements in a rule created in custom SQL mode cannot be modified in the console. You must delete the SQL statements from the table and then add them again.

Delete a blacklist rule

Log on to the PolarDB console.
In the upper-left corner of the console, select the region where the cluster that you want to manage is deployed.
Find the cluster you want to manage and click the cluster ID.
In the left-side navigation pane, choose Settings and Management > Security Management.
On the SQL Firewall tab, find the rule and click Delete in the Actions column.

Note You can select multiple rules in the rule table and then click Delete to batch delete the rules.
In the Delete message, click OK.

Note When you delete a blacklist rule created in custom parameterized SQL mode or custom SQL mode, the SQL statements in the blacklist rule table in the database are retained in the proxy_auditing.sql_list table even if the blacklist rule is deleted. If you want to completely delete the blacklist rule, you can connect to the primary node in the cluster by using the super administrator account and delete the SQL statements from the proxy_auditing.sql_list table. When you delete the SQL statements this way, the SQL statements will not be blocked after five seconds. When you delete the SQL statements from the proxy_auditing.sql_list table, do not execute the DROP statement to delete the table.

Cancel a blacklist rule created in custom parameterized SQL mode or custom SQL mode

Cancel a blacklist rule created in custom parameterized SQL mode
You can use one of the following methods to cancel a blacklist rule created in custom parameterized SQL mode:
- Cancel a blacklist rule created in custom parameterized SQL mode as stated in Enable or disable a blacklist rule or Delete a blacklist rule.
  
  Note If you only disable a blacklist rule created in custom parameterized SQL mode in the console and do not delete the parameterized SQL statements from the proxy_auditing.sql_list table, the rule of the same account still take effect when the blacklist rule is enabled again in the console.
- Connect to the primary node in the cluster by using the super administrator account and delete the SQL statements from the proxy_auditing.sql_list table. The parameterized SQL statement will not be blocked after five seconds.
  
  Note When you delete the SQL statements from the proxy_auditing.sql_list table, do not execute the DROP statement to delete the table.
Cancel a blacklist rule created in custom SQL mode
You can use one of the following methods to cancel a blacklist rule created in custom SQL mode:
- Cancel a blacklist rule created in custom SQL mode as stated in Enable or disable a blacklist rule or Delete a blacklist rule.
  
  Note If you only disable a blacklist rule created in custom SQL mode in the console and do not delete the SQL statements from the proxy_auditing.org_sql_list table, the rule of the same account still take effect when the blacklist rule is enabled again in the console.
- Connect to the primary node in the cluster by using the super administrator account and delete the SQL statements from the proxy_auditing.org_sql_list table. The SQL statement will not be blocked after five seconds.
  
  Note When you delete the SQL statements from the proxy_auditing.org_sql_list table, do not execute the DROP statement to delete the table.