TDE 12.2 : Encryption and Decryption of Existing Tablespaces

The Oracle Transparent Data Encryption feature encrypts the sensitive data (database tables and tablespaces) stored on the disk. This prevents misuse of the data if the disks or storage mediums are lost or stolen. The data is transparently decrypted for the authorized users when they access the data.

 

1. Create a wallet location : Create directory  D:\wallet\tde_wallet

2. Update the keystore location in SQLNET.ORA :

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\wallet\tde_wallet )))

3. Create a keystore :

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'D:\wallet\tde_wallet' IDENTIFIED BY oracle123;

4. Open the keystore :

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle123;

5. Check the v$encryption_keys view to see the key activated:

SELECT con_id, key_id FROM v$encryption_keys;

 < NO ROWS >

6. Set the encryption key

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle123 with backup;

SELECT con_id, key_id FROM v$encryption_keys;

    CON_ID     KEY_ID

---------- ------------------------------------------------------------------------------

         0         AczvQT4Wh08gvxQ18W7N/5MAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

How to encrypt and decrypt the existing tablespaces in 12.2?

***************************************

*        O F F L I N E   E N C R Y P T I O N     *

***************************************

The default encryption algorithm in offline conversion is AES128.

We cannot encrypt using any other algorithm.

1. alter tablespace HRM_TBS offline normal;

2.  alter tablespace HRM_TBS encryption offline encrypt;

< COMMAND FAILED >

The option to encrypt a tablespace is available in 12.2 only. 

alter database datafile 'D:\APP\ORACLEDB\ORADATA\xxxx\HRM_TBS.xxxx.dbf' encrypt;

3. alter tablespace HRM_TBS online;

TEST

  => select * from hrm.employee; -- displays results

  =>  Close the keystore and see if you can read the data from the HRM_TBS tablespace

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY oracle123;

 =>   select * from hrm.employee; -- errors out wallet not open

 =>   Reopen wallet to read data from HRM_TBS tablespace

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle123;

=>  select * from hrm.employee; -- displays results

***************************************

*      O F F L I N E     D E C R Y P T I O N    *

***************************************

=>  alter tablespace HRM_TBS offline normal;

=>  alter tablespace hrm_tbs encryption offline decrypt;

< COMMAND FAILED >

The option to encrypt/decrypt a tablespace is available in 12.2 only.

=>  alter database datafile 'D:\APP\ORACLEDB\ORADATA\xxxx\HRM_TBS.xxxx.dbf'  decrypt;

=>  alter tablespace HRM_TBS online;

TEST

=> close the keystore

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY oracle123;

=> select * from hrm.employee;   --return records

***************************************

*        O N L I N E   E N C R Y P T I O N       *

***************************************

You can encrypt, decrypt, or rekey existing user tablespaces, and the SYSTEM, SYSAUX, and UNDO tablespace when they are online.

However, you cannot encrypt, decrypt, or rekey a temporary tablespace online.

Note: Online encryption requires at least the same amount of space as the largest data file in the tablespace you are encrypting.

The option to encrypt/decrypt a tablespace is available in 12.2 only.

1.  Ensure that the COMPATIBLE initialization parameter is set to 12.2.0.0.

2.  Encrypt the tablespace using the alter tablespace command.

alter tablespace HRM_TBS encryption online encrypt file_name_convert = ('HRM_TBS.xxxx.dbf', 'HRM_TBS_enc01.dbf');

FILE_NAME_CONVERT specifies one or more pairs of data files that are associated with the tablespace. 

HRM_TBS.xxxx.dbf is the existing datafile and HRM_TBS_enc01.dbf is the encrypted version of the datafile. This clause is not required when using Oracle-managed files.

 Note: Online encryption requires at least the same amount of space as the largest data file in the tablespace you are encrypting. If your HRM_TBS.xxxx.dbf is 10 gb then it will create  HRM_TBS_enc01.dbf of 10gb 

 

***************************************

*         O N L I N E   D E C R Y P T I O N     *

***************************************

1. Ensure that the COMPATIBLE initialization parameter is set to 12.2.0.0.

2. Decrypt the tablespace using the alter tablespace command.

alter tablespace HRM_TBS encryption online encrypt file_name_convert = ('HRM_TBS_enc01.dbf', 'HRM_TBS.xxxx.dbf');