管理员需要在某些场景中禁止某些pattern的SQL,避免比如可能触发集群crash的SQL或者客户预期之外的高并发查询。
「查询黑名单」给客户一个SQL黑名单的机制,让他能够自己添加/浏览/删除 sql黑名单。
通过 enable_sql_blacklist 开启sql黑名单(默认关闭):
admin set frontend config ("enable_sql_blacklist" = "true")
Admin用户(拥有ADMIN_PRIV权限的用户)可以执行以下命令设置黑名单:
ADD SQLBLACKLIST #sql#
DELETE SQLBLACKLIST #sql#
SHOW SQLBLACKLISTS
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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://docs.starrocks.com/zh-cn/main/administration/Blacklist
内容来源于网络,如有侵权,请联系作者删除!