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.

  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;

Procedure

1. 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.

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

      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.

2. Log in to FusionInsight Manager as a user with the Manager_administrator and Manager_viewer permissions and choose Cluster > SQL Inspector.
3. 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 a SQL statement that contains more than one count distinct.

   Figure 2 Adding a Doris SQL inspection rule
   

4. 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 1 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 FE instance, 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.

5. Check the configured SQL inspection rules.

   use __internal_schema;
   select * from sqldefend_rule;

   The query result is as follows:

   +-------------+----------+--------------------+--------+-----------------+------+------+------+
   | 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 |
   +-------------+----------+--------------------+--------+-----------------+------+------+------+

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

   create database test;
   use test;

7. 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);

8. 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 3.

   • 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

   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.