SQL Block Rule

This function is used to limit any sql statement (no matter DDL or DML statement). Support SQL block rule by user level:

1. by regex way to deny specify SQL

2. by setting partition_num, tablet_num, cardinality, check whether a sql reaches one of the limitations

   • partition_num, tablet_num, cardinality could be set together, and once reach one of them, the sql will be blocked.

Rule

SQL block rule CRUD

• create SQL block rule,For more creation syntax seeCREATE SQL BLOCK RULE
  • sql: Regex pattern, Special characters need to be translated, "NULL" by default
  • sqlHash: Sql hash value, Used to match exactly, We print it in fe.audit.log, This parameter is the only choice between sql and sql, "NULL" by default
  • partition_num: Max number of partitions will be scanned by a scan node, 0L by default
  • tablet_num: Max number of tablets will be scanned by a scan node, 0L by default
  • cardinality: An inaccurate number of scan rows of a scan node, 0L by default
  • global: Whether global(all users)is in effect, false by default
  • enable: Whether to enable block rule, true by default

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

Notes:

That the sql statement here does not end with a semicolon

When we execute the sql that we defined in the rule just now, an exception error will be returned. An 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, limits the maximum number of scanning partitions to 30 and the maximum scanning cardinality to 10 billion rows. As shown in the following example:

CREATE SQL_BLOCK_RULE test_rule2 PROPERTIES("partition_num" = "30", "cardinality"="10000000000","global"="false","enable"="true")

• show configured SQL block rules, or show all rules if you do not specify a rule name,Please see the specific grammar SHOW SQL BLOCK RULE

SHOW SQL_BLOCK_RULE [FOR RULE_NAME]

• alter SQL block rule, Allows changes sql/sqlHash/global/enable/partition_num/tablet_num/cardinality anyone,Please see the specific grammarALTER SQL BLOCK RULE
  • sql and sqlHash cannot be set both. It means if sql or sqlHash is set in a rule, another property will never be allowed to be altered
  • sql/sqlHash and partition_num/tablet_num/cardinality cannot be set together. For example, partition_num is set in a rule, then sql or sqlHash will never be allowed to be altered.

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, Support multiple rules, separated by ,,Please see the specific grammarDROP SQL BLOCK RULE

DROP SQL_BLOCK_RULE test_rule1,test_rule2

User bind rules

If global=false is configured, the rules binding for the specified user needs to be configured, with multiple rules separated by ', '

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