ApsaraDB for SelectDB:SQL blocking

Overview

In some scenarios in the production environment, you need to restrict the SQL statements that can be executed, such as blocking the DELETE statement or some high-risk SQL statements. To meet the requirements in such scenarios, ApsaraDB for SelectDB provides the SQL blocking feature. If an SQL statement that you execute in ApsaraDB for SelectDB matches an SQL blocking rule, ApsaraDB for SelectDB prevents the execution of the SQL statement and returns an error message.

Create an SQL blocking rule

You can create an SQL blocking rule to restrict all SQL statements, including DDL and DML statements. You can also configure an SQL blacklist based on database accounts.

You can use one of the following methods to configure an SQL blocking rule:

• Use regular expressions to match and reject the specified SQL queries.

• Check whether a query reaches one of the thresholds that are specified by the partition_num, tablet_num, and cardinality parameters. You can use these parameters to specify thresholds at the same time. If a query reaches one of the thresholds, the query is blocked.

Syntax

CREATE SQL_BLOCK_RULE rule_name 
[PROPERTIES ("key"="value", ...)];

Parameters

The following table describes the common parameters that you can specify in the PROPERTIES parameter.

Examples

• Create an SQL blocking rule named test_rule. Sample code:

  CREATE SQL_BLOCK_RULE test_rule 
  PROPERTIES(
    "sql"="select \\* from order_analysis",
    "global"="false",
    "enable"="true",
    "sqlHash"=""
  )

  The following error message is returned if the SQL query that you want to perform matches the preceding SQL blocking rule:

  SELECT * FROM order_analysis;
  ERROR 1064 (HY000): errCode = 2, detailMessage = sql match regex sql block rule: order_analysis_rule

• Create an SQL blocking rule named test_rule2. Set the partition_num parameter to 30 and the cardinality parameter to 10000000000. Sample code:

  CREATE SQL_BLOCK_RULE test_rule2 
  PROPERTIES
  (
     "partition_num" = "30",
     "cardinality" = "10000000000",
     "global" = "false",
     "enable" = "true"
  );
  Query OK, 0 rows affected (0.01 sec)

• Create an SQL blocking rule that contains special characters. ( and ) are special characters in regular expressions and need to be escaped.

  CREATE SQL_BLOCK_RULE test_rule3
  PROPERTIES
  ( 
      "sql" = "select count\\(1\\) from test_db.test_table"
  );
  
  CREATE SQL_BLOCK_RULE test_rule4
  PROPERTIES
  ( 
      "sql" = "select \\* from db1.tbl1"
  );

Query an SQL blocking rule

You can query the configured SQL blocking rules. If you do not specify the name of the SQL blocking rule that you want to query, all SQL blocking rules are queried.

Syntax

SHOW SQL_BLOCK_RULE [FOR RULE_NAME];

Parameters

Examples

• Query all SQL blocking rules. Sample code:

  SHOW SQL_BLOCK_RULE;
  +------------+------------------------+---------+--------------+-----------+-------------+--------+--------+
  | Name       | Sql                    | SqlHash | PartitionNum | TabletNum | Cardinality | Global | Enable |
  +------------+------------------------+---------+--------------+-----------+-------------+--------+--------+
  | test_rule  | select * from order_analysis | NULL    | 0            | 0         | 0           | true   | true   |
  | test_rule2 | NULL                   | NULL    | 30           | 0         | 10000000000 | false  | true   |
  +------------+------------------------+---------+--------------+-----------+-------------+--------+--------+
  2 rows in set (0.01 sec)

• Query the specified SQL blocking rule. Sample code:

  SHOW SQL_BLOCK_RULE FOR test_rule2;
  +------------+------+---------+--------------+-----------+-------------+--------+--------+
  | Name       | Sql  | SqlHash | PartitionNum | TabletNum | Cardinality | Global | Enable |
  +------------+------+---------+--------------+-----------+-------------+--------+--------+
  | test_rule2 | NULL | NULL    | 30           | 0         | 10000000000 | false  | true   |
  +------------+------+---------+--------------+-----------+-------------+--------+--------+
  1 row in set (0.00 sec)

Modify an SQL blocking rule

You can modify all the parameters of an SQL blocking rule.

Syntax

ALTER SQL_BLOCK_RULE rule_name 
[PROPERTIES ("key"="value", ...)];

Parameters

Examples

1. Modify the PROPERTIES parameter of an SQL blocking rule. Sample code:

ALTER SQL_BLOCK_RULE test_rule PROPERTIES("sql"="select \\* from test_table","enable"="true")

2. If the partition_num parameter is specified for an SQL blocking rule, you cannot modify the sql or sqlHash parameter. Sample code:

ALTER SQL_BLOCK_RULE test_rule2 PROPERTIES("partition_num" = "10","tablet_num"="300","enable"="true")

Delete an SQL blocking rule

You can delete one or more SQL blocking rules at a time.

Syntax

DROP SQL_BLOCK_RULE test_rule1,...

Parameters

Example

Delete the SQL blocking rules named test_rule1 and test_rule2. Sample code:

DROP SQL_BLOCK_RULE test_rule1,test_rule2;
Query OK, 0 rows affected (0.00 sec)

Associate one or more SQL blocking rules with a specific database account

If you want to configure one or more SQL blocking rules for a specific database account, set the global parameter of the rule to false. Then, execute the following statement to associate one or more SQL blocking rules with the database account. Separate multiple SQL blocking rule names with commas (,).

SET PROPERTY [FOR 'jack'] 'sql_block_rules' = 'test_rule1,test_rule2';