SQL Review Rules and Enforcement Actions Overview - Data Management

SQL review optimization enforces SQL quality standards before a change reaches your database. When a developer submits SQL statements for a data change or on the SQLConsole tab, DMS checks each statement against your security rule configuration and returns feedback — blocking execution, flagging issues, or suggesting improvements depending on the rule severity you configure.

Prerequisites

Before you begin, ensure that you have:

The DBA or DMS administrator role — see Manage users
A security rule set configured — see Manage security rules
One of the following database engines:
- MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, ApsaraDB for MariaDB, PolarDB for Xscale, AnalyticDB for MySQL, and third-party MySQL databases
- Oracle
- PolarDB for PostgreSQL (Compatible with Oracle)
- OceanBase

Behavioral actions

Each SQL review rule has a behavioral action that controls what DMS does when a statement violates the rule. DMS provides three behavioral actions:

Behavioral action	Effect	Blocks execution
Must Improve	DMS stops the process. The SQL statement cannot proceed until the violation is fixed.	Yes
Potential Issue	DMS flags the violation but does not stop the process.	No
Suggest Improve	DMS suggests an optimization but does not stop the process.	No

Default SQL review rules in DMS do not include Must Improve. To use it, configure the behavioral action manually — see Configure a security rule.

Supported operations by control mode

DMS supports three control modes. The operations available for SQL review optimization differ by control mode. For details, 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	Supported	Supported	Not supported
Enter the description of a behavioral action	Supported	Not supported	Not supported

Rule parameters are configurable only in Security Collaboration and Stable Change modes. Examples include the maximum number of indexes or fields allowed in a table.

Configure a security rule

Security rule sets include default SQL review rules. The following example shows how to set the behavioral action of the The table must have a primary key rule to Must Improve, so that DMS blocks any SQL statement that creates a table without a primary key.

Log on to the DMS console V5.0.
Move the pointer over the icon in the upper-left corner and choose All Features > Security and Specifications (DBS) > Security Rules.
In normal mode, choose Security and Specifications (DBS) > Security Rules in the top navigation bar.
Find the security rule set you want to modify and click Edit in the Actions column.
For Flexible Management or Stable Change rule sets, click SQL audit optimization recommendations in the Actions column instead of Edit.
In the left-side pane of the Details page, click the SQL audit optimization recommendations tab.
Find the The table must have a primary key rule and click Edit in the Actions column.
Use the icon next to Tag, Behavioral action, or Status to filter the rule list. Tag indicates the scope of a rule (DDL or DML). Status indicates whether a rule is enabled or disabled.
In the Rule content configuration dialog box, configure the following parameters.
Parameter Description
Behavioral action Select Must Improve.
Remarks Enter background information about this behavioral action, such as why the rule is required.
Click OK.

Parameter	Description
Behavioral action	Select Must Improve.
Remarks	Enter background information about this behavioral action, such as why the rule is required.

If you use 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 The table must have a primary key rule, DMS stops the process.

Check items

The following tables list all rules available in the SQL review and SQL review optimization features.

SQL review

Schema design

Check item	Applicable SQL statement	Rules
Database properties	`CREATE DATABASE`	The character set used to create the database must be restricted. (`CREATE_DATABASE_LIMIT_CHARSET`)
Table properties	`CREATE TABLE`, `ALTER TABLE`	- The table must have a primary key. (`TABLE_MUST_HAVE_PRIMARY_KEY`) - The table must contain remarks. (`TABLE_MUST_HAVE_COMMENTS`) - The table cannot contain foreign keys. (`TABLE_FORBID_USE_FOREIGN_KEY`) - The case sensitivity of the table name must be restricted. (`TABLE_NAME_LIMIT_CHAR_CASE`) - The table storage engine must be restricted. (`TABLE_LIMIT_STORE_ENGINE`) - The table cannot be partitioned. (`TABLE_FORBID_USE_PARTITION`) - The table must contain specific columns. (`TABLE_MUST_HAVE_SOME_COLUMN`) - The character set of the table must be restricted. (`TABLE_MUST_USE_SOME_CHARSET`) - The validation rules of the table must be restricted. (`TABLE_MUST_USE_SOME_COLLATION`) - The table name cannot be a keyword. (`TABLE_NAME_FORBID_KEYWORD`) - The number of indexes in the table must be restricted. (`TABLE_LIMIT_INDEX_COUNT`) - The number of fields in the table must be restricted. (`TABLE_LIMIT_COLUMN_COUNT`) - The initial value of an auto-increment column in the table must be restricted. (`TABLE_LIMIT_INIT_AUTO_INCREMENT`) - The table must contain an auto-increment primary key column. (`LIMIT_PRIMARY_COLUMN_AUTO_INCREMENT`) - The use of views must be restricted for the table. (`TABLE_FORBID_USE_VIEW`) - The use of triggers must be restricted for the table. (`TABLE_FORBID_USE_TRIGGER`) - The use of events must be restricted for the table. (`TABLE_FORBID_USE_EVENT`) - The use of stored procedures must be restricted for the table. (`TABLE_FORBID_USE_STORED_PROCEDURE`) - The use of user-defined functions (UDFs) must be restricted for the table. (`TABLE_FORBID_USE_CUSTOM_FUNCTION`) - Use the `ALTER TABLE CONVERT` syntax to change the character set of the table. (`MODIFY_CHARSET_USE_ALTER_TABLE_CONVERT`)
Column properties	`CREATE TABLE`, `ALTER TABLE`	- The field name cannot be a keyword. (`COLUMN_NAME_FORBID_KEYWORD`) - The case sensitivity of the field name must be restricted. (`COLUMN_NAME_LIMIT_CHAR_CASE`) - The character set of a column cannot be specified. (`COLUMN_FORBID_SET_CHARSET`) - Specific data types cannot be used in a column. (`COLUMN_FORBID_DATA_TYPES`) - A column must contain comments. (`COLUMN_MUST_HAVE_COMMENTS`) - The length of a `CHAR` field must be restricted. (`COLUMN_LIMIT_CHAR_LENGTH`) - The length of a `VARCHAR` field must be restricted. (`COLUMN_LIMIT_VARCHAR_LENGTH`) - Each column must use the `NOT NULL` clause. (`COLUMN_MUST_SET_NOT_NULL`) - The recommended name for an auto-increment column is `ID`. (`COLUMN_AUTO_INCREMENT_NAME_ID`) - An auto-increment column must contain the UNSIGNED attribute. (`COLUMN_AUTO_INCREMENT_UNSIGNED`) - The `FLOAT` and `DOUBLE` data types cannot be used. Replace them with the `DECIMAL` data type. (`COLUMN_FORBID_FLOAT_DOUBLE_TYPE`) - Each column must have a default value. (`EACH_COLUMN_NEED_DEFAULT_VALUE`) - The collation of a column cannot be specified. (`COLUMN_FORBID_SET_COLLATE`) - Columns cannot be renamed when you modify a table. (`ALTER_TABLE_FORBID_RENAME_COLUMN`) - Columns cannot be deleted when you modify a table. (`ALTER_TABLE_FORBID_DROP_COLUMN`) - The data type cannot be changed when you modify a table. (`ALTER_TABLE_FORBID_MODIFY_DATA_TYPE`) - All fields added when you modify a table can be empty. (`ALTER_TABLE_ADD_COLUMN_NULLABLE`) - A default value must be specified for a non-empty field added when you modify a table. (`ALTER_ADD_NOT_NULL_COLUMN_NEED_DEFAULT`) - A default value must be specified when a nullable field is modified to non-nullable. (`COLUMN_NULLABLE_TO_NOT_NEED_DEFAULT`) - The `ENUM` data type cannot be used. Replace it with `TINYINT` or `CHAR`. (`COLUMN_FORBID_USE_ENUM_TYPE`) - A field cannot contain the `ZEROFILL` attribute. (`COLUMN_DATA_TYPE_FORBID_ZEROFILL`)
Index properties	`CREATE TABLE`, `ALTER TABLE`	- An index must have a name. (`INDEX_MUST_HAVE_CLEARLY_NAME`) - The naming format of a unique index must be restricted. (`UNIQUE_INDEX_NAME_PATTERN`) - The naming format of a regular index must be restricted. (`COMMON_INDEX_NAME_PATTERN`) - The number of index columns must be restricted. (`INDEX_LIMIT_CONTAINS_COLUMNS`) - The number of primary key columns must be restricted. (`PRIMARY_LIMIT_CONTAINS_COLUMNS`) - The data type of a primary key column must be restricted. (`PRIMARY_LIMIT_COLUMN_DATA_TYPE`) - The primary key cannot be deleted when you modify a table. (`ALTER_TABLE_FORBID_DROP_PRIMARY`) - Indexes cannot be deleted when you modify a table. (`ALTER_TABLE_FORBID_DROP_INDEX`)

Data query

Check item	Applicable SQL statement	Rules
`SELECT` statement	`SELECT`, `INSERT SELECT`, subqueries in `UPDATE` or `DELETE`	- Use the `WHERE` clause in `SELECT` statements. (`SELECT_SUGGEST_ASSIGN_WHERE`) - Do not use the `ORDER BY RAND()` function. (`SELECT_FORBID_USE_ORDER_BY_RAND`) - Do not use `GROUP BY` on constants. (`SELECT_FORBID_GROUP_BY_CONST`) - Do not use `ORDER BY` on constants. (`SELECT_FORBID_ORDER_BY_CONST`) - Do not use `GROUP BY` or `ORDER BY` on columns from different tables. (`SELECT_FORBID_GROUP_ORDER_BY_DISTINCT_TABLE`) - Do not sort multiple fields in different ordering directions with `ORDER BY`. (`SELECT_FORBID_ORDER_BY_MULTI_COLUMN_RANK`) - Do not use `GROUP BY` or `ORDER BY` inside an expression or function. (`SELECT_FORBID_GROUP_ORDER_BY_EXPR_OR_FUNCTION`) - Do not use the `UNION` operator. (`SELECT_FORBID_USE_UNION`) - The number of tables in a join is restricted. (`SELECT_LIMIT_TABLE_JOIN_COUNT`) - The offset value of the `LIMIT` clause is restricted. (`SELECT_CONFINE_LIMIT_MAX_OFFSET`) - Do not use the `HAVING` clause. (`SELECT_FORBID_USE_HAVING`)
`WHERE` clause	`SELECT`, `UPDATE`, `DELETE`, or `INSERT SELECT` with a `WHERE` clause	- Indexed fields in the `WHERE` clause cannot contain mathematical operators or functions. (`WHERE_FORBID_INDEX_COLUMN_HAS_MATH`) - Do not use leading wildcards to search for fields. (`WHERE_FORBID_BEFORE_WILDCARD_SEARCH`) - DMS checks whether the `LIKE` clause contains wildcards. (`WHERE_CHECK_LIKE_HAS_NOT_WILDCARD`) - Do not use reverse queries such as `NOT IN` and `NOT LIKE`. (`WHERE_FORBID_USE_REVERSE_SEARCH`) - The number of elements in the `IN` clause is restricted. (`WHERE_LIMIT_IN_ITEM_MAX_COUNT`) - DMS checks whether field data types are implicitly converted. (`WHERE_CHECK_COLUMN_IMPLICIT_TYPE_CONVERSION`) - DMS checks whether filter conditions are connected with the `OR` operator. (`WHERE_CHECK_OR_LINK_CONDITION`)

Data change

Check item	Applicable SQL statement	Rules
Data insertion	`INSERT SELECT`, `INSERT [IGNORE]`, `REPLACE`	- Specify the list of fields when inserting data. (`ASSIGN_INSERT_COLUMN_NAME_LIST`) - Field names in the `INSERT` statement cannot be duplicated. (`INSERT_COLUMN_NAME_FORBID_DUPLICATE`) - A `NULL` value cannot be inserted into a `NOT NULL` column. (`NOT_NULL_COLUMN_FORBID_INSERT_NULL`) - The field list and value list in the `INSERT` statement must match. (`INSERT_COLUMN_MUST_MATCH_VALUES`) - The total number of rows in an `INSERT VALUES` clause is restricted. (`LIMIT_INSERT_VALUES_TOTAL_ROWS`) - DMS checks whether the target table or field exists. (`INSERT_CHECK_TABLE_COLUMN_EXISTS`) - Do not use the `SYSDATE()` function in `INSERT` statements. (`INSERT_FORBID_USE_SYSDATE_FUNCTION`)
Data update and deletion	`UPDATE`, `DELETE`	- The number of tables in a join is restricted. (`UPDELETE_LIMIT_TABLE_JOIN_COUNT`) - Use the `WHERE` clause in `UPDATE` or `DELETE` statements. (`UPDELETE_ASSIGN_WHERE_CONDITION`) - DMS checks whether the `WHERE` clause contains subqueries. (`UPDELETE_CHECK_WHERE_EXIST_SUB_QUERY`) - The data size specified by the `LIMIT` clause is restricted. (`UPDELETE_CHECK_LIMIT_AFFECTED_ROWS`) - DMS checks whether the join syntax is complete (e.g., the `ON` clause is not omitted for a `JOIN`). (`UPDELETE_CHECK_TABLE_JOIN_LOSS_ON`) - `UPDATE` and `DELETE` statements cannot contain the `ORDER BY` clause. (`UPDELETE_FORBID_ORDER_BY`) - DMS checks whether the delimiters among multiple columns in the `SET` clause are valid. (`UPDATE_CHECK_SET_ITEM_DELIMITER`) - DMS checks whether the table prefix is specified for columns of multiple tables in the `SET` clause. (`UPDATE_MULTI_TABLE_CHECK_SET_COLUMN_PREFIX`) - DMS checks whether the target tables or fields exist. (`UPDATE_CHECK_TABLE_COLUMN_EXIST`) - DMS checks whether the primary key is updated. (`UPDATE_CHECK_PRIMARY_KEY_CHANGE`) - DMS checks whether the unique key is updated. (`UPDATE_CHECK_UNIQUE_KEY_CHANGE`) - Update the Modify Time column when updating other columns. (`UPDATE_ALSO_TO_UPDATE_MODIFY_TIME_COLUMN`) - Do not update the Creation Time column. (`UPDATE_FORBID_MODIFY_CREATE_TIME_COLUMN`)

SQL review optimization

Scenario Rules

N/A - The table whose primary key uses the INT data type needs to be optimized. (OPTIMIZE_PRIMARY_IS_INT_TABLE)
- The auto-increment primary key column needs sufficient space. (SNIFFING_AUTO_PRIMARY_REMAIN_SPACE)
- The value of the unique index cannot be empty. (SNIFFING_UNIQUE_EXIST_NULL_RISK)
- The DDL statement needs to be optimized due to the risk of a data change failure or a table lock. (ALTER_LOCK_FAIL_SNIFFING_OPTIMIZE)
- SQL injection risks need to be detected. (CHECK_SQL_INJECTION_RISK)
- Force index risks need to be detected. (CHECK_SQL_ASSIGN_FORCE_INDEX)
- Indexes need to be checked in SQL execution plans. (SQL_EXPLAIN_INDEX_CHECK)
- DMS needs to analyze indexes and provide suggestions. (DMS_INDEX_ANALYZE_AND_SUGGEST)

Scenario	Rules
N/A	- The table whose primary key uses the `INT` data type needs to be optimized. (`OPTIMIZE_PRIMARY_IS_INT_TABLE`) - The auto-increment primary key column needs sufficient space. (`SNIFFING_AUTO_PRIMARY_REMAIN_SPACE`) - The value of the unique index cannot be empty. (`SNIFFING_UNIQUE_EXIST_NULL_RISK`) - The DDL statement needs to be optimized due to the risk of a data change failure or a table lock. (`ALTER_LOCK_FAIL_SNIFFING_OPTIMIZE`) - SQL injection risks need to be detected. (`CHECK_SQL_INJECTION_RISK`) - Force index risks need to be detected. (`CHECK_SQL_ASSIGN_FORCE_INDEX`) - Indexes need to be checked in SQL execution plans. (`SQL_EXPLAIN_INDEX_CHECK`) - DMS needs to analyze indexes and provide suggestions. (`DMS_INDEX_ANALYZE_AND_SUGGEST`)