Encryption

Encryption #

Syntax	Description	Type
CREATE ENCRYPT RULE encryptRuleDefinition [, encryptRuleDefinition] …	Create encryption rule	RDL
ALTER ENCRYPT RULE encryptRuleDefinition [, encryptRuleDefinition] …	Modify encryption rule	RDL
DROP ENCRYPT RULE tableName [, tableName] …	Delete encryption rule	RDL
SHOW ENCRYPT RULES [FROM databaseName] SHOW ENCRYPT TABLE RULE tableName [FROM databaseName]	View encryption rule	RQL
COUNT ENCRYPT RULE	Count the number of encryption rules, this syntax will be deprecated in subsequent versions	RQL
CREATE ENCRYPT KEY MANAGER keyManagerName keyManagerDefinition	Create key management rule	RDL
ALTER ENCRYPT KEY MANAGER keyManagerName keyManagerDefinition	Modify key management rule	RDL
DROP ENCRYPT KEY MANAGER ifExists? keyManagerName	Delete key management	RDL
select [,string] … [,] decrypt({tableName}, {encryptColumn}, {encryptValue}), [,string] …	Query decryption result	RQL
CONVERT TABLE tableName BY ENCRYPT RULE …	Convert non-encrypted table to encrypted table	RDL
CONVERT TABLE tableName ADD PLAIN addPlainColumnsDefinition	Add plaintext column to encrypted table	RDL
CONVERT TABLE tableName DROP PLAIN dropColumnsDefinition	Remove plaintext columns for encrypted tables	RDL
CONVERT TABLE tableName ADD LIKE addLikeColumnsDefinition	Add fuzzy query columns to encrypted tables	RDL
CONVERT TABLE tableName DROP LIKE dropColumnsDefinition	Remove fuzzy query columns for encrypted tables	RDL
ALTER ENCRYPT RULE setQueryWithPlainItem (’,’ setQueryWithPlainItem)* SET QUERY_WITH_PLAIN ‘=’ booleanLiterals	Toggle plaintext query switch	RDL
CONVERT TABLE tableName DROP ENCRYPT RULE	Convert encrypted table to non-encrypted table	RDL

1. Create Encryption Rule #

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

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

encryptDefinition ::=
  ruleName '(' 'COLUMNS' '(' columnDefinition (',' columnDefinition)*  ')' (',' queryWithPlainOrCipher)? ')'

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

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)* ')'

queryWithPlainOrCipher ::=
    : ('QUERY_WITH_CIPHER_COLUMN' | 'QUERY_WITH_PLAIN') '=' ('TRUE' | 'FALSE')
    ;

tableName ::=
  identifier

columnName ::=
  identifier

dataType ::=
  string

plainColumnName ::=
  identifier

cipherColumnName ::=
  identifier

assistedQueryColumnName ::=
  identifier

likeQueryColumnName ::=
  identifier

encryptAlgorithmType ::=
  string

key ::=
  string

value ::=
  literal

Parameter Description

Parameter Description #

Name	Data type	Description
tableName	IDENTIFIER	Table name
columnName	IDENTIFIER	Logical column name
DATA_TYPE	IDENTIFIER	Logical column data type
PLAIN	IDENTIFIER	Plain column name
CIPHER	IDENTIFIER	Cipher column name
CIPHER_DATA_TYPE	IDENTIFIER	Cipher column data type
ASSISTED_QUERY_COLUMN	IDENTIFIER	Auxiliary query column
ASSISTED_QUERY_DATA_TYPE	IDENTIFIER	Auxiliary query column data type
LIKE_QUERY_COLUMN	IDENTIFIER	Fuzzy query column
LIKE_QUERY_DATA_TYPE	IDENTIFIER	Fuzzy query column data type
ENCRYPT_ALGORITHM	STRING	Encrypt algorithm name
ASSISTED_QUERY_ALGORITHM	STRING	Auxiliary query algorithm
LIKE_QUERY_ALGORITHM	STRING	Fuzzy query algorithm

PLAIN specifies the plain column, CIPHER specifies the cipher column, after creating an encrypt rule, Proxy will prohibit DDL operations other than ADD COLUMN on plaintext columns and ciphertext columns.
encryptAlgorithmType specifies the encryption algorithm, please refer to encrypt algorithm;
Duplicate tableName will not be created
QUERY_WITH_CIPHER_COLUMN Query using the ciphertext column: support true or false case-insensitive, support table-level, column-level and global settings.Mutually exclusive with QUERY_WITH_PLAIN;
QUERY_WITH_PLAIN Use plain text column query: supports uppercase or lowercase true or false, supports table level, column level and global level settings, Mutually exclusive with QUERY_WITH_CIPHER_COLUMN;
ASSISTED_QUERY_COLUMN Auxiliary query column: Cannot have the same name as a logical 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 encryption of the same value must be the same.
LIKE_QUERY_COLUMN Fuzzy query column: Cannot have the same name as a logical 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 data encryption rule

copyCREATE ENCRYPT RULE t_encrypt (
  COLUMNS (
    (
      NAME=merchant_name, DATA_TYPE='varchar(20)', 
      PLAIN=merchant_name_plain, 
      CIPHER=merchant_name_cipher, CIPHER_DATA_TYPE='varchar(100)', 
      ASSISTED_QUERY=merchant_name_assisted, ASSISTED_QUERY_DATA_TYPE='varchar(100)', 
      LIKE_QUERY=merchant_name_like, LIKE_QUERY_DATA_TYPE='varchar(50)', 
      ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), 
      ASSISTED_QUERY_ALGORITHM(TYPE(NAME='MD5',PROPERTIES('salt'='121212'))), 
      LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE',PROPERTIES('delta'='2'))), QUERY_WITH_CIPHER_COLUMN=true
    )
  ), QUERY_WITH_PLAIN=true
);

Create data encryption rule with ifNotExists clause

copyCREATE ENCRYPT RULE IF NOT EXISTS t_encrypt (
  COLUMNS (
    (
      NAME=merchant_name, DATA_TYPE='varchar(20)', 
      PLAIN=merchant_name_plain, 
      CIPHER=merchant_name_cipher, CIPHER_DATA_TYPE='varchar(100)', 
      ASSISTED_QUERY=merchant_name_assisted, ASSISTED_QUERY_DATA_TYPE='varchar(100)', 
      LIKE_QUERY=merchant_name_like, LIKE_QUERY_DATA_TYPE='varchar(50)', 
      ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), 
      ASSISTED_QUERY_ALGORITHM(TYPE(NAME='MD5',PROPERTIES('salt'='121212'))), 
      LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE',PROPERTIES('delta'='2'))), QUERY_WITH_CIPHER_COLUMN=true
    )
  ), QUERY_WITH_PLAIN=true
);

2. Modify Encryption Rule #

copyALTEREncryptRule ::=
  'ALTER' 'ENCRYPT' 'RULE' encryptDefinition (',' encryptDefinition)* (',' queryWithPlainOrCipher)?

encryptDefinition ::=
  ruleName '(' 'COLUMNS' '(' columnDefinition (',' columnDefinition)*  ')' (',' queryWithPlainOrCipher)? ')'

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

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)* ')'

queryWithPlainOrCipher ::=
    : ('QUERY_WITH_CIPHER_COLUMN' | 'QUERY_WITH_PLAIN') '=' ('TRUE' | 'FALSE')
    ;

tableName ::=
  identifier

columnName ::=
  identifier

dataType ::=
  string

plainColumnName ::=
  identifier

cipherColumnName ::=
  identifier

assistedQueryColumnName ::=
  identifier

likeQueryColumnName ::=
  identifier

encryptAlgorithmType ::=
  string

key ::=
  string

value ::=
  literal

Parameter Explanation

Name	Data type	Description
tableName	IDENTIFIER	Table name
columnName	IDENTIFIER	Logical column name
DATA_TYPE	IDENTIFIER	Logical column data type
PLAIN	IDENTIFIER	Plain column name
PLAIN_DATA_TYPE	IDENTIFIER	Plain column data type
CIPHER	IDENTIFIER	Cipher column name
CIPHER_DATA_TYPE	IDENTIFIER	Cipher column data type
ASSISTED_QUERY_COLUMN	IDENTIFIER	Auxiliary query column
ASSISTED_QUERY_DATA_TYPE	IDENTIFIER	Auxiliary query column data type
LIKE_QUERY_COLUMN	IDENTIFIER	Fuzzy query column
LIKE_QUERY_DATA_TYPE	IDENTIFIER	Fuzzy query column data type
ENCRYPT_ALGORITHM	STRING	Encrypt algorithm name
ASSISTED_QUERY_ALGORITHM	STRING	Auxiliary query algorithm
LIKE_QUERY_ALGORITHM	STRING	Fuzzy query algorithm

PLAIN specifies the plain column, CIPHER specifies the cipher column, after creating an encrypt rule, Proxy will prohibit DDL operations other than ADD COLUMN on plaintext columns and ciphertext columns.
encryptAlgorithmType specifies the encryption algorithm, please refer to encrypt algorithm;
QUERY_WITH_CIPHER_COLUMN Query using the ciphertext column: support true or false case-insensitive, support table-level, column-level and global settings.Mutually exclusive with QUERY_WITH_PLAIN;
QUERY_WITH_PLAIN Use plain text column query: supports uppercase or lowercase true or false, supports table level, column level and global level settings, Mutually exclusive with QUERY_WITH_CIPHER_COLUMN;
ASSISTED_QUERY_COLUMN Auxiliary query column: Cannot have the same name as a logical 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 encryption of the same value must be the same.
LIKE_QUERY_COLUMN Fuzzy query column: Cannot have the same name as a logical 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

copyALTER ENCRYPT RULE t_encrypt (
  COLUMNS (
    (
      NAME=merchant_name, DATA_TYPE='varchar(20)', 
      PLAIN=merchant_name_plain, 
      CIPHER=merchant_name_cipher, CIPHER_DATA_TYPE='varchar(100)', 
      ASSISTED_QUERY=merchant_name_assisted, ASSISTED_QUERY_DATA_TYPE='varchar(100)', 
      LIKE_QUERY=merchant_name_like, LIKE_QUERY_DATA_TYPE='varchar(50)', 
      ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), 
      ASSISTED_QUERY_ALGORITHM(TYPE(NAME='MD5',PROPERTIES('salt'='121212'))), 
      LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE',PROPERTIES('delta'='2'))), QUERY_WITH_CIPHER_COLUMN=true
    )
  ), QUERY_WITH_PLAIN=true
);

3. Delete Encryption Rule #

Example

Delete encryption rule

copyDROP ENCRYPT RULE t_encrypt, t_encrypt_2;

Delete encryption rule with ifExists clause

copyDROP ENCRYPT RULE IF EXISTS t_encrypt, t_encrypt_2;

4. View Encryption Rule #

copySHOW ENCRYPT RULES [FROM databaseName]

Example

Query all data encryption rules for specified logical database

copymysql> SHOW ENCRYPT RULES FROM encrypt_db;
+------------+---------------+-----------------+----------------------+------------------+---------------------+-----------------+------------------------+--------------------------+--------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+--------------------------+
| table      | logic_column  | logic_data_type | cipher_column        | cipher_data_type | plain_column        | plain_data_type | assisted_query_column  | assisted_query_data_type | like_query_column  | like_query_data_type | encryptor_type | encryptor_props         | assisted_query_type | assisted_query_props | like_query_type  | like_query_props | query_with_plain         |
+------------+---------------+-----------------+----------------------+------------------+---------------------+-----------------+------------------------+--------------------------+--------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+--------------------------+
| t_merchant | merchant_name | varchar(20)     | merchant_name_cipher | varchar(100)     | merchant_name_plain | varchar(20)     | merchant_name_assisted | varchar(100)             | merchant_name_like | varchar(50)          | AES            | aes-key-value=123456abc | MD5                 | salt=121212          | CHAR_DIGEST_LIKE | delta=2          | true                     |
+------------+---------------+-----------------+----------------------+------------------+---------------------+-----------------+------------------------+--------------------------+--------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+--------------------------+
1 rows in set (0.78 sec)

Example

Query specified data encryption rules for specified logical database

copymysql> SHOW ENCRYPT TABLE RULE t_encrypt;
+------------+---------------+-----------------+----------------------+------------------+---------------------+-----------------+------------------------+--------------------------+--------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+--------------------------+
| table      | logic_column  | logic_data_type | cipher_column        | cipher_data_type | plain_column        | plain_data_type | assisted_query_column  | assisted_query_data_type | like_query_column  | like_query_data_type | encryptor_type | encryptor_props         | assisted_query_type | assisted_query_props | like_query_type  | like_query_props | query_with_plain         |
+------------+---------------+-----------------+----------------------+------------------+---------------------+-----------------+------------------------+--------------------------+--------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+--------------------------+
| t_encrypt  | merchant_name | varchar(20)     | merchant_name_cipher | varchar(100)     | merchant_name_plain | varchar(20)     | merchant_name_assisted | varchar(100)             | merchant_name_like | varchar(50)          | AES            | aes-key-value=123456abc | MD5                 | salt=121212          | CHAR_DIGEST_LIKE | delta=2          | true                     |
+------------+---------------+-----------------+----------------------+------------------+---------------------+-----------------+------------------------+--------------------------+--------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+--------------------------+
2 rows in set (0.01 sec)

mysql> SHOW ENCRYPT TABLE RULE t_encrypt FROM encrypt_db;
+------------+---------------+-----------------+----------------------+------------------+---------------------+-----------------+------------------------+--------------------------+--------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+--------------------------+
| table      | logic_column  | logic_data_type | cipher_column        | cipher_data_type | plain_column        | plain_data_type | assisted_query_column  | assisted_query_data_type | like_query_column  | like_query_data_type | encryptor_type | encryptor_props         | assisted_query_type | assisted_query_props | like_query_type  | like_query_props | query_with_plain         |
+------------+---------------+-----------------+----------------------+------------------+---------------------+-----------------+------------------------+--------------------------+--------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+--------------------------+
| t_encrypt  | merchant_name | varchar(20)     | merchant_name_cipher | varchar(100)     | merchant_name_plain | varchar(20)     | merchant_name_assisted | varchar(100)             | merchant_name_like | varchar(50)          | AES            | aes-key-value=123456abc | MD5                 | salt=121212          | CHAR_DIGEST_LIKE | delta=2          | true                     |
+------------+---------------+-----------------+----------------------+------------------+---------------------+-----------------+------------------------+--------------------------+--------------------+----------------------+----------------+-------------------------+---------------------+----------------------+------------------+------------------+--------------------------+
2 rows in set (0.01 sec))

Output Description

Column	Description
table	Logical table name
logic_column	Logical column name
logic_data_type	Logical data type
cipher_column	Cipher column name
cipher_data_type	Cipher column data type
plain_column	Plain column name
plain_data_type	Plain column data type
assisted_query_column	Auxiliary query column name
assisted_query_data_type	Auxiliary query column data type
encryptor_type	Encryption algorithm type
encryptor_props	Encryption algorithm properties
query_with_plain	Whether to use plain text columns for query

5. Create Key Management Rule #

copycreateEncryptKeyManager
    : CREATE ENCRYPT KEY MANAGER keyManagerName keyManagerDefinition
    ;

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

keyManagerProperties:
    keyManagerProperty [, keyManagerProperty] ...

keyManagerProperty:
    key=value

Parameter Description

Name	Data Type	Description
keyManagerName	STRING	Key manager name

Example

Local key management

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

Cloud key management

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

6. Modify Key Management Rule #

copyalterEncryptKeyManager
    : ALTER ENCRYPT KEY MANAGER keyManagerName keyManagerDefinition
    ;

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

keyManagerProperties:
    keyManagerProperty [, keyManagerProperty] ...

keyManagerProperty:
    key=value

Parameter Description

Name	Data Type	Description
keyManagerName	STRING	Key manager name

Example

Local key management

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

Cloud key management

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

7. Delete Key Management Rule #

copyDROP ENCRYPT KEY MANAGER ifExists? keyManagerName;

Parameter Description

Name	Data Type	Description
keyManagerName	STRING	Key manager name

Example

copyDROP ENCRYPT KEY MANAGER ifExists Aws_key_Manager;

8. Query Decryption Result #

Consistent with select usage, the decrypt() function is provided. tableName is the table to be decrypted, encryptColumn is the column to be decrypted, and encryptValue is the real value to be decrypted. When using it, the corresponding encryption rules need to be configured.

copyselect [,string] … [,] decrypt({tableName}, {encryptColumn}, {encryptValue}), [,string] …

Example

copySELECT 'Lm04PRW+sp+mG/QuimAUew==', decrypt(t_user, pwd_cipher, 'Lm04PRW+sp+mG/QuimAUew==') UNION SELECT 'XXX', decrypt(t_user, pwd_cipher, 'XXX');

9. Convert non-encrypted table to encrypted table #

Syntax

copyconvertEncryptTable
    : CONVERT TABLE tableName BY ENCRYPT RULE encryptTableRuleDefinition startEncryptingJob?
    ;

startEncryptingJob
    : ',' START_ENCRYPTING_JOB '=' booleanLiterals
    ;

booleanLiterals
    : true | false
    ;

Used to convert an ordinary single table into an encrypted table. The execution results include:
- Execute DDL to create derived columns required by encryption rules
- Create encryption rules
- If startEncryptingJob is true, the number washing task is automatically triggered
Supports MySQL, Oracle and Hive type storage units

Parameter Description

Name	Data Type	Description
tableName	IDENTIFIER	Table name
encryptTableRuleDefinition	TEXT	Encryption rule definition, consistent with the format in CREATE ENCRYPT RULE
booleanLiterals	BOOLEAN	Boolean value

Example

copyCONVERT TABLE t_single BY ENCRYPT RULE (
COLUMNS(
(NAME=username, DATA_TYPE='varchar(100)', PLAIN=username,
CIPHER=cipher_column, CIPHER_DATA_TYPE='varchar(200)',
ASSISTED_QUERY_COLUMN=assisted_column, ASSISTED_QUERY_DATA_TYPE='varchar(300)',
LIKE_QUERY=like_column, LIKE_QUERY_DATA_TYPE='varchar(400)',
ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
ASSISTED_QUERY_ALGORITHM(TYPE(NAME='MD5')),
LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE'))
)
), QUERY_WITH_PLAIN=true, START_ENCRYPTING_JOB=TRUE);

10. Add plaintext column to encrypted table #

Syntax

copyconvertTableAddPlain
    : CONVERT TABLE tableName ADD PLAIN addPlainColumnsDefinition
    ;

addPlainColumnsDefinition
    : COLUMNS '(' columnName (',' columnName)* ')' startDecryptingJob?
    ;

startDecryptingJob
    : ',' START_DECRYPTING_JOB '=' booleanLiterals
    ;

booleanLiterals
    : true | false
    ;

Used to add plaintext columns to an existing encrypted table. The execution results include:
- Execute DDL to create the plaintext columns required by the encryption rules
- Modify encryption rules
- If startDecryptingJob is true, the anti-washing task will be automatically triggered
Supports MySQL, Oracle and Hive type storage units

Parameter Description

Name	Data Type	Description
tableName	IDENTIFIER	Table name
columnName	IDENTIFIER	Logic column name
booleanLiterals	BOOLEAN	Boolean value

Example

copyCONVERT TABLE t_encrypt ADD PLAIN COLUMNS(password), START_DECRYPTING_JOB=TRUE;

11. Remove plaintext column for encrypted table #

Syntax

copyconvertTableDropPlain
    : CONVERT TABLE tableName DROP PLAIN dropColumnsDefinition
    ;

dropColumnsDefinition
    : COLUMNS '(' columnName (',' columnName)* ')'
    ;

Used to remove plaintext columns from an existing encrypted table. The execution results include:
- Execute DDL and delete the specified plaintext column
- Modify encryption rules
Supports MySQL and Oracle type storage units

Parameter Description

Name	Data Type	Description
tableName	IDENTIFIER	Table name
columnName	IDENTIFIER	Logic column name

Example

copyCONVERT TABLE t_encrypt DROP PLAIN COLUMNS(password);

12. Add fuzzy query columns to encrypted tables #

Syntax

copyconvertTableAddLike
    : CONVERT TABLE tableName ADD LIKE addLikeColumnsDefinition
    ;

addLikeColumnsDefinition
    : '(' COLUMNS '(' likeColumnDefinition (',' likeColumnDefinition)* ')' startEncryptingJob? ')'
    ;

likeColumnDefinition
    : '(' NAME '=' columnName ',' likeQueryColumnDefinition ',' likeQueryAlgorithm ')'
    ;

likeQueryColumnDefinition
    : (LIKE_QUERY | LIKE_QUERY_COLUMN) '=' likeQueryColumnName (',' LIKE_QUERY_DATA_TYPE '=' dataType)?
    ;

likeQueryAlgorithm
    : LIKE_QUERY_ALGORITHM '(' algorithmDefinition ')'
    ;

startEncryptingJob
    : ',' START_ENCRYPTING_JOB '=' booleanLiterals
    ;

booleanLiterals
    : true | false
    ;

Used to add fuzzy query columns to existing encrypted tables. The execution results include:
- Execute DDL to create the like columns required by the encryption rules
- Modify encryption rules
- If startEncryptingJob is true, the number washing task is automatically triggered (only the like column is washed)
Supports MySQL, Oracle and Hive type storage units

Parameter Description

Name	Data Type	Description
tableName	IDENTIFIER	Table name
columnName	IDENTIFIER	Logic column name
likeQueryColumnName	IDENTIFIER	like column name
dataType	STRING	Data type
booleanLiterals	BOOLEAN	Boolean value

Example

copyCONVERT TABLE t_encrypt ADD like (
COLUMNS(
(name=password,
LIKE_QUERY=password_like,
LIKE_QUERY_DATA_TYPE='varchar(200)',
LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE')))
), START_ENCRYPTING_JOB=TRUE
);

13. Remove fuzzy query columns for encrypted tables #

Syntax

copyconvertTableDropLike
    : CONVERT TABLE tableName DROP LIKE dropColumnsDefinition
    ;

dropColumnsDefinition
    : COLUMNS '(' columnName (',' columnName)* ')'
    ;

Used to remove the like column from an existing encrypted table. The execution results include:
- Execute DDL and delete the specified like column
- Modify encryption rules
Supports MySQL and Oracle type storage units

Parameter Description

Name	Data Type	Description
tableName	IDENTIFIER	Table name
columnName	IDENTIFIER	Logic column name

Example

copyCONVERT TABLE t_encrypt DROP LIKE COLUMNS(password);

14. Toggle plaintext query switch #

Syntax

copyalterEncryptRuleSetQueryWithPlain
    : ALTER ENCRYPT RULE setQueryWithPlainItem (',' setQueryWithPlainItem)* SET QUERY_WITH_PLAIN '=' booleanLiterals
    ;

setQueryWithPlainItem
    : tableName ('(' columnName (',' columnName)* ')')?
    ;

booleanLiterals
    : true | false
    ;

Parameter Description

Name	Data Type	Description
tableName	IDENTIFIER	Table name
columnName	IDENTIFIER	Logic column name

Example

copy-- Switch by whole table
ALTER ENCRYPT RULE t_encrypt SET QUERY_WITH_PLAIN = true;
-- Switch by specified column
ALTER ENCRYPT RULE t_encrypt(password) SET QUERY_WITH_PLAIN = false;

15. Convert encrypted table to non-encrypted table #

Syntax

copyconvertTableDropEncryptRule
    : CONVERT TABLE tableName DROP ENCRYPT RULE
    ;

Used to convert an encrypted table into a non-encrypted table. The execution results include:
- Execute DDL to delete derived columns created for encryption rules
- Delete encryption rules
Supports MySQL and Oracle type storage units

Parameter Description

Name	Data Type	Description
tableName	IDENTIFIER	Table name

Example

copyCONVERT TABLE t_single DROP ENCRYPT RULE;