Decrypt a table

You can decrypt a table by simply removing the table from the encrypted tablespace.

Background information

Assume that table t1 is in the following encryption status and is stored in the encrypted tablespace sectest_ts1.

obclient> SELECT * FROM oceanbase.V$OB_ENCRYPTED_TABLES;
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
| TABLE_ID | TABLE_NAME | TABLESPACE_ID | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY                     | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS | CON_ID |
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
|   500010 | t1         |        500009 | aes-256       | YES       | xxxxxxxxxxxxxxxxxxxxxxxxxxxx7882 |      xxxx08 |                0 |                0 | NORMAL |      0 |
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
1 row in set

obclient> SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table

                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id1` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0  TABLESPACE `sectest_ts1` |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Remove the table from the encrypted tablespace

1. Log on to a MySQL tenant as the administrator.

2. Perform progressive compactions on the table.

   1. Set the progressive_merge_num parameter to a value greater than 1, and run the OPTIMIZE command.

      Here is an example:

      obclient> ALTER TABLE t1 SET progressive_merge_num = 3;       
      obclient> OPTIMIZE TABLE t1;
      

   2. Manually initiate multiple progressive compactions.

      The statement to initiate a progressive compaction is as follows:

      obclient> ALTER SYSTEM MAJOR FREEZE;
      

      Note

      Decryption is an asynchronous process. The decryption process requires progressive compactions of the table to ensure that all data is decrypted.

3. Create an unencrypted tablespace named ts2.

   obclient> CREATE TABLESPACE ts2;
   

4. Access the database where the table is located.

5. Move the t1 table from the encrypted tablespace sectest_ts1 to the unencrypted tablespace ts2.

   obclient> ALTER TABLE t1 TABLESPACE ts2;
   

   Then, execute the SHOW CREATE TABLE statement to check whether the t1 table has been moved to the tablespace ts2:

   obclient> SHOW CREATE TABLE t1;
   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table                                                                                                                                                                                                                                                                 |
   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | t2    | CREATE TABLE `t1` (
     `id1` int(11) DEFAULT NULL,
     `id2` int(11) DEFAULT NULL
   ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0  TABLESPACE `ts2` |
   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

6. Query the oceanbase.V$OB_ENCRYPTED_TABLES view to check the encryption status of the table.

   obclient>  SELECT * FROM oceanbase.V$OB_ENCRYPTED_TABLES;
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+
   | TABLE_ID         | TABLE_NAME | TABLESPACE_ID    | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS     | CON_ID |
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+
   | 500010           | t1         | 500015           |               | NO        |              |           0 |                2 |                0 | NORMAL     |      0 |
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+
   1 row in set
   

   The query result shows that the ENCRYPTED field of the t1 table is set to NO, indicating that the t1 table has been decrypted.

References

• Enable storage encryption for a new table
• Enable storage encryption for an existing table