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.

  1. 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.
  2. Execute INSERT statements.
    1. Log on to the DMS console as a regular user.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click SQL Console.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose SQLConsole > Single Database Query.
    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.

      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');
    5. 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.
    6. 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 this data change.
      Business Background Required. The purpose or objective of this data change.
      Stakeholder Optional. The stakeholders of this 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
      Affected Rows Required. The estimated number of data rows that will be affected by this 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.
    7. Click OK. DMS automatically generates a ticket. In the Submit ticket success dialog box, click View.
    8. 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.
    9. After the ticket details are confirmed and the precheck is complete, click Submit for Approval and wait until the approval is complete. Submit for Approval
      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.
    10. After the ticket is approved, click Execute Change. In the dialog box that appears, set the parameters that are described in the following table and click Confirm Execution. Set the parameters in the Task Settings dialog box
      Parameter Description
      Execute Immediately Specifies whether to run the task immediately or at a scheduled time. Valid values:
      • Running immediately: DMS runs the task immediately after you submit the task.
      • Schedule: DMS runs the task at the scheduled time that you specify.
      Default value: Running immediately.
      Transaction Control 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.
      Default value: off.
      Data Backup Specifies whether to back up data. Valid values:
      • on: DMS generates INSERT scripts to back up the data that will be affected when UPDATE or DELETE statements are executed.
      • off: No backup scripts are generated.
      Default value: on.
      Note If you set the Execution Method parameter to After Audit Approved, Auto Execute when you create the ticket, this step is automatically skipped.
    11. Enable DMS to run the task for the ticket.

      After the task is run, you can click Details in the Actions column to view operation logs that contain detailed information, such as SQL statements, execution duration, and scheduling details.

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.

  1. Configure security rules.
    1. Log on to the DMS console as a DMS administrator.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click Security and Specifications. In the left-side navigation pane, click Security Rules.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose System > Security > Security Rules.
    3. Find the Security Rules for POC Development 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 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.
  2. Execute INSERT statements.
    1. Log on to the DMS console as a regular user.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click SQL Console.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose SQLConsole > Single Database Query.
    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.

      Each statement inserts one row 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');
    5. In the Execution Confirmation message, click Execute.

      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.

  1. Configure an approval process.
    1. Log on to the DMS console as a DMS administrator.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click Security and Specifications. In the left-side navigation pane, click Approval Processes.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose System > Security > Approval Processes.
    3. On the Approval Processes page, click Create Approval Template.
    4. Set the parameters that are described in the following table and click Submit.
      Parameter Description
      Template Name The name of the approval template.
      Remarks The description of the approval template.
      Approval Node The approval nodes that you want to add to the approval template. To add an approval node to the approval template, click Add Node. You must add the approval nodes in sequence. For example, the approval nodes whose values of the Approval Order parameter 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.

      After you click Submit, the approval template is displayed on the Approval Processes page. In this example, the template ID is 770096.

  2. Configure security rules.
    1. Log on to the DMS console as a DMS administrator.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click Security and Specifications. In the left-side navigation pane, click Security Rules.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose System > Security > Security Rules.
    3. Find the Security Rules for POC Production Databases rule set and click Edit in the Actions column. Modify the Security Rules for POC Production Databases rule set
    4. On the Details page, click the SQL Correct tab in the left-side pane.
    5. Set the Checkpoints parameter to Risk Identification Rules and click Create Rule next to Actions.
    6. In the Create Rule - SQL Correct dialog box, set the parameters that are described in the following table and click Submit.
      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
    7. 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.
    8. Set the Checkpoints parameter to Risk Approval Rules. Find the High risk approval process rule and click Edit in the Actions column.
    9. In the dialog box that appears, replace the template ID in the Rule DSL field with 770096 and click Submit.

      The 770096 template is created in Step 1.

    10. Find the High risk approval process rule and click Enable in the Actions column. In the message that appears, click OK.
  3. Execute a DELETE statement.
    1. Log on to the DMS console as a regular user.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click SQL Console.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose SQLConsole > Single Database Query.
    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 statement in the SQL editor and click Execute in the upper part of the SQL editor.

      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. 

      SELECT * FROM `data_modify` ;
    5. On the SQLConsole tab, enter the following SQL statement in the SQL editor and click Execute in the upper part of the SQL editor.

      This SQL statement deletes one row of data. 

      DELETE FROM data_modify WHERE id = 1;
    6. 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.
    7. 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 this 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 Statements for Rollback Optional. The SQL statements that can be executed to roll back the data deletion operation.
    8. Click OK. DMS automatically generates a ticket. In the Submit ticket success dialog box, click View.
    9. 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.
    10. After the ticket details are confirmed and the precheck is complete, click Submit for Approval and wait until the approval is complete. Submit for Approval
      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.
    11. After the ticket is approved, click Execute Change. In the dialog box that appears, set the parameters that are described in the following table and click Confirm Execution. Set the parameters in the Task Settings dialog box
      Parameter Description
      Execute Immediately Specifies whether to run the task immediately or at a scheduled time. Valid values:
      • Running immediately: DMS runs the task immediately after you submit the task.
      • Schedule: DMS runs the task at the scheduled time that you specify.
      Default value: Running immediately.
      Transaction Control 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.
      Default value: off.
      Data Backup Specifies whether to back up data. Valid values:
      • on: DMS generates INSERT scripts to back up the data that will be affected when UPDATE or DELETE statements are executed.
      • off: No backup scripts are generated.
      Default value: on.
      Note If you set the Execution Method parameter to After Audit Approved, Auto Execute when you create the ticket, this step is automatically skipped.
    12. Enable DMS to run the task for the ticket.

      After the task is run, you can click Details in the Actions column to view operation logs that contain detailed information, such as SQL statements, execution duration, and scheduling details.

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.

  1. Configure security rules.
    1. Log on to the DMS console as a DMS administrator.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click Security and Specifications. In the left-side navigation pane, click Security Rules.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose System > Security > Security Rules.
    3. Find the Security Rules for POC Production Databases rule set and click Edit in the Actions column. Modify the Security Rules for POC Production Databases rule set
    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.
  2. Execute a TRUNCATE statement on the SQLConsole tab.
    1. Log on to the DMS console as a regular user.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click SQL Console.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose SQLConsole > Single Database Query.
    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 statement 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.
  3. Submit a ticket to execute the TRUNCATE statement.
    1. Log on to the DMS console as a regular user.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Change > Normal Data Modify.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose Data Plans > Change > Normal Data Modify.
    3. 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, which helps you find the ticket in subsequent operations.
      Business Background Required. The purpose or objective of this 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 cannot 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 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. 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.
    4. 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.