You can change data by entering SQL statements on the SQLConsole tab in the Data Management (DMS) console or submitting a ticket to change data. 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.

Precautions

In the following examples:
  • Security rules for the POC_dev instance are Security Rules for POC Development Databases.
  • Security rules for the POC_prod instance are Security Rules for POC Production Databases.

Submit a ticket to change data

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

  1. Configure security rules.
    Note The default security rules require tickets to be submitted to execute data manipulation language (DML) statements. For more information, see Create and apply security rules.
  2. Execute an INSERT statement.
    1. Log on to the DMS console as a common user.
    2. In the left-side navigation pane, click the POC_prod instance and double-click the poc_prod database to go to the SQL poc_prod tab.
    3. On the SQL poc_prod tab, enter the following SQL statements in the SQL editor and click Execute.

      This statement inserts 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');
    4. 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.
    5. In the Create a SQL change ticket dialog box, set the following parameters.
      Parameter Description
      Reason Category Required. The reason for the data insert operation.
      Business Background Required. The purpose or objective of the data insert operation.
      Stakeholder Optional. The stakeholders can view the ticket details and assist developers in accelerating the approval process. Except for DMS administrators and database administrators (DBAs), users irrelevant to the ticket are not allowed to view the ticket details.
      Execution Method Required. The default value After Audit Approved, Order Submitter Execute is used. DMS also supports the following methods:
      • After Audit Approved, Auto Execute
      • Last Auditor Execute
      Affected Rows Required. The estimated number of data rows that will be affected by the data insert operation. If you want to obtain the actual number of affected rows, run the SQL COUNT function on the SQLConsole tab.
      SQL Statements for Rollback Optional. The SQL statements that you can execute to roll back the data insert operation.
    6. Click OK. DMS automatically generates a ticket. In the Submit ticket success dialog box, click View.
    7. 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 that submitted the ticket, and the estimated number of rows to scan.
    8. After the ticket details are confirmed and the precheck is completed, click Submit for Approval and wait until the approval is complete.Submit for Approval
      Note
      • You can change 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.
    9. After the ticket is approved, click Execute Change. In the dialog box that appears, set the following parameters and click Confirm Execution.Set parameters in the Task Settings dialog box
      Parameter Description
      Execute Immediately Specifies whether to execute the ticket immediately or execute the ticket at the specified point in time. Valid values:
      • Running immediately: After the ticket is submitted, the ticket is executed.
      • Schedule: The ticket is executed at the specified point in time.
      Default value: Running immediately.
      Transaction Control Specifies whether to enable the transaction control. Valid values:
      • on: If an SQL statement fails to be executed, all the other executed SQL statements in the same transaction are rolled back. Only data DML statements can be 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.
    10. Enable DMS to automatically execute the ticket.

      After the ticket is executed, you can click Details in the Actions column to view operations 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 how to execute an INSERT statement in the poc_dev database on the SQLConsole tab.

  1. Configure a security rule.
    1. Log on to the DMS console as an administrator.
    2. In the top navigation bar, choose System > Security > Security Rules. The Security Rules tab appears.
    3. Find Security Rules for POC Development Databases. In the Actions column, click Edit.
    4. In the left-side navigation tree of the Details page, click SQL Correct and SQL execution rules.
    5. Find the All DML can be executed directly in the SQLConsole tab rule and click Edit on the right.
    6. Overwrite the original domain-specific language (DSL) code with the following DSL code. Then, 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 about the DSL syntax for security rules, 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 an INSERT statement.
    1. Log on to the DMS console as a common user.
    2. 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.
    3. On the SQL poc_dev tab, enter the following SQL statements in the SQL editor and click Execute.

      This statement inserts 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');
    4. In the Execution Confirmation message, click Execute.

      After you click Execute, DMS executes the SQL statement.

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 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 an administrator.
    2. In the top navigation bar, choose System > Security > Approval Processes.
    3. On the Approval Processes tab, click Create Approval Template.
    4. Set the following parameters and click Submit.
      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. 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 displyed on the Approval Processes tab. In this example, the template ID is 770096.

  2. Configure a security rule.
    1. Log on to the DMS console as an administrator.
    2. In the top navigation bar, choose System > Security > Security Rules.
    3. Find Security Rules for POC Production Databases. In the Actions column, click Edit.Modify security rules for the poc_prod database
    4. In the left-side navigation tree of the Details page, click SQL Correct.
    5. Click Risk Identification Rules and Create Rule next to Actions.
    6. In the Create Rule - SQL Correct dialog box, set the following parameters and click Submit.
      Parameter Description
      Checkpoints Required. Select Risk Identification Rules. DMS also supports the following checkpoints:
      • SQL execution rules
      • Risk Apporval 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 field next to the Rule DSL parameter.
      Rule Name Required. Enter Production environments. The execution of DELETE statements is a high-risk operation.
      Rule DSL Required. 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 on the right. 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. Click Risk Approval Rules. Find the High risk approval process process and click Edit in the Action column.
    9. In the dialog box that appears, replace the template ID in the field next to the Rule DSL parameter with 770096 and click Submit.

      The 770096 template is created in Step 1.

    10. Find the High risk approval process process and click Enable on the right. In the message that appears, click OK.
  3. Execute a DELETE statement.
    1. Log on to the DMS console as a common user.
    2. In the left-side navigation pane, click the POC_prod instance and double-click the poc_prod database to go to the SQL poc_prod tab.
    3. On the SQL poc_prod tab, enter the following SQL statement in the SQL editor and click Execute.

      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` ;
    4. On the SQL poc_prod tab, enter the following SQL statement in the SQL editor and click Execute.

      This SQL statement deletes one row of data.

      DELETE FROM data_modify WHERE id = 1; 
    5. 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.
    6. In the Create a SQL change ticket dialog box, set the following parameters.
      Parameter Description
      Reason Category Required. The reason for the data delete operation.
      Business Background Required. The purpose or objective of the data deletion operation.
      Stakeholder Optional. The stakeholders can view the ticket details and assist developers in accelerating the approval process. Except for DMS administrators and DBAs, users irrelevant to the ticket are not allowed to view the ticket details.
      Execution Method Required. The default value After Audit Approved, Order Submitter Execute is used. DMS also supports the following methods:
      • 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. If you want to obtain the actual number of affected rows, run the SQL COUNT function on the SQLConsole tab.
      SQL Statements for Rollback Optional. The SQL statements that you can execute to roll back the data deletion operation.
    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 that submitted the ticket, and the estimated number of rows to scan.
    9. After the ticket details are confirmed and the precheck is completed, click Submit for Approval and wait until the approval is complete.Submit for Approval
      Note
      • You can change 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.
    10. After the ticket is approved, click Execute Change. In the dialog box that appears, set the following parameters and click Confirm Execution.Set parameters in the Task Settings dialog box
      Parameter Description
      Execute Immediately Specifies whether to execute the ticket immediately or execute the ticket at the specified point in time. Valid values:
      • Running immediately: After the ticket is submitted, the ticket is executed.
      • Schedule: The ticket is executed at the specified point in time.
      Default value: Running immediately.
      Transaction Control Specifies whether to enable the transaction control. Valid values:
      • on: If an SQL statement fails to be executed, all the other executed SQL statements in the same transaction are rolled back. Only data DML statements can be 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 automatically execute the ticket.

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

Configure security rules to forbid the TRUNCATE statement 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 the security rules to forbid TRUNCATE statements. This example shows how to forbid TRUNCATE statements and verify whether TRUNCATE statements are forbidden.

  1. Configure a security rule.
    1. Log on to the DMS console as an administrator.
    2. In the top navigation bar, choose System > Security > Security Rules.
    3. Find Security Rules for POC Production Databases. In the Actions column on the right, click Edit.Modify security rules for the poc_prod database
    4. In the left-side navigation tree of the Details page, click SQL Correct and click SQL execution rules.
    5. Find the Allow TRUNCATE to be executed directly in the SQL console rule and click Edit on the right.
    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 about the DSL syntax for security rules, see DSL syntax for security rules.
    7. Find the Forbid TRUNCATE statements to be executed rule and click Enable on the right. 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 common user.
    2. In the left-side navigation pane, click the POC_prod instance and double-click the poc_prod database to go to the SQL poc_prod tab.
    3. On the SQL poc_prod tab, enter the following SQL statement in the SQL editor and click Execute.

      This SQL statement deletes specific 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 common user.
    2. In the top navigation bar, choose Data Plans > Data Changes > Normal Data Modify.
    3. Set the following parameters and click Submit.
      Parameter Description
      Database Required. 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 the data clear operation, which helps accelerate the approval process.
      Change Stakeholder Optional. The stakeholders can view the ticket details and assist developers in accelerating the approval process. Except for DMS administrators and DBAs, users irrelevant to the ticket are not allowed to view the ticket details.
      Execution Method Required. The default value After Audit Approved, Order Submitter Execute is used. DMS also supports the following methods:
      • After Audit Approved, Auto Execute
      • Last Auditor Execute
      Affected Rows Required. The estimated number of data rows that will be affected by the data change. If you want to obtain the actual number of affected rows, run the SQL COUNT function on the SQLConsole tab.
      SQL Statements for Change Required. Enter the following TRUNCATE statement:
      TRUNCATE TABLE `data_modify`;
      SQL Statements for Rollback Optional. The SQL statements that you can execute to roll back the data clear operation.
    4. After you submit the ticket, confirm the precheck results. If the precheck fails, the security rule is in effect.
      Note As specified by the configured security rule, DMS forbids TRUNCATE statements.