Enable transparent data encryption for an existing table|V4.1.0| docs|Distributed Database

Enable transparent data encryption for an existing table

Last Updated：2023-10-20 06:33:11 Updated

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

OceanBase Database provides data encryption methods that are compatible with Oracle databases and encrypts data in tablespaces. OceanBase Database does not support multiple files, and tablespaces are designed for compatibility. The encryption feature of OceanBase Database is designed to be compatible with Oracle Database as much as possible. OceanBase Database encrypts data in tablespaces. OceanBase Database does not support multiple files, and the concept of tablespaces is designed for compatibility.

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

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.

Log on to an Oracle tenant of a cluster as an administrator.
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';
```
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';
```

Create a Keystore.

SQL syntax:

obclient>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE keystore_name IDENTIFIED BY password;

Here is an example:

obclient> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE sectest1 IDENTIFIED BY **3**;

Enable the Keystore.

SQL syntax:

obclient> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;

You must replace password with the key specified for the Keystore in the previous step.

Here is an example:

obclient> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY **3**;

Execute the following statement to generate the master key:
```
obclient> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password;
```
You must replace password with the key specified for the Keystore in the previous step.

Here is an example:
```
obclient> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY **3**;
```
Create a tablespace and specify the encryption algorithm.

You can specify any one of the following encryption algorithms: 'aes-256', 'aes-128', 'aes-192', and 'sm4-cbc'.

Here is an example:
```
obclient> CREATE TABLESPACE sectest_ts1 ENCRYPTION USING 'aes-256';
```

Move an existing table into an encrypted tablespace

Log on to an Oracle tenant of the database as a regular user.

Move the t1 table to the sectest_ts1 tablespace.

obclient> ALTER TABLE t1 TABLESPACE sectest_ts1;

Modify the index table and add the indexes in the table to the sectest_ts1 tablespace.

Here is an example:
```
obclient> ALTER INDEX idx1 TABLESPACE sectest_ts1;
```

Perform TDE on a table

Log on to an Oracle tenant of the database as a regular user.
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 rounds 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 perform the SHRINK SPACE operation.
    
    Here is an example:
```
obclient> ALTER TABLE t1 set progressive_merge_num = 3;

obclient> ALTER TABLE t1 SHRINK SPACE;
```
  2. Manually initiate multiple rounds of progressive compactions to encrypt all the macroblocks of the table and table indexes.
    
    Execute the following statement to initiate a progressive compaction:
```
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.

After the compaction is completed, you can execute the following statement to check the view and verify whether all macroblocks are encrypted:

obclient> SELECT * FROM V$OB_ENCRYPTED_TABLES;
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
| TABLE_ID | TABLE_NAME | TABLESPACE_ID | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY                     | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS | CON_ID |
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
|   500005 | T1         |        500004 | aes-256       | YES       | xxxxxxxxxxxxxxxxxxxxxxxxxxxx66F3 |      xxxx03 |                0 |                0 | NORMAL |   1010 |
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
1 row in set

Check the query results. If the value of the BLOCKS_DECRYPTED field is 0, all macroblocks are encrypted.

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

Enable transparent data encryption for an existing table

Enable TDE in internal mode

Notice

Move an existing table into an encrypted tablespace

Perform TDE on a table

Note:

Note:

References