Encrypted tablespace

In Oracle 11g you can encrypt the entire tablespace simply by using a pair of clauses during tablespace creation.

Tablespace creation depends on the Tablespace Data Encryption (TDE) feature of oracle database, which requires you to create and maintain a secure credentials repository called “Oracle wallet” to store master encryption key for the database.

Oracle Wallet

Oracle wallet is a container that contains authentication and signing credentials. The tablespace encryption wallet feature relies on the oracle wallet to store and protect the master key used in the encryption.

There are two types of oracle wallet

1.       Encryption wallet – you must manually open encryption wallet after database startup. It is recommended for tablespace encryption

2.       Auto-open wallet – it automatically opens upon database startup.

Oracle wallet is actually a file in your directory system, named ewallet.p12 under windows/linux/unix based systems.

Before you create oracle wallet, you must first create directory named wallet under directory $oracle_base/admin/$oracle_sid

Simplest way to create oracle wallet is through SQL statement.

Alter system set encryption key identified by “password”; --this statement both creates the wallet if it doesn’t exist and add master key to it.

 Now that you have successfully created oracle wallet, you’re ready to encrypt tablespaces using the new tablespace encryption feature.

Create Tablespace <tablespace_name>

Encryption

Default storage (encrypt)

Encryption keyword in the second line doesn’t actually encrypt the tablespace. It merely provides the encryption properties.

Encrypt keyword passed to the storage clause in the third line performs actual encryption of tablespace.

You may specify the “using” clause to specify the name of the encryption algorithm you want to use such as 3DES168, AES128, AES192, AES256. Default encryption algorithm is AES128.

Example:

Create tablespace encrypt1

Datafile ‘c:\orcl\app\oracle\oradata\encrypt.dbf’ size 100m

Encryption

Default  storage (encrypt)

You can use below query to check the encryption status of the tablespaces

Select tablespace_name, encrypted from dba_tablespaces