SQL review optimization engine - Security Management| Alibaba Cloud Documentation Center

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.

Log on to the DMS console.
In the top navigation bar, choose System > Security > Security Rules. 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 Table must have primary key rule and 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 effective range of a rule. 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 the system 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 key rule, the system automatically stops the process.

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	`CREATE TABLE` `ALTER TABLE`	The table must have a primary key. The table must contain remarks. The table cannot contain foreign keys. The case sensitivity of the table name must be restricted. The table storage engine must be restricted. The table cannot be partitioned. The table must contain specified columns. The character set of the table must be restricted. The validation rules of the table must be restricted. The table name cannot be a keyword. The number of indexes in the table must be restricted. The number of fields in the table must be restricted. The initial value of an auto-increment column in the table must be restricted. The table must contain an auto-increment primary key column. The use of views must be restricted. The use of triggers must be restricted. The use of events must be restricted. The use of stored procedures must be restricted. The use of user-defined functions (UDFs) must be restricted. We recommend that you use the `ALTER TABLE CONVERT` syntax to change the character set of the table.
	Column properties	`CREATE TABLE` `ALTER TABLE`	The field name cannot be a keyword. The case sensitivity of the field name must be restricted. The character set of a column cannot be set. The data types that can be used in a column must be restricted. A column must contain comments. The length of a field whose data type is `CHAR` must be restricted. The length of a field whose data type is `VARCHAR` must be restricted. Each column must use the `NOT NULL` clause. The recommended name for an auto-increment column is `ID`. An auto-increment column must contain the UNSIGNED attribute. The `FLOAT` and `DOUBLE` data types cannot be used. We recommend that you replace them with the `DECIMAL` data type. Each column must have a default value. The validation set of a column cannot be set. The columns cannot be renamed when you modify a table. The columns cannot be deleted when you modify a table. The data type cannot be changed when you modify a table. All fields that are added when you modify a table can be empty. The default value must be explicitly specified for a non-empty field that is added when you modify a table. The default value must be explicitly specified when a field that can be left empty is modified as a non-empty field. The `ENUM` data type cannot be used. We recommend that you replace it with the `TINYINT` or `CHAR` data type. A field cannot contain the `ZEROFILL` attribute.
	Index properties	`CREATE TABLE` `ALTER TABLE`	An index must have a name. The naming format of a unique index must be restricted. The naming format of a common index must be restricted. The number of index columns must be restricted. The number of primary key columns must be restricted. The data type of a primary key column must be restricted. The primary key cannot be deleted when you modify a table. The indexes cannot be deleted when you modify a table.
Data query	N/A	`SELECT` `INSERT SELECT` Subquery clauses that are nested in the `UPDATE` or `DELETE` statement	In the `SELECT` statement, we recommend that you use the `WHERE` clause. In the `SELECT` statement, we recommend that you do not use the `ORDER BY RAND()` function. In the `SELECT` statement, we recommend that you do not perform the `GROUP BY` operation on constants. In the `SELECT` statement, we recommend that you do not perform the `ORDER BY` operation on constants. In the `SELECT` statement, we recommend that you do not perform the `GROUP BY` or `ORDER BY` operation on different tables. In the `SELECT` statement, we recommend that you do not perform the `ORDER BY` operation to sort multiple fields in different ordering directions. In the `SELECT` statement, we recommend that you do not use the `GROUP BY` or `ORDER BY` clause in an expression or a function. In the `SELECT` statement, we recommend that you do not use the `UNION` operator. In the `SELECT` statement, the number of tables to be joined is restricted. In the `SELECT` statement, the offset value of the `LIMIT` clause is restricted. In the `SELECT` statement, we recommend that you do not use the `HAVING` clause.
Data change	Data insertion	`INSERT SELECT` `INSERT [IGNORE]` `REPLACE`	In the `INSERT` statement, we recommend that you specify the list of fields that you want to insert data. In the `INSERT` statement, the names of fields that you want to insert data cannot be duplicated. In the INSERT statement, a `NULL` value cannot be inserted into a `NOT NULL` column. In the `INSERT` statement, the list of fields that you want to insert data must match the list of values that you want to insert. In the INSERT statement, the total number of rows in an `INSERT VALUES` clause is restricted. In the `INSERT` statement, the engine checks whether the table or fields that you want to insert data exist. In the INSERT statement, we recommend that you do not use the `SYSDATA()` function.
Data change	Data update and deletion	`UPDATE` `DELETE`	In the `UPDATE` or `DELETE` statement, the number of tables to be joined is restricted. In the `UPDATE` or `DELETE` statement, we recommend that you use the `WHERE` clause. In the `UPDATE` or `DELETE` statement, the engine checks whether the `WHERE` clause includes subqueries. In the `UPDATE` or `DELETE` statement, the data size that is specified by the `LIMIT` clause is restricted. In the `UPDATE` or `DELETE` statement, the engine checks whether the multi-table join syntax is complete, for example, whether the `ON` clause is omitted for the `JOIN` clause. The `UPDATE` or `DELETE` statement cannot include the `ORDER BY` clause. In the `UPDATE` statement, the engine checks whether the delimiters such as `AND` among multiple columns in the `SET` clause are valid. In the `UPDATE` statement, the engine checks whether the table prefix is specified for the columns of multiple tables in the `SET` clause. In the `UPDATE` statement, the engine checks whether the tables or fields that you want to update exist. In the `UPDATE` statement, the engine checks whether the primary key is updated. In the `UPDATE` statement, the engine checks whether the unique key is updated. In the `UPDATE` statement, we recommend that you update the Modify Time column in all tables. In the `UPDATE` statement, we recommend that you do not update the Creation Time column in tables.
SQL `WHERE` clause	N/A	The `SELECT`, `UPDATE`, `DELETE`, or `INSERT SELECT` statement that includes the `WHERE` clause	In the `WHERE` clause, fields to be indexed cannot contain mathematical operators or functions. In the `WHERE` clause, we recommend that you do not use wildcards to search for fields. In the `WHERE` clause, the engine checks whether the `LIKE` clause contains wildcards. In the `WHERE` clause, we recommend that you do not use reverse queries such as `NOT IN` and `NOT LIKE`. In the `WHERE` clause, the number of elements in the `IN` clause is restricted. In the `WHERE` clause, the engine checks whether the data types of fields are implicitly converted. In the `WHERE` clause, the engine checks whether filter conditions are connected by using the `OR` operator.