CREATE-SQL-BLOCK-RULE

Name

CREATE SQL BLOCK RULE

Description

This statement creates a SQL blocking rule. it can restrict any kind of sql statements(no matter DDL or DML statement).

Supports configuring SQL blacklists by user:

  • Refuse to specify SQL by regular matching
  • Check if a sql reaches 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 these limits, the query will be intercepted

grammar:

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

Parameter Description:

  • sql: matching rule (based on regular matching, special characters need to be translated,for exampleselect *useselect \\*), optional, the default value is “NULL”
  • sqlHash: sql hash value, used for exact matching, we will print this value in fe.audit.log, optional, this parameter and sql can only be selected one, the default value is “NULL”
  • partition_num: the maximum number of partitions a scan 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

Example

  1. Create a block rule named test_rule

    1. CREATE SQL_BLOCK_RULE test_rule
    2. PROPERTIES(
    3. "sql"="select \\* from order_analysis",
    4. "global"="false",
    5. "enable"="true"
    6. );

    Notes:

    That the sql statement here does not end with a semicolon

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

    1. select * from order_analysis;
    2. ERROR 1064 (HY000): errCode = 2, detailMessage = sql match regex sql block rule: order_analysis_rule

  2. Create test_rule2, limit the maximum number of scanned partitions to 30, and limit the maximum scan base to 10 billion rows. The example is as follows:

    1.  CREATE SQL_BLOCK_RULE test_rule2
    2.  PROPERTIES (
    3.  "partition_num" = "30",
    4.  "cardinality" = "10000000000",
    5.  "global" = "false",
    6.  "enable" = "true"
    7.  );

  3. Create SQL BLOCK RULE with special chars

    1. CREATE SQL_BLOCK_RULE test_rule3
    2. PROPERTIES
    3. ( 
    4. "sql" = "select count\\(1\\) from db1.tbl1"
    5. );
    6. CREATE SQL_BLOCK_RULE test_rule4
    7. PROPERTIES
    8. ( 
    9. "sql" = "select \\* from db1.tbl1"
    10. );

  4. In SQLBLOCK_RULE, SQL matching is based on regular expressions. If want to match more patterns of SQL, need to write the corresponding regex. For example, to ignore spaces in SQL and not query tables that start with ‘order‘, as shown below:

    1.  CREATE SQL_BLOCK_RULE test_rule4 
    2.  PROPERTIES(
    3.    "sql"="\\s*select\\s*\\*\\s*from order_\\w*\\s*",
    4.    "global"="false",
    5.    "enable"="true"
    6.  );

APPENDIX

Here are some commonly used regular expressions:

  1. . ：Matches any single character except for a newline character \n.
  3. * ：Matches the preceding element zero or more times. For example, a matches zero or more 'a'.
  5. + ：Matches the preceding element one or more times. For example, a+ matches one or more 'a'.
  7. ? ：Matches the preceding element zero or one time. For example, a? matches zero or one 'a'.
  9. [] ：Used to define a character set. For example, [aeiou] matches any one vowel letter.
  11. [^] ：In a character set, use ^ to indicate negation, matching characters that are not in the set. For example, [^0-9] matches any non-digit character.
  13. () ：Used for grouping expressions and applying quantifiers. For example, (ab)+ matches consecutive 'ab'.
  15. | ：Represents logical OR. For example, a|b matches 'a' or 'b'.
  17. ^ ：Matches the beginning of a string. For example, ^abc matches a string that starts with 'abc'.
  19. $ ：Matches the end of a string. For example, xyz$ matches a string that ends with 'xyz'.
  21. \ ：Used to escape special characters to treat them as ordinary characters. For example, \\. matches the period character '.'.
  23. \s ：Matches any whitespace character, including spaces, tabs, newline characters, etc.
  25. \d ：Matches any digit character, equivalent to [0-9].
  27. \w ：Matches any word character, including letters, digits, and underscores, equivalent to [a-zA-Z0-9_].

Keywords

  1. CREATE, SQL_BLCOK_RULE

Best Practice