Enable transparent data encryption for a new table

This topic describes how to create an encrypted tablespace to enable transparent data encryption (TDE) for new tables.

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. A tablespace is a collection of tables.

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

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 an Oracle 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. Create a Keystore.

   SQL syntax:

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

   Example:

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

5. 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.

   Example:

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

6. 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.

   Example:

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

7. 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'.

   Example:

   obclient> CREATE TABLESPACE sectest_ts1 ENCRYPTION USING 'aes-256';
   

Create a table in an encrypted tablespace

1. Log on to the Oracle tenant as a regular user.

2. Create a table and specify the tablespace.

   obclient> CREATE TABLE t1 (id1 int, id2 int) TABLESPACE sectest_ts1;
   

   After the table is created, all transactions related to the table are encrypted.

3. Check whether table t1 in the tablespace is marked as encrypted.

   Execute the following statement to check whether the value of the encryptionalg column is aes-256 or as specified:

   obclient> SELECT table_name,encryptionalg,encrypted FROM V$OB_ENCRYPTED_TABLES;
   +------------+---------------+-----------+
   | TABLE_NAME | ENCRYPTIONALG | ENCRYPTED |
   +------------+---------------+-----------+
   | T1         | aes-256       | YES       |
   +------------+---------------+-----------+
   1 row in set
   

   If the value of the encryptionalg column is aes-256 or as specified, and that of the encrypted column is YES, the table is encrypted.

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

References

• Enable transparent data encryption for an existing table
• Decrypt a table