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.

TDE Part-1: Transparent Data Encryption

Transparent Data Encryption (TDE), part of Oracle Advanced Security,  encrypts critical data that are stored in data files in the form of tables, indexes, materialized views etc. Oracle provides several security mechanism for authentication and authorization to validate database users who access data. All these measures are there for secure access control but not any for database files such as datafiles, redo log and archive log files. If you have the datafiles, you can see the data though you have no access in the database. Therefore file level security is required to protect the data. On other side, you want minimal configurations or changes in middle and top layer of the application to achieve database file level security.

In 10g Release 2, Oracle first introduce TDE, a transparent data encryption-decryption mechanism to secure data stored in datafiles. Here transparent means database users or application need not to do any changes to achieve TDE. Database itself will encrypt data while writing into datafiles and decrypt then while reading. database users and applications not even bother for data encryption-decryption, Oracle database will do these transparently for them.    To prevent unauthorized decryption, transparent data encryption stores the encryption keys in a
security module (oracle wallet) external to the database. Data is transparently encrypt/decrypted by the database, user does not require any action on their part.

There are two types of TDE:

1. TDE Column Encryption - data encryption-decryption for columns of a table. It encrypts/decrypts data at the SQL layer. This feature introduce in Oracle 10g R2.
2. TDE tablespace Encryption - data encryption-decryption for all objects that stored into the encrypted tablespace. It encrypts/decrypts data during read/write operations This feature introduced in Oracle 11g R1.

Restrictions on Using TDE Column Encryption

Do not use TDE column encryption with the following database features:

1. Index types other than B-tree
2. Range scan search through an index
3. External large objects (BFILE)
4. Synchronous Change Data Capture
5. Transportable Tablespaces
6. Original import/export utilities does not support. You need to use Oracle Data Pump.
7. TDE encrypted columns can't be use as foreign key constraints.

Restrictions on Using TDE Tablespace Encryption

Majority of the restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions are not applicable to TDE tablespace encryption. Though, the are some other restrictions
that apply to TDE tablespace encryption:

1. External Large Objects (BFILEs) can not be encrypted using TDE tablespace encryption. This is because these files reside outside the database.
2. To perform import and export operations, you need to use Oracle Data Pump.

Things You Need To Consider Before Implementing TDE

1. 4-8% performance impact in end-user response time and an increase of 1-5% in CPU usage. Though it ensure below 10% performance impact.
2. Existing tablespace can not be converted to encrypted tablespace. You need to use table reorganize/redefinition or datapump Export-Import for Migration.
3. Increase disk consumption. Encrypting a single column would require between 32 and 48 bytes of additional storage for each row, on average. Though TDE tablespace encryption has no storage overheads.
4. Require Goldengate 11.1.1.1 for replication
5. Increase RMAN(backup/restore) & DataPump(export/import) duration.
6. Need Separate backup task for Wallet that contains encryption decryption master key.
7. Need Oracle Advanced Security Licence (11,500 USD/Core).
8. Using a PKI key pair (PKI certificate) as a master encryption key may result in greater performance degradation when accessing encrypted columns in the database.