Customize usage rules for the SQLConsole - Data Management

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 describes usage rules for the SQLConsole by using a POC_dev instance as the operation object. For more information, see Make preparations and Design schemas.

Prerequisites

You are a DMS administrator, a database administrator (DBA), or a security administrator.

Go to the details page of a security rule set

Log on to the DMS console as a DMS administrator.
In the top navigation bar, click Security and Specifications. In the left-side navigation pane, click Security Rules.
Note
If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the DMS console and choose All functions > Security and Specifications > Security Rules.
Find the Security Rules for POC Development Databases rule set and click Edit in the Actions column.

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

In this example, the maximum number of SQL statements that can be executed at a time is configured to 5.

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.
4. Replace the 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.
Check whether the rule is modified on the SQL Console tab.
1. In the left-side instance list, click the POC_dev instance and double-click the poc_dev database to go to the SQL poc_dev tab.
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.

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

In this example, the maximum number of rows that can be returned per query is configured to 3,000.

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.
4. Set the Configuration Value parameter to 3000 and click Submit.
  Note
  The default value of the Configuration Value parameter is 200.
Check whether the rule is modified on the SQLConsole tab.
1. In the left-side instance list, click the POC_dev instance and double-click the poc_dev database to go to the SQL poc_dev tab.
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 * 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.

Configure result sets to be editable

In this example, you can modify SQL result sets by configuring security rules.

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.
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 SQL poc_dev tab.
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 * 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.
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

In this example, the maximum number of rows that can be affected by a DML statement is configured to 1,000.

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.
Check whether the rules are 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 SQL poc_dev tab.
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:
```
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.

Set the query timeout period

In this example, the query timeout period is configured to 10 seconds.

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.
  Note
  The default value of the query timeout(s) parameter is 60.
Check whether the query timeout period takes effect on the SQLConsole tab.
1. In the left-side instance list, click the POC_dev instance and double-click the poc_dev database to go to the SQL poc_dev tab.
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 COUNT( *)  FROM `big_table` ;
```
  Note
  Ten seconds after you execute the SQL statement, the query times out.