Enable storage encryption for an existing table|V3.2.4|OceanBase Database| docs|Distributed Database

Enable storage encryption for an existing table

Last Updated：2023-10-27 09:57:43 Updated

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

Background information

The following sections describe how to enable storage encryption for the t1 table in the encrypted sectest_ts1 tablespace.

Enable storage encryption 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 the database as the administrator.
Execute the following statement to enable storage encryption 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.

The SQL syntax is as follows:

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

For example:

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

Enable the Keystore.

The SQL syntax is as follows:

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.

For example:

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

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.

For example:
```
obclient> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY ******;
```
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.

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

For example:
```
obclient> ALTER INDEX idx1 TABLESPACE sectest_ts1;
```

Perform storage encryption 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. Log on to the sys tenant as the root user and manually initiate a major compaction.
    
    For more information, see Manually initiate a major compaction.
    
    Note
    
    After a full compaction, all table data is encrypted. You can query the sys.v$encrypted_tables view to check the encryption status.
  3. After the major 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.
    
    For example:
```
obclient> ALTER TABLE t1 set progressive_merge_num = 3;

obclient> ALTER TABLE t1 SHRINK SPACE;
```
  2. Log on to the sys tenant as the root user, and manually initiate multiple rounds of progressive compactions to encrypt all the macroblocks of the table and table indexes.
    
    The statement for initiating a progressive compaction is as follows:
```
obclient> ALTER SYSTEM MAJOR FREEZE;
```
    The number of rounds of manual incremental compactions is progressive_merge_num × max_kept_major_version_number.
    
    Note
    
    During a progressive compaction, you can query the sys.v$encrypted_tables view to check the encryption status in real time.
    
    The max_kept_major_version_number parameter specifies the number of frozen versions to be retained. It can have a value between 1 and 16, with a default value of 2.
    
    For more information about the max_kept_major_version_number parameter, see max_kept_major_version_number.

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

obclient> SELECT * FROM v$encrypted_tables;
+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
| TABLE_ID         | TABLE_NAME | TABLESPACE_ID    | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY                     | MASTERKEYID      | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS |
+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
| 1103909674337105 |   T1       | 1103909674288105 | aes-256       | YES       | 16E9DE76E8A3B87C58DAC6E2F1C69953 | 1103909674288105 |                3 |                0 | NORMAL |
+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
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 fields in the V$ENCRYPTED_TABLES view, see V$ENCRYPTED_TABLES.