Data Encryption #

-!ENCRYPT
  encryptors:
    aes_encryptor:
      type: AES
      props:
        aes-key-value: 123456abc
  tables:
    t_user:
      columns:
        pwd:
          cipherColumn: pwd
          encryptorName: aes_encryptor

# mysql -utest -p -h192.168.xx.106

mysql> CREATE DATABASE ds_encryption;
Query OK, 1 row affected (0.05 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| ds_encryption      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

# mysql -uroot -p -P3307 -h127.0.0.1

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.85 sec)

mysql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

mysql> USE testdb;
Database changed

mysql> REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://192.168.xx.103:3306/mysql_0?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="Test@123"
);
Query OK, 0 rows affected (0.81 sec)

mysql> CREATE ENCRYPT RULE t_user (
  COLUMNS(
    (
      NAME = id_card_no,
      PLAIN = id_card_no_plain,
      CIPHER = id_card_no_cipher,
      ENCRYPT_ALGORITHM(
        TYPE(
          NAME = 'AES',
          PROPERTIES('aes-key-value' = '123456abc')
        )
      )
    ),
    (
      NAME = mobile,
      PLAIN = mobile_plain,
      CIPHER = mobile_cipher,
      ENCRYPT_ALGORITHM(
        TYPE(
          NAME = 'AES',
          PROPERTIES('aes-key-value' = '123456abc')
        )
      )
    )
  ),
  QUERY_WITH_CIPHER_COLUMN = true
);
Query OK, 0 rows affected (6.56 sec)

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: id_card_no
         logic_data_type:
           cipher_column: id_card_no_cipher
        cipher_data_type:
            plain_column: id_card_no_plain
         plain_data_type:
   assisted_column:
assisted_query_data_type:
       like_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: true
*************************** 2. row ***************************
                   table: t_user
            logic_column: mobile
         logic_data_type:
           cipher_column: mobile_cipher
        cipher_data_type:
            plain_column: mobile_plain
         plain_data_type:
   assisted_column:
assisted_query_data_type:
       like_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: true
2 rows in set (0.00 sec)

mysql> CREATE TABLE t_user (
    id INT(8), 
    mobile VARCHAR(50), 
    id_card_no VARCHAR(50)
);
Query OK, 0 rows affected (1.90 sec)

mysql> INSERT INTO t_user (id, mobile, id_card_no)
VALUES (1, 18236483857, 220605194709308170),
       (2, 15686689114, 360222198806088804),
       (3, 14523360225, 411601198601098107),
       (4, 18143924353, 540228199804231247),
       (5, 15523349333, 360924195311103360),
       (6, 13261527931, 513229195302236086),
       (7, 13921892133, 500108194806107214),
       (8, 15993370854, 451322194405305441),
       (9, 18044280924, 411329199808285772),
       (10, 13983621809, 430204195612042092);
Query OK, 10 rows affected (0.61 sec)

mysql> SELECT * FROM t_user;
+------+-------------+--------------------+
| id   | mobile      | id_card_no         |
+------+-------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |
|    2 | 15686689114 | 360222198806088804 |
|    3 | 14523360225 | 411601198601098107 |
|    4 | 18143924353 | 540228199804231247 |
|    5 | 15523349333 | 360924195311103360 |
|    6 | 13261527931 | 513229195302236086 |
|    7 | 13921892133 | 500108194806107214 |
|    8 | 15993370854 | 451322194405305441 |
|    9 | 18044280924 | 411329199808285772 |
|   10 | 13983621809 | 430204195612042092 |
+------+-------------+--------------------+
10 rows in set (0.25 sec)

mysql> SELECT * FROM t_user WHERE mobile = '18236483857';
+------+-------------+--------------------+
| id   | mobile      | id_card_no         |
+------+-------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |
+------+-------------+--------------------+
1 row in set (0.50 sec)

mysql>
mysql> SELECT * FROM t_user WHERE mobile = '18236483857'
    -> AND id_card_no = '220605194709308170';
+------+-------------+--------------------+
| id   | mobile      | id_card_no         |
+------+-------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |
+------+-------------+--------------------+
1 row in set (0.26 sec)

mysql -utest -p -h192.168.xx.103

mysql> USE ds_encryption;
Database changed

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_ds_encryption |
+-------------------------+
| t_user                  |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t_user;
+------+--------------------------+----------------------------------------------+
| id   | mobile_cipher            | id_card_no_cipher                            |
+------+--------------------------+----------------------------------------------+
|    1 | p31Pkl9nIunYdH+AngyNUA== | pQv0JEkM94QzktJdM8UMg/uLrU71G6n6DALdPp9w6L0= |
|    2 | CV8+uYRaWOzcTQnQX3RcwA== | dCF7k4haK0aIV/d7dtwgzIb4lIFlJ913hrPim1+J278= |
|    3 | jnfu7o44KgN/PV1zhiu7jw== | 8iulp3+XTSv2XHGUUHKV0UsLuFx7yEpQVT+47EFfg94= |
|    4 | ZJDrTv/XIjdqdG1yp0t95w== | iqU6myMGfgI/XnxCtjhbMrwIauriWu8crxPS6BH2pMk= |
|    5 | FnQMYGnFJaiWmTHeNYzbFA== | KAPrCXoo1svMt5NWe0UaKYZIl1rSEVddHbBJO1jPIqw= |
|    6 | lv2ECfTCgQQksvdPp6k3Ug== | BBBPAuwU+iJluI9d9TA+H81BPnVXBaly1BE3EplN4e8= |
|    7 | z46vpnHCFTkIF2EtntxpHQ== | Bc39nPtyz1ji9Rc8k4f7G9CKfPew23mKFwp8guK7ybg= |
|    8 | p/IJdGcCikhpCu5gVZj4jg== | nnv/kS1i7uHXKncUOuLzE8OWM0nGlcGkLokT2dltSaQ= |
|    9 | NvPcQv4w3EqD77+VAX0KCA== | +yeo5LWKNWcekFqYawCKjsctAZqe104DrI7AeZdR/Uk= |
|   10 | xOyg9E0X9lhy9mUx0QyL0A== | U7P1CMcxn6VPHYHPgTAtjHEbb6N6vhGOpdJtVjAdHlA= |
+------+--------------------------+----------------------------------------------+
10 rows in set (0.00 sec)

--First delete the inscription field in the database
--Then just refresh the metadata information in the Proxy
mysql> REFRESH table METADATA;

CREATE TABLE t_encrypt(
    id INT(8), 
    mobile VARCHAR(50), 
    id_card_no VARCHAR(50)
);

# mysql -uroot -proot -P3307 -h192.168.xx.102

mysql>use logical001;
mysql> INSERT INTO t_encrypt (id, mobile_plain, id_card_no)
    -> VALUES (1, 18236483857, 220605194709308170),
    ->        (2, 15686689114, 360222198806088804),
    ->        (3, 13983621809, 430204195612042092);
Query OK, 3 rows affected (0.02 sec)

mysql -uroot -proot -P3307 -h192.168.xx.102

mysql>use logical001;
mysql> SELECT * FROM t_encrypt;
+------+----------------------------------+--------------------+
| id   | mobile                           | id_card_no         |
+------+----------------------------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |                    |
|    2 | 15686689114 | 360222198806088804 |                    |
|    3 | 13983621809 | 430204195612042092 |                    |
+------+----------------------------------+--------------------+
3 rows in set (0.00 sec)

mysql -uroot -proot -P3306 -h192.168.xx.106

mysql> use t_encrypt_db;
mysql> select * from t_encrypt;
+------+----------------------------------+--------------+--------------------+
| id   | mobile_cipher                    | mobile_plain | id_card_no         |
+------+----------------------------------+--------------+--------------------+
|    1 | 6f655ea3b73e32d54ae2d34d672c0c55 | 18236483857  | 220605194709308170 |
|    2 | a2193d0e8de4c2c4f166448e22ba97ae | 15686689114  | 360222198806088804 |
|    3 | 3cf1fd442630071ca664bbd5e683f7f6 | 13983621809  | 430204195612042092 |
+------+----------------------------------+--------------+--------------------+

mysql> CREATE DATABASE ds_mysql;
Query OK, 1 row affected (0.00 sec)

mysql> USE ds_mysql;
Database changed

mysql> CREATE TABLE t_user (id int NOT NULL PRIMARY KEY, username varchar(50), password varchar(50), email varchar(50));
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO t_user (id, username, password, email) VALUES (1,'a','aa','a@t.c'),(2,'b','bb','b@t.c'),(3,'c','cc','c@t.c');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+
| id | username | password | email |
+----+----------+----------+-------+
|  1 | a        | aa       | a@t.c |
|  2 | b        | bb       | b@t.c |
|  3 | c        | cc       | c@t.c |
+----+----------+----------+-------+
3 rows in set (0.02 sec)

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.00 sec)

mysql> USE testdb;
Database changed

mysql> REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://192.168.xx.103:3306/ds_mysql?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="Test@123"
);
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW STORAGE UNITS\G
*************************** 1. row ***************************
                           name: ds_0
                           type: MySQL
                           host: 192.168.xx.103
                           port: 3306
                             db: ds_mysql
connection_timeout_milliseconds: 30000
      idle_timeout_milliseconds: 60000
      max_lifetime_milliseconds: 2100000
                  max_pool_size: 50
                  min_pool_size: 1
                      read_only: false
               other_attributes: {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"keepaliveTime":0,"leakDetectionThreshold":0,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false}
1 row in set (0.00 sec)

mysql> LOAD SINGLE TABLE ds_0.t_user;
Query OK, 0 rows affected (3.87 sec)

mysql> SHOW TABLES;
+------------------+------------+
| Tables_in_testdb | Table_type |
+------------------+------------+
| t_user           | BASE TABLE |
+------------------+------------+
1 row in set (0.01 sec)

mysql> ALTER TABLE t_user ADD COLUMN username_cipher VARCHAR(50);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_user;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | NO   | PRI | NULL    |       |
| username        | varchar(50) | YES  |     | NULL    |       |
| password        | varchar(50) | YES  |     | NULL    |       |
| email           | varchar(50) | YES  |     | NULL    |       |
| username_cipher | varchar(50) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+-----------------+
| id | username | password | email | username_cipher |
+----+----------+----------+-------+-----------------+
|  1 | a        | aa       | a@t.c | NULL            |
|  2 | b        | bb       | b@t.c | NULL            |
|  3 | c        | cc       | c@t.c | NULL            |
+----+----------+----------+-------+-----------------+
3 rows in set (0.02 sec)

CREATE ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=false)
));
Query OK, 0 rows affected (0.05 sec)


-- NAME：It is the field name in the business. Based on the starting point of application modification-free, it can be set to be consistent with the plain text field name.
-- PLAIN：Plain text field name
-- CIPHER：Cipher text field name
-- ENCRYPT_ALGORITHM：Encryption algorithm related configuration
-- QUERY_WITH_CIPHER_COLUMN：Whether to enable the ciphertext field. The current ciphertext field is empty, so it needs to be set to false.

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: true
1 row in set (0.05 sec)

mysql> PREVIEW SELECT * FROM t_user;
+------------------+----------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                         |
+------------------+----------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_user.`id`, t_user.`username` AS `username`, t_user.`password`, t_user.`email` FROM t_user |
+------------------+----------------------------------------------------------------------------------------------------+
1 row in set (0.99 sec)

CREATE ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=false)
));
Query OK, 0 rows affected (0.05 sec)


-- NAME：It is the field name in the business. Based on the starting point of application modification-free, it can be set to be consistent with the plain text field name.
-- PLAIN：Plain text field name
-- CIPHER：Cipher text field name
-- ENCRYPT_ALGORITHM：Encryption algorithm related configuration
-- QUERY_WITH_CIPHER_COLUMN：Whether to enable the ciphertext field. The current ciphertext field is empty, so it needs to be set to false.

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: true
1 row in set (0.05 sec)

mysql> PREVIEW SELECT * FROM t_user;
+------------------+----------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                         |
+------------------+----------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_user.`id`, t_user.`username` AS `username`, t_user.`password`, t_user.`email` FROM t_user |
+------------------+----------------------------------------------------------------------------------------------------+
1 row in set (0.99 sec)

ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true)
));
Query OK, 0 rows affected (0.02 sec)


mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: false
1 row in set (0.01 sec)

mysql> PREVIEW SELECT * FROM t_user;
+------------------+-----------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                |
+------------------+-----------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_user.`id`, t_user.`username_cipher` AS `username`, t_user.`password`, t_user.`email` FROM t_user |
+------------------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> COMMIT ENCRYPTING j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789;
Query OK, 0 rows affected (0.76 sec)

mysql> ALTER TABLE t_user ADD COLUMN password_cipher VARCHAR(50);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_user;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | NO   | PRI | NULL    |       |
| username        | varchar(50) | YES  |     | NULL    |       |
| password        | varchar(50) | YES  |     | NULL    |       |
| email           | varchar(50) | YES  |     | NULL    |       |
| username_cipher | varchar(50) | YES  |     | NULL    |       |
| password_cipher | varchar(50) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+--------------------------+-----------------+
| id | username | password | email | username_cipher          | password_cipher |
+----+----------+----------+-------+--------------------------+-----------------+
|  1 | a        | aa       | a@t.c | T25iRZWea6YFzmT4LqH9ag== | NULL            |
|  2 | b        | bb       | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | NULL            |
|  3 | c        | cc       | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | NULL            |
+----+----------+----------+-------+--------------------------+-----------------+
3 rows in set (0.01 sec)

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=false)
, (NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=false)
));

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: true
*************************** 2. row ***************************
                   table: t_user
            logic_column: password
         logic_data_type:
           cipher_column: password_cipher
        cipher_data_type:
            plain_column: password
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: true
2 rows in set (0.02 sec)

mysql> ENCRYPT TABLE t_user (password);
Query OK, 0 rows affected (0.27 sec)

mysql> SHOW ENCRYPTING LIST;
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| id                                         | tables | columns  | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789 | t_user | password | 1              | true   | 2023-09-25 17:36:41 | NULL      |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
1 row in set (0.03 sec)

mysql> SHOW ENCRYPTING STATUS j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | ds_0        | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.03 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+--------------------------+--------------------------+
| id | username | password | email | username_cipher          | password_cipher          |
+----+----------+----------+-------+--------------------------+--------------------------+
|  1 | a        | aa       | a@t.c | T25iRZWea6YFzmT4LqH9ag== | gg9Qid/hjjHgNk8TgeBK5g== |
|  2 | b        | bb       | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 7i7GQMpZxkWXoXB01NJryQ== |
|  3 | c        | cc       | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | TTDgqe6nMJhnqmy2soADnw== |
+----+----------+----------+-------+--------------------------+--------------------------+
3 rows in set (0.01 sec)

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true),
(NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true)
));

mysql> PREVIEW SELECT * FROM t_user;
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                     |
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_user.`id`, t_user.`username_cipher` AS `username`, t_user.`password_cipher` AS `password`, t_user.`email` FROM t_user |
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+
| id | username | password | email |
+----+----------+----------+-------+
|  1 | a        | aa       | a@t.c |
|  2 | b        | bb       | b@t.c |
|  3 | c        | cc       | c@t.c |
+----+----------+----------+-------+
3 rows in set (0.02 sec)

mysql> COMMIT ENCRYPTING j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789;
Query OK, 0 rows affected (0.69 sec)

mysql> ALTER TABLE t_user ADD COLUMN username_like VARCHAR(50);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_user;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | NO   | PRI | NULL    |       |
| username        | varchar(50) | YES  |     | NULL    |       |
| password        | varchar(50) | YES  |     | NULL    |       |
| email           | varchar(50) | YES  |     | NULL    |       |
| username_cipher | varchar(50) | YES  |     | NULL    |       |
| password_cipher | varchar(50) | YES  |     | NULL    |       |
| username_like   | varchar(50) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
| id | username | password | email | username_cipher          | password_cipher          | username_like |
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
|  1 | a        | aa       | a@t.c | T25iRZWea6YFzmT4LqH9ag== | gg9Qid/hjjHgNk8TgeBK5g== | NULL          |
|  2 | b        | bb       | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 7i7GQMpZxkWXoXB01NJryQ== | NULL          |
|  3 | c        | cc       | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | TTDgqe6nMJhnqmy2soADnw== | NULL          |
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
3 rows in set (0.01 sec)

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, LIKE_QUERY_COLUMN=username_like, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE')), QUERY_WITH_CIPHER_COLUMN=false)
, (NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=false)
));

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column: username_like
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type: CHAR_DIGEST_LIKE
        like_query_props:
        query_with_plain: true
*************************** 2. row ***************************
                   table: t_user
            logic_column: password
         logic_data_type:
           cipher_column: password_cipher
        cipher_data_type:
            plain_column: password
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: true
2 rows in set (0.02 sec)

mysql> ENCRYPT TABLE t_user (username(LIKE_QUERY));
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW ENCRYPTING LIST;
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| id                                         | tables | columns  | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
| j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789 | t_user | username | 1              | true   | 2023-09-25 17:47:26 | NULL      |
+--------------------------------------------+--------+----------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> SHOW ENCRYPTING STATUS j5202p0000e2af8bea809d29f6ab4b3ac4c99b5789;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | ds_0        | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.03 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
| id | username | password | email | username_cipher          | password_cipher          | username_like |
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
|  1 | a        | aa       | a@t.c | T25iRZWea6YFzmT4LqH9ag== | gg9Qid/hjjHgNk8TgeBK5g== | `             |
|  2 | b        | bb       | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 7i7GQMpZxkWXoXB01NJryQ== | a             |
|  3 | c        | cc       | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | TTDgqe6nMJhnqmy2soADnw== | d             |
+----+----------+----------+-------+--------------------------+--------------------------+---------------+
3 rows in set (0.00 sec)

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, LIKE_QUERY_COLUMN=username_like, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE')), QUERY_WITH_CIPHER_COLUMN=true)
, (NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true)
));

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: username
         logic_data_type:
           cipher_column: username_cipher
        cipher_data_type:
            plain_column: username
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column: username_like
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type: CHAR_DIGEST_LIKE
        like_query_props:
        query_with_plain: false
*************************** 2. row ***************************
                   table: t_user
            logic_column: password
         logic_data_type:
           cipher_column: password_cipher
        cipher_data_type:
            plain_column: password
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
        query_with_plain: false
2 rows in set (0.01 sec)

mysql> PREVIEW SELECT * FROM t_user;
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                     |
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT t_user.`id`, t_user.`username_cipher` AS `username`, t_user.`password_cipher` AS `password`, t_user.`email` FROM t_user |
+------------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT * FROM t_user;
+----+----------+----------+-------+
| id | username | password | email |
+----+----------+----------+-------+
|  1 | a        | aa       | a@t.c |
|  2 | b        | bb       | b@t.c |
|  3 | c        | cc       | c@t.c |
+----+----------+----------+-------+
3 rows in set (0.03 sec)

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
port=3306
character-set-server=utf8mb4
character-set-filesystem=utf8mb4
collation-server=utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'

CREATE DATABASE encrypt_db;

USE encrypt_db;

REGISTER STORAGE UNIT ds_0 (
    HOST="127.0.0.1",
    PORT=3306,
    DB="demo_ds_0",
    USER="root",
    PASSWORD="123456"
), ds_1 (
    HOST="127.0.0.1",
    PORT=3306,
    DB="demo_ds_1",
    USER="root",
    PASSWORD="123456"
);

CREATE ENCRYPT KEY MANAGER aws_kms_key_manager (TYPE(NAME='SphereEx:AWS_KMS', PROPERTIES('access-key'='AKIAXRZPPZNGXS6VJUGY','secret-key'='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','aws-region'='ap-northeast-1','secret-name'='dbplus-engine/encrypt-key')));

CREATE ENCRYPT RULE t_user (
        COLUMNS(
                (
                        NAME=user_name,PLAIN=user_name_plain,CIPHER=user_name_cipher,LIKE_COLUMN=user_name_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                ),
                (
                        NAME=password, CIPHER =password_cipher,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
                ),
                (
                        NAME=email, CIPHER =email_cipher,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
                ),
                (
                        NAME=telephone,PLAIN=telephone_plain,CIPHER=telephone_cipher,LIKE_COLUMN=telephone_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                )
        ), QUERY_WITH_CIPHER_COLUMN = TRUE
), t_merchant (
        COLUMNS(
                (
                        NAME=business_code,PLAIN=business_code_plain,CIPHER=business_code_cipher,LIKE_COLUMN=business_code_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                ),
                (
                        NAME=telephone,PLAIN=telephone_plain,CIPHER=telephone_cipher,LIKE_COLUMN=telephone_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                )
        ), QUERY_WITH_CIPHER_COLUMN = TRUE
);

USE encrypt_db;

DROP TABLE IF EXISTS t_merchant;


CREATE TABLE t_merchant (
  merchant_id INT PRIMARY KEY, 
  country_id SMALLINT NOT NULL, 
  merchant_name VARCHAR(50) NOT NULL, 
  business_code VARCHAR(50) NOT NULL, 
  telephone CHAR(50) NOT NULL, 
  creation_date DATE NOT NULL);

INSERT INTO t_merchant ......

CREATE TABLE t_user (
  user_id INT PRIMARY KEY, 
  user_name VARCHAR(50) NOT NULL, 
  password VARCHAR(50) NOT NULL, 
  email VARCHAR(50) NOT NULL, 
  telephone CHAR(50) NOT NULL, 
  creation_date DATE NOT NULL);

INSERT INTO t_user ......

-- 测试 % 前缀匹配
SELECT * FROM t_merchant WHERE business_code LIKE '%1';
+-------------+------------+---------------+---------------+-------------+---------------+
| merchant_id | country_id | merchant_name | business_code | telephone   | creation_date |
+-------------+------------+---------------+---------------+-------------+---------------+
|           1 |         86 | test101       | 86000001      | 86100000001 | 2017-08-08    |
|          11 |          1 | test111       | 01000011      | 01100000011 | 2017-08-08    |
+-------------+------------+---------------+---------------+-------------+---------------+
2 rows in set (0.01 sec)

-- 测试 % 后缀匹配
SELECT * FROM t_merchant WHERE business_code LIKE '86%';
+-------------+------------+---------------+---------------+-------------+---------------+
| merchant_id | country_id | merchant_name | business_code | telephone   | creation_date |
+-------------+------------+---------------+---------------+-------------+---------------+
|           1 |         86 | test101       | 86000001      | 86100000001 | 2017-08-08    |
|           2 |         86 | test102       | 86000002      | 86100000002 | 2017-08-08    |
|           3 |         86 | test103       | 86000003      | 86100000003 | 2017-08-08    |
|           4 |         86 | test104       | 86000004      | 86100000004 | 2017-08-08    |
|           5 |         86 | test105       | 86000005      | 86100000005 | 2017-08-08    |
|           6 |         86 | test106       | 86000006      | 86100000006 | 2017-08-08    |
|           7 |         86 | test107       | 86000007      | 86100000007 | 2017-08-08    |
|           8 |         86 | test108       | 86000008      | 86100000008 | 2017-08-08    |
|           9 |         86 | test109       | 86000009      | 86100000009 | 2017-08-08    |
|          10 |         86 | test110       | 86000010      | 86100000010 | 2017-08-08    |
+-------------+------------+---------------+---------------+-------------+---------------+
10 rows in set (0.01 sec)

-- 测试 % 前缀匹配
SELECT * FROM t_user WHERE user_name LIKE '%san';
+---------+-----------+----------+--------------------+-------------+---------------+
| user_id | user_name | password | email              | telephone   | creation_date |
+---------+-----------+----------+--------------------+-------------+---------------+
|      10 | zhangsan  | 111111   | zhangsan@gmail.com | 12345678900 | 2017-08-08    |
|      19 | zhousan   | 123456   | zhousan@gmail.com  | 12345678909 | 2017-08-08    |
+---------+-----------+----------+--------------------+-------------+---------------+
2 rows in set (0.01 sec)

-- 测试 % 后缀匹配
SELECT * FROM t_user WHERE user_name LIKE 'j%';
+---------+-----------+----------+------------------+-------------+---------------+
| user_id | user_name | password | email            | telephone   | creation_date |
+---------+-----------+----------+------------------+-------------+---------------+
|      22 | jerry     | 456789   | jerry@gmail.com  | 12345678912 | 2017-08-08    |
|      23 | james     | 567890   | james@gmail.com  | 12345678913 | 2017-08-08    |
|      27 | jack      | 222333   | jack@gmail.com   | 12345678917 | 2017-08-08    |
|      28 | jordan    | 333444   | jordan@gmail.com | 12345678918 | 2017-08-08    |
|      29 | julie     | 444555   | julie@gmail.com  | 12345678919 | 2017-08-08    |
+---------+-----------+----------+------------------+-------------+---------------+
5 rows in set (0.01 sec)

- 测试 % 前缀匹配
SELECT * FROM t_user WHERE user_name LIKE '%靖';
+---------+-----------+----------+-------------------+-------------+---------------+
| user_id | user_name | password | email             | telephone   | creation_date |
+---------+-----------+----------+-------------------+-------------+---------------+
|      34 | 郭靖      | 123123   | guojing@gmail.com | 18012512345 | 2017-08-08    |
+---------+-----------+----------+-------------------+-------------+---------------+
1 row in set (0.01 sec)

-- 测试 % 后缀匹配
SELECT * FROM t_user WHERE user_name LIKE '王%';
+---------+-----------+----------+--------------------------+-------------+---------------+
| user_id | user_name | password | email                    | telephone   | creation_date |
+---------+-----------+----------+--------------------------+-------------+---------------+
|      35 | 王之涣    | 234234   | wangzhihuan@gmail.com | 18012512345 | 2017-08-08    |
|      36 | 王安石    | 345345   | jeff@gmail.com           | 18012512345 | 2017-08-08    |
|      37 | 王羲之    | 345345   | wangxizhi@gmail.com      | 18012512345 | 2017-08-08    |
|      38 | 王莽      | 456456   | wangmang@gmail.com       | 18012512345 | 2017-08-08    |
|      39 | 王勃      | 567567   | wangbo@gmail.com         | 18012512345 | 2017-08-08    |
+---------+-----------+----------+--------------------------+-------------+---------------+
5 rows in set (0.01 sec)

-- Remove plaintext columns in encryption rules：

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, CIPHER=username_cipher, LIKE_QUERY_COLUMN=username_like, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE')), QUERY_WITH_CIPHER_COLUMN=true)
, (NAME=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true)
));

-- Remove plaintext columns in MySQL：

mysql> ALTER TABLE t_user DROP COLUMN username;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_user DROP COLUMN password;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_user ADD COLUMN username VARCHAR(50);
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_user ADD COLUMN password VARCHAR(50);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_user;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id              | int(11)     | NO   | PRI | NULL    |       |
| email           | varchar(50) | YES  |     | NULL    |       |
| username_cipher | varchar(50) | YES  |     | NULL    |       |
| password_cipher | varchar(50) | YES  |     | NULL    |       |
| username_like   | varchar(50) | YES  |     | NULL    |       |
| username        | varchar(50) | YES  |     | NULL    |       |
| password        | varchar(50) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
7 rows in set (0.02 sec)

mysql> SELECT * FROM t_user;
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
| id | email | username_cipher          | password_cipher          | username_like | username | password |
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
|  1 | a@t.c | T25iRZWea6YFzmT4LqH9ag== | gg9Qid/hjjHgNk8TgeBK5g== | `             | NULL     | NULL     |
|  2 | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 7i7GQMpZxkWXoXB01NJryQ== | a             | NULL     | NULL     |
|  3 | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | TTDgqe6nMJhnqmy2soADnw== | d             | NULL     | NULL     |
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
3 rows in set (0.01 sec)

mysql> ALTER ENCRYPT RULE t_user (COLUMNS(
(NAME=username, PLAIN=username, CIPHER=username_cipher, LIKE_QUERY_COLUMN=username_like, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE')), QUERY_WITH_CIPHER_COLUMN=true)
, (NAME=password, PLAIN=password, CIPHER=password_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))), QUERY_WITH_CIPHER_COLUMN=true)
));

mysql> SELECT * FROM t_user;
+----+-------+----------+----------+
| id | email | username | password |
+----+-------+----------+----------+
|  1 | a@t.c | a        | aa       |
|  2 | b@t.c | b        | bb       |
|  3 | c@t.c | c        | cc       |
+----+-------+----------+----------+
3 rows in set (0.04 sec)

mysql> ALTER DECRYPTING RULE(
READ(
  WORKER_THREAD=20,
  BATCH_SIZE=1000,
  SHARDING_SIZE=1000000
),
WRITE(
  WORKER_THREAD=20,
  BATCH_SIZE=1000
),
STREAM_CHANNEL(TYPE(NAME="MEMORY", PROPERTIES("block-queue-size"=1000)))
);

mysql> SHOW DECRYPTING RULE;

-- Specify any number of deshuffle data fields, that is, deshuffle data by column. If not specified, all encrypted fields will be deshuffle, that is, the data will be deshuffle by tables.mysql> DECRYPT table t_user (username, password);
Query OK, 0 rows affected (1.18 sec)

mysql> SHOW DECRYPTING LIST;
+--------------------------------------------+--------+-------------------+----------------+--------+---------------------+-----------+
| id                                         | tables | columns           | job_item_count | active | create_time         | stop_time |
+--------------------------------------------+--------+-------------------+----------------+--------+---------------------+-----------+
| j5302p0000e2af8bea809d29f6ab4b3ac4c99b5789 | t_user | username,password | 1              | true   | 2023-09-25 18:20:24 | NULL      |
+--------------------------------------------+--------+-------------------+----------------+--------+---------------------+-----------+
1 row in set (0.02 sec)

mysql> SHOW DECRYPTING STATUS j5302p0000e2af8bea809d29f6ab4b3ac4c99b5789;
+------+-------------+----------+--------+---------------------+---------------+
| item | data_source | status   | active | finished_percentage | error_message |
+------+-------------+----------+--------+---------------------+---------------+
| 0    | testdb      | FINISHED | true   | 100                 |               |
+------+-------------+----------+--------+---------------------+---------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t_user;
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
| id | email | username_cipher          | password_cipher          | username_like | username | password |
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
|  1 | a@t.c | T25iRZWea6YFzmT4LqH9ag== | gg9Qid/hjjHgNk8TgeBK5g== | `             | a        | aa       |
|  2 | b@t.c | fRwca8JpNUTHo0xBYTfWuw== | 7i7GQMpZxkWXoXB01NJryQ== | a             | b        | bb       |
|  3 | c@t.c | GnlS+xZTncXuH5D7JLi4CA== | TTDgqe6nMJhnqmy2soADnw== | d             | c        | cc       |
+----+-------+--------------------------+--------------------------+---------------+----------+----------+
3 rows in set (0.00 sec)

mysql> COMMIT DECRYPTING j5302p0000e2af8bea809d29f6ab4b3ac4c99b5789;
Query OK, 0 rows affected (0.60 sec)

mysql> create datbase demo_ds_0;

mysql> create database encrypt_db;

mysql> use encrypt_db

mysql> REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=True",
    USER="root",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"="100","idleTimeout"="30000")
)

CREATE ENCRYPT RULE t_encrypt (
COLUMNS(
(NAME=user_id,PLAIN=user_plain,CIPHER=user_cipher,ASSISTED_COLUMN=user_assisted,LIKE_COLUMN=user_like,ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),ASSISTED_QUERY_ALGORITHM(TYPE(NAME='MD5')), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE'))),
(NAME=order_id, PLAIN=order_plain,CIPHER =order_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='654321abc'))))
), QUERY_WITH_CIPHER_COLUMN=true);

mysql> create table t_encrypt (
  id int primary key, 
  user_id varchar(200),
  rder_id varchar(200), 
  name varchar(200));

mysql> insert into t_encrypt values 
  (1,'a','aa','aaa'),
  (2,'b','bb','bbb'),
  (3,'c','cc','ccc');

mysql> update t_encrypt set user_plain = null,order_plain =null;

mysql> select * from t_encrypt;
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
| id | user_cipher              | user_assisted                    | user_like | user_plain | order_cipher             | order_plain | name |
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
|  1 | FrpvlLvZyXewBRRnbeNnbA== | 0cc175b9c0f1b6a831c399e269772661 | `         | NULL       | 7xINTg7CqT86N1xDAHXgxg== | NULL        | aaa  |
|  2 | XWj53fUjwGnpP/LDiJWvow== | 92eb5ffee6ae2fec3ad71c777531578f | a         | NULL       | oTVrU2sLf+HWvYn9FJ04Zw== | NULL        | bbb  |
|  3 | MglbJfLRYx339IXZ3VHDVw== | 4a8a08f09d37b73795649038408b5f33 | d         | NULL       | zsz41oPgr06KG3WWrYixIg== | NULL        | ccc  |
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
3 rows in set (0.00 sec)

mysql> REENCRYPT TABLE WITH RULE t_encrypt (
COLUMNS(
(NAME=user_id,PLAIN=user_plain,CIPHER=user_cipher,ASSISTED_COLUMN=user_assisted,LIKE_COLUMN=user_like,ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='665544aaa'))),ASSISTED_QUERY_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='523234abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE'))),
(NAME=order_id, PLAIN=order_plain,CIPHER =order_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='445533ccc'))))
), QUERY_WITH_CIPHER_COLUMN=true);

mysql> SHOW REENCRYPTING LIST;

mysql> SHOW REENCRYPTING LIST;
+---------------------------------------+-----------+----------------+--------+---------------------+-----------+
| id                                    | tables    | job_item_count | active | create_time         | stop_time |
+---------------------------------------+-----------+----------------+--------+---------------------+-----------+
| j54014fb26631db0f7149741422d86cc5dfe6 | t_encrypt | 1              | true   | 2022-12-23 18:08:19 | NULL      |
+---------------------------------------+-----------+----------------+--------+---------------------+-----------+
1 row in set (0.21 sec)

-- When the task status is found to reach SUSPEND, the first phase of the task is completed (completion of the backwash count and switch to plaintext query)
mysql> SHOW REENCRYPTING STATUS j54014fb26631db0f7149741422d86cc5dfe6;
+------+-------------+---------+--------+--------------------------------+---------------+
| item | data_source | status  | active | encrypting_finished_percentage | error_message |
+------+-------------+---------+--------+--------------------------------+---------------+
| 0    | encrypt_db  | SUSPEND | true   | 50                             |               |
+------+-------------+---------+--------+--------------------------------+---------------+
1 row in set (0.01 sec)

SHOW ENCRYPT RULES;
-- Can be seen that the rule uses plaintext queries
mysql> SHOW ENCRYPT RULES\G
*************************** 1. row ***************************
                   table: t_encrypt
            logic_column: user_id
         logic_data_type:
           cipher_column: user_cipher
        cipher_data_type:
            plain_column: user_plain
         plain_data_type:
   assisted_column: user_assisted
assisted_query_data_type:
       like_column: user_like
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type: MD5
    assisted_query_props:
         like_query_type: CHAR_DIGEST_LIKE
        like_query_props:
query_with_cipher_column: false
*************************** 2. row ***************************
                   table: t_encrypt
            logic_column: order_id
         logic_data_type:
           cipher_column: order_cipher
        cipher_data_type:
            plain_column: order_plain
         plain_data_type:
   assisted_column:
assisted_query_data_type:
       like_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=654321abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: false
2 rows in set (0.00 sec)

mysql> select * from t_encrypt;
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
| id | user_cipher              | user_assisted                    | user_like | user_plain | order_cipher             | order_plain | name |
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
|  1 | FrpvlLvZyXewBRRnbeNnbA== | 0cc175b9c0f1b6a831c399e269772661 | `         | a          | 7xINTg7CqT86N1xDAHXgxg== | aa          | aaa  |
|  2 | XWj53fUjwGnpP/LDiJWvow== | 92eb5ffee6ae2fec3ad71c777531578f | a         | b          | oTVrU2sLf+HWvYn9FJ04Zw== | bb          | bbb  |
|  3 | MglbJfLRYx339IXZ3VHDVw== | 4a8a08f09d37b73795649038408b5f33 | d         | c          | zsz41oPgr06KG3WWrYixIg== | cc          | ccc  |
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
3 rows in set (0.01 sec)

START REENCRYPTING j54014fb26631db0f7149741422d86cc5dfe6;

-- When the task status is found to be FINISHED, the second phase of the task is completed (key exchange, clean up encrypted data, shard, and switch to ciphertext query)
    
mysql> SHOW REENCRYPTING STATUS j54014fb26631db0f7149741422d86cc5dfe6;
+------+-------------+----------+--------+--------------------------------+---------------+
| item | data_source | status   | active | encrypting_finished_percentage | error_message |
+------+-------------+----------+--------+--------------------------------+---------------+
| 0    | encrypt_db  | FINISHED | true   | 100                            |               |
+------+-------------+----------+--------+--------------------------------+---------------+
1 row in set (0.00 sec)

-- The key was found to be replaced and queried using the encrypted column

mysql> show encrypt rules\G
*************************** 1. row ***************************
                   table: t_encrypt
            logic_column: user_id
         logic_data_type:
           cipher_column: user_cipher
        cipher_data_type:
            plain_column: user_plain
         plain_data_type:
   assisted_column: user_assisted
assisted_query_data_type:
       like_column: user_like
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=665544aaa
     assisted_query_type: AES
    assisted_query_props: aes-key-value=523234abc
         like_query_type: CHAR_DIGEST_LIKE
        like_query_props:
query_with_cipher_column: true
*************************** 2. row ***************************
                   table: t_encrypt
            logic_column: order_id
         logic_data_type:
           cipher_column: order_cipher
        cipher_data_type:
            plain_column: order_plain
         plain_data_type:
   assisted_column:
assisted_query_data_type:
       like_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=445533ccc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: true
2 rows in set (0.01 sec)

mysql> select * from t_encrypt;
+----+--------------------------+--------------------------+-----------+------------+--------------------------+-------------+------+
| id | user_cipher              | user_assisted            | user_like | user_plain | order_cipher             | order_plain | name |
+----+--------------------------+--------------------------+-----------+------------+--------------------------+-------------+------+
|  1 | Ktmk9NUTHYXS+OApcRCg+A== | FMAjB5mOAmqotmfX7lQwhQ== | `         | a          | 5owposzhT/Zpk70GAQCBKw== | aa          | aaa  |
|  2 | 9MbqQaWIea72vZomWLoUMQ== | qiZJCKlQF7+/LzjoU9KYTQ== | a         | b          | aHp/Hywm/TkEMgnvjbOLOw== | bb          | bbb  |
|  3 | TLXTYnAbSCstVOz0Cv3iNg== | 3fGlHRInkQn+FcIzMUWS8Q== | d         | c          | 4PhsQk1WvNg1gbD5KfwPpw== | cc          | ccc  |
+----+--------------------------+--------------------------+-----------+------------+--------------------------+-------------+------+
3 rows in set (0.00 sec)

mysql> COMMIT REENCRYPTING j54014fb26631db0f7149741422d86cc5dfe6;