Data Management (DMS) provides the SQLConsole to simplify read and write operations on databases. You can customize query rules in security rules by using the SQLConsole. This topic provides examples on how to customize usage rules for the SQLConsole.

Description

  • In the following examples, an administrator account is used to improve demonstration effects.
  • In the following examples, a database in the development environment is used. You can configure the staging or production environment for your database. For more information, see Change the environment type of an instance.
    Note For the configurations such as table schemas and security rules of the POC_dev instance used in the following examples, see Make preparations and Design schemas.

Go to the details page of a security rule

  1. Log on to the DMS console as an administrator.
  2. In the top navigation bar, choose System > Security > Security Rules.

    The Security Rules tab appears.

    The path to the Security Rules tab
  3. Find the Security Rules for POC Development Databases rule and click Edit in the Actions column.
    Note For more information about how to add a security rule, see Create security rules.
    Modify the security rules for the poc_prod database in a production environment

Configure the maximum number of SQL statements that can be executed at a time

  1. Modify the Control the number of SQL statements executed at a time rule.
    1. Go to the details page of a security rule.
    2. On the Details page, click SQLConsole in the left-side navigation pane and then set Checkpoints to SQL Execution Quantity Criteria.
    3. Find the Control the number of SQL statements executed at a time rule and click Edit in the Actions column.Modify the maximum number of SQL statements that can be executed at a time
    4. Replace the DSL code in the rule with the following code and click Submit:
      if
          @fac.sql_count > 5
      then
          @act.reject_execute 'The number of SQL statements executed at a time cannot exceed five.'
      else
          @act.allow_execute
      end
      Note By default, up to 1,000 SQL statements can be executed at a time. The replacement code specifies that up to five SQL statements can be executed at a time in the SQLConsole. If the number of SQL statements that you attempt to execute at a time is greater than five, the system does not execute the SQL statements. For more information about DSL syntax for security rules, see DSL syntax for security rules.
  2. Check whether the rule is modified on the SQLConsole tab.
    1. In the left-side navigation pane, click the POC_dev instance and double-click the poc_dev database to go to the SQLConsole tab.Go to the SQLConsole tab of the POC_dev instance
    2. On the SQLConsole tab, enter the following SQL statements in the SQL editor and click Execute on the top of the SQL editor:
      SELECT 1;
      SELECT 2;
      SELECT 3;
      SELECT 4;
      SELECT 5;
      SELECT 6;
      Note An error is returned indicating that a maximum of five SQL statements can be executed at a time.
      Check the number of SQL statements that you are attempting to execute at a time

Configure the maximum number of rows that can be returned per query

  1. Modify the Maximum number of returned rows per query rule.
    1. Go to the details page of a security rule.
    2. On the Details page, click SQLConsole in the left-side navigation pane and then set Checkpoints to Basic Configuration Item.
    3. Find the Maximum number of returned rows per query rule and click Edit in the Actions column.Modify the maximum number of rows that can be returned per query
    4. Set Configuration Value to 3000 and click Submit.Modify the maximum number of rows that can be returned per query
      Note The default value of Configuration Value is 200.
  2. Check whether the rule is modified on the SQLConsole tab.
    1. In the left-side navigation pane, click the POC_dev instance and double-click the poc_dev database to go to the SQLConsole tab.
    2. On the SQLConsole tab, enter the following SQL statement in the SQL editor and click Execute on the top of the SQL editor:
      SELECT * FROM `big_table`;
      Note When you query a large table, the system returns only 3,000 entries. You can submit a data export submit to export more data.
      Check the maximum number of rows that can be required per query

Configure result sets to be editable

  1. Modify the Does the result set support editing? rule.
    1. Go to the details page of a security rule.
    2. On the Details page, click SQLConsole in the left-side navigation pane and then set Checkpoints to Basic Configuration Item.
    3. Find the Does the result set support editing? rule and click Edit in the Actions column.
    4. Turn on Configuration Value and click Submit.
      Note By default, Configuration Value is turned off.
  2. Check whether the rule is modified on the SQLConsole tab.
    1. In the left-side navigation pane, click the POC_dev instance and double-click the poc_dev database to go to the SQLConsole tab.
    2. On the SQLConsole tab, enter the following SQL statement and click Execute on the top of the SQL editor:
      SELECT * FROM `big_table`
       LIMIT 20;
    3. On the Execution... tab below the SQL editor, change data in the result set and click Submit Change.Modify the result set
    4. In the dialog box that appears, click OK. In the Execution Confirmation message, click OK.

Modify the maximum number of rows that a DML statement can affect

  1. Modify the Prohibit full table UPDATE execution and Prohibit DML affects the number of rows exceeding the threshold execution rules.
    1. Go to the details page of a security rule.
    2. On the Details page, click SQLConsole in the left-side navigation pane and then set Checkpoints to Other SQL Criteria.
    3. Find the Prohibit full table UPDATE execution rule and click Disable in the Actions column. In the Prompt message, click OK.
    4. Set Checkpoints to SQL Execution Performance Criteria. Find the Prohibit DML affects the number of rows exceeding the threshold execution rule and click Edit in the Actions column.
    5. Replace the DSL code in the rule with the following code and click Submit:
      if
          @fac.sql_type == 'DML' and @fac.sql_affected_rows > 1000
      then
          @act.reject_execute 'DML influence rows more than threshold 1000, refused to implementation'
      else
          @act.allow_execute
      end
      Note By default, up to 2,000 rows can be affected by a DML statement. The replacement code specifies that up to 1,000 rows can be affected by a DML statement in the SQLConsole. If the number of rows to be affected by a DML statement exceeds 1,000, the system does not execute the DML statement. For more information about DSL syntax for security rules, see DSL syntax for security rules.
    6. Click Enable in the Actions column corresponding to the Prohibit DML affects the number of rows exceeding the threshold execution rule. In the Prompt message, click OK.
  2. Check whether the rule is modified on the SQLConsole tab.
    1. In the left-side navigation pane, click the POC_dev instance and double-click the poc_dev database to go to the SQLConsole tab.
    2. On the SQLConsole tab, enter the following SQL statement in the SQL editor and click Execute on the top of the SQL editor:
      UPDATE `big_table` SET `name` = CONCAT(name,`a`);
      Note When you attempt to execute the DML statement on a large table, an error is returned indicating that the DML statement is to affect more than 1,000 rows and cannot be executed.
      Check the maximum number of rows that a DML statement can affect

Set the query timeout period

  1. Set the query timeout(s) parameter.
    1. In the left-side navigation pane, right-click the POC_dev instance and select Edit Instance.
    2. In the Edit instance dialog box, click Advanced information.
    3. Set query timeout(s) to 10 and click Submit.Set the query timeout(s) parameter
      Note The default value of query timeout(s) is 60.
  2. Check whether the query timeout period is set on the SQLConsole tab.
    1. In the left-side navigation pane, click the POC_dev instance and double-click the poc_dev database to go to the SQLConsole tab.
    2. On the SQLConsole tab, enter the following SQL statement in the SQL editor and click Execute on the top of the SQL editor:
      SELECT COUNT( *)  FROM `big_table` ;
      Note Ten seconds after you execute the SQL statement, the query times out.
      Check whether the query timeout period is set