Data Management (DMS) integrates the SQL review optimization feature into security rules. After you submit SQL statements for data change 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 change.
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.
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 Change the security rules of a database instance.
Step 1: Configure security rules
- Log on to the DMS console.
- In the top navigation bar, move the pointer over the More icon and choose . The Security Rules tab appears.
- Find the security rule set that you want to modify and click Edit in the Actions column.
- In the left-side pane of the Details tab, click the SQL audit optimization recommendations tab.
- Find the rule named
Table must have primary keyand click Edit in the Actions column.Note You can click the icon to the right of Tag, Behavioral action, or Status to filter rules. The Tag parameter specifies the scope within which a rule is effective. A rule can apply to data definition language (DDL) statements and data manipulation language (DML) statements that are used to insert, delete, update, or query data. The Status parameter specifies whether a rule is enabled or disabled.
- In the Rule content configuration dialog box, set the parameters as required. The following table describes the parameters.
Parameter Description Behavioral action 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 Enter the description of the behavioral action for this rule, such as the background information.
- Click OK.
The SQL review optimization feature checks the validity of SQL statements based on the configured security rules during the development process.
Step 2: Change data
After you configure the security rules, submit a ticket to change data.
- Log on to the DMS console.
- In the top navigation bar, move the pointer over the More icon and choose .
- On the Data Change Ticket Application tab, set the parameters as required and click Submit.
Note For more information about the parameters, see Table 1.
- Select the database instance that is associated with the security rule set that you have configured.
- Execute the following SQL statements for data change:
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());
- 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.
- Click View Details. Move the pointer over the items in the SQL Review column to view details.
- 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:
The following SQL statements are obtained after modification:
- In the
CREATE TABLEstatement, add a primary key and remarks for the
- In the
INSERTstatement, remove the duplicate field named
gmt_createand insert values for the
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());
- In the
- DMS reviews the SQL statements again.
The statements pass the review.
- Click Submit for Approval and wait for approval.
After approval, a task is generated.
- Click Execute Change. In the Task Settings dialog box, set the parameters as required and click Confirm Execution. The following table describes the parameters.
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 Execute Immediately Specifies whether to run the task immediately or at a scheduled time.
- Running immediately: DMS immediately runs the task after you submit the task.
- Schedule: DMS runs the task at a specified time.
Transaction Control Specifies whether to enable transaction control.
- on: If an SQL statement fails to be executed, all the executed data manipulation language (DML) statements are rolled back. Data definition language (DDL) statements cannot be rolled back.
- off: The SQL statements are executed one by one. If an SQL statement fails to be executed, the task stops running. The executed statements are not rolled back.
Data Backup Specifies whether to back up data.
- on: DMS generates
INSERTscripts to back up the data that will be affected when
DELETEstatements are executed.
- off: DMS does not generate backup files.