数据加密 #

语法说明 #

CREATE ENCRYPT RULE

CreateEncryptRule ::=
  'CREATE' 'ENCRYPT' 'RULE' ifNotExists? encryptDefinition (',' encryptDefinition)*

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

encryptDefinition ::=
  ruleName '(' 'COLUMNS' '(' columnDefinition (',' columnDefinition)*  ')' (',' 'QUERY_WITH_CIPHER_COLUMN' '=' ('TRUE' | 'FALSE'))? ')'

columnDefinition ::=
  '(' 'NAME' '=' columnName (',' 'DATA_TYPE' '=' dataType)? (',' 'PLAIN' '=' plainColumnName (',' 'PLAIN_DATA_TYPE' '=' dataType)?)? ',' 'CIPHER' '=' cipherColumnName (',' 'CIPHER_DATA_TYPE' '=' dataType)? (',' 'ASSISTED_QUERY_COLUMN' '=' assistedQueryColumnName (',' 'ASSISTED_QUERY_DATA_TYPE' '=' dataType)?)? (',' 'LIKE_QUERY_COLUMN' '=' likeQueryColumnName (',' 'LIKE_QUERY_DATA_TYPE' '=' dataType)?)? ',' encryptAlgorithmDefinition (',' assistedQueryAlgorithmDefinition)? (',' likeQueryAlgorithmDefinition)? ')' 

encryptAlgorithmDefinition ::=
  'ENCRYPT_ALGORITHM' '(' 'TYPE' '(' 'NAME' '=' encryptAlgorithmType (',' propertiesDefinition)? ')'

assistedQueryAlgorithmDefinition ::=
  'ASSISTED_QUERY_ALGORITHM' '(' 'TYPE' '(' 'NAME' '=' encryptAlgorithmType (',' propertiesDefinition)? ')'

likeQueryAlgorithmDefinition ::=
  'LIKE_QUERY_ALGORITHM' '(' 'TYPE' '(' 'NAME' '=' encryptAlgorithmType (',' propertiesDefinition)? ')'

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

tableName ::=
  identifier

columnName ::=
  identifier

dataType ::=
  string

plainColumnName ::=
  identifier

cipherColumnName ::=
  identifier

assistedQueryColumnName ::=
  identifier

likeQueryColumnName ::=
  identifier

encryptAlgorithmType ::=
  string

key ::=
  string

value ::=
  literal

ALTER ENCRYPT RULE

ALTEREncryptRule ::=
  'ALTER' 'ENCRYPT' 'RULE' encryptDefinition (',' encryptDefinition)*

encryptDefinition ::=
  ruleName '(' 'COLUMNS' '(' columnDefinition (',' columnDefinition)*  ')' (',' 'QUERY_WITH_CIPHER_COLUMN' '=' ('TRUE' | 'FALSE'))? ')'

columnDefinition ::=
  '(' 'NAME' '=' columnName (',' 'DATA_TYPE' '=' dataType)? (',' 'PLAIN' '=' plainColumnName (',' 'PLAIN_DATA_TYPE' '=' dataType)?)? ',' 'CIPHER' '=' cipherColumnName (',' 'CIPHER_DATA_TYPE' '=' dataType)? (',' 'ASSISTED_QUERY_COLUMN' '=' assistedQueryColumnName (',' 'ASSISTED_QUERY_DATA_TYPE' '=' dataType)?)? (',' 'LIKE_QUERY_COLUMN' '=' likeQueryColumnName (',' 'LIKE_QUERY_DATA_TYPE' '=' dataType)?)? ',' encryptAlgorithmDefinition (',' assistedQueryAlgorithmDefinition)? (',' likeQueryAlgorithmDefinition)? ')' 

encryptAlgorithmDefinition ::=
  'ENCRYPT_ALGORITHM' '(' 'TYPE' '(' 'NAME' '=' encryptAlgorithmType (',' propertiesDefinition)? ')'

assistedQueryAlgorithmDefinition ::=
  'ASSISTED_QUERY_ALGORITHM' '(' 'TYPE' '(' 'NAME' '=' encryptAlgorithmType (',' propertiesDefinition)? ')'

likeQueryAlgorithmDefinition ::=
  'LIKE_QUERY_ALGORITHM' '(' 'TYPE' '(' 'NAME' '=' encryptAlgorithmType (',' propertiesDefinition)? ')'

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

tableName ::=
  identifier

columnName ::=
  identifier

dataType ::=
  string

plainColumnName ::=
  identifier

cipherColumnName ::=
  identifier

assistedQueryColumnName ::=
  identifier

likeQueryColumnName ::=
  identifier

encryptAlgorithmType ::=
  string

key ::=
  string

value ::=
  literal

DROP ENCRYPT RULE

DropEncryptRule ::=
  'DROP' 'ENCRYPT' 'RULE' ifExists? encryptRuleName (',' encryptRuleName)*

ifExists ::=
  'IF' 'EXISTS'

encryptRuleName ::=
  identifier

参数解释 #

名称	数据类型	说明
tableName	IDENTIFIER	表名称
columnName	IDENTIFIER	逻辑列名称
DATA_TYPE	IDENTIFIER	逻辑列数据类型
PLAIN	IDENTIFIER	明文列名称
PLAIN_DATA_TYPE	IDENTIFIER	明文列数据类型
CIPHER	IDENTIFIER	加密列名称
CIPHER_DATA_TYPE	IDENTIFIER	加密列数据类型
ASSISTED_QUERY_COLUMN	IDENTIFIER	辅助查询列
ASSISTED_QUERY_DATA_TYPE	IDENTIFIER	辅助查询列数据类型
LIKE_QUERY_COLUMN	IDENTIFIER	模糊查询列
LIKE_QUERY_DATA_TYPE	IDENTIFIER	模糊查询列数据类型
ENCRYPT_ALGORITHM	STRING	加密算法名称
ASSISTED_QUERY_ALGORITHM	STRING	辅助查询算法
LIKE_QUERY_ALGORITHM	STRING	模糊查询算法

注意事项 #

PLAIN 指定明文数据列，CIPHER 指定密文数据列；
encryptAlgorithmType 指定加密算法类型，请参考加密算法；
重复的 tableName 将无法被创建；
QUERY_WITH_CIPHER_COLUMN 支持大写或小写的 true 或 false,支持表级别和列级别的设置;
ASSISTED_QUERY_COLUMN 辅助查询列：在用户的 CIPHER (加密字段)以及对应的加密算法无法支持查询时，可以配置该字段用来辅助查询。例如用户配置的加密算法对于同一个值进行多次加密的结果不同，那么就无法使用 CIPHER (加密字段)进行查询，这时候就需要使用辅助查询列进行查询。辅助查询列对应的算法一般可以使用不可逆的算法，但是对于同一个值进行多次加密的结果需要相同。
LIKE_QUERY_COLUMN 模糊查询列：用于帮助用户进行 like 查询时使用的列。一般 CIPHER (加密字段) 配置的算法无法支持 like 查询，所以当用户需要使用 like查询时，需要配置模糊查询列以及对应的模糊查询算法。
ASSISTED_QUERY_ALGORITHM 辅助查询算法：即辅助查询列对应的算法。一般是不可逆，并且多次加密结果一致的算法。
LIKE_QUERY_ALGORITHM 模糊查询算法：即模糊查询列对应的算法，可以支持 like 查询的算法。

示例 #

CREATE ENCRYPT RULE t_encrypt (
COLUMNS(
(NAME=user_id,PLAIN=user_plain,CIPHER=user_cipher,ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))),
(NAME=order_id, CIPHER =order_cipher,ENCRYPT_ALGORITHM(TYPE(NAME='MD5')))
),QUERY_WITH_CIPHER_COLUMN=true),
t_encrypt_2 (
COLUMNS(
(NAME=user_id,DATA_TYPE='int(11)',PLAIN=user_plain,PLAIN_DATA_TYPE='int(11)',CIPHER=user_cipher,CIPHER_DATA_TYPE='varchar(255)',ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))),
(NAME=order_id, CIPHER=order_cipher,ENCRYPT_ALGORITHM(TYPE(NAME='MD5')))
), QUERY_WITH_CIPHER_COLUMN=FALSE);

ALTER ENCRYPT RULE t_encrypt (
COLUMNS(
(NAME=user_id,PLAIN=user_plain,CIPHER=user_cipher,ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))),
(NAME=order_id,CIPHER=order_cipher,CIPHER_DATA_TYPE='varchar(255)',ENCRYPT_ALGORITHM(TYPE(NAME='MD5')))
), QUERY_WITH_CIPHER_COLUMN=TRUE);

DROP ENCRYPT RULE t_encrypt,t_encrypt_2;

KeyManager 语法说明 #

createEncryptKeyManager
    : CREATE ENCRYPT KEY MANAGER keyManagerName keyManagerDefinition
    ;

alterEncryptKeyManager
    : ALTER ENCRYPT KEY MANAGER keyManagerName keyManagerDefinition
    ;

dropEncryptKeyManager
    : DROP ENCRYPT KEY MANAGER ifExists? keyManagerName
    ;

keyManagerDefinition
    : TYPE (NAME = keyManagerName [, PROPERTIES ([keyManagerProperties]) ])
    ;

keyManagerProperties:
    keyManagerProperty [, keyManagerProperty] ...

keyManagerProperty:
    key=value

参数解释 #

名称	数据类型	说明
keyManagerName	STRING	密钥管理器名称

示例 #

-- local
CREATE ENCRYPT KEY MANAGER local_key_manage (
  TYPE(NAME='LOCAL',PROPERTIES("aes-key-value"="123456abc"))
);

ALTER ENCRYPT KEY MANAGER local_key_manage (
TYPE(NAME='LOCAL',PROPERTIES("aes-key-value"="123456abcd"))
);

-- aws
CREATE ENCRYPT KEY MANAGER aws_key_manage (
TYPE(NAME='SphereEx:AWS_KMS',PROPERTIES(
  "access-key"="aaaaa",
  "secret-key"="bbbbb",
  "aws-region"="us-east-1",
  "secret-name"="testA"))
);

ALTER ENCRYPT KEY MANAGER aws_key_manage (
TYPE(NAME='SphereEx:AWS_KMS',PROPERTIES(
  "access-key"="aaaaa",
  "secret-key"="bbbbb",
  "aws-region"="us-east-1",
  "secret-name"="testB"))
);