PolarDB：Configure a blacklist rule

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 one of the following ways:

• Fixed rule mode: You can configure common blacklist rules in the PolarDB console. Each rule can be configured to take effect on an Alibaba Cloud account or a cluster. For more information about common blacklist rules, see Add a blacklist rule.

• Custom parameterized SQL mode: You can parameterize all variables in SQL statements that you want to execute in a database, generate a parameterized template, and record the template in the database. PolarProxy blocks SQL statements that match the parameterized template.

• Custom SQL mode: You can specify SQL statements to be blocked without the need to parameterize the variables of the statements. SQL statements that use other parameters are not blocked.

Create a blacklist rule

1. Log on to the PolarDB console.
2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
3. Find the cluster and click the cluster ID.

4. In the left-side navigation pane, choose Settings and Management > Security Management.

5. In the upper-left corner of the SQL Firewall tab, click Add.

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

   • Fixed rule mode

     1. If you select the fixed rule mode, configure parameters described in the following table.

        Table 1. Parameters of a blacklist rule

        Parameter		Required	Description
        Basic Information	Rule Name	Yes	The name of the rule. The name must meet the following requirements: It can contain digits and letters. It 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 applicable.
        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 match the blacklist rule.
        	Database Account Name	No	The name of the database account to which the rule is applicable. Valid values: All Accounts: The rule is applicable to all database accounts in the cluster. The field on the right side of the ALL Accounts option can be left empty. Include: The rule is applicable only to specified database accounts. You must enter at least one database account name in the field on the right side of the Include option. Separate multiple database account names with commas (,). Exclude: The rule is applicable to all database accounts in the cluster except the specified database accounts. You must enter at least one database account name in the field on the right side of the Exclude option. Separate multiple database account names with commas (,). Note The database account name must 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 asterisks (*). Valid values: Enable: blocks SQL statements that contain asterisks (*). Disable: does not block SQL statements that contain asterisks (*).
        	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 set the Block SQLs of Specific Types parameter to Enable, select at least one type of SQL statement. The following types of SQL statements 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 set the Block SQLs Without WHERE parameter to Enable, select at least one type of SQL statement. The following types of SQL statements 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. PolarProxy does not block the SELECT 1; statement.
        	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 set the Block SQLs With Specific Columns parameter to Enable, the following options are supported: All: The rule is applicable to all column names in the cluster. The field on the right side of the All option can be left empty. Include: The rule is applicable only to specified column names. You must enter at least one database column name in the field on the right side of the Include option. Separate multiple column names with commas (,). Exclude: The rule is applicable to all column names in the cluster except the specified column names. You must enter at least one column name in the field on the right side of the Exclude option. 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 set the Block SQLs With Specific Functions parameter to Enable, the following options are supported: All: The rule is applicable to all functions in the cluster. The field on the right side of the All option can be left empty. Include: The rule is applicable only to specified functions. You must enter at least one function in the field on the right side of the Include option. Separate multiple functions with commas (,). Exclude: The rule is applicable to all functions in the cluster except the specified functions. You must enter at least one function in the field on the right side of the Exclude option. 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 set the Block SQLs With Specific Columns and Specific Functions parameter to Enable, you must enter at least one function and one column name in the field on the right side. If you set the Function Name parameter to Include and the Column Name parameter to Include, the rule takes effect on SQL statements that contain specific functions and specific column names. If you set the Function Name parameter to Include and the Column Name parameter to Exclude, the rule takes effect on SQL statements that contain specific functions and that do not contain specific column names. If you set the Function Name parameter to Exclude and the Column Name parameter to Include, the rule takes effect on SQL statements that do not contain specific functions and that contain specific column names. If you set the Function Name parameter to Exclude and the Column Name parameter to Exclude, the rule takes effect on 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.

     2. Click OK.

   • Custom parameterized SQL mode

     1. If you select custom parameterized SQL mode, configure the required parameters. For more information about the parameters, see Add a blacklist rule.

        Note

        If you select custom parameterized SQL mode, you can disable all blacklist rules in the Configurations parameter described in Table 1.

     2. Click OK.

     3. Connect to the specified database endpoint by using the previously defined database account name. You can specify the SQL statement that you want to block 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 = ?

        The question mark (?) in the template indicates any value.

        Wait for 5 seconds. When you use the account to execute an SQL statement that matches the preceding parameterized template on the specified cluster, PolarProxy blocks the statement. The following information is displayed after an 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 5 seconds.

   • Custom SQL mode

     1. If you select custom SQL mode, configure the required parameters. For more information about the parameters, see Add a blacklist rule.

        Note

        If you select custom SQL mode, you can disable all options in the Configurations parameter described in Table 1.

     2. Click OK.

     3. Connect to the specified database endpoint by using the previously defined database account name. You can specify the SQL statement that you want to block 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 5 seconds. When you use the account to execute 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 an 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 5 seconds.

Enable or disable a blacklist rule

1. Log on to the PolarDB console.
2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
3. Find the cluster and click the cluster ID.

4. In the left-side navigation pane, choose Settings and Management > Security Management.

5. On the SQL Firewall tab, find the rule that you want to manage and turn on Enable/Disable.

   Note

   You can select multiple rules in the rule list and then click Enable or Disable to batch enable or disable the rules.

6. In the Enable or Disable message, click OK.

Modify a blacklist rule

1. Log on to the PolarDB console.
2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
3. Find the cluster and click the cluster ID.

4. In the left-side navigation pane, choose Settings and Management > Security Management.

5. On the SQL Firewall tab, find the rule that you want to manage and then 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 Add a blacklist rule.

   Note

   When you modify a rule, you cannot modify the rule name.

6. Click OK.

Delete a blacklist rule

1. Log on to the PolarDB console.
2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
3. Find the cluster and click the cluster ID.

4. In the left-side navigation pane, choose Settings and Management > Security Management.

5. On the SQL Firewall tab, find the rule that you want to manage and then click Delete in the Actions column.

   Note

   You can select multiple rules in the rule list and then click Delete to batch delete the rules.

6. In the Delete message, click OK.

Cancel a blacklist rule created in custom parameterized SQL 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:

  • See the Enable or disable a blacklist rule or Delete a blacklist rule section of this topic.

    Note

    If you disable a blacklist rule created in custom parameterized SQL mode only in the PolarDB console and do not remove the parameterized SQL statements from the proxy_auditing.sql_list table, the rule of the same account still takes effect when you enable the blacklist rule again in the PolarDB console.

  • Connect to the primary node in the cluster by using the super administrator account and remove the SQL statements from the proxy_auditing.sql_list table. The parameterized SQL statement is no longer blocked after 5 seconds.

    Note

    When you remove the SQL statements from the proxy_auditing.sql_list table, do not execute the DROP statement to remove 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:

  • See the Enable or disable a blacklist rule or Delete a blacklist rule section of this topic.

    Note

    If you disable a blacklist rule created in custom parameterized SQL mode only in the PolarDB console and do not remove the parameterized SQL statements from the proxy_auditing.org_sql_list table, the rule of the same account still takes effect when you enable the blacklist rule again in the PolarDB console.

  • Connect to the primary node in the cluster by using the super administrator account and remove the SQL statements from the proxy_auditing.org_sql_list table. The SQL statement is no longer blocked after 5 seconds.

    Note

    When you remove the SQL statements from the proxy_auditing.org_sql_list table, do not execute the DROP statement to remove the table.