Data Management:Configure SQL review and optimization suggestions

System actions

DMS provides three predefined system actions:

• must be improved: If you do not improve the statement, the system automatically blocks the process and displays a notification. The initial SQL review rules do not include any rules set to must be improved.

  Note

  For a list of checks performed by the SQL review rules, see Check items.

  For example, if the Tables must have a primary key rule is set to must be improved, the system will block the execution of an SQL statement that creates a table without a primary key and require the developer to modify the statement to pass validation before execution.

• Potential issue: This indicates an issue with the SQL statement, but it does not block the process.

• Suggested improvement: An improvement is suggested, but it does not block the process.

Feature support in different control modes

DMS offers three different control modes. For more information, see Control Modes.

Procedure

By default, each security rule includes a set of SQL review rules. This example shows you how to customize the configuration by setting the A table must have a primary key rule to must be improved.

1. Log in to DMS 5.0.

2. Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Security Rules.

   Note

   If you use the DMS console in normal mode, choose Security and disaster recovery (DBS) > Security Rules in the top navigation bar.

3. In the Actions column for the target security rule, click Edit.

   Note

   To configure settings for the Flexible Management or Stable Change mode, click SQL audit optimization recommendations to the right of the corresponding control mode.

4. In the left-side navigation pane of the Details page, click SQL audit optimization recommendations.

5. For the A table must have a primary key rule, click Edit in the Actions column.

   Note

   You can quickly filter the rules by clicking the icon next to Tag (the scope where the rule applies, including DDL and DML statements), Action, or Status (enabled or disabled).

6. In the Configure Rule Content dialog box, configure the following parameters.

   The available values for Action also include potential issue and suggested improvement.

   Parameter	Description
   Action	Set Action to must be improved. Note By default, no initial rules are set to must be improved.
   Remarks	Enter remarks for the rule, such as business context.

7. Click OK.

   In subsequent tasks such as data development, data change, and SQL review, DMS checks your SQL statements against the configured security rules.

   If a statement violates the A table must have a primary key rule, the system automatically blocks the process.

Check items

• SQL Specification Check includes the following items.

  Table schema definition

  Check item	Applicable SQL	Rule name and ID
  Database attributes	CREATE DATABASE	Restrict the character set for database creation: CREATE_DATABASE_LIMIT_CHARSET
  Table attributes	CREATE TABLE ALTER TABLE	A table must have a primary key: TABLE_MUST_HAVE_PRIMARY_KEY A table must have comments: TABLE_MUST_HAVE_COMMENTS Do not use foreign keys in tables: TABLE_FORBID_USE_FOREIGN_KEY Restrict character case for table names: TABLE_NAME_LIMIT_CHAR_CASE Restrict the storage engine for tables: TABLE_LIMIT_STORE_ENGINE Do not use partitions for tables: TABLE_FORBID_USE_PARTITION A table must contain specific columns: TABLE_MUST_HAVE_SOME_COLUMN A table must use a specific character set: TABLE_MUST_USE_SOME_CHARSET A table must use a specific collation: TABLE_MUST_USE_SOME_COLLATION Table names cannot be keywords: TABLE_NAME_FORBID_KEYWORD Limit the number of indexes in a table: TABLE_LIMIT_INDEX_COUNT Limit the number of columns in a table: TABLE_LIMIT_COLUMN_COUNT Limit the initial auto-increment value for a table: TABLE_LIMIT_INIT_AUTO_INCREMENT The primary key column must be auto-incrementing: LIMIT_PRIMARY_COLUMN_AUTO_INCREMENT Do not use views: TABLE_FORBID_USE_VIEW Do not use triggers: TABLE_FORBID_USE_TRIGGER Do not use events: TABLE_FORBID_USE_EVENT Do not use stored procedures: TABLE_FORBID_USE_STORED_PROCEDURE Do not use custom functions: TABLE_FORBID_USE_CUSTOM_FUNCTION Use the ALTER TABLE CONVERT syntax to modify the table character set: MODIFY_CHARSET_USE_ALTER_TABLE_CONVERT
  Column attributes	CREATE TABLE ALTER TABLE	Column names cannot be keywords: COLUMN_NAME_FORBID_KEYWORD Restrict character case for column names: COLUMN_NAME_LIMIT_CHAR_CASE Do not set a character set for a column: COLUMN_FORBID_SET_CHARSET Do not use certain data types for columns: COLUMN_FORBID_DATA_TYPES Columns must have comments: COLUMN_MUST_HAVE_COMMENTS Limit the length of char type columns: COLUMN_LIMIT_CHAR_LENGTH Limit the length of varchar type columns: COLUMN_LIMIT_VARCHAR_LENGTH All columns must be set to NOT NULL: COLUMN_MUST_SET_NOT_NULL The name of the auto-incrementing column must be ID: COLUMN_AUTO_INCREMENT_NAME_ID Auto-incrementing columns must be unsigned: COLUMN_AUTO_INCREMENT_UNSIGNED Do not use float or double types (use decimal instead): COLUMN_FORBID_FLOAT_DOUBLE_TYPE Every column must have a default value: EACH_COLUMN_NEED_DEFAULT_VALUE Do not set a collation for a column: COLUMN_FORBID_SET_COLLATE Do not rename columns when using ALTER TABLE: ALTER_TABLE_FORBID_RENAME_COLUMN Do not drop columns when using ALTER TABLE: ALTER_TABLE_FORBID_DROP_COLUMN Do not change data types when using ALTER TABLE: ALTER_TABLE_FORBID_MODIFY_DATA_TYPE New columns added with ALTER TABLE must be nullable: ALTER_TABLE_ADD_COLUMN_NULLABLE When adding a non-nullable column with ALTER TABLE, a default value must be specified: ALTER_ADD_NOT_NULL_COLUMN_NEED_DEFAULT When changing a nullable column to non-nullable, a default value must be specified: COLUMN_NULLABLE_TO_NOT_NEED_DEFAULT Do not use the enum type (use tinyint or char instead): COLUMN_FORBID_USE_ENUM_TYPE Do not use the ZEROFILL attribute for columns: COLUMN_DATA_TYPE_FORBID_ZEROFILL
  Index attributes	CREATE TABLE ALTER TABLE	Each index must be explicitly named: INDEX_MUST_HAVE_CLEARLY_NAME Restrict the naming pattern for Unique indexes: UNIQUE_INDEX_NAME_PATTERN Restrict the naming pattern for common indexes: COMMON_INDEX_NAME_PATTERN Limit the number of columns in a single index: INDEX_LIMIT_CONTAINS_COLUMNS Limit the number of columns in a primary key: PRIMARY_LIMIT_CONTAINS_COLUMNS Restrict the data type of primary key columns: PRIMARY_LIMIT_COLUMN_DATA_TYPE Do not drop the primary key when using ALTER TABLE: ALTER_TABLE_FORBID_DROP_PRIMARY Do not drop an index when using ALTER TABLE: ALTER_TABLE_FORBID_DROP_INDEX

  Data query

  Check item	Applicable SQL	Rule name and ID
  SELECT	SELECT INSERT SELECT Subqueries (nested in UPDATE or DELETE statements)	SELECT statements should specify a WHERE clause: SELECT_SUGGEST_ASSIGN_WHERE Do not use ORDER BY RAND() in SELECT statements: SELECT_FORBID_USE_ORDER_BY_RAND Do not use GROUP BY on a constant in SELECT statements: SELECT_FORBID_GROUP_BY_CONST Do not use ORDER BY on a constant in SELECT statements: SELECT_FORBID_ORDER_BY_CONST A SELECT statement should not use GROUP BY or ORDER BY on different tables: SELECT_FORBID_GROUP_ORDER_BY_DISTINCT_TABLE In SELECT statements, do not use ORDER BY to sort multiple columns in different directions: SELECT_FORBID_ORDER_BY_MULTI_COLUMN_RANK SELECT statement: Do not GROUP BY or ORDER BY an expression or a function: SELECT_FORBID_GROUP_ORDER_BY_EXPR_OR_FUNCTION Do not use UNION in SELECT statements: SELECT_FORBID_USE_UNION Limit the number of tables joined in a SELECT statement: SELECT_LIMIT_TABLE_JOIN_COUNT The SELECT statement limit on the LIMIT clause offset size: SELECT_CONFINE_LIMIT_MAX_OFFSET Do not use a HAVING clause in SELECT statements: SELECT_FORBID_USE_HAVING
  WHERE clause	Statements that contain a WHERE clause, such as SELECT, UPDATE, DELETE, and INSERT SELECT.	Do not apply mathematical or function operations to indexed columns in a WHERE clause: WHERE_FORBID_INDEX_COLUMN_HAS_MATH Do not use a leading wildcard in searches within a WHERE clause: WHERE_FORBID_BEFORE_WILDCARD_SEARCH Check for LIKE statements without a wildcard in the WHERE clause: WHERE_CHECK_LIKE_HAS_NOT_WILDCARD Do not use negative conditions such as NOT IN or NOT LIKE in a WHERE clause: WHERE_FORBID_USE_REVERSE_SEARCH The limit in a WHERE clause on the number of elements in an IN clause: WHERE_LIMIT_IN_ITEM_MAX_COUNT Check for implicit type conversions on columns in a WHERE clause: WHERE_CHECK_COLUMN_IMPLICIT_TYPE_CONVERSION Check for filter conditions linked by the OR operator in a WHERE clause: WHERE_CHECK_OR_LINK_CONDITION

  Data change

  Check item	Applicable SQL	Rule name and ID
  Insert data	INSERT SELECT INSERT [IGNORE] REPLACE	An INSERT statement should specify a column list: ASSIGN_INSERT_COLUMN_NAME_LIST Do not use duplicate column names in the INSERT column list: INSERT_COLUMN_NAME_FORBID_DUPLICATE Do not insert a NULL value into a NOT NULL column: NOT_NULL_COLUMN_FORBID_INSERT_NULL The column list must match the values list in an INSERT statement: INSERT_COLUMN_MUST_MATCH_VALUES Limit the total number of rows in a single INSERT VALUES statement: LIMIT_INSERT_VALUES_TOTAL_ROWS Check whether the table or column specified in an INSERT statement exists: INSERT_CHECK_TABLE_COLUMN_EXISTS Do not use the SYSDATE() function in insert statements: INSERT_FORBID_USE_SYSDATE_FUNCTION
  Update and delete data	UPDATE DELETE	Limit the number of tables joined in UPDATE or DELETE statements: UPDELETE_LIMIT_TABLE_JOIN_COUNT UPDATE or DELETE statements should specify a WHERE clause: UPDELETE_ASSIGN_WHERE_CONDITION Check whether the WHERE clause contains a subquery in UPDATE or DELETE statements: UPDELETE_CHECK_WHERE_EXIST_SUB_QUERY Limit the number of affected rows by using LIMIT in UPDATE or DELETE statements: UPDELETE_CHECK_LIMIT_AFFECTED_ROWS Detects whether the multi-table join syntax is complete for UPDATE or DELETE statements (for example, a join clause is missing an on clause): UPDELETE_CHECK_TABLE_JOIN_LOSS_ON Do not include an ORDER BY clause in UPDATE or DELETE statements: UPDELETE_FORBID_ORDER_BY UPDATE statement check: SET delimiter between multiple columns (and is invalid): UPDATE_CHECK_SET_ITEM_DELIMITER UPDATE on multiple tables: Checks if columns in the SET clause are specified with a table prefix: UPDATE_MULTI_TABLE_CHECK_SET_COLUMN_PREFIX Check whether the tables or columns in an UPDATE statement exist: UPDATE_CHECK_TABLE_COLUMN_EXIST Check whether an UPDATE statement modifies the primary key: UPDATE_CHECK_PRIMARY_KEY_CHANGE Check whether an UPDATE statement modifies a unique key: UPDATE_CHECK_UNIQUE_KEY_CHANGE An UPDATE statement should also update the table's "modified time" column: UPDATE_ALSO_TO_UPDATE_MODIFY_TIME_COLUMN Do not update the table's "creation time" column in an UPDATE statement: UPDATE_FORBID_MODIFY_CREATE_TIME_COLUMN

• SQL Optimization Suggestions include the following check items.

  Category

  Check item

  None

  Optimize tables where the primary key is of int type: OPTIMIZE_PRIMARY_IS_INT_TABLE Warn about insufficient remaining space for auto-incrementing primary keys: SNIFFING_AUTO_PRIMARY_REMAIN_SPACE Warn about the risk of NULL values in a unique key (UK): SNIFFING_UNIQUE_EXIST_NULL_RISK Warn about and optimize DDL table locks or change failures: ALTER_LOCK_FAIL_SNIFFING_OPTIMIZE Detect SQL injection risks: CHECK_SQL_INJECTION_RISK Detect risks from using forced indexes (FORCE INDEX): CHECK_SQL_ASSIGN_FORCE_INDEX Check the index in the execution plan: SQL_EXPLAIN_INDEX_CHECK DMS index analysis and suggestions: DMS_INDEX_ANALYZE_AND_SUGGEST