Decrypt a table

To decrypt a table, you need only to remove the table from the encrypted tablespace.

Background information

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

obclient> SELECT * FROM oceanbase.v$encrypted_tables;
+-----------+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
| TENANT_ID | TABLE_ID         | TABLE_NAME | TABLESPACE_ID    | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY                     | MASTERKEYID      | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS |
+-----------+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
|      1003 | 1102810162709330 |   t1       | 1102810162660329 | aes-256       | YES       | 4B0C00AFEFEEF506D4AB804E5071A733 | 1102810162660329 |                2 |                0 | NORMAL |
+-----------+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
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 of the database as a regular user.

2. Create an unencrypted tablespace ts2.

   obclient> CREATE TABLESPACE ts2;
   

3. Enter the database where the table is located.

4. 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 is 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
   

5. Query the oceanbase.v$encrypted_tables view to check the encryption status of the table.

   obclient>  SELECT * FROM oceanbase.v$encrypted_tables;
   +-----------+------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+
   | TENANT_ID | TABLE_ID         | TABLE_NAME | TABLESPACE_ID    | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS     |
   +-----------+------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+
   |      1003 | 1102810162709330 | t1         | 1102810162660329 |               | NO        |              |           0 |                2 |                0 | NORMAL     |
   +-----------+------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+
   1 row in set (0.03 sec)
   

   It is clear that the STATUS field of the t1 table is NORMAL, which means that the t1 table is unencrypted.