Logo
Encrypt

Encrypt #

Syntax #

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

Parameters Explained #

nameDateTypeDescription
tableNameIDENTIFIERTable name
columnNameIDENTIFIERLogic column name
plainColumnNameIDENTIFIERPlain column name
cipherColumnNameIDENTIFIERCipher column name
encryptAlgorithmTypeSTRINGEncryption algorithm type name
ASSISTED_QUERY_COLUMNIDENTIFIERAuxiliary query column
LIKE_QUERY_COLUMNIDENTIFIERFuzzy query column
ASSISTED_QUERY_ALGORITHMSTRINGAuxiliary query algorithm
LIKE_QUERY_ALGORITHMSTRINGFuzzy query algorithm

| LIKE_QUERY_DATA_TYPE | IDENTIFIER Fuzzy query column data type| | ENCRYPT_ALGORITHM | STRING Encryption algorithm name| | ASSISTED_QUERY_ALGORITHM| STRING Auxiliary query algorithm| | LIKE_QUERY_ALGORITHM| STRING Fuzzy query algorithm|

Notes #

  • PLAIN specifies the plain column, CIPHER specifies the cipher column
  • encryptAlgorithmType specifies the encryption algorithm type, please refer to Encryption Algorithm
  • Duplicate tableName will not be created
  • queryWithCipherColumn support uppercase or lowercase true or false
  • ASSISTED_QUERY_COLUMN Auxiliary query column: This field is used for auxiliary query when the user’s CIPHER field and corresponding encryption algorithm do not support query. For example, if the results of multiple encrypts for the same value are different based on the encryption algorithm configured by the user, the CIPHER field cannot be used for query. In this case, auxiliary query columns are required for query. The algorithm corresponding to the auxiliary query column can generally use an irreversible algorithm, but the result of multiple encryptions of the same value must be the same.
  • LIKE_QUERY_COLUMN Fuzzy query column: Columns used to help users perform like queries. Generally, the CIPHER (encryption field) algorithm does not support the like query. Therefore, to use the like query, you need to configure the fuzzy query column and the corresponding fuzzy query algorithm.
  • ASSISTED_QUERY_ALGORITHM Auxiliary query algorithm: Algorithm of auxiliary query columns. Generally, the algorithm is not reversible, and the result of multiple encryption is consistent.
  • LIKE_QUERY_ALGORITHM Fuzzy query algorithm: Indicates the algorithm for fuzzy query columns. Like_query_algorithm supports the like query algorithm.

Example #

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 Syntax #

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

Parameters Explained #

nameDateTypeDescription
keyManagerNameSTRINGName of key manager

Example #

-- 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"))
);