Enable transparent data encryption for an existing table

This topic describes how to enable transparent data encryption for an existing table.

In OceanBase Database, data is encrypted at the tablespace level. OceanBase Database does not store data in multiple data files; tablespaces are introduced for compatibility and can be simply thought of as a collection of tables.

As an example, this topic describes how to enable encryption for an existing table t1 on the encrypted tablespace sectest_ts1.

Limitations

• You cannot enable encryption on the system tenant.
• After transparent data encryption is enabled for a tenant, the encryption mode cannot be changed unless the tenant is rebuilt.

Prerequisites

Before configuring transparent data encryption, you must generate the tenant master key. For more information, see Generate a tenant master key (MySQL-compatible mode).

Create an encrypted tablespace

After the tenant master key is generated, you can create an encrypted tablespace.

1. Log in to the MySQL-compatible tenant of the cluster as the administrator user.

2. Create a tablespace and specify the encryption algorithm.

   You can specify the encryption algorithms aes-256, aes-128, aes-192, sm4-cbc, aes-128-gcm, aes-192-gcm, aes-256-gcm, or sm4-gcm. If you specify 'y', aes-256 is used by default.

   Example:

   CREATE TABLESPACE sectest_ts1 encryption = 'y'; 
   

Move an existing table to the encrypted tablespace

1. Log in to the MySQL-compatible tenant of the database as a regular user.

2. Connect to the database where the table resides, and then move the table t1 to the tablespace sectest_ts1.

   ALTER TABLE t1 TABLESPACE sectest_ts1;
   

Perform transparent data encryption on the table

1. Log in to the MySQL-compatible tenant of the database as a regular user.

2. Set the progressive_merge_num value of the table to perform full or progressive compaction on the table.

   progressive_merge_num specifies the number of progressive compaction rounds for a table. The default value is 0, which indicates incremental compaction, where encryption is completed in 100 rounds. A value of 1 indicates full compaction.

   Full compaction is typically used when you compact a table. However, if the table contains a large amount of data, a full compaction may take an excessively long time. In this case, progressive compaction is recommended.

   • Perform full compaction on a table

     a. Set the progressive_merge_num value to 1.

     ALTER TABLE t1 set progressive_merge_num = 1;
     

     b. Manually initiate one round of compaction.

     For more information about how to manually initiate a major compaction, see Manually initiate a major compaction.

     Note

     After full compaction is completed, data encryption may still be in progress. In this case, query the oceanbase.V$OB_ENCRYPTED_TABLES view. The STATUS column may show ENCRYPTING, which indicates that encryption is still in progress rather than a compaction failure. Wait until ENCRYPTED is YES and STATUS is NORMAL before you confirm that encryption is complete.

     c. After compaction is complete, set the progressive_merge_num value back to 0.

     ALTER TABLE t1 set progressive_merge_num = 0;
     

   • Perform progressive compaction on a table

     a. Set the progressive_merge_num value to a number greater than 1.

     Example:

     ALTER TABLE t1 SET progressive_merge_num = 3;
     

     b. Manually initiate multiple rounds of progressive compaction to encrypt all existing macroblocks in the table and its indexes.

     The statement to initiate one round of progressive compaction is as follows:

     ALTER SYSTEM MAJOR FREEZE;
     

     Note

     During progressive compaction, you can query the V$OB_ENCRYPTED_TABLES view to monitor the encryption progress in real time.

3. After completion, you can query the following view to confirm whether all macroblocks have been encrypted.

   Example:

   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
   

   Based on the query results, all macroblocks are encrypted when all the following conditions are met:

   • ENCRYPTED is YES.
   • STATUS is NORMAL (if the status is ENCRYPTING, encryption is still in progress. Please wait).
   • BLOCKS_DECRYPTED is 0.

   For more information about the fields in the V$OB_ENCRYPTED_TABLES view, see V$OB_ENCRYPTED_TABLES.

References

• Set up transparent encryption for a newly created table
• Decryption