防篡改规则

语法清单 #

语法	描述	类型
CREATE DATA_INTEGRITY RULE ifNotExists? dataIntegrityRuleDefinition [, dataIntegrityRuleDefinition]	创建防篡改规则	RDL
ALTER DATA_INTEGRITY RULE dataIntegrityRuleDefinition [, dataIntegrityRuleDefinition]	修改防篡改规则	RDL
DROP DATA_INTEGRITY RULE ifExists? ruleName [, ruleName]	删除防篡改规则	RDL
SHOW DATA_INTEGRITY RULES	查看防篡改规则	RQL
ALTER DATA_INTEGRITY RULE ruleName digestGroupDefinition [, digestGroupDefinition]	修改指定防篡改规则的摘要列配置	RDL

1. 创建防篡改规则 #

createDataIntegrityRule ::=
    'CREATE' 'DATA_INTEGRITY' 'RULE' ifNotExists? dataIntegrityRuleDefinition (COMMA_ dataIntegrityRuleDefinition)*

ifNotExists ::=
  'IF' 'NOT' 'EXISTS'

dataIntegrityRuleDefinition ::=
    ruleName '(' digesGroupsDefinition ')'

digesGroupsDefinition ::=
    digesGroupDefinition (',' digesGroupDefinition)*

digesGroupDefinition ::=
    '(' 'DIGEST_COLUMN' '=' digestColumnName ',' columnNames ',' algorithmDefinition ')'

columnNames ::=
    'COLUMNS' '(' columnName (',' columnName)* ')'

ruleName ::=
  identifier

digestColumnName ::=
  identifier

columnName ::=
  identifier

algorithmDefinition ::=
    'TYPE' '(' 'NAME' '=' algorithmTypeName (',' propertiesDefinition)? ')'

algorithmTypeName ::=
  string

propertiesDefinition ::=
  'PROPERTIES' '(' key '=' value (',' key '=' value)* ')'

key ::=
  string

value ::=
  literal

参数说明

参数解释 #

名称	数据类型	说明
ruleName	IDENTIFIER	规则名称
digestColumnName	IDENTIFIER	摘要列名称
columnName	IDENTIFIER	列名称
algorithmTypeName	STRING	算法名称

示例

创建数据防篡改规则

CREATE DATA_INTEGRITY RULE `t_user` (
  (DIGEST_COLUMN=`id_digest`, COLUMNS(`id`, `name`), TYPE(NAME='MD5', PROPERTIES('salt'='ASDF'))), 
  (DIGEST_COLUMN=`name_digest`, COLUMNS(`id`, `name`, `password`), TYPE(NAME='MD5', PROPERTIES('salt'='add')))
);

使用 ifNotExists 子句创建数据防篡改规则

CREATE DATA_INTEGRITY RULE IF NOT EXISTS `t_user` (
  (DIGEST_COLUMN=`id_digest`, COLUMNS(`id`, `name`), TYPE(NAME='MD5', PROPERTIES('salt'='ASDF'))), 
  (DIGEST_COLUMN=`name_digest`, COLUMNS(`id`, `name`, `password`), TYPE(NAME='MD5', PROPERTIES('salt'='add')))
);

2. 修改防篡改规则 #

alterDataIntegrityRule ::=
    'ALTER' 'DATA_INTEGRITY' 'RULE' dataIntegrityRuleDefinition (COMMA_ dataIntegrityRuleDefinition)*

dataIntegrityRuleDefinition ::=
    ruleName '(' digesGroupsDefinition ')'

digesGroupsDefinition ::=
    digesGroupDefinition (',' digesGroupDefinition)*

digesGroupDefinition ::=
    '(' 'DIGEST_COLUMN' '=' digestColumnName ',' columnNames ',' algorithmDefinition ')'

columnNames ::=
    'COLUMNS' '(' columnName (',' columnName)* ')'

ruleName ::=
  identifier

digestColumnName ::=
  identifier

columnName ::=
  identifier

algorithmDefinition ::=
    'TYPE' '(' 'NAME' '=' algorithmTypeName (',' propertiesDefinition)? ')'

algorithmTypeName ::=
  string

propertiesDefinition ::=
  'PROPERTIES' '(' key '=' value (',' key '=' value)* ')'

key ::=
  string

value ::=
  literal

参数说明

名称	数据类型	说明
ruleName	IDENTIFIER	规则名称
digestColumnName	IDENTIFIER	摘要列名称
columnName	IDENTIFIER	列名称
algorithmTypeName	STRING	算法名称

ruleName 规则名称，修改时按照规则名字进行对应规则修改

示例

ALTER DATA_INTEGRITY RULE `t_user` (
  (DIGEST_COLUMN=`id_digest`, COLUMNS(`id`, `name`), TYPE(NAME='MD5', PROPERTIES('salt'='1234')))
);

3. 删除防篡改规则 #

示例

删除防篡改规则

DROP DATA_INTEGRITY RULE `t_user`, `t_order`;

使用 ifExists 删除防篡改规则

DROP DATA_INTEGRITY RULE IF EXISTS `t_user`, `t_order`;

4. 查看防篡改规则 #

SHOW DATA_INTEGRITY RULES [FROM databaseName]
SHOW DATA_INTEGRITY RULE ruleName [FROM databaseName]

示例

查询所有防篡改规则

mysql> SHOW DATA_INTEGRITY RULES;
+--------+---------------+------------------+-------------+-----------------+
| table  | digest_column | columns          | digest_type | digest_props    |
+--------+---------------+------------------+-------------+-----------------+
| t_user | id_digest     | id,name          | MD5         | {"salt":"ASDF"} |
| t_user | name_digest   | id,name,password | MD5         | {"salt":"add"}  |
+--------+---------------+------------------+-------------+-----------------+
2 rows in set (0.48 sec)

mysql> SHOW DATA_INTEGRITY RULES FROM test;
+--------+---------------+------------------+-------------+-----------------+
| table  | digest_column | columns          | digest_type | digest_props    |
+--------+---------------+------------------+-------------+-----------------+
| t_user | id_digest     | id,name          | MD5         | {"salt":"ASDF"} |
| t_user | name_digest   | id,name,password | MD5         | {"salt":"add"}  |
+--------+---------------+------------------+-------------+-----------------+

查询指定防篡改规则

示例

mysql> SHOW DATA_INTEGRITY RULE `t_user`;
+--------+---------------+------------------+-------------+-----------------+
| table  | digest_column | columns          | digest_type | digest_props    |
+--------+---------------+------------------+-------------+-----------------+
| t_user | id_digest     | id,name          | MD5         | {"salt":"ASDF"} |
| t_user | name_digest   | id,name,password | MD5         | {"salt":"add"}  |
+--------+---------------+------------------+-------------+-----------------+
2 rows in set (0.00 sec)

mysql> SHOW DATA_INTEGRITY RULE `t_user` FROM test;
+--------+---------------+------------------+-------------+-----------------+
| table  | digest_column | columns          | digest_type | digest_props    |
+--------+---------------+------------------+-------------+-----------------+
| t_user | id_digest     | id,name          | MD5         | {"salt":"ASDF"} |
| t_user | name_digest   | id,name,password | MD5         | {"salt":"add"}  |
+--------+---------------+------------------+-------------+-----------------+
2 rows in set (0.02 sec)

输出说明

列	说明
table	规则名称(表名)
digest_column	摘要列
columns	参与计算摘要值的列
digest_type	摘要算法类型
digest_props	摘要孙峰参数

5. 修改指定规则的摘要列配置 #

alterDataIntegrityRuleGroup ::=
    'ALTER' 'DATA_INTEGRITY' 'RULE' ruleName ruleDigestGroupDefinition [, ruleDigestGroupDefinition]

ruleDigestGroupDefinition ::=
    ['ADD' digesGroupDefinition | 'MODIFY' digesGroupDefinition | 'DROP' digestColumnName]

digesGroupDefinition ::=
    '(' 'DIGEST_COLUMN' '=' digestColumnName ',' columnNames ',' algorithmDefinition ')'

columnNames ::=
    'COLUMNS' '(' columnName (',' columnName)* ')'

ruleName ::=
  identifier

digestColumnName ::=
  identifier

columnName ::=
  identifier

algorithmDefinition ::=
    'TYPE' '(' 'NAME' '=' algorithmTypeName (',' propertiesDefinition)? ')'

algorithmTypeName ::=
  string

propertiesDefinition ::=
  'PROPERTIES' '(' key '=' value (',' key '=' value)* ')'

key ::=
  string

value ::=
  literal

参数说明

名称	数据类型	说明
ruleName	IDENTIFIER	规则名称
digestColumnName	IDENTIFIER	摘要列名称
columnName	IDENTIFIER	列名称
algorithmTypeName	STRING	算法名称

ruleName 规则名称，修改时按照规则名字进行对应规则修改
digestColumnName 摘要列名称，MODIFY 操作时按照摘要列匹配修改，DROP 操作时按照摘要列名称匹配删除

指定规则新增摘要列配置

示例

ALTER DATA_INTEGRITY RULE `t_user` ADD (DIGEST_COLUMN=`test_digest`, COLUMNS(`id`, `name`), TYPE(NAME='MD5', PROPERTIES('salt'='123')));

指定规则修改摘要列配置

示例

ALTER DATA_INTEGRITY RULE `t_user` MODIFY (DIGEST_COLUMN=`id_digest`, COLUMNS(`id`, `name`, `password`), TYPE(NAME='MD5', PROPERTIES('salt'='123')));

指定规则删除摘要列配置

示例

ALTER DATA_INTEGRITY RULE `t_user` DROP `test_digest`;

指定规则同时新增、修改、删除摘要列

示例

ALTER DATA_INTEGRITY RULE `t_user` 
ADD (DIGEST_COLUMN=`test_digest`, COLUMNS(`id`, `name`), TYPE(NAME='MD5', PROPERTIES('salt'='123'))),
MODIFY (DIGEST_COLUMN=`id_digest`, COLUMNS(`id`, `name`), TYPE(NAME='MD5', PROPERTIES('salt'='asd'))),
DROP `name_digest`;

mysql> SHOW DATA_INTEGRITY RULE `t_user`;
+--------+---------------+------------------+-------------+----------------+
| table  | digest_column | columns          | digest_type | digest_props   |
+--------+---------------+------------------+-------------+----------------+
| t_user | id_digest     | id,name,password | MD5         | {"salt":"123"} |
| t_user | name_digest   | id,name,password | MD5         | {"salt":"add"} |
+--------+---------------+------------------+-------------+----------------+
2 rows in set (0.03 sec)

mysql> ALTER DATA_INTEGRITY RULE `t_user`
    -> ADD (DIGEST_COLUMN=`test_digest`, COLUMNS(`id`, `name`), TYPE(NAME='MD5', PROPERTIES('salt'='123'))),
    -> MODIFY (DIGEST_COLUMN=`id_digest`, COLUMNS(`id`, `name`), TYPE(NAME='MD5', PROPERTIES('salt'='asd'))),
    -> DROP `name_digest`;
Query OK, 0 rows affected (4.96 sec)

mysql> SHOW DATA_INTEGRITY RULE `t_user`;
+--------+---------------+---------+-------------+----------------+
| table  | digest_column | columns | digest_type | digest_props   |
+--------+---------------+---------+-------------+----------------+
| t_user | id_digest     | id,name | MD5         | {"salt":"asd"} |
| t_user | test_digest   | id,name | MD5         | {"salt":"123"} |
+--------+---------------+---------+-------------+----------------+
2 rows in set (0.01 sec)