Enable transparent data encryption for an existing table

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

In OceanBase Database, the unit of data encryption is a tablespace. OceanBase Database is not a multi-data-file database system; the concept of tablespace is designed for compatibility and can be simply understood as a collection of tables.

This topic uses enabling encryption for an existing table t1 on the encrypted tablespace sectest_ts1 as an example to provide operational guidance on transparent data encryption.

Limitations

• The system tenant cannot enable encryption.
• After a tenant is configured with transparent encryption, it cannot switch to another encryption mode unless the tenant is rebuilt.

Configure encryption

This section provides configuration examples for the internal and obcloud methods.

Configure transparent encryption using the internal method

For the internal method, the encryption information of the tenant master key is mainly managed in internal tables. To avoid circular dependencies during log replay, clogs are not encrypted in this encryption method.

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

2. Execute the following statement to enable transparent encryption using the internal method.

   The tde_method parameter is used to set the mode of transparent tablespace encryption. The default value is none, which indicates that transparent tablespace encryption is disabled.

   For more information about the tde_method parameter, see tde_method.

   Notice

   The tde_method parameter cannot be modified after it is set.

   obclient> ALTER SYSTEM SET tde_method='internal';
   

3. Execute the following statement to confirm that the value of the tde_method parameter is internal on all OBServer nodes of the tenant.

   obclient> SHOW PARAMETERS LIKE 'tde_method';
   

4. After confirmation, execute the following statement to generate the master key.

   Note

   This statement can be executed only when the value of the tde_method parameter is internal on all OBServer nodes of the tenant.

   obclient> ALTER INSTANCE ROTATE INNODB MASTER KEY;
   

5. 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 use 'y', aes-256 is used by default.

   Example:

   obclient> CREATE TABLESPACE sectest_ts1 encryption = 'y'; 
   

Configure transparent encryption using the obcloud method

The obcloud method is suitable for scenarios where the master key is managed through a unified KMS proxy service.

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

2. Execute the following statement to enable transparent data encryption for the obcloud method.

   The tde_method parameter is used to set the method for transparent tablespace encryption. The default value is none, which disables transparent tablespace encryption.

   For more information about the tde_method parameter, see tde_method.

   Notice

   The tde_method parameter cannot be modified after it is set.

   obclient> ALTER SYSTEM SET tde_method = 'obcloud';
   

3. Execute the following statement to confirm that the value of the tde_method parameter is obcloud on all OBServer nodes of the tenant.

   obclient> SHOW PARAMETERS LIKE 'tde_method';
   

4. Execute the following statement to configure the KMS proxy service parameters.

   obclient> ALTER SYSTEM SET external_kms_info = '{
     "kms_host": "https://kms.example.oceanbase.com",
     "access_key_id": "LTAI****************",
     "access_key_secret": "your_access_key_secret",
     "cmk_id": "your_customer_master_key_id"
   }';
   

5. After confirmation, execute the following statement to generate the master key.

   Note

   This statement can be executed only if the value of the tde_method parameter is obcloud on all OBServer nodes of the tenant and the external_kms_info parameter is correctly configured.

   obclient> ALTER INSTANCE ROTATE INNODB MASTER KEY;
   

6. 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 use 'y', aes-256 is used by default.

   Example:

   obclient> CREATE TABLESPACE sectest_ts1 encryption = 'y';
   

Move an existing table to an encrypted tablespace

After configuring the encryption method, you can move an existing table to an encrypted tablespace. The specific steps are as follows:

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

2. After entering the database where the table is located, move the t1 table into the sectest_ts1 tablespace.

   obclient> ALTER TABLE t1 TABLESPACE sectest_ts1;
   

Transparently encrypt a table

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

2. Set the value of progressive_merge_num for the table to perform a full or progressive major compaction.

   progressive_merge_num specifies the number of rounds of progressive major compaction for a table. The default value is 0, which indicates that incremental compaction is performed. If the value is set to 1, it indicates that a full major compaction is performed.

   When performing a major compaction on a table, the full major compaction method is usually used. However, if the table contains a large amount of data and enabling a full major compaction may cause a single compaction to take an excessively long time, it is recommended to use progressive major compaction.

   • Perform a full major compaction on a table

     1. Set the value of progressive_merge_num to 1.

        obclient> ALTER TABLE t1 set progressive_merge_num = 1;
        

     2. Manually initiate one round of major compaction.

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

        Note

        After a full major 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 (encrypting), indicating that the encryption task has not ended and does not mean the major compaction failed. Wait until ENCRYPTED changes to YES and STATUS changes to NORMAL before confirming that the encryption is complete.

     3. After the major compaction is completed, set the value of progressive_merge_num back to 0.

        obclient> ALTER TABLE t1 set progressive_merge_num = 0;
        

   • Perform a progressive major compaction on a table

     1. Set the value of progressive_merge_num to a number greater than 1 and execute the OPTIMIZE command to prepare for progressive major compaction.

        Example:

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

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

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

        obclient> ALTER SYSTEM MAJOR FREEZE;
        

        Note

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

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

   Here is an example:

   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
   

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

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

   For more information about the fields and descriptions of the V$OB_ENCRYPTED_TABLES view, see V$OB_ENCRYPTED_TABLES.

References

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