How to configure Transparent Data Encryption using a software keystore, the right way to call the old Oracle Wallet

Tweet

Oracle uses an operating system file to contains authentication and signing credentials: this file, named ewallet.p12, is the so called Oracle wallet (now called more appropriately software/hardware keystore)

The transparent data encryption (TDE) feature, used when you encrypt an entire tablespace, columns of a table, safely export data or complete your backup, is strongly based on the creation of the Oracle wallet to store the master key of the database.
There are two kind of Oracle wallets: that one which you must manually open each time you restart a database instance to reenable encryption and decryption and that one automatically opened when an encryption operation is required.

The first is protected by a password specified when you create it and before accessing the keys contained you are responsible of its opening. The second is protected by a system-generated password and you don't need to open it manually.
Once an Oracle Wallet is open, it remains open until you shutdown the database or manually close it. 

As already stated here when talking about granting the minimum privilege, the Oracle Database 12c introduced the syskm administrative privilege: with this type of privilege you can perform every kind of key management operation without using the powerful sysdba privilege.
It also unified some already known commands under a set of key management statements (ADMINISTER KEY MANAGEMENT) that you can see in today's scenario.

To create a standard Oracle wallet and then add a master key to it you have to follow few basic steps:
1) Configure the sqlnet.ora file to define a file system location for the Software Keystore
2) Create the Software Keystore 
3) Opening a Software Keystore 
4) Setting the TDE Master Encryption Key in the Software Keystore 
5) Encrypt the Data (have a look at the next post) 

1) Configure the sqlnet.ora file 
Oracle should know where to find the Oracle Wallet so you have to define a directory accessible by the Oracle Software.
In the multitenant solution the Oracle Wallet location is valid for the CDB and every PDBs at the same time. Edit your sqlnet.ora file and use the following syntax to let the database know where the software keystore is located on file system.
Be sure that the directory exists to avoid the error "ORA-46633: creation of a password-based keystore failed":

[oracle@vsi08devpom admin]$ pwd
/opt/app/oracle/product/12.1.0/db_1/network/admin
[oracle@vsi08devpom admin]$ echo "ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet)))" >> sqlnet.ora 

[oracle@vsi08devpom admin]$ cat sqlnet.ora 
# sqlnet.ora Network Configuration File: /app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet)))

2) Create the Oracle Wallet 
It's possible to create the Oracle Wallet using the owm gui utility (as you can read on this post) or from sqlplus with a new set of key management statements (ADMINISTER KEY MANAGEMENT).
The steps to create an Oracle Wallet must be executed from the sqlplus command line with a user who has been granted the new SYSKM administrative privilege:
when in a multitenant environment you have to log in to the root container.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

To avoid the error "ORA-46633: creation of a password-based keystore failed", the directory you are going to specify in the create keystore statement must be already present.

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/missing_directory' IDENTIFIED BY "0racle0racle";
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/missing_directory' IDENTIFIED BY "0racle0racle"
*
ERROR at line 1:
ORA-46633: creation of a password-based keystore failed

To create the keystore under the path specified in the sqlnet.ora file use the following statement:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet' IDENTIFIED BY "0racle0racle";

keystore altered.

Querying the V$ENCRYPTION_WALLET view you can see the location, status and type of the wallet.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                            STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- -------------------------------------------------------- ------ ----------- --------- --------- ------
FILE     /app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet  CLOSED UNKNOWN     SINGLE    UNDEFINED 0

3) Opening a Software Keystore 
To setup, configure and use encrypted tablespace or column the Oracle Wallet needs to be open.
The v$encryption_wallet view says the status of the wallet is closed so you need to open it using the following statement:

SQL> administer key management set keystore open identified by "0racle0racle";

keystore altered.

The status is now OPEN_NO_MASTER_KEY. This means that the wallet is open, but still a master key needs to be created.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                            STATUS             WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- -------------------------------------------------------- ------------------ ----------- --------- --------- ------
FILE     /app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet  OPEN_NO_MASTER_KEY UNKNOWN     PASSWORD  UNDEFINED 0

4) Setting the TDE Master Encryption Key in the Software Keystore
You need to set a master key for the Oracle wallet used in the TDE activities on tables or tablespace.

SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'tde_mek' IDENTIFIED BY "0racle0racle" WITH BACKUP USING 'tde_mek_backup';

keystore altered.

If you need to change the password of your Oracle Wallet because of your company's security guidelines, you must use the WITH BACKUP option: in this way you are forced to take a backup of your "old" wallet.

SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY "0racle0racle" set "0racl30racle3";
ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY "0racle0racle" set "0racl30racle3"
*
ERROR at line 1:
ORA-46631: keystore needs to be backed up

A change of the password doesn't prevent the normal use of every TDE operations: they continue to work as usual with the new password without interruptions. You need to provide the old password and the new password.

SQL> ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY "0racle0racle" set "0racl30racle3" WITH BACKUP USING 'tde_mek_backup_001';

keystore altered.

To see now how to encrypt you data, read this post.

That's all.