Apache Doris SQL 黑名单功能介绍及使用

数据是一个企业非常重要的资产，这些资产在数据库中存放，为业务提供各种数据服务支撑，在实际的使用中一旦误操作或者认为的一些不可逆的操作，会引发灾难性的后果，还有一些操作可能会引发整个系统IO负载过高，导致系统不可用，为了避免这些情况的发生，Doris内置了SQL 黑名单机制，可以通过黑名单规则来拦截指定类型的SQL语句或具体的SQL语句，避免上面情况的发生。

黑名单使用的一些场景：

1. DBA定义一些比较危险的SQL，放在SQL黑名单文件中。可以避免前端应用发过来的SQL对数据库造成危害。这种SQL有可能是开发者粗心编写的，也有可能是被SQL注入生成的SQL。例如：delete from mytable
   ，这种不带where条件的SQL，会把整个表删除。

2. 在项目上线后，通过 FE 审计日志现存在大量某条SQL给DB造成了很大的压力。这时候可以动态地将这条SQL加入黑名单，阻止该SQL的执行，从而使数据库压力降低。例如:select count(*) from mytable where xxxx
   ,这类SQL如果没有优化得当，是很容易造成系统的IO过高的。

1. Doris 黑名单使用

1.1 Doris 黑名单机制使用条件

1. Doris 黑名单机制对 DDL 和 DML 语句都可以进行限制

2. 支持正则表达式来配置指定SQL

3. 还支持你可以通过 Partition num 、Tablet Num、Cardinality 来限定一个 SQL 扫描的数据范围，一旦达到限定，SQL 就会被拦截不被执行

1.2 创建 SQL 黑名单规则

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

• sql：匹配规则(基于正则匹配,特殊字符需要转译,如select *
  使用select \\*
  )，可选，默认值为 "NULL", 最后不要带分号

• sqlHash: sql hash值，用于完全匹配，我们会在fe.audit.log
  打印这个值，可选，这个参数和sql只能二选一，默认值为 "NULL"

• partition_num: 一个扫描节点会扫描的最大partition数量，默认值为0L

• tablet_num: 一个扫描节点会扫描的最大tablet数量，默认值为0L

• cardinality: 一个扫描节点粗略的扫描行数，默认值为0L

• global：是否全局(所有用户)生效，默认为false

• enable：是否开启阻止规则，默认为true

注意：

这里sql 语句最后不要带分号

1.3 修改 SQL 黑名单规则

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

• sql 和 sqlHash 不能同时被设置。这意味着，如果一个 rule 设置了 sql 或者 sqlHash，则另一个属性将无法被修改；

• sql sqlHash 和 partition_num tablet_num cardinality 不能同时被设置。举个例子，如果一个 rule 设置了 partition_num，那么 sql 或者 sqlHash 将无法被修改

1.4 删除 SQL 黑名单规则

DROP SQL_BLOCK_RULE test_rule1,...

这里之多一次删除多个规则，规则名称之间使用英文逗号隔开

1.5 查看定义好的 SQL 黑名单规则

查看已经定义好的 SQL 黑名单规则

mysql> SHOW SQL_BLOCK_RULE;
+---------------------+-------------------------------------+---------+--------------+-----------+-------------+--------+--------+
| Name                | Sql                                 | SqlHash | PartitionNum | TabletNum | Cardinality | Global | Enable |
+---------------------+-------------------------------------+---------+--------------+-----------+-------------+--------+--------+
| count_block_rule    | select count\(\*\) from .+          | NULL    | 0            | 0         | 0           | true   | true   |
| distinct_block_rule | select count\(distinct .+\) from .+ | NULL    | 0            | 0         | 0           | true   | true   |
| delete_block_rule   | delete from .+ where *              | NULL    | 0            | 0         | 0           | true   | true   |
+---------------------+-------------------------------------+---------+--------------+-----------+-------------+--------+--------+
3 rows in set (0.03 sec)

1.6 用户规则绑定

如果配置global=false，则需要配置指定用户的规则绑定，多个规则使用,
分隔

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

2. 应用举例

1. 禁止 count(*)
   

   CREATE SQL_BLOCK_RULE count_block_rule  PROPERTIES(   
     "sql"="select count\\(\\*\\) from .+",   
     "global"="true",   
     "enable"="true" 
   );

   定义好规则之后执行 SQL , 会直接被拦截掉，SQL 不会被执行

   mysql> select count(*) from test_load_001;
   ERROR 1105 (HY000): errCode = 2, detailMessage = sql match regex sql block rule: count_block_rule

   
   

2. 禁止 count(distinct )
   

   CREATE SQL_BLOCK_RULE distinct_block_rule  PROPERTIES(   
     "sql"="select count\\(distinct .+\\) from .+",   
     "global"="true",   
     "enable"="true" 
   );

   执行我们定义好规则的 SQL 就会被拦截掉

   mysql> select count(distinct day_time) from test_load_001;
   ERROR 1105 (HY000): errCode = 2, detailMessage = sql match regex sql block rule: distinct_block_rule

   
   

3. 禁止 delete from table
   

   CREATE SQL_BLOCK_RULE delete_block_rule  PROPERTIES(   
     "sql"="delete from .+ where *",   
     "global"="true",   
     "enable"="true" 
   );

   看看效果

   mysql> delete from test_load_001 where app_id=1;
   ERROR 1105 (HY000): errCode = 2, detailMessage = sql match regex sql block rule: delete_block_rule

4. 禁用 select *
   

   CREATE SQL_BLOCK_RULE select_all_block_rule  PROPERTIES(   
     "sql"="select \\* from .+",   
     "global"="true",   
     "enable"="true" 
   );

   查看效果

   mysql> select * from test_load_001;
   ERROR 1105 (HY000): errCode = 2, detailMessage = sql match regex sql block rule: select_all_block_rule

3. 总结

是不是使用也很简单，而且能起到不错的 SQL 拦截效果，更多使用场景快快探索起来。