Change data - Data Management - Alibaba Cloud Documentation Center

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.

Usage notes

In the following examples:

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 change data

In this example, a ticket is submitted to execute INSERT statements.

Configure security rules.

Note The default security rules require tickets to be submitted to execute DML statements. For more information, see Create and apply security rules.

Execute INSERT statements.

Log on to the DMS console as a regular user.
In the top navigation bar, choose SQL Console > SQL Console.
In the Please select the database first dialog box, select the poc_prod database from the drop-down list or enter a keyword in the field to search for the database. Then, click Confirm.

On the SQLConsole tab, enter the following SQL statements in the SQL editor and click Execute in the upper part of the SQL editor.

The statements insert three rows of data into the database.

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');

Click Apply for Data Change.

Note The default security rules require data change tickets to be submitted to execute INSERT statements. Therefore, DMS prompts that you must submit a data change ticket.

In the Create a SQL change ticket dialog box, set the parameters that are described in the following table.


Parameter	Description
Reason Category	Required. The reason for the data change.
Business Background	Required. The purpose or objective of the data change.
Stakeholder	Optional. The stakeholders of the data change. All the specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.
Execution Method	Required. The way in which you want the ticket to be submitted for execution. In this example, select the default value After Audit Approved, Order Submitter Execute. Other valid values: After Audit Approved, Auto Execute Last Auditor Execute
SQL Text	The SQL statements to be executed for the data change.
Affected Rows	Required. The estimated number of data rows that will be affected by the data change. To obtain the actual number of affected rows, you can use the `COUNT` function in SQL statements and execute the SQL statements on the SQLConsole tab.
SQL Statements for Rollback	Optional. The SQL statements that can be executed to roll back the data change.

Click OK. In the Submit ticket success message, click the generated ticket number to go to the Ticket Details page.

Note Alternatively, you can choose Database Development > R&D Space > My Tickets in the top navigation pane. DMS prechecks the SQL statement that you want to execute. The precheck items include the syntax of the SQL statement, whether the type of the SQL statement matches the type that you configured in the security rules, the permissions of the user who submitted the ticket, and the estimated number of rows to scan.
After the ticket details are confirmed and the precheck is complete, click Submit for Approval and wait until the approval is complete.
Note
- You can modify the ticket details only before you submit the ticket for approval.
- For more information about how to approve tickets, see Approve a data change ticket.
- In the Approval step, you can click View Approval Details to view the approval progress.

After the ticket is approved, click Execute Change. In the dialog box that appears, set the parameters that are described in the following table


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 enable transaction control. Valid values: on: If an SQL statement fails to be executed, all the executed DML statements in the same transaction are rolled back. DDL statements cannot be rolled back. off: One SQL statement is executed at a time. If an SQL statement fails to be executed, the transaction is stopped. The other executed SQL statements in the same transaction are not rolled back. This is the default value.
Enable Backup	Specifies whether to back up data. Valid values: on: DMS generates REPLACE INTO statements to back up the data that will be affected when `UPDATE` and `DELETE` statements are executed. This helps with data restoration after misoperations. Note The REPLACE INTO statements are stored in an Object Storage Service (OSS) bucket. off: No backup statements are generated.

Click Confirm Execution.
After the data change is executed, you can view the execution information of this ticket, such as operation logs and scheduling logs.

Configure security rules to allow DML statement execution without the need to submit tickets

If tickets are required for every data change operation in development databases, the research and development (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 shows you how to execute INSERT statements in the poc_dev database on the SQLConsole tab.

Configure security rules.
1. Log on to the DMS console as a DMS administrator.
2. In the top navigation bar, choose Security and Specifications > Security Rules.
3. In the search box, enter Security Rules for POC Development Databases. Find the security rule set and click Edit in the Actions column.
4. On the Details page, click the SQL Correct tab in the left-side pane and set the Checkpoints parameter to SQL execution rules.
5. Find the All DML can execute directly in SQLConsole rule and click Edit in the Actions column.
6. Replace the domain-specific language (DSL) code in the rule with the following DSL code and click Submit.
```
if
    @fac.sql_type in
    [ 'UPDATE','DELETE','INSERT','INSERT_SELECT','REPLACE','REPLACE_INTO','MERGE']
    AND
    @fac.env_type not in ['product','pre']
then
    @act.allow_execute_direct
elseif
    @fac.sql_type in
    [ 'UPDATE','DELETE','INSERT','INSERT_SELECT','REPLACE','REPLACE_INTO','MERGE']
    AND
    @fac.env_type in ['product','pre']
then
    @act.allow_submit
end
```
  Note In DMS, you can define the type of environment where a database resides. For example, you can define the product, pre, dev, or test environment for different databases. The preceding DSL code for security rules indicates that you can execute DML statements on databases whose environment type is not product or pre. For other environment types, you must submit tickets to execute DML statements. The DML statements include the UPDATE, DELETE, and INSERT statements. For more information, see DSL syntax for security rules.
7. Enable the All DML can execute directly in SQLConsole rule and disable the opposite rule.
  Note
  
  The opposite rule of the All DML can execute directly in SQLConsole rule is the All DML must execute by ticket rule.
  
  If you enable two rules that have opposite effects at the same time, the rule that imposes strict control on SQL statement execution takes effect. In this example, tickets must be submitted to execute DML statements.
Execute INSERT statements.
1. Log on to the DMS console as a regular user.
2. In the top navigation bar, choose SQL Console > SQL Console.
3. In the Please select the database first dialog box, select the poc_dev database from the drop-down list or enter a keyword in the field to search for the database. Then, click Confirm.
4. On the SQLConsole tab, enter the following SQL statements in the SQL editor and click Execute in the upper part of the SQL editor:
```
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');
```
5. After you click Execute, DMS executes the SQL statements.

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. The following example shows you how to add a data owner and a DBA to the ticket approval process that is used to execute DELETE statements.

Configure an approval process.

Log on to the DMS console as a DMS administrator.
In the top navigation bar, choose Security and Specifications > Approval Processes.
Click Create Approval Template.

In the Create Approval Template dialog box, set the parameters that are described in the following table.


Parameter	Description
Template Name	The name of the approval template.
Remarks	The description of the approval template.
Approval Node	Click Add Node to add one or more approval nodes to the approval template. You must add the approval nodes in ascending order. For example, the approval nodes whose sequence numbers are 0 and 1 are the first and second approval nodes. Note In this example, the ticket is approved by the data owner and then by the DBA.

Click Submit.

Configure security rules.

Log on to the DMS console as a DMS administrator.
In the top navigation bar, choose Security and Specifications > Security Rules.
Find the Security Rules for POC Production Databases rule set and click Edit in the Actions column.
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 that are described in the following table.


Parameter	Description
Checkpoints	Required. In this example, select Risk Identification Rules. DMS also supports the following checkpoints: SQL execution rules Risk Approval Rules Batch Data import rules
Template Database	Optional. DMS provides a large number of security rule templates for different checkpoints. For more information, see SQL Correct. Note In this example, you must enter DSL code in the Rule DSL field.
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 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.
On the Details page, 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.

Note 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 create 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.

Log on to the DMS console as a regular user.
In the top navigation bar, choose SQL Console > SQL Console.
In the Please select the database first dialog box, select the poc_prod database from the drop-down list or enter a keyword in the field to search for the database. Then, click Confirm.
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 `data_modify` ;
```
Note This SQL statement queries data in the data_modify table. After you execute this SQL statement, three data records are returned and displayed below the SQL editor.
On the SQLConsole tab, enter the following SQL statements in the SQL editor and click Execute in the upper part of the SQL editor:
```
DELETE FROM data_modify WHERE id = 1; 
```
Note This SQL statement deletes one row of data.
Click Apply for Data Change.

Note As specified by the configured security rule, the execution of DELETE statements in a production database is a high-risk operation. You must submit a ticket and wait for the data owner and the DBA to approve the ticket.

In the Create a SQL change ticket dialog box, set the parameters that are described in the following table.


Parameter	Description
Reason Category	Required. The reason for the data deletion operation.
Business Background	Required. The purpose or objective of the data deletion operation.
Stakeholder	Optional. The stakeholders of the data deletion operation. All the specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and DBAs are not allowed to view the ticket details.
Execution Method	Required. The way in which you want the ticket to be submitted for execution. In this example, select the default value After Audit Approved, Order Submitter Execute. Other valid values: After Audit Approved, Auto Execute Last Auditor Execute
Affected Rows	Required. The estimated number of data rows that will be affected by the data deletion operation. To obtain the actual number of affected rows, you can use the `COUNT` function in SQL statements and execute the SQL statements on the SQLConsole tab.
SQL Text	Required. The SQL Statements to be executed for the data deletion operation.
SQL Statements for Rollback	Optional. The SQL statements that can be executed to roll back the data deletion operation.

Click OK. In the Submit ticket success message, click the generated ticket number to go to the Ticket Details page.

Note Alternatively, you can choose Database Development > R&D Space > My Tickets in the top navigation pane.
On the Ticket Details page, confirm the ticket details.

Note You can view the ticket details on the Ticket Details page. DMS prechecks the SQL statement that you want to execute. The precheck items include the syntax of the SQL statement, whether the type of the SQL statement matches the type that you configured in the security rules, the permissions of the user who submitted the ticket, and the estimated number of rows to scan.
After the ticket details are confirmed and the precheck is complete, click Submit for Approval and wait until the approval is complete.
Note
- You can modify the ticket details only before you submit the ticket for approval.
- In the Approval Details dialog box, you can view the approval details and progress. In this example, the approval nodes for the data deletion operation are the data owner and the DBA, which are assigned to the same user. For more information about how to approve tickets, see Approve a data change ticket.

After the ticket is approved, click Execute Change. In the dialog box that appears, set the parameters that are described in the following table


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 enable transaction control. Valid values: on: If an SQL statement fails to be executed, all the executed DML statements in the same transaction are rolled back. DDL statements cannot be rolled back. off: One SQL statement is executed at a time. If an SQL statement fails to be executed, the transaction is stopped. The other executed SQL statements in the same transaction are not rolled back. This is the default value.
Enable Backup	Specifies whether to back up data. Valid values: on: DMS generates REPLACE INTO statements to back up the data that will be affected when `UPDATE` and `DELETE` statements are executed, which helps with data restoration after misoperations. This is the default value. Note The REPLACE INTO statements are stored in an OSS bucket. off: No backup statements are generated.

Click Confirm Execution.
After the data change is executed, you can view the execution information of this ticket, such as operation logs and scheduling logs.

Configure security rules to forbid TRUNCATE statements in the poc_prod database

You can configure security rules to define the database types for different types of SQL statements. TRUNCATE statements delete all data from a table, which can cause unexpected data losses. Therefore, the TRUNCATE statements are often forbidden in production databases. You can configure security rules to forbid TRUNCATE statements. This example shows you how to forbid TRUNCATE statements and verify whether TRUNCATE statements are forbidden.

Configure security rules.
1. Log on to the DMS console as a DMS administrator.
2. In the top navigation bar, choose Security and Specifications > Security Rules.
3. Find the Security Rules for POC Production Databases rule set and click Edit in the Actions column.
4. On the Details page, click the SQL Correct tab in the left-side pane and set the Checkpoints parameter to SQL execution rules.
5. Find the Allow TRUNCATE to be executed directly in the SQL console rule and click Edit in the Actions column.
6. Change the rule name to Forbid TRUNCATE statements to be executed, overwrite the original DSL code with the following DSL code, and then click Submit.
```
if
    @fac.sql_type in
      ['TRUNCATE']
then
    @act.forbid_execute
end
```
  Note The preceding DSL code for security rules indicates that TRUNCATE statements cannot be executed on the SQLConsole tab. You must submit tickets to execute TRUNCATE statements. For more information, see DSL syntax for security rules.
7. Find the Forbid TRUNCATE statements to be executed rule and click Enable in the Actions column. In the message that appears, click OK.
Execute a TRUNCATE statement on the SQLConsole tab.
1. Log on to the DMS console as a regular user.
2. In the top navigation bar, choose SQL Console > SQL Console.
3. In the Please select the database first dialog box, select the poc_prod database from the drop-down list or enter a keyword in the field to search for the database. Then, click Confirm.
4. On the SQLConsole tab, enter the following SQL statements in the SQL editor and click Execute in the upper part of the SQL editor:
  This SQL statement deletes all data from the data_modify table.
```
TRUNCATE TABLE `data_modify`;
```
  Note As specified by the configured security rule, DMS forbids TRUNCATE statements.

Submit a ticket to execute the TRUNCATE statement.

Log on to the DMS console as a regular user.
In the top navigation bar, choose Database Development > Data Change > Normal Data Modify.

Set the parameters that are described in the following table and click Submit.


Parameter	Description
Database	Required. In this example, select the `poc_prod` database.
Reason Category	Required. The reason for the data clear operation. This helps you find the ticket in subsequent operations.
Business Background	Required. The purpose or objective of the data clear operation. This reduces unnecessary communication.
Change Stakeholder	Optional. The stakeholders of the data clear operation. All the specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and DBAs are not allowed to view the ticket details.
Execution Method	Required. The way in which you want the ticket to be submitted for execution. In this example, select the default value After Audit Approved, Order Submitter Execute. Other valid values: After Audit Approved, Auto Execute Last Auditor Execute
Affected Rows	Required. The estimated number of data rows that may be affected by the data clear operation. To obtain the actual number of affected rows, you can use the `COUNT` function in SQL statements and execute the SQL statements on the SQLConsole tab.
SQL Statements for Change	Required. You can select Text or Attachment. In this example, select Text.
SQL Text	Required. In this example, enter the following `TRUNCATE` statement: TRUNCATE TABLE `data_modify`;
SQL Statements for Rollback	Optional. The SQL statements that can be executed to roll back the data clear operation.

After you submit the ticket, confirm the precheck results.
If the precheck fails, the security rule takes effect.

Note As specified by the configured security rule, DMS forbids TRUNCATE statements.