Help Center/ MapReduce Service/ User Guide/ Managing Clusters/ Managing SQL Inspection Rules for an MRS Cluster/ Adding an MRS SQL Inspection Rule
Updated on 2025-12-10 GMT+08:00
View PDF

Adding an MRS SQL Inspection Rule

You can add rules for specified tenants and SQL engines on FusionInsight Manager. The system will display hints on, intercept, or block SQL requests matched by the rules.

Exercise caution when adding or modifying SQL inspection rules for a cluster, enabling rules, or configuring thresholds. Misconfigured rules may result in interruptions to upper-layer services.

Adding a Rule

  1. Log in to MRS Manager as a user who has been assigned MRS Manager administrator permissions.

    For details about how to log in to MRS Manager, see Accessing an MRS Cluster's Manager (Version 2.x or Earlier).

  2. Click Cluster and choose SQL Inspector. The SQL Inspector page is displayed.

    You can click View Supported Rules to view all SQL inspection rules supported by the current cluster.

  3. Click Add Rule. After the password of the current user is verified, the Add Rule page is displayed.
  4. Set the required parameters and click OK.

    Parameter

    Example Value

    Description

    Name

    SQL_defenses01

    Name of a SQL inspection rule

    ID

    static_0001

    Rule ID

    For details about the meanings of specific rule IDs, see Table 1.

    Tenant

    default

    Click Add to select the name of the tenant to which the current rule will be associated.

    If you need to add a new tenant, plan and create a cluster tenant by referring to Managing MRS Cluster Tenants.

    Services and Actions

    -

    Click Add to specify the SQL engine to which this rule will be associated with and set the threshold parameters of the rule.

    Each rule can be associated with one SQL engine. If you want to configure a rule for other SQL engines, add new rules.

    • Service: Select the SQL engine associated with the current rule.
    • If an SQL request meets the rule, the system performs the following operations:
      • Hint: Record logs and display a hint for handling the SQL request. If the rule has parameters, you need to configure the threshold.
      • Intercept: Intercept the SQL request that meets the rule. If the rule has parameters, you need to configure the threshold.
      • Block: Block the SQL request that meets the rule. If the rule has parameters, you need to configure the threshold.

    For static and dynamic SQL interception rules, Hint and Block operations are supported. Only the Block option is available for blocking rules.

  5. View the added prevention rule on the SQL Defense page. The rule takes effect dynamically.

    To adjust the current rule, click Modify in the Operation column of the row that contains the target rule. After the user password is verified, you can modify rule parameters.

    Figure 1 Viewing SQL inspection rules

MRS SQL Inspection Rules

Table 1 MRS SQL inspection rules

ID

Description

Engine

Threshold

Example SQL Statement

static_0001

Check whether the number of occurrences of count(distinct) in the SQL statement exceeds the limit.
  • Hive
  • Spark
  • HetuEngine

Number of occurrences of count(distinct)

Recommended value: 10

SELECT COUNT(DISTINCT deviceId), COUNT(DISTINCT collDeviceId)

FROM table

GROUP BY deviceName, collDeviceName, collCurrentVersion;

static_0002

Check whether the not in <subquery> statement is used in the SQL statement.
  • Hive
  • Spark
  • HetuEngine

N/A

SELECT *

FROM Orders o

WHERE Orders.Order_ID not in (Select Order_ID

FROM HeldOrders h

where h.order_id = o.order_id);

static_0003

Check whether the number of joins in the SQL statement exceeds the limit.
  • Hive
  • Spark
  • HetuEngine

Number of joins

Recommended value: 20

N/A

static_0004

Check whether the number of the union all operators in a SQL statement exceeds the threshold.
  • Hive
  • Spark
  • HetuEngine

Number of the union all operators in a statement

Recommended value: 20

select * from tables t1

union all select * from tables t2

union all select * from tables t3

union all select * from tables t4

union all select * from tables t5

union all select * from tables t6

union all select * from tables t7

union all select * from tables t8

union all select * from tables t9;

static_0005

The number of subquery nesting layers exceeds the limit.
  • Hive
  • Spark
  • HetuEngine

Maximum number of nested subqueries

Recommended value: 20

select * from (

with temp1 as (select * from tables)

select * from temp1);

static_0006

Check whether the length of the SQL statement string exceeds the upper limit.
  • Hive
  • Spark
  • HetuEngine

Length of the SQL string, in KB

Recommended value: 10

N/A

static_0007

Check whether the Cartesian product exists when multiple tables are associated.
  • Hive
  • Spark
  • HetuEngine

N/A

select * from A,B;

static_0008

Check whether alter table update operation is performed at the cluster level (on cluster).

ClickHouse

N/A

alter table testtb1 on cluster default_cluster update price=10.0 where id='100'

static_0009

Check whether alter table delete operation is performed at the cluster level (on cluster).

ClickHouse

N/A

alter table testtb1 on cluster default_cluster delete where id ='10'

static_0010

Check whether the alter table add column operation is performed at the cluster level (on cluster).

ClickHouse

N/A

alter table testtb1 on cluster default_cluster add column testc String

static_0011

Check whether the alter table drop column operation is performed at the cluster level (on cluster).

ClickHouse

N/A

alter table testtb1 on cluster default_cluster drop column testc

static_0012

Check whether the optimize final operation is performed at the cluster level (on cluster).

ClickHouse

N/A

optimize table testtb1 on cluster default_cluster final

static_0013

Check whether the drop table operation is performed at the cluster level (on cluster).

ClickHouse

N/A

drop table testtb1 on cluster default_cluster;

static_0014

Check whether the truncate table operation is performed at the cluster level (on cluster).

ClickHouse

N/A

truncate table testtb1 on cluster default_cluster;

dynamic_0001

Check whether the number of scanned files exceeds the limit.
  • Hive
  • Spark
  • HetuEngine

Number of files that will be scanned or have been scanned

Recommended value: 100,000

SELECT ss_ticket_number FROM store_sales WHERE ss_ticket_number=72291252 LIMIT 10;

dynamic_0002

Check whether the number of partitions involved in operations (select, delete, update, and alter) on a table exceeds the upper limit.
  • Hive
  • Spark
  • HetuEngine
  • ClickHouse

Number of partitions involved in the delete or alter operation

Recommended value: 10,000

DELETE FROM table_name WHERE column_name = value

dynamic_0003

When the right table of a join is a distributed table, check whether the data volume of the right table exceeds the upper limit.

ClickHouse

Number of rows in the right table when the join operation is performed.

Recommended value: 100,000,000

SELECT name, text FROM table_1 JOIN table_2 ON table_1.Id = table_2.Id

running_0001

Check whether the number of result rows returned by the Select statement to the client exceeds the upper limit.
  • Hive
  • Spark
  • HetuEngine
  • ClickHouse

Number of rows in the query result

Recommended value: 100,000

select * from table

running_0002

Check whether the peak memory usage of the SQL statement exceeds the absolute value limit.
  • Hive
  • Spark
  • HetuEngine
  • ClickHouse

Memory occupied by SQL running, in MB

N/A

running_0003

Check whether the running duration of the SQL statement exceeds the upper limit.
  • Hive
  • Spark
  • HetuEngine
  • ClickHouse

SQL running duration threshold, in seconds

N/A

running_0004

The amount of data scanned by the SQL statements.
  • Hive
  • Spark
  • HetuEngine
  • ClickHouse

Amount of data scanned by the SQL statement, in GB

Recommended value: 10,240

N/A
Parent Topic: Managing SQL Inspection Rules for an MRS Cluster