Configuring Doris SQL Inspection

Scenarios

You can configure inspection rules for Doris SQL statements on MRS Manager and set rule parameters based on your needs.

Prerequisites

• The MySQL client is installed, and the node that will connect to the Doris database can communicate with the MRS cluster.
• The Doris FE and BE instances in the MRS cluster are functioning normally.

Notes and Constraints

• An SQL inspection rule is automatically applied in 5 minutes.
• Interception and blocking rules will interrupt SQL queries, so you need to set parameters of these rules properly based on the site requirements.
• This function is supported only in MRS 3.5.0 or later. In MRS 3.5.0, the static rule static_0001 does not take effect for count(distinct xxx) operations in Doris. In MRS 3.6.0 and later versions, the static rule static_0001 takes effect on count(distinct xxx) operations in Doris.

  For example, the SQL statement is as follows:

  with t1 as (select count(distinct user_id) from example_tbl),t2 as (select count(distinct user_id) from example_tbl1),t3 as (select count(distinct error_code) from example_tbl2) select * from t1,t2,t3;
  select * from example_tbl where (select count(distinct user_id) from example_tbl) > 1 or (select count(distinct user_id) from example_tbl1) > 1 or (select count(distinct error_code) from example_tbl2) > 1;

• In MRS 3.6.0 and later versions, the Doris SQL inspection rules are ineffective for Doris tables with no data.

Procedure

1. MRS 3.6.0-LTS or later: Log in to FusionInsight Manager, choose Cluster > Services > Doris > Configurations, search for big_sql_defend_enable, and change its value to true to enable the Doris SQL inspection.

   Click Save. On the Instances tab page, select all FE instances, and choose More > Instance Rolling Restart to restart the FE instances for the settings to take effect.

2. Create a user with the Doris administrator permissions to connect to the Doris service.
   1. Log in to FusionInsight Manager as user admin, choose System > Permission > Role, click Create Role, set the following parameters, and click OK.
      • Role Name: Enter a role name, for example, dorisrole.
      • Configure resource permission.

        MRS 3.5.0: In the Configure Resource Permission area, choose Name of the desired cluster > Doris and select Doris Admin Privilege.

        For MRS 3.6.0 or later: In the Configure Resource Permission area, choose Name of the desired cluster > Doris and select Doris Admin Privilege based on your service needs. Click Doris Read Write Privileges and select Select, Drop, Load, Alter, Create, and Grant in the internal row.

      Figure 1 Creating a role with Doris administrator permission
      
   2. Click User. If Kerberos authentication is enabled for the cluster (the cluster is in security mode), add a human-machine user. If Kerberos authentication is disabled for the cluster (the cluster is in normal mode), add a machine-machine user. Bind the user to the created role.
   3. Log in to Manager as the newly-created user and change the initial password.

3. Log in to FusionInsight Manager as a user with the Manager_administrator and Manager_viewer permissions and choose Cluster > SQL Inspector.
4. Add an SQL inspection rule for Doris by referring to Adding an MRS SQL Inspection Rule.

   For details about the rules supported by the Doris SQL engine, see MRS SQL Inspection Rules.

   For example, add the static_0001 rule while setting the threshold to 1 for the Hint action and 6 for the Intercept action. This rule detects any SQL statement that contains more than one count distinct clause excluding those executed by user doris_user (available for MRS 3.6.0 and later).

   Figure 2 Adding a Doris SQL inspection rule (MRS 3.5.0)
   
   Figure 3 Adding a Doris SQL inspection rule (MRS 3.6.0 and later versions)
   

5. Log in to the node where MySQL is installed and connect to the Doris database.

   If Kerberos authentication (security mode) has been enabled for the cluster, run the following commands to connect to the Doris database:

   export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1

   • Directly connect to the FE node to access the Doris database.

     mysql -uDatabase login username -p -PConnection port for FE queries -hIP address of the Doris FE instance

     Enter the password for logging in to the database.

   • Connect the DBalancer service. DBalancer connects to the FE node to access the Doris database based on the configured policy.

     mysql -uDatabase login user -p -PTCP access port of DBalancer -hIP address of the Doris DBalancer instance

     Enter the password for logging in to the database.

   Parameters are as follows:

   • The database login user is the user created in Step 2 and has the Doris administrator permissions.
   • To obtain the query connection port of the Doris FE instance, log in to MRS Manager, choose Cluster > Services > Doris > Configurations, and check the query_port value of the Doris service.
   • To obtain the TCP access port of the Doris DBalancer, log in to MRS Manager, choose Cluster > Services > Doris > Configurations, and check the balancer_tcp_port value of the Doris service.
   • To obtain the IP address of the Doris FE or DBalancer instance, log in to MRS Manager of the cluster, choose Cluster > Services > Doris > Instances, and check the service IP address of any FE or DBalancer instance.
   • You can also connect to the database using the MySQL connection software or Doris web UI.

6. Check the configured SQL inspection rules.

   use __internal_schema;
   select * from sqldefend_rule;

   The query result is as follows:

   MRS 3.5.0:

   +-------------+----------+--------------------+--------+-----------------+------+------+------+
   | rule_name   | is_group | group_or_user_name | action | threshold_value | p_1  | p_2  | p_3  |
   +-------------+----------+--------------------+--------+-----------------+------+------+------+
   | static_0001 |        1 | A                  |      1 |               3 | NULL | NULL | NULL |
   | static_0001 |        1 | A                  |      2 |               2 | NULL | NULL | NULL |
   +-------------+----------+--------------------+--------+-----------------+------+------+------+

   MRS 3.5.1 and later:

   +-------------+----------+--------------------+--------+-----------------+----------------------+------------+------------+
   | rule_name   | is_group | group_or_user_name | action | threshold_value | group_user_whitelist | reserved_2 | reserved_3 |
   +-------------+----------+--------------------+--------+-----------------+----------------------+------------+------------+
   | static_0001 |        1 | normal             |      1 |               6 | doris_user           | NULL       | NULL       |
   | static_0002 |        1 | normal             |      1 |               0 | NULL                 | NULL       | NULL       |
   | static_0001 |        1 | normal             |      2 |               1 | doris_user           | NULL       | NULL       |
   +-------------+----------+--------------------+--------+-----------------+----------------------+------------+------------+

   For MRS 3.6.0 and later, if the group_user_whitelist column in an SQL inspection rule is set to user1,user2, SQL statements executed by these users are exempt from that SQL inspection rule.

7. Create a database and then switch to that database.

   create database test;
   use test;

8. Create tables t1, t2, and t3.

   create table if not exists t1(id int) engine=olap distributed by hash(id);
   create table if not exists t2(id int) engine=olap distributed by hash(id);
   create table if not exists t3(id int) engine=olap distributed by hash(id);

9. Check whether the static_0001 rule has taken effect.

   select count(distinct id) from t1 except select count(distinct id) from t2 intersect select count(distinct id) from t3 union all select count(distinct id) from t1 except select count(distinct id) from t2 intersect select count(distinct id) from t3;

   The number of count distinct expressions in this statement exceeds the threshold configured in Step 4.

   MRS 3.5.0:

   • The system displays the following hint:

     ... join count = 0, countDistinct = 6 ,not in query = false ,union count = 1, nested subquery = 0 ,cartesian join = false

   • The interception information is as follows:

     ERROR ... detailMessage = static_0001 number of count(distinct) in the query 6 more than the allowed limit 3

   MRS 3.6.0 and later:

   • If the number of count distinct is greater than the threshold for a hint but is no more than the threshold for interception, the statement will be executed successfully. Run the following command to view the detailed prompt information:

     show warnings;

     +---------+------+-----------------------------------------------------------------------------------------------------------------------+
     | Level   | Code | Message                                                                                                               |
     +---------+------+-----------------------------------------------------------------------------------------------------------------------+
     | WARNING | NULL | a1dd6603d3244032-989c874d9a6d6515: static_0001 number of count(distinct) in the query 6 more than the allowed limit 1 |
     +---------+------+-----------------------------------------------------------------------------------------------------------------------+
     1 row in set (0.00 sec)

     If no exempt user is configured in the SQL inspection rule, the command output does not contain "Whitelisted User Action".

     The query ID is 887f43753bb749c9-b6ed36350f9454a8. To view the specific SQL statement corresponding to this message, enable Doris slow query detection (for details, see Enabling Slow Query Detection) and then run the following commands:

     use __internal_schema;

     select * from query_history where query_id='887f43753bb749c9-b6ed36350f9454a8';

   • If number of count distinct is greater than the interception threshold, the statement will be interrupted and fail to be executed and the following information is displayed:

     ERROR ... detailMessage = static_0001 number of count(distinct) in the query 7 more than the allowed limit 6

     show warnings;

     The statement output is empty.

     Empty set (0.00 sec)

   If the action specified in the SQL inspection rule is Block, the system may display the following message:

   ERROR ... detailMessage = running_0001 Num of result rows num reaches the fuse threshold(1000), so cancel the output

   You can query Doris SQL inspection details in /var/log/Bigdata/doris/fe/fe.log and /var/log/Bigdata/audit/doris/fe/fe.audit.log. For MRS 3.6.0 and later, if an exempt user is configured in an SQL inspection rule, the prompt information for the corresponding SQL statement in the fe.audit.log audit log will include (Whitelisted User Action). The following is an example:

   ...|Stmt=OriginStatement{originStmt='select count(distinct id) from t1 except select count(distinct id) from t2 intersect select count(distinct id) from t3 union all select count(distinct id) from t1 except select count(distinct id) from t2 intersect select count(distinct id) from t3', idx=0}|CpuTimeMS=0|ShuffleSendBytes=-1|ShuffleSendRows=-1|SqlHash=489b4ceb2f4301a7132628303f99240d|PeakMemoryBytes=0|SqlDigest=|TraceId=|WorkloadGroup=normal|HintMsg=static_0001 number of count(distinct) in the query 6 more than the allowed limit 1 (Whitelisted User Action)|FuzzyVariables ...