Blacklist Management
In some cases, administrators need to disable certain patterns of SQL to avoid SQL from triggering cluster crashes or unexpected high concurrent queries.
StarRocks allows users to add, view, and delete SQL blacklists.
Syntax
EnableSQL blacklisting via enable_sql_blacklist. The default is False (off).
admin set frontend config ("enable_sql_blacklist" = "true")
The admin user who has ADMIN_PRIV privileges can manage blacklists by executing the following commands:
ADD SQLBLACKLIST "<sql>"
DELETE SQLBLACKLIST <sql_index_number>
SHOW SQLBLACKLISTS
- When
enable_sql_blacklistis true, every SQL query needs to be filtered by sqlblacklist. If it matches, the user will be informed that theSQL is in the blacklist. Otherwise, the SQL will be executed normally. The message may be as follows when the SQL is blacklisted:
ERROR 1064 (HY000): Access denied; sql 'select count (*) from test_all_type_select_2556' is in blacklist
Add blacklist
ADD SQLBLACKLIST "<sql>"
sql is a regular expression for a certain type of SQL.
Currently, StarRocks supports adding SELECT statements to the SQL Blacklist.
Since SQL itself contains the common characters (, ), *, . that may be mixed up with the semantics of regular expressions, so we need to distinguish those by using escape characters. Given that ( and ) are used too often in SQL, there is no need to use escape characters. Other special characters need to use the escape character \ as a prefix. For example:
- Prohibit
count(\*):
ADD SQLBLACKLIST "select count(\\*) from .+"
- Prohibit
count(distinct):
ADD SQLBLACKLIST "select count(distinct .+) from .+"
- Prohibit 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]"
- Prohibit complex 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"
View blacklist
SHOW SQLBLACKLIST
Result format: Index | Forbidden SQL
For example:
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 .+ |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The SQL shown in Forbidden SQL is escaped for all SQL semantic characters.
Delete blacklist
DELETE SQLBLACKLIST <sql_index_number>
<sql_index_number> is a list of SQL IDs separated by comma (,).
For example, delete the No.3 and No.4 SQLs in the above blacklist:
delete sqlblacklist 3, 4;
Then, the remaining sqlblacklist is as follows:
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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+