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 can help you review SQL statements and prevent invalid SQL statements before you perform a data change. This can also prevent business from being affected.

Prerequisites

  • 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 types: 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.

Behavioral actions

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 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, 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.

Operation 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.

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.

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the More icon and choose System > Security Rules. The Security Rules tab appears.
  3. Find the security rule set that you want to modify and click Edit in the Actions column.
    SQL review optimization: Edit a security rule
  4. In the left-side pane of the Details page, click the SQL audit optimization recommendations tab.
  5. Find the rule named Table must have 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 within which a rule is effective. A rule can apply to data definition language (DDL) statements and data manipulation language (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 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.
  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.

    In this example, if an SQL statement does not comply with the Table must have primary key rule, DMS stops the process.

Check items

  • The following table describes the check items that are supported by the SQL review feature.
    Scenario Check item Applicable SQL statement Checkpoint
    Schema design 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 specific 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.
    • Specific data types cannot be used in a column.
    • 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 regular 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 where you want to insert data.
    • In the INSERT statement, the names of fields where 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 where 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, DMS checks whether the table or field where you want to insert data exists.
    • In the INSERT statement, we recommend that you do not use the SYSDATA() function.
    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, DMS 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, DMS checks whether the syntax that is used to join multiple tables is complete. For example, DMS checks 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, DMS checks whether the delimiters such as AND among multiple columns in the SET clause are valid.
    • In the UPDATE statement, DMS checks whether the table prefix is specified for the columns of multiple tables in the SET clause.
    • In the UPDATE statement, DMS checks whether the tables or fields that you want to update exist.
    • In the UPDATE statement, DMS checks whether the primary key is updated.
    • In the UPDATE statement, DMS checks whether the unique key is updated.
    • In the UPDATE statement, we recommend that you also update the Modify Time column in 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, DMS 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, DMS checks whether the data types of fields are implicitly converted.
    • In the WHERE clause, DMS checks whether filter conditions are connected by using the OR operator.
  • The following table describes the check items that are supported by the SQL review optimization feature.
    Scenario Checkpoint
    N/A
    • The table whose primary key uses the INT data type needs to be optimized.
    • The auto-increment primary key column needs sufficient space.
    • The value of the unique index cannot be empty.
    • The DDL statement needs to be optimized due to the risk of a data change failure or a table lock.
    • SQL injection risks need to be detected.
    • Force index risks need to be detected.
    • Indexes need to be checked in SQL execution plans.
    • DMS needs to analyze indexes and provide suggestions.