How to perform regular data change operations? - Data Management

You can change data by entering SQL statements on the SQLConsole tab or submitting a data change ticket in the Data Management (DMS) console. You can also create custom security rules to manage data change operations in databases that reside in different environments. This topic uses four examples to describe how to configure security rules and submit data change tickets in typical scenarios.

Preparations

In the four examples of this topic, the schema design feature of DMS has been used to create the data_modify table in the poc_prod production database and the poc_dev development database that are managed in Security Collaboration mode. You can also create the data_modify table on the SQLConsole tab, without the need to submit tickets to execute DDL statements. For more information, see the Configure the development database to execute DML statements without submitting tickets section of this topic.
SQL create table statement
```
CREATE TABLE `data_modify` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
 `name` varchar(256) NOT NULL COMMENT 'Name',
 `phone` varchar(32) DEFAULT NULL COMMENT 'Phone number',
 `sex` varchar(32) DEFAULT NULL COMMENT 'Gender',
 `email` varchar(256) DEFAULT NULL COMMENT 'Email address',
 `remarks` varchar(1024) DEFAULT NULL COMMENT 'Remarks',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Personal information';
```
The security rule set for the POC_dev instance is Security Rules for POC Development Databases. The security rule set for the POC_prod instance is Security Rules for POC Production Databases.

Submit a ticket to perform regular data change operations

In this example, data is inserted into the data_modify table in the poc_prod database by submitting a ticket.

Log on to the DMS console V5.0.
Move the pointer over the icon in the upper-left corner of the DMS console and choose All Features > Database Development > Data Change > Normal Data Modify.
Note
If you use the DMS console in normal mode, choose Database Development > Data Change > Normal Data Modify in the top navigation bar.
Set the parameters in the ticket. The following information describes some of the parameters.
- SQL text: Enter the correct SQL statement that can be executed. Separate multiple SQL statements with semicolons (;).
- Attachment: Only SQL attachments of the .txt, .zip, and .sql file types can be changed or rolled back. The size of the file cannot exceed 15 MB.
Click Submit.
After you submit a ticket, the system automatically performs a precheck on the ticket. If the ticket fails the precheck, modify the SQL statements as prompted and submit the ticket again.
After the ticket passes the precheck, click Submit for Approval. In the Prompt message, click OK.
Note
By default, data change tickets are approved by DBAs. For more information about how to change the default approval template, see the Change the default approval template section of the "SQL Correct" topic.

After the ticket is approved, click Execute Change. In the Task Settings dialog box, set the parameters as required and click Confirm Execution.

Note

If you set the Execution Method parameter to After Audit Approved, Auto Execute in the Apply step, this step is automatically skipped.
After a suspended task is resumed, the script can be executed from the position at which the script was suspended.

Parameter	Description
Execution Strategy	The time when the data change is executed. Valid values: Running immediately: The data change is immediately executed after the ticket is submitted. This is the default value. Schedule: The data change is executed as scheduled.
Enable Submit as Single Transaction	Specifies whether to control the statements as a single transaction. Default value: off. Valid values: on: If an SQL statement fails, all the executed data manipulation language (DML) statements in the same transaction are rolled back. DDL statements cannot be rolled back. off: The specified SQL statements are executed individually. If an SQL statement fails, the transaction is stopped. The other executed SQL statements in the same transaction are not rolled back.
Enable Backup	Specifies whether to back up data. Default value: on. Valid values: Note Data backup is supported only for `UPDATE` and `DELETE` statements. You cannot back up data for MongoDB and Redis databases. on: DMS generates specific statements to back up the data that is affected when `UPDATE` or `DELETE` statements are executed. For a MySQL or MariaDB database, DMS generates a `REPLACE INTO` statement. Note The supported MySQL database types include ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, and MySQL databases that are not on Alibaba Cloud. If the database is not a MySQL or MariaDB database, DMS generates an `INSERT` statement. off: DMS does not generate statements for data backup.

Optional. After the ticket is successfully executed, go to the SQLConsole tab for the poc_prod database to check whether the data change meets the expected results.

Configure security rules to allow DML statement execution in the development database without tickets required

If tickets are required for every data change operation in development databases, the R&D efficiency of developers decreases. To resolve this issue, DMS allows you to configure security rules that allow DML statements to be executed on the SQLConsole tab without the need to submit tickets. This improves R&D efficiency and ensures data security. The security rules are powered by the security rules engine of DMS.

This example demonstrates executing an INSERT statement in the SQL console.

Modify the security rules for the poc_dev database to POC development database rules.
Note
By default, DMS security rules require DML statements to be executed by using tickets.
1. Log on to the DMS console V5.0 as a DMS administrator.
2. Move the pointer over the icon in the upper-left corner and choose All functions > Security and Specifications > Security Rules.
  Note
  If you use the DMS console in normal mode, choose Security and Specifications > Security Rules in the top navigation bar.
3. On the Security Rules page, click Edit in the Actions column that corresponds to the POC development database rules.
4. In the left-side navigation pane, click SQL Correct. Select SQL execution rules for Checkpoints.
5. Enable All DML can execute directly in SQLConsole and disable All DML must execute by ticket.
Insert data into the data_modify table.
1. In the upper-left corner of the DMS console, search for the poc_dev database instance by entering keywords in the search box.
2. On the SQLConsole tab, enter the following INSERT statements in the SQL editor and click Execute:
```
INSERT INTO data_modify (name, phone, sex) VALUES ('dms_a', '19000001','Male');
INSERT INTO data_modify (name, phone, sex) VALUES ('dms_b', '19000002','Female');
INSERT INTO data_modify (name, phone, sex) VALUES ('dms_c', '19000003','Male');
```
3. In the Execution Confirmation dialog box, click Execute. If an Executed message appears, the security rules are successfully modified.

Configure security rules to allow high-risk SQL statement execution in the poc_prod database with tickets required

You can configure security rules to define the approval processes for different types of SQL statements. For example, security risks may occur after you execute a DELETE statement. Therefore, you must configure a strict approval process to execute DELETE statements.

In this example, the DELETE statement is configured as a high-risk SQL statement and a ticket approval process is specified for executing the DELETE statement.

Configure an approval process.
1. Log on to the DMS console as a DMS administrator.
2. Move the pointer over the icon in the upper-left corner and choose All functions > Security and Specifications > Approval Processes.
  Note
  If you use the DMS console in normal mode, choose Security and Specifications > Approval Processes in the top navigation bar.
3. Click Create Approval Template. On the message that appears, configure the parameters.
  Approval Node: Click Add Node to add approval nodes. You must add the approval nodes in ascending order. For example, the approval nodes whose sequence numbers are 1 and 2 are the first and second approval nodes.
4. After the configuration is complete, click Submit.

Configure security rules.

Move the pointer over the icon in the upper-left corner and choose All functions > Security and Specifications > Security Rules.
Note
If you use the DMS console in normal mode, choose Security and Specifications > Security Rules in the top navigation bar.
On the Security Rules page, click Edit in the Actions column to the right of the POC production database rules.
On the Details page, click the SQL Correct tab in the left-side pane.
Set the Checkpoints parameter to Risk Identification Rules and click Create Rule.

In the Create Rule - SQL Correct dialog box, set the parameters for security rules.

The following information describes some of the parameters.

Parameter

Description

Rule Name

Required. In this example, enter Production environments. The execution of DELETE statements is a high-risk operation.

Rule DSL

Required. In this example, enter the following domain-specific language (DSL) code:

if
    @fac.env_type in ['product','pre']
    and
    @fac.sql_type in
    [ 'DELETE']
then
    @act.mark_risk 'high' 'High-risk SQL statements: DELETE in the production environments'
end

Click Submit.
Find the Production environments. The execution of DELETE statements is a high-risk operation rule and click Enable in the Actions column. In the message that appears, click OK.
After you click OK, the rule takes effect. All DELETE statements are identified as high-risk operations.
Set the Checkpoints parameter to Risk Approval Rules, find the High risk approval process rule and click Edit in the Actions column.
In the Change Rule - SQL Correct dialog box, change the template ID in the Rule DSL field to the ID of the template that you created in Step 1, and click Submit.
Find the High risk approval process rule and click Enable in the Actions column. In the message that appears, click OK.

Execute a DELETE statement.
1. In the upper-left corner of the DMS console, search for the poc_prod database instance by entering keywords in the search box.
2. On the SQLConsole tab, enter the following DELETE statement in the SQL editor and click Execute:
```
DELETE FROM data_modify WHERE id = 1; 
```
3. If the Execution History area displays an error message indicating that the execution failed due to security rules, you can click Apply for Data Change to submit a ticket to execute the DELETE statement.
  Note
  Executing DELETE statements in a production database is a high-risk operation. You must submit a ticket and wait for the approver that is specified in the Approval Template to approve the ticket.

Configure security rules to forbid TRUNCATE statements in the poc_prod database

TRUNCATE statements are executed to clear all data from a table. In most cases, they are not executed in a production database. If executed, they may cause a severe data loss. You can configure security rules to forbid TRUNCATE statements.

This example demonstrates how to forbid the execution of a TRUNCATE statement in the SQL console or by submitting a ticket.

Modify security rules.
1. Log on to the DMS console as a DMS administrator.
2. Move the pointer over the icon in the upper-left corner and choose All functions > Security and Specifications > Security Rules.
  Note
  If you use the DMS console in normal mode, choose Security and Specifications > Security Rules in the top navigation bar.
3. On the Security Rules page, click Edit in the Actions column to the right of the POC production database rules.
4. In the left-side navigation pane, click SQL Correct. Select SQL execution rules for Checkpoints.
5. Find the Allow TRUNCATE to be executed directly in the SQL console rule and click Edit in the Actions column to the right of the rule.
6. Modify the rule name to Forbid TRUNCATE statements. Replace the original DSL syntax with the following DSL syntax, and click Submit.
```
if
    @fac.sql_type in
      ['TRUNCATE']
then
    @act.forbid_execute
end
```
  Note
  The preceding DSL syntax forbids the execution of TRUNCATE statements in the SQL console or by submitting a ticket. For more information about DSL syntax for security rules, see DSL syntax for security rules.
7. Find the TRUNCATE cannot be executed directly in the SQL console. It must be executed as a ticket. rule and click Enable in the Actions column. In the message that appears, click OK.
Execute a TRUNCATE statement on the SQLConsole tab.
1. In the upper-left corner of the DMS console, search for the poc_prod database instance by entering keywords in the search box.
2. On the SQLConsole tab, enter the following TRUNCATE statement in the SQL editor and click Execute:
```
TRUNCATE TABLE `data_modify`;
```
3. Execution Failed is displayed. Due to the security rules, the system forbids the execution of TRUNCATE statements in the SQL console.
Submit a ticket to execute the TRUNCATE statement. For more information, see the Submit a ticket to perform regular data change operations section of this topic.
Note
Specify the following SQL statement for the Change SQL parameter in the data change ticket.
```
TRUNCATE TABLE `data_modify`;
```
After you submit the ticket, confirm the precheck results. If the precheck fails, the security rule which forbids the TRUNCATE statement to be executed by submitting a ticket is in effect.