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 use the SQL review optimization feature and customize security rules based on this feature.
- You are a database administrator (DBA) or a DMS administrator. For more information, see Manage users.
- A security rule set is configured based on one of the following database engines: MySQL, ApsaraDB RDS for MySQL, PolarDB for MySQL, MariaDB, PolarDB-X, AnalyticDB for MySQL, and ApsaraDB OceanBase for MySQL. For more information, see Create security rules.
DMS predefines three behavioral actions:
- Must Improve: DMS stops the process and returns a message if the developer does not
optimize the SQL statement. Default SQL review rules in DMS do not contain Must Improve.
Note For more information about the check items of SQL review rules, see Check items.
Assume that you have set the behavioral action of the
Table must have primary keyrule to Must Improve. If a developer submits an SQL statement to create a table but does not specify a primary key for the table, DMS stops the execution of the SQL statement and returns a message to request the developer to specify a primary key. After the modified SQL statement passes verification, DMS can execute it.
- Potential Issue: DMS reminds the developer that the SQL statement has potential issues, but does not stop the process.
- Suggest Improve: DMS suggests that the developer optimize the SQL statement, but does not stop the process.
Supported features in different control modes
DMS provides three control modes. For more information, see Control modes.
|Supported item||Security Collaboration||Stable Change||Flexible Management|
|Enable or disable a rule.||Supported||Supported||Supported|
|Set a behavioral action for a rule.||Supported||Not supported||Not supported|
|Modify the parameters of a rule.
You can set parameters for specific rules, such as the number of indexes in a table and the number of fields in a table.
|Enter the description of a behavioral action.
You can enter the background of a behavioral action for a rule.
|Supported||Not supported||Not supported|
Security rules contain default SQL review rules. This example shows you how to customize
a security rule and set the behavioral action of the
Table must have primary key rule to Must Improve.
- 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. Remarks Enter the description of the behavioral action for this rule, such as the background information.
- Click OK.Note Assume that you have configured this SQL review rule. If a developer submits an SQL statement to create a table for an instance or modify a schema but the SQL statement does not comply with the
Table must have primary keyrule, DMS automatically stops the process.
The following table describes the check items that are supported by the SQL review optimization feature.
|Scenario||Check item||Applicable SQL statement||Checkpoint|
|Schema design||Database properties||
||The character set of the database must be restricted.|
|Data change||Data insertion||
|Data update and deletion||