Use SQL blocking to manage SQL queries - ApsaraDB for SelectDB

In production environments, uncontrolled SQL statements—such as full table scans or high-risk DML operations—can exhaust cluster resources and destabilize your database. SQL blocking lets you define rules that reject matching statements before any cluster resources are consumed. When a statement matches a rule, SelectDB returns an error immediately and does not execute it.

How it works

Two matching strategies are available:

Strategy	How it matches	Best for
Regular expression	Matches the SQL text against a Java regex pattern	Blocking known dangerous query patterns, such as full table scans or specific functions
Scan thresholds	Blocks a query when the estimated number of partitions, tablets, or rows exceeds your configured limits	Protecting cluster stability from unexpectedly large queries

You can combine both strategies in a single rule. Rules can apply globally to all database accounts, or only to accounts you explicitly associate them with.

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.

Syntax

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

Parameters

Parameter	Required	Description
`rule_name`	Yes	The name of the SQL blocking rule.
`PROPERTIES`	Yes	The rule properties. See the table below.

Properties

Property	Required	Default	Values	Description
`sql`	No	`NULL`	Java regex string	A regular expression to match against incoming SQL statements. Escape special regex characters (for example, `select ` becomes `select \\`). Do not include a trailing semicolon. Mutually exclusive with `sqlHash`.
`sqlHash`	No	`NULL`	SQL hash string	An exact SQL hash value for precise matching. The hash is recorded in `fe.audit.log`. Mutually exclusive with `sql`.
`partition_num`	No	`0` (no limit)	Integer ≥ 0	Maximum number of partitions a scan node can scan. `0` means no limit.
`tablet_num`	No	`0` (no limit)	Integer ≥ 0	Maximum number of tablets a scan node can scan. `0` means no limit.
`cardinality`	No	`0` (no limit)	Integer ≥ 0	Maximum estimated number of rows a scan node can scan. `0` means no limit.
`global`	No	`"false"`	`"true"` / `"false"`	`"true"` applies the rule to all database accounts. `"false"` limits the rule to accounts explicitly associated with it.
`enable`	No	`"true"`	`"true"` / `"false"`	Whether the rule is active.

Examples

Block a specific query pattern using a regular expression

To block all queries against the order_analysis table:

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

When a blocked query runs, SelectDB returns an error:

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

Block queries that exceed combined partition and row count thresholds

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

A query is blocked when it exceeds either threshold.

Escape special regex characters

Parentheses ( and ) are special characters in Java regular expressions and must be escaped with \\:

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"
);

View SQL blocking rules

SHOW SQL_BLOCK_RULE [FOR rule_name];

Omit FOR rule_name to return all rules.

View all rules

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)

View a specific rule

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

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

All properties are modifiable with one constraint: if partition_num is set on a rule, you cannot modify sql or sqlHash on that same rule.

Update the SQL pattern and re-enable a rule

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

Adjust threshold values

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

Delete SQL blocking rules

Delete one or more rules in a single statement by separating rule names with commas:

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

Associate rules with a database account

Rules with global="false" are inactive until associated with a specific database account. Run the following statement to assign one or more rules to an account (separate multiple rule names with commas):

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