TDE Part-2: Implement TDE Tablespace Encryption

In this part, we will implement TDE Tablespace Encryption. As TDE Tablespace Encryption have several major benefits over TDE Column Encryption ( Please read Part-1 for details ), we skip TDE column encryption and give our full focus on TDE Tablespace Encryption.

In order to implement TDE tablespace encryption, your Oracle Database version must be 11g release 1 (11.1) or higher. If you wish to test the enhanced tablespace encryption features, Oracle Database 11g Release 2 (11.2) is required.

TDE tablespace encryption will encrypting entire tablespaces. That means all objects stored in such tablespace will be 'By Default' encrypted. There is no restrictions to have encrypted and unencrypted tablespaces simultaneously in you database. You only encrypt those tablespaces which contain user/application data, system tablespaces must be remain as it is (i.e, unencrypted).

How to Encrypt a Tablespace Using TDE

Step 1 : Configure Oracle Wallet

Oracle Wallet is a secure password storage container which is placed outside of Oracle Database. To reduce the content of this post, we will give a brief configure of Oracle wallet here (if you have more interest please through this post for details). For TDE, oracle recommended to place the wallet outside of Oracle database though default wallet locations are inside database directories:

• $ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet
• $ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet.

When selecting which wallet to use, TDE first try to use the wallet specified by the parameter ENCRYPTION_WALLET_LOCATION. If the parameter is not set, then it looks the parameter WALLET_LOCATION. If this is not set as well, then TDE looks for a wallet at the default database location which i mention above. There are several usages of Oracle Wallet, TDE is one of them. Oracle strongly recommends that you use a separate wallet to store TDE master encryption keys. To set-up a separate wallet, set the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file to point to the wallet used exclusively by TDE.

We Like to put the wallet files outside of Oracle installation directories. Therefore we chose location '/u01/encryption_wallet/'. Now, add following lines to sqlnet.ora and save it.

ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/encryption_wallet)))

Step 2: Create an Oracle PKCS#12 standard wallet 

Command: orapki wallet create -wallet '/u01/encryption_wallet' -pwd "oracle123"
This command creates a wallet file 'ewallet.p12' in specified location. The wallet password must be consist of eight or more alphanumeric characters. If no password has been specified on the command line, it prompts you to enter and reenter the wallet password.

Step 3: Generate Master encryption Key into wallet.

SQL> CONN / AS SYSDBA
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "oracle123";
These will generate a random TDE master encryption key and save it to wallet. The password you given here is the wallet password not the master encryption key.

Step 4: Open the wallet if it is closed.

Before you proceed to create an encrypted tablespace, the Oracle wallet containing the tablespace master encryption key must be open. The wallet must also be open before you can access data in an encrypted tablespace. Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted data. Once the wallet has been opened, it remains open until you shut down the database
instance, or close it explicitly by issuing the close command. When you restart the instance, you must issue the OPEN command again.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "oracle123";
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "oracle123";

Step 5: Create Encrypted Tablespace

CREATE TABLESPACE enc_tbs DATAFILE '/oradata/enc_tbs01.dbf' SIZE 200M AUTOEXTEND ON NEXT 20M ENCRYPTION USING 'AES128' DEFAULT STORAGE(ENCRYPT);
You can chose other alternative encryption algorithms that are 3DES168, AES192, AES256. The key lengths are included in the names of the algorithms themselves. If no encryption algorithm is specified, the default encryption algorithm AES128 is used.

Step 6: Data Migration

You cannot encrypt an existing tablespace. So if you wish to encrypt existing data, you need to move them from unencrypted tablespaces to encrypted tablespaces. For doing this you use:

1. Oracle Data Pump utility.
2. Commands like CREATE TABLE...AS SELECT... 
3. Move tables like ALTER TABLE...MOVE..  or rebuild indexes.
4. Oracle Table Redefinition. 

To check whether the tablesspaces is encrypted
SQL> SELECT TABLESPACE_NAME,ENCRYPTED FROM DBA_TABLESPACES;                                                            
TABLESPACE_NAME                ENCRYPTED
------------------------------ ---------------------
SYSAUX                       NO
USER                             NO
ENC_TBS                   YES

Setting and Resetting the Master Encryption Key
ALTER SYSTEM SET ENCRYPTION KEY ["certificate_ID"] IDENTIFIED BY "password";

Opening and Closing the Encrypted Wallet

The database must load the master encryption key into memory before it can encrypt or decrypt columns/tablespaces. Opening the wallet allows the database to access the master encryption key. Use the following ALTER SYSTEM command to explicitly open the wallet:
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";
Once the wallet has been opened, it remains open until you shut down the database instance, or close it explicitly by issuing the following command:
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "password";
Each time you restart a database instance, you must explicitly open the wallet before open the database.
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";
SQL> ALTER DATABASE OPEN;

Backup and Recovery of Master Encryption Keys

You cannot access any encrypted data without the master encryption key. As the master encryption key is stored in the Oracle wallet, the wallet should be periodically backed up in a secure location. You must back up a copy of the wallet whenever a new master encryption key is set. Recovery Manager (RMAN) does not back up the wallet as part of the database backup. If you lose the wallet that stores the master encryption key, you can restore access to encrypted data by copying the backed-up version of the wallet to the appropriate location. If the restored wallet was archived after the last time that the master encryption key was reset, then no additional action needs to be taken. If the restored wallet does not contain the most recent master encryption key, then you can recover old data up to the point when the master encryption key was reset by rolling back the state of the database to that point in time. All modifications to encrypted columns after the master encryption key was reset are lost.