Enable transparent data encryption for existing tables

This topic describes how to enable transparent data encryption (TDE) for existing tables.

OceanBase Database encrypts data in tablespaces. OceanBase Database does not support multiple files, and the concept of tablespace is designed for compatibility. A tablespace is a collection of tables.

This topic describes how to enable TDE for table t1 in an encrypted tablespace sectest_ts1.

Limitations

• Encryption cannot be enabled for the sys tenant.
• After you enable TDE for a tenant, the tenant cannot use other encryption methods. To use another encryption method, re-create a tenant.

Enable TDE in internal mode

In internal mode, the encryption information of the master key is managed in internal tables, and clogs are not encrypted to avoid circular dependency during log replay.

1. Log on to a MySQL tenant of a cluster as an administrator.

2. Execute the following statement to enable TDE in internal mode.

   The tde_method parameter specifies the encryption method for a transparent tablespace. The default value is none, which indicates that encryption is disabled for the transparent tablespace.

   For more information about the tde_method parameter, see tde_method.

   Notice

   After the tde_method parameter is set, it cannot be modified.

   obclient> ALTER SYSTEM SET tde_method='internal';
   

3. Execute the following statement to check whether the value of the tde_method parameter is internal on all OBServer nodes of the tenant:

   obclient> SHOW PARAMETERS LIKE 'tde_method';
   

4. If yes, execute the following statement to generate the master key:

   Note

   The statement takes effect 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 one of the following encryption algorithms: 'aes-256', 'aes-128', 'aes-192', and 'sm4-cbc'. If you set the sectest_ts1 encryption parameter to 'y', the aes-256 algorithm is used.

   Here is an example:

   obclient> CREATE TABLESPACE sectest_ts1 encryption = 'y';
   

Move an existing table into an encrypted tablespace

1. Log on to a MySQL tenant of the database as a regular user.

2. Log on to the database where the t1 table resides and move the table to the sectest_ts1 tablespace.

   obclient> ALTER TABLE t1 TABLESPACE sectest_ts1;
   

Perform TDE on a table

1. Log on to a MySQL tenant of the database as a regular user.

2. Set the progressive_merge_num parameter to configure a full compaction or progressive compaction task for the table.

   The progressive_merge_num parameter specifies the number of progressive compactions on a table. The default value is 0, which indicates incremental compaction. If you set the parameter to 1, a full compaction is performed.

   In general, a full compaction is performed on a table. However, a full compaction of a table with a large amount of data may take a long time. In that case, we recommend that you perform a progressive compaction.

   • Perform a full compaction on the table

     1. Set the progressive_merge_num parameter to 1.

        obclient> ALTER TABLE t1 set progressive_merge_num = 1;
        

     2. Manually initiate a major compaction.

        For more information, see Manually trigger a major compaction.

        Note

        After a full compaction, all table data is encrypted. You can query the V$OB_ENCRYPTED_TABLES view to check the encryption status.

     3. After the compaction is completed, set the progressive_merge_num parameter to 0.

        obclient> ALTER TABLE t1 set progressive_merge_num = 0;
        

   • Perform a progressive compaction 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 to encrypt all the macroblocks of the table and table indexes.

        The statement to initiate a progressive compaction is as follows:

        obclient> ALTER SYSTEM MAJOR FREEZE;
        

        Note

        During a progressive compaction, you can query the V$OB_ENCRYPTED_TABLES view to check the encryption status in real time.

3. After the compaction is completed, you can execute the following statement to check the view and verify whether all macroblocks are 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
   

   Check the query result. If the value of the BLOCKS_DECRYPTED field is 0, all macroblocks have been encrypted.

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

References

• Enable transparent data encryption for a new table
• Decrypt a table