DMS的安全规则中新接入了SQL审核优化建议,在SQLConsole或数据变更中,对提交的SQL语句进行规范审核并提出相关优化建议。可协助您在提交变更操作前审核、解决问题SQL,规避潜在问题SQL在变更到数据库后产生异常。
前提条件
系统行为动作
DMS预定义了三种系统行为动作:
- 必须改进:若不改进该语句,系统会自动阻断流程,并进行相应提示,系统初始化SQL审核规则中不包含必须改进。说明 SQL审核规则的检测项请参见检测项。
例如:将
表要有主键
的规则设置为必须改进,若开发人员在创建表时未设置主键,系统将会拦截SQL语句的执行,并要求开发人员针对该项规则进行修改,验证通过后才允许系统执行。 - 潜在问题:提醒该SQL存在问题,但不会阻断流程。
- 建议改进:建议进行改进,且不阻断流程。
不同管控模式下的功能支持情况
DMS提供三种不同的管控模式,更多信息,请参见管控模式。
支持项 | 安全协同 | 稳定变更 | 自由操作 |
---|---|---|---|
开启或关闭规则 | 支持 | 支持 | 支持 |
配置行为动作 | 支持 | 不支持配置 | 不支持配置 |
更改规则参数 部分规则可填写参数,例如表的索引数量、表的字段数量。 | 支持 | 支持 | 不支持配置 |
配置备注 配置规则的业务背景。 | 支持 | 不支持配置 | 不支持配置 |
操作步骤
安全规则中已生成默认的SQL审核规则,本示例向您介绍如何进行自定义配置,将表要有主键
的规则设置为必须改进。
- 登录数据管理DMS 5.0。
- 在顶部菜单栏中,选择 。
- 单击目标安全规则右侧操作列下的编辑。说明 若您需要配置自由操作或稳定变更的配置项,请单击对应管控模式右侧的SQL审核优化建议。
- 在详情页左侧的导航栏中,单击SQL审核优化建议。
- 单击
表要有主键
规则右侧的编辑。说明 您可以单击标记(规则生效范围,目前包含DDL语句、DML语句)、行为动作、状态(开启或关闭规则)右侧的图标,进行快速筛选。 - 在规则内容配置弹窗中,配置如下信息。
参数名 说明 行为动作 将行为动作设置为必须改进。 说明 系统初始化规则中不包含必须改进。备注 输入此规则的备注信息,如业务背景信息。 - 单击确定。
在后续的数据开发、数据变更及SQL审核等功能中,SQL审核优化建议将根据配置的安全规则检验SQL语句。
若不符合表要有主键
该项规则,系统会自动阻断流程。
检测项
- SQL规范检查支持的检测项如下。
表结构定义
检测项 适用的SQL语句 规则名称与规则标识 库属性 CREATE DATABASE
限制创建库的字符集:CREATE_DATABASE_LIMIT_CHARSET 表属性 CREATE TABLE
ALTER TABLE
- 表要有主键:TABLE_MUST_HAVE_PRIMARY_KEY
- 表要有备注:TABLE_MUST_HAVE_COMMENTS
- 表不能使用外键:TABLE_FORBID_USE_FOREIGN_KEY
- 限制表名大小写:TABLE_NAME_LIMIT_CHAR_CASE
- 限制表存储引擎:TABLE_LIMIT_STORE_ENGINE
- 限制表使用分区设置:TABLE_FORBID_USE_PARTITION
- 表需要包含某些列:TABLE_MUST_HAVE_SOME_COLUMN
- 限制表字符集:TABLE_MUST_USE_SOME_CHARSET
- 限制表校验规则:TABLE_MUST_USE_SOME_COLLATION
- 表名不能是关键字:TABLE_NAME_FORBID_KEYWORD
- 限制表索引的数量:TABLE_LIMIT_INDEX_COUNT
- 限制表字段的数量:TABLE_LIMIT_COLUMN_COUNT
- 限制建表自增初始值:TABLE_LIMIT_INIT_AUTO_INCREMENT
- 限制主键列必须自增:LIMIT_PRIMARY_COLUMN_AUTO_INCREMENT
- 限制使用视图:TABLE_FORBID_USE_VIEW
- 限制使用触发器:TABLE_FORBID_USE_TRIGGER
- 限制使用事件:TABLE_FORBID_USE_EVENT
- 限制使用存储过程:TABLE_FORBID_USE_STORED_PROCEDURE
- 限制使用自定义函数:TABLE_FORBID_USE_CUSTOM_FUNCTION
- 修改表字符集建议用
ALTER TABLE CONVERT
语法:MODIFY_CHARSET_USE_ALTER_TABLE_CONVERT
列属性 CREATE TABLE
ALTER TABLE
- 字段名不能是关键字:COLUMN_NAME_FORBID_KEYWORD
- 限制字段名大小写:COLUMN_NAME_LIMIT_CHAR_CASE
- 不能设置列的字符集:COLUMN_FORBID_SET_CHARSET
- 限制列不能使用部分数据类型:COLUMN_FORBID_DATA_TYPES
- 列要有注释:COLUMN_MUST_HAVE_COMMENTS
- 限制
char
类型字段长度:COLUMN_LIMIT_CHAR_LENGTH - 限制
varchar
类型字段长度:COLUMN_LIMIT_VARCHAR_LENGTH - 限制列都不可空(
NOT NULL
):COLUMN_MUST_SET_NOT_NULL - 限制自增列名字为
ID
:COLUMN_AUTO_INCREMENT_NAME_ID - 限制自增列为无符号:COLUMN_AUTO_INCREMENT_UNSIGNED
- 不能使用
float
或double
类型(建议用decimal
替换):COLUMN_FORBID_FLOAT_DOUBLE_TYPE - 每个列都要有默认值:EACH_COLUMN_NEED_DEFAULT_VALUE
- 不能设置列的校验集:COLUMN_FORBID_SET_COLLATE
- 限制修改表重命名列:ALTER_TABLE_FORBID_RENAME_COLUMN
- 限制修改表删除列:ALTER_TABLE_FORBID_DROP_COLUMN
- 修改表禁止更改数据类型:ALTER_TABLE_FORBID_MODIFY_DATA_TYPE
- 修改表新增字段要求可空:ALTER_TABLE_ADD_COLUMN_NULLABLE
- 修改表新增非空字段时要明确指定默认值:ALTER_ADD_NOT_NULL_COLUMN_NEED_DEFAULT
- 修改表将可空字段改为非空时要明确指定默认值:COLUMN_NULLABLE_TO_NOT_NEED_DEFAULT
- 不能使用
enum
类型(建议用tinyint
或char
代替):COLUMN_FORBID_USE_ENUM_TYPE - 字段禁用
ZEROFILL
属性:COLUMN_DATA_TYPE_FORBID_ZEROFILL
索引属性 CREATE TABLE
ALTER TABLE
- 索引需要设置名字:INDEX_MUST_HAVE_CLEARLY_NAME
- 限制
Unique
索引名格式:UNIQUE_INDEX_NAME_PATTERN - 限制普通索引名格式:COMMON_INDEX_NAME_PATTERN
- 限制单个索引包含列的个数:INDEX_LIMIT_CONTAINS_COLUMNS
- 限制主键包含列的个数:PRIMARY_LIMIT_CONTAINS_COLUMNS
- 限制主键列类型:PRIMARY_LIMIT_COLUMN_DATA_TYPE
- 限制修改表删除主键:ALTER_TABLE_FORBID_DROP_PRIMARY
- 修改表禁止删除索引:ALTER_TABLE_FORBID_DROP_INDEX
数据查询
检测项 适用的SQL语句 规则名称与规则标识 SELECT
SELECT
INSERT SELECT
- 子查询(嵌套在
UPDATE
或DELETE
中)
SELECT
语句建议指定WHERE
条件:SELECT_SUGGEST_ASSIGN_WHERESELECT
语句不建议使用ORDER BY RAND()
:SELECT_FORBID_USE_ORDER_BY_RANDSELECT
语句不建议对常量进行GROUP BY
:SELECT_FORBID_GROUP_BY_CONSTSELECT
语句不建议对常量进行ORDER BY
:SELECT_FORBID_ORDER_BY_CONSTSELECT
语句不建议对不同的表GROUP BY
或ORDER BY
:SELECT_FORBID_GROUP_ORDER_BY_DISTINCT_TABLESELECT
语句不建议ORDER BY
多个字段使用不同方向排序:SELECT_FORBID_ORDER_BY_MULTI_COLUMN_RANKSELECT
语句不建议GROUP BY
或ORDER BY
表达式或函数:SELECT_FORBID_GROUP_ORDER_BY_EXPR_OR_FUNCTIONSELECT
语句不建议使用union
:SELECT_FORBID_USE_UNIONSELECT
语句限制多表关联的数量:SELECT_LIMIT_TABLE_JOIN_COUNTSELECT
语句限制LIMIT
的offset
大小:SELECT_CONFINE_LIMIT_MAX_OFFSETSELECT
语句不建议使用HAVING
子句:SELECT_FORBID_USE_HAVING
WHERE
条件包含 WHERE
条件子句的SELECT
、UPDATE
、DELETE
、INSERT SELECT
语句。WHERE
条件中不建议索引字段包含数学运算或函数运算:WHERE_FORBID_INDEX_COLUMN_HAS_MATHWHERE
条件中不建议使用前通配符查找:WHERE_FORBID_BEFORE_WILDCARD_SEARCHWHERE
条件中检测没有通配符的LIKE
语句:WHERE_CHECK_LIKE_HAS_NOT_WILDCARDWHERE
条件中不建议使用反向查询(not in
/not like
):WHERE_FORBID_USE_REVERSE_SEARCHWHERE
条件中限制IN
子句包含元素个数:WHERE_LIMIT_IN_ITEM_MAX_COUNTWHERE
条件中检测字段是否存在隐式类型转换:WHERE_CHECK_COLUMN_IMPLICIT_TYPE_CONVERSIONWHERE
条件中检测是否通过OR
操作符连接过滤条件:WHERE_CHECK_OR_LINK_CONDITION
数据变更
检测项 适用的SQL语句 规则名称与规则标识 插入数据 INSERT SELECT
INSERT [IGNORE]
REPLACE
- 插入语句建议指定
INSERT
字段列表:ASSIGN_INSERT_COLUMN_NAME_LIST - 插入语句中
INSERT
字段名不能重复:INSERT_COLUMN_NAME_FORBID_DUPLICATE - 插入语句不能为
NOT NULL
列插入NULL
值:NOT_NULL_COLUMN_FORBID_INSERT_NULL - 插入语句中
INSERT
字段列表要和值列表匹配:INSERT_COLUMN_MUST_MATCH_VALUES - 插入语句限制一条
INSERT VALUES
的总行数:LIMIT_INSERT_VALUES_TOTAL_ROWS - 插入语句检测
INSERT
的表或字段是否存在:INSERT_CHECK_TABLE_COLUMN_EXISTS - 插入语句不建议使用
SYSDATA()
函数:INSERT_FORBID_USE_SYSDATE_FUNCTION
修改、删除数据 UPDATE
DELETE
UPDATE
或DELETE
语句限制多表关联的数量:UPDELETE_LIMIT_TABLE_JOIN_COUNTUPDATE
或DELETE
语句建议指定WHERE
条件:UPDELETE_ASSIGN_WHERE_CONDITIONUPDATE
或DELETE
语句检测WHERE
条件是否包含子查询:UPDELETE_CHECK_WHERE_EXIST_SUB_QUERYUPDATE
或DELETE
语句限制LIMIT
数据量大小:UPDELETE_CHECK_LIMIT_AFFECTED_ROWSUPDATE
或DELETE
语句检测多表关联语法是否完整(如join
遗漏on
子句):UPDELETE_CHECK_TABLE_JOIN_LOSS_ONUPDATE
或DELETE
语句不能有ORDER BY
子句:UPDELETE_FORBID_ORDER_BYUPDATE
语句检测SET
多个列之间的分隔符(and
非法):UPDATE_CHECK_SET_ITEM_DELIMITERUPDATE
多表时检测SET
的列是否指定表前缀:UPDATE_MULTI_TABLE_CHECK_SET_COLUMN_PREFIXUPDATE
语句检测涉及表或字段是否存在:UPDATE_CHECK_TABLE_COLUMN_EXISTUPDATE
语句检测是否更新了主键:UPDATE_CHECK_PRIMARY_KEY_CHANGEUPDATE
语句检测是否更新了唯一键:UPDATE_CHECK_UNIQUE_KEY_CHANGEUPDATE
语句建议同时更新表上的“修改时间”列:UPDATE_ALSO_TO_UPDATE_MODIFY_TIME_COLUMNUPDATE
语句不建议更新表上的“创建时间”列:UPDATE_FORBID_MODIFY_CREATE_TIME_COLUMN
- SQL优化建议支持的检测项如下。
类别 检测点 无 - 优化主键为
int
类型的表:OPTIMIZE_PRIMARY_IS_INT_TABLE - 预警自增主键剩余可用空间风险:SNIFFING_AUTO_PRIMARY_REMAIN_SPACE
- 预警唯一索引
(uk)null
值风险:SNIFFING_UNIQUE_EXIST_NULL_RISK - DDL锁表或变更失败预警与优化:ALTER_LOCK_FAIL_SNIFFING_OPTIMIZE
- SQL注入风险检测:CHECK_SQL_INJECTION_RISK
- 强制索引
(force index)
风险检测:CHECK_SQL_ASSIGN_FORCE_INDEX - 执行计划索引检查:SQL_EXPLAIN_INDEX_CHECK
- DMS索引分析与推荐:DMS_INDEX_ANALYZE_AND_SUGGEST
- 优化主键为