Skip to content

Latest commit

 

History

History
105 lines (73 loc) · 6.93 KB

Blacklist.md

File metadata and controls

105 lines (73 loc) · 6.93 KB

黑名单管理

StarRocks的客户可以维护一个SQL黑名单，在某些场景下禁止指定的一类SQL，避免这类SQL导致集群crash或者其他预期之外的行为。 客户可以自由添加/浏览/删除 SQL黑名单。

语法

这个功能通过FE配置enable_sql_blacklist控制(默认关闭)，开启命令如下：

admin set frontend config ("enable_sql_blacklist" = "true")

Admin用户(拥有ADMIN_PRIV权限的用户)可以执行以下命令设置黑名单：

ADD SQLBLACKLIST #sql#
DELETE SQLBLACKLIST #sql#
SHOW SQLBLACKLISTS
  • 当 enable_sql_blacklist 为true时，每条查询SQL都会和sqlblacklist进行匹配，对于匹配成功的SQL会直接返回错误信息：通报此条SQL处于黑名单之中；匹配失败的SQL会正常执行并输出结果。错误信息示例如下： ERROR 1064 (HY000): Access denied; sql 'select count (*) from test_all_type_select_2556' is in blacklist

增加黑名单

ADD SQLBLACKLIST #sql#

#sql# 是某类sql的正则表达式，由于SQL常用字符里面就包含 "(", ")", "*", "."等字符，这些会和正则表达式中的语义混淆，所以在设置黑名单的时候需要通过转义符作出区分，鉴于"("和")"在SQL中使用频率过高，我们内部进行了处理，设置的时候不需要转义，其他特殊字符需要使用转义字符""作为前缀。例如:

  • 禁止count(*):

    ADD SQLBLACKLIST "select count(\\*) from .+"

  • 禁止count(distinct ):

    ADD SQLBLACKLIST "select count(distinct .+) from .+"

  • 禁止order by limit x, y，1 <= x <=7, 5 <=y <=7:

    ADD SQLBLACKLIST "select id_int from test_all_type_select1 order by id_int limit [1-7], [5-7]"

  • 禁止复杂sql，这里主要是展示要转义的写法"*","-":

    ADD SQLBLACKLIST "select id_int \\* 4, id_tinyint, id_varchar from test_all_type_nullable except select id_int, id_tinyint, id_varchar from test_basic except select (id_int \\* 9 \\- 8) \\/ 2, id_tinyint, id_varchar from test_all_type_nullable2 except select id_int, id_tinyint, id_varchar from test_basic_nullable"

展示黑名单列表

SHOW SQLBLACKLIST

结果格式：Index | Forbidden SQL

比如：

mysql> show sqlblacklist;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Index | Forbidden SQL                                                                                                                                                                                                                                                                                          |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1     | select count\(\*\) from .+                                                                                                                                                                                                                                                                             |
| 2     | select id_int \* 4, id_tinyint, id_varchar from test_all_type_nullable except select id_int, id_tinyint, id_varchar from test_basic except select \(id_int \* 9 \- 8\) \/ 2, id_tinyint, id_varchar from test_all_type_nullable2 except select id_int, id_tinyint, id_varchar from test_basic_nullable |
| 3     | select id_int from test_all_type_select1 order by id_int limit [1-7], [5-7]                                                                                                                                                                                                                            |
| 4     | select count\(distinct .+\) from .+                                                                                                                                                                                                                                                                    |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Forbidden SQL中展示的sql对于所有sql语义的字符做了转义处理。

删除黑名单

DELETE SQLBLACKLIST #indexlist#

比如对SHOW SQLBLACKLIST中的sqlblacklist做delete:

delete sqlblacklist  3, 4;   --（#indexlist#是以","分隔的id）

之后剩下的sqlblacklist为：

mysql> show sqlblacklist;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Index | Forbidden SQL                                                                                                                                                                                                                                                                                          |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1     | select count\(\*\) from .+                                                                                                                                                                                                                                                                             |
| 2     | select id_int \* 4, id_tinyint, id_varchar from test_all_type_nullable except select id_int, id_tinyint, id_varchar from test_basic except select \(id_int \* 9 \- 8\) \/ 2, id_tinyint, id_varchar from test_all_type_nullable2 except select id_int, id_tinyint, id_varchar from test_basic_nullable |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+