Quick guide to set up Transparent Data Encryption for a tablespace

If not already done, create a folder for the wallet:

# mkdir -p  /u01/oracle/admin/proddb01/wallet

Step 1: configure the software keystore location in the sqlnet.ora file

Edit your $TNS_ADMIN/sqlnet.ora:

SQLNET.WALLET_OVERRIDE = TRUE
SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = FALSE
ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/oracle/admin/proddb01/wallet)
    )
  )
SSL_CIPHER_SUITES = (SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_DES_CBC_SHA)

Step 2: create the software keystore

There are three different types of software keystores.

You can create a) password-based software keystores, b) auto-login software keystores, and c) local auto-login software keystores.
In this guide, I am setting up password-based software keystore.
Password-based software keystores are protected by using a password that you create.

You must open this type of keystore before the keys can be retrieved or used.


Verify wallet location:

SYS@proddb01 SQL> select wrl_parameter, status,wallet_type from  v$encryption_Wallet;

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        NOT_AVAILABLE                  UNKNOWN

Note that wallet type = UNKNOWN , status=NOT_AVALABLE
Run "ADMINISTER KEY MANAGEMENT" to create the keystore:

SYS@proddb01 SQL> administer key management create keystore '/u01/oracle/admin/proddb01/wallet' identified by "secretpassword";

Keystore altered.

Check the view v$encryption_Wallet now, and you can see that it exists, but the wallet type is still UNKNOWN:

SYS@proddb01 SQL> select wrl_parameter, status,wallet_type from  v$encryption_Wallet;

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        CLOSED                         UNKNOWN

Step 3: Open the Software Keystore

Depending on the type of keystore you create, you must manually open the keystore before you can use it.
You must manually open a password-based software keystore before any TDE master encryption keys can be created or accessed in the keystore.

After you open a keystore, it remains open until you manually close it.
Each time you restart a database instance, you must manually open the password keystore to reenable encryption and decryption operations.

Open the keystore:

SYS@proddb01 SQL> administer key management set keystore open identified by "secretpassword";
 
Keystore altered.

Check the view v$encryption_wallet again:

SYS@proddb01 SQL> select wrl_parameter, status,wallet_type from  v$encryption_Wallet;
 

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        OPEN_NO_MASTER_KEY             PASSWORD

Note that if the keystore is open but you have not created a TDE master encryption key yet, the STATUS column of the V$ENCRYPTION_WALLET view reminds you with a status of OPEN_NO_MASTER_KEY.

Step 4: Set the Software TDE Master Encryption Key


The TDE master encryption key is stored in the keystore.
This key protects the TDE table keys and tablespace encryption keys.
By default, the TDE master encryption key is a key that Transparent Data Encryption (TDE) generates.
Password-based software keystores must be open before you can set the TDE master encryption key.

Create a master key:

SYS@proddb01 SQL> administer key management set key identified by "****" with backup using 'initial_key_backup';

Keystore altered.

Check the view v$encryption_wallet again:

SYS@proddb01 SQL>  select wrl_parameter, status,wallet_type from  v$encryption_Wallet;

WRL_PARAMETER                            STATUS                         WALLET_TYPE
---------------------------------------- ------------------------------ --------------------
/u01/oracle/admin/proddb01/wallet/        OPEN                           PASSWORD

Step 5: Encrypt Your Data

After you complete the software keystore configuration, you can begin to encrypt data.
You can encrypt data in individual table columns or in entire tablespaces, like I do below:

create bigfile tablespace SECRET_DATA_TBS datafile '/u02/oradata/proddb01/secret_data_01.dbf'  size 256M autoextend on next 128M maxsize unlimited
encryption using 'AES128'
default storage(encrypt);

Check status of the tablespaces:

SYS@proddb01 SQL> SELECT t.name, e.encryptionalg algorithm
FROM  v$tablespace t, v$encrypted_tablespaces e
WHERE t.ts# = e.ts#;
 
NAME                           ALGORIT
------------------------------ -------
SECRET_DATA_TBS                AES128

When the database is restarted, the wallet will be in status CLOSED.

To access your data, the wallet must be opened first:

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "*****";

You may want to add a startup trigger that opens the wallet every time the database starts. See this post for how I solved this.

Sources:

1. configure the software keystore location in the sqlnet.ora file
2. log in to the database instance to create and open the keystore
3. Open the Software keystore
4. set the TDE master encryption key
5. begin to encrypt data