Data Management (DMS) integrates the SQL review optimization feature into security rules. After you submit SQL statements for data changes or on the SQLConsole tab, DMS reviews the submitted SQL statements based on the specifications in security rules and offers optimization suggestions. This topic shows you how to configure security rules for reviewing SQL statements and perform data changes.

Overview

Security rules contain default SQL review rules. For example, the table must contain remarks, a NULL value cannot be inserted into a NOT NULL column in the INSERT statement, and the names of fields in the INSERT statement cannot be duplicated.

When developers submit SQL statements, the SQL review optimization feature reviews the submitted SQL statements and checks whether the table properties, column properties, index properties, data query rules, and data change rules meet the requirements. This helps database administrators (DBAs) review the submitted SQL statements and improves the quality of R&D. For more information, see SQL review optimization.

Preparations

  • Create a table named migration_job. You can use the following SQL statement to create the table:
    CREATE TABLE `migration_job` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
      `gmt_create` datetime NOT NULL COMMENT 'Creation time',
      `ref_id` bigint(20) unsigned NOT NULL COMMENT '',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Test';
  • If a database instance is managed in Security Collaboration mode, you can create a custom security rule set and associate the instance with the security rule set that you create. For more information, see Create security rules and Apply security rules.

Step 1: Configure security rules

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, click Security and Specifications > Security Rules.
  3. Find the security rule set that you want to modify and click Edit in the Actions column.
    SQL review optimization: Modify a security rule
    Note To configure a security rule set of the Flexible Management or Stable Change mode, click SQL audit optimization recommendations in the Actions column of the control mode.
  4. On the Details page, click the SQL audit optimization recommendations tab in the left-side pane.
  5. Find the rule named The table must have a primary key and click Edit in the Actions column.
    Edit an SQL review rule
    Note You can click the Filter icon icon to the right of Tag, Behavioral action, or Status to filter rules. The Tag parameter specifies the scope to which a rule applies. A rule can apply to DDL and DML statements. The Status parameter specifies whether a rule is enabled or disabled.
  6. In the Rule content configuration dialog box, set the parameters as required. The following table describes the parameters.
    Rule content configuration
    Parameter Description
    Behavioral action The type of action to be performed based on the security rule. In this example, set the Behavioral action parameter to Must Improve.
    Note Default SQL review rules in DMS do not contain Must Improve. For more information about the Behavioral action parameter, see Behavioral actions.
    Remarks The description of the behavioral action for this rule, such as the background information.
  7. Click OK.

    If you use the features such as data development, data change, and SQL review, the SQL review optimization feature verifies SQL statements based on the configured security rules.

Step 2: Change data

After you configure the security rules, submit a ticket to change data.

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Change > Normal Data Modify.
  3. On the Data Change Ticket Application page, set the parameters as required and click Submit.
    Note For more information about the parameters, see Change regular data.
    • Select the database instance that is associated with the security rule set that you have configured.
    • Execute the following SQL statements for data changes:
      CREATE TABLE test1 ( 
          id bigint COMMENT 'id', 
          name varchar(60) COMMENT 'name'
      ) DEFAULT CHARSET = utf8mb4 COLLATE utf8mb4_bin ENGINE = INNODB;
      
      INSERT INTO migration_job(id, ref_id, gmt_create, gmt_create) 
      VALUES(1, null, now(), now());
    Submit a data change ticket
  4. After you submit the ticket, DMS checks whether your configurations are valid based on the security rules that are configured in Step 1.
    The SQL review optimization feature reviews the submitted statements and returns suggestions. In this example, the following suggestions are returned: One item must be improved, two potential issues exist, and one item can be improved.
  5. Click View Details. Move the pointer over the items in the SQL Review column to view details.
    Item 1
  6. Click SQL Statements for Modification. Optimize the SQL statements based on the suggestions and click Confirm Change.
    You can modify the statements by performing the following operations:
    • In the CREATE TABLE statement, add a primary key and remarks for the test1 table.
    • In the INSERT statement, remove the duplicate field named gmt_create and insert values for the ref_id field.
    The following SQL statements are obtained after modification:
    CREATE TABLE test1 (
        id bigint PRIMARY KEY COMMENT 'id',
        name varchar(60) COMMENT  'name'
    ) DEFAULT  CHARSET = utf8mb4 COLLATE  utf8mb4_bin ENGINE = INNODB  COMMENT  = 'Remarks';
    INSERT INTO migration_job(id, ref_id, gmt_create) VALUES(1, 2, now());
  7. DMS reviews the SQL statements again.
    The statements pass the review. The statements pass the review
  8. Click Submit for Approval and wait for approval.
    After approval, a task is generated.
  9. 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.
    Parameter Description
    Execution Strategy Specifies whether to run the task for the ticket immediately or at a scheduled time. Valid values:
    • Running immediately: If you select this option and click Confirm Execution, DMS immediately runs the task.
    • Schedule: If you select this option, you need to specify a time point. After you click Confirm Execution, DMS runs the task at the scheduled time point.
    Transaction Control Specifies whether to enable transaction control. Default value: off. 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: The specified SQL statements are executed one by one. 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.
    Data Backup Specifies whether to back up data. Default value: on. Valid values:
    Note
    • Data backup is supported for only UPDATE and DELETE statements.
    • You cannot back up data for MongoDB or Redis databases.
    • on: DMS generates specific statements to back up the data that is affected when UPDATE or DELETE statements are executed.
      • If the database is a MySQL or MariaDB database, DMS generates a REPLACE INTO statement.
        Note The supported MySQL databases are databases in ApsaraDB RDS for MySQL, PolarDB for MySQL, ApsaraDB MyBase for MySQL, and PolarDB-X 1.0, 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.
    Note The execution of SQL tasks is monitored by the checkpoints that are configured in security rules to control SQL execution. Examples of the checkpoints include Database lock timeout mechanism before SQL execution, Database load check before SQL execution, and sleep policy after SQL execution. To check the checkpoints that are configured in a security rule to control SQL execution, go to the details page of the security rule, and then click SQL execution control in the left-side pane. For more information about how to modify the default checkpoint settings, see Configure the control on SQL execution.
    • After the task is complete, you can click Details in the Actions column of the ticket to view the ticket execution status, number of times the task is run, number of affected rows, executed statements, and logs.
    • After the task is complete, you can go to the SQLConsole tab of the database to check whether the data change meets expectations.

What to do next

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