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

Usage notes

  • In the following examples, a DMS 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 information about 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 set

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

    The Security Rules tab appears.

  3. Find the Security Rules for POC Development Databases rule set and click Edit in the Actions column.
    Note For more information about how to create security rules, see Create security rules.

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 set.
    2. On the Details page, click the SQL Console tab in the left-side pane and set the Checkpoints parameter 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 domain-specific language (DSL) code in the rule with the following DSL 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, DMS does not execute the SQL statements. For more information, see DSL syntax for security rules.
  2. Check whether the rule is modified on the SQLConsole tab.
    1. In the instance list, 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 database
    2. On the SQLConsole tab, enter the following SQL statements in the SQL editor and click Execute in the upper part of the SQL editor:
      SELECT 1;
      SELECT 2;
      SELECT 3;
      SELECT 4;
      SELECT 5;
      SELECT 6;
      Note An error is returned to remind you that a maximum of five SQL statements can be executed at a time.
      Check the number of SQL statements that you can 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 set.
    2. On the Details page, click the SQL Console tab in the left-side pane and set the Checkpoints parameter 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 the Configuration Value parameter to 3000 and click Submit. Modify the maximum number of rows that can be returned per query
      Note The default value of the Configuration Value parameter is 200.
  2. Check whether the rule is modified on the SQLConsole tab.
    1. In the instance list, 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 in the upper part of the SQL editor:
      SELECT * FROM `big_table`;
      Note When you query a large table, DMS returns only a maximum of 3,000 entries. You can submit a data export ticket to export more data.
      Check the maximum number of rows that can be returned 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 set.
    2. On the Details page, click the SQL Console tab in the left-side pane and set the Checkpoints parameter 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 instance list, 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 in the upper part of the SQL editor:
      SELECT * FROM `big_table`
       LIMIT 20;
    3. On the execution result 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 can be affected by a DML statement

  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 set.
    2. On the Details page, click the SQL Console tab in the left-side pane and set the Checkpoints parameter 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 the Checkpoints parameter 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 DSL code and click Submit.
      if
          @fac.sql_type == 'DML' and @fac.sql_affected_rows > 1000
      then
          @act.reject_execute 'If a DML statement may affect more than 1,000 rows of data, DMS refuses to execute the DML statement.'
      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, DMS does not execute the DML statement. For more information, see DSL syntax for security rules.
    6. Find the Prohibit DML affects the number of rows exceeding the threshold execution rule and click Enable in the Actions column. In the Prompt message, click OK.
  2. Check whether the rules are modified on the SQLConsole tab.
    1. In the instance list, 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 in the upper part 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 to remind you that the DML statement may affect more than 1,000 rows and cannot be executed.
      Check the maximum number of rows that can be affected by a DML statement

Set the query timeout period

  1. Set the query timeout(s) parameter.
    1. In the instance list, right-click the POC_dev instance and select Edit.
    2. In the Edit dialog box, click Advanced Information.
    3. Set the query timeout(s) parameter to 10 and click Submit. Set the query timeout(s) parameter
      Note The default value of the query timeout(s) parameter is 60.
  2. Check whether the query timeout period takes effect on the SQLConsole tab.
    1. In the instance list, 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 in the upper part 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 takes effect