An SQL review optimization engine is integrated into the security rules of Data Management (DMS). After you submit SQL statements for data change or schema design, DMS can review the submitted SQL statements and offer optimization suggestions. This topic shows you how to use the SQL review optimization engine and customize security rules based on the engine.
Prerequisites
- You are a database administrator (DBA) or a DMS administrator. For more information, see User management.
- A security rule set is created for MySQL databases. For more information, see Create a security rule.
Background information
Database change is essential to business development. Different developers have different technical skills for database development and the quality of SQL statements depends on their technical levels. In this case, the SQL statements must be reviewed.
To better perform manual review, experienced DBAs have formulated a series of schema research and development (R&D) specifications based on business needs and made the developers understand the specifications by means of oral presentations or training. However, the DBAs must repeat a large amount of review work every day and formulate more and more specifications. This increases the cost of manual review.
To resolve this issue, the SQL review optimization engine is supported in DMS and is closely integrated with processes such as schema design and data change. After a developer submits an SQL statement, the SQL review optimization engine checks whether the SQL statement conforms to the R&D specifications and assists the DBAs in reviewing the SQL statement. This way, the R&D quality can be improved. The R&D specifications cover items such as table properties, column properties, index properties, data query, and data change.
Behavioral actions
DMS predefines three behavioral actions:
- Must Improve: The engine stops the process and returns a message if the developer
does not optimize the SQL statement. Default SQL review rules in the system 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 key
rule to Must Improve. If a developer submits an SQL statement to create a table but does not specify a primary key for the table, the SQL review optimization engine 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 the verification, the system can execute it. - Potential Issue: The engine reminds the developer that the SQL statement has potential issues, but does not stop the process.
- Suggest Improve: The engine 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 specify parameters for part of rules, such as the number of indexes in a table and the number of fields in a table. |
Supported | Supported | Not supported |
Enter the description of a behavioral action.
You can enter the background of a behavioral action for a rule. |
Supported | Not supported | Not supported |
Procedure
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.
Check items
The following table describes the check items that are supported by the SQL review optimization engine.
Scenario | Check item | Applicable SQL statement | Checkpoint |
---|---|---|---|
Schema | Database properties | CREATE DATABASE |
The character set of the database must be restricted. |
Table properties |
|
|
|
Column properties |
|
|
|
Index properties |
|
|
|
Data query | N/A |
|
|
Data change | Data insertion |
|
|
Data update and deletion |
|
|
|
SQL WHERE clause
|
N/A | The SELECT , UPDATE , DELETE , or INSERT SELECT statement that includes the WHERE clause
|
|