VeloDB Cloud
Developer Guide
Others
SQL Blacklist

SQL Blacklist

It is used to limit the query statement, and does not limit the execution of the explain statement. Support to configure SQL blacklist by user:

  1. Reject the specified SQL by means of regular matching

  2. Check if a query hits one of these limits by setting partition_num, tablet_num, cardinality

    • partition_num, tablet_num, cardinality can be set together, once a query reaches one of the limits, the query will be blocked

Add、delete、modify and query SQL rules

  • Create SQL block rules, for more creation syntax, please refer to CREATE SQL BLOCK RULE
    • sql: matching rules (based on regular matching, special characters need to be translated), optional, the default value is "NULL"
    • sqlHash: sql hash value, used for exact match, we will fe.audit.logprint this value, optional, this parameter and sql can only choose one, the default value is "NULL"
    • partition_num: The maximum number of partitions that a scanning node will scan, the default value is 0L
    • tablet_num: The maximum number of tablets that a scanning node will scan, the default value is 0L
    • cardinality: the rough scan line number of a scan node, the default value is 0L
    • global: Whether to take effect globally (all users), the default is false
    • enable: whether to enable blocking rules, the default is true
CREATE SQL_BLOCK_RULE test_rule
PROPERTIES(
  "sql"="select \\* from order_analysis",
  "global"="false",
  "enable"="true",
  "sqlHash"=""
)

Notice:

Here, the sql statement should not have a semicolon at the end

When we execute the sql we defined in the rules just now, an abnormal error will be returned. The example is as follows:

mysql> select * from order_analysis;
ERROR 1064 (HY000): errCode = 2, detailMessage = sql match regex sql block rule: order_analysis_rule
  • Create test_rule2, limit the maximum number of scanned partitions to 30, and the maximum scan base to 10 billion rows. The example is as follows:
CREATE SQL_BLOCK_RULE test_rule2 PROPERTIES("partition_num" = "30", "cardinality"="10000000000","global"="false","enable"="true")
  • View the configured SQL blocking rules. If you do not specify a rule name, you will view all the rules. For specific syntax, please refer to SHOW SQL BLOCK RULE
SHOW SQL_BLOCK_RULE [FOR RULE_NAME]
  • Modify the SQL blocking rules to allow modification of each item such as sql/sqlHash/partition_num/tablet_num/cardinality/global/enable. For specific syntax, please refer to ALTER SQL BLOCK RULE
    • sql and sqlHash cannot be set at the same time. This means that if a rule sets sql or sqlHash, the other property will not be modifiable
    • sql/sqlHash and partition_num/tablet_num/cardinality cannot be set at the same time. For example, if a rule sets partition_num, then sql or sqlHash cannot be modified
ALTER SQL_BLOCK_RULE test_rule PROPERTIES("sql"="select \\* from test_table","enable"="true")
ALTER SQL_BLOCK_RULE test_rule2 PROPERTIES("partition_num" = "10","tablet_num"="300","enable"="true")
DROP SQL_BLOCK_RULE test_rule1,test_rule2

User rule binding

If you configure global=false, you need to configure the rule binding for the specified user, and multiple rules are ,separated

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