DocsICP FilingConsole
Documentation
Home Page Data Management Getting Started Solutions for R&D processes SQL Console usage rules

SQL Console usage rules

Updated at:
Copy as MD
Product
My Favorites

Data Management (DMS) provides a powerful SQL Console that simplifies database read and write operations. You can also use security rules to customize query rules for the SQL Console. This topic uses the POC_dev instance as an example to show how to configure several usage rules for the SQL Console. For related configurations, see Prerequisites and Schema design.

Prerequisites

You are an Administrator, a DBA, or a Security Administrator.

Security rule details

  1. Log on to the Data Management (DMS) console.

  2. Move the pointer over the 2023-01-28_15-57-17.png icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Security Rules.

    Note

    If you use the DMS console in normal mode, choose Security and disaster recovery (DBS) > Security Rules in the top navigation bar.

  3. Find the rule set named POC Development Library Rules and click Edit in the Actions column.

Maximum SQL statements per execution

This example shows how to set the maximum number of SQL statements that can be executed at once in the SQL Console to 5.

  1. Configure the security rule.

    1. Go to the security rule details page.

    2. In the left-side navigation pane of the Details page, click SQL Console, and then click SQL Execution Quantity Criteria.

    3. Find the rule named Control the number of SQL statements executed at a time and click Edit in the Actions column.

    4. Replace the existing security rule DSL with the following code and click Submit.

      if
    @fac.sql_count > 5
then
    @act.reject_execute 'The number of SQL statements per execution cannot exceed 5.'
else
    @act.allow_execute
end
      Note

      This security rule DSL specifies that if a user attempts to run more than 5 SQL statements at once in the SQL Console, the system rejects the execution. The default limit is 1,000. If the number of statements is 5 or fewer, the system allows the execution. For more information about the syntax, see Security rule DSL syntax.

  2. Verify the rule in the SQL Console.

    1. In the database instance list, expand the POC_dev instance and double-click the poc_dev database to open the SQL Console.

    2. In the SQL Console window, enter the following statements and click Execute.

      SELECT 1;
SELECT 2;
SELECT 3;
SELECT 4;
SELECT 5;
SELECT 6;
      Note

      When you try to run 6 SQL statements at once, the system blocks the execution and displays a message indicating that the number of SQL statements cannot exceed 5.

Maximum returned rows per query

This example shows how to set the maximum number of rows returned by a single query in the SQL Console to 3,000.

  1. Configure the security rule.

    1. Go to the security rule details page.

    2. In the left-side navigation pane of the Details page, click SQL Console, and then click Basic Configuration Item.

    3. Find the rule named Maximum number of returned rows per query and click Edit in the Actions column.

    4. Set Configuration Content to 3000 and click Submit.

      Note

      The default value is 200.

  2. Verify the rule in the SQL Console.

    1. In the database instance list, expand the POC_dev instance and double-click the poc_dev database to open the SQL Console.

    2. In the SQL Console window, enter the following statement and click Execute.

      SELECT * FROM `big_table`;
      Note

      When you query a large table, the system returns only 3,000 rows from the result set. To view more data, you can submit a data export ticket.

Result set editing

This example shows how to configure a security rule to allow users to edit the result set of a query directly in the SQL Console.

  1. Configure the security rule.

    1. Go to the security rule details page.

    2. In the left-side navigation pane of the Details page, click SQL Console, and then click Basic Configuration Item.

    3. Find the rule named Does the result set support editing? and click Edit in the Actions column.

    4. Turn on the Configuration Content switch and click Submit.

      Note

      This feature is disabled by default.

  2. Verify the rule in the SQL Console.

    1. In the database instance list, expand the POC_dev instance and double-click the poc_dev database to open the SQL Console.

    2. In the SQL Console window, enter the following statement and click Execute.

      SELECT * FROM `big_table` LIMIT 20;

    3. Click Start Editing, modify the target data in the Execution Result area, and then click Submit Change.

    4. In the pop-up window, click Confirm, and then click Execute Directly.

DML affected row threshold

This example shows how to limit the number of DML affected rows in the SQL Console to 1,000.

  1. Configure the security rule.

    1. Go to the security rule details page.

    2. In the left-side navigation pane of the Details page, click SQL Console, and then click Other SQL Criteria.

    3. Find the rule named Prohibit full table UPDATE execution, click Disable in the Actions column, and then click Confirm.

    4. Click SQL Execution Performance Criteria. Find the rule named Prohibit DML execution when the number of affected rows exceeds the threshold and click Edit in the Actions column.

    5. Replace the existing security rule DSL with the following code and click Submit.

      if
    @fac.sql_type == 'DML' and @fac.sql_affected_rows > 1000
then
    @act.reject_execute 'The number of DML affected rows exceeds the threshold of 1000. Execution is rejected.'
else
    @act.allow_execute
end
      Note

      This security rule DSL specifies that if a DML statement affects more than 1,000 rows in the SQL Console, the system rejects the execution. The default limit is 2,000. If the number of affected rows is 1,000 or fewer, the system allows the execution. For more information about the syntax, see Security rule DSL syntax.

    6. For the Prohibit DML execution when the number of affected rows exceeds the threshold rule, click Enable in the Actions column, and then click Confirm.

  2. Verify the rule in the SQL Console.

    1. In the database instance list, expand the POC_dev instance and double-click the poc_dev database to open the SQL Console.

    2. In the SQL Console window, enter the following statement and click Execute.

      UPDATE `big_table` SET `name` = CONCAT(name,`a`);
      Note

      When you try to run a DML statement on a large table, the system blocks the execution and displays a message indicating that the number of DML affected rows exceeds the threshold of 1,000.

Query timeout

This example shows how to set the query timeout in the SQL Console to 10 seconds.

  1. Configure the query timeout.

    1. In the DMS object list, right-click the POC_dev instance and select Edit Instance.

    2. On the Edit Instance page, click Advanced Information.

    3. Set the query timeout to 10 seconds and click Submit.

      Note

      The default value is 60 seconds.

  2. Verify the setting in the SQL Console.

    1. In the database instance list, expand the POC_dev instance and double-click the poc_dev database to open the SQL Console.

    2. In the SQL Console window, enter the following statement and click Execute.

      SELECT COUNT( *)  FROM `big_table` ;
      Note

      When you run a long query, the system terminates it after 10 seconds.

Previous：Operation auditNext：Approve tickets