Enable TDE, auto-login wallet, and Secure External Password Store (SEPS)

Introduction

In this blog post we are going to have a step by step instruction to

• Enable Transparent Data Encryption (TDE).
• Create an encrypted tablespace.
• Create an auto-login wallet/keystore.
• Create a Secure External Password Store (SEPS).
• Clone PDBs from local and remote CDBs and create their master encryption keys.

I’ll try to keep it as simple as possible.

The Environment

As TDE is already enabled by default in all Database Cloud Service databases, I wanted to get an Oracle Database provisioned very quickly without TDE enabled for demo purposes. One option is to use the Marketplace image in the Oracle Cloud. This will create a database on a conventional IaaS compute instance. From the main menu, go to “Marketplace”, “Applications” and search for “Oracle Database”. The database version is 19.7.

Step 1: Check TDE status

By executing the following query, we get STATUS=NOT_AVAILABLE.

set lines 300
col name for a15
col wrl_type for a10
col status for a30
select p.con_id, p.name, p.open_mode, ew.wrl_type, ew.wallet_type, ew.status 
from v$pdbs p join v$encryption_wallet ew on (ew.con_id = p.con_id)
order by p.con_id;

Step 2: Create a new directory for the Keystore

Create a new directory where the keystore (=wallet file) will be created. Keystore is the new term for Wallet, but we are using them here interchangeably.

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
export ORACLE_BASE=/u01/app/oracle/
export ORACLE_SID=ORCL
mkdir $ORACLE_BASE/admin/$ORACLE_SID/wallet

Step 3: Set the location for the Keystore

The location is defined by the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora.

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/ORCL/wallet/tde)))

However, the sqlnet parameter got deprecated in 18c. Instead, we are going to use the new WALLET_ROOT and TDE_CONFIGURATION database parameter.

alter system set wallet_root='$ORACLE_BASE/admin/$ORACLE_SID/wallet' scope=spfile;
shutdown immediate;
startup;

alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;

Step 4: Create the password-protected Keystore (ewallet.p12)

When using the WALLET_ROOT database parameter, the TDE wallet will be stored in a subdirectory named “tde“. The tde subdirectory will automatically be created if it does not exist.

# optional: 
mkdir $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde

The following command will create the password-protected keystore, which is the ewallet.p12 file.

-- you don't need to provide the wallet location when WALLET_ROOT is set
administer key management 
create keystore ['$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde'] 
identified by MyWalletPW_12;

ls -ltr $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde

NEVER forget your wallet password!!!

NEVER forget your wallet password!!!

NEVER delete your wallet file!!!

NEVER delete your wallet file!!!

Step 5: Open the Keystore

Now we have a wallet, but the STATUS is CLOSED.

Execute the following command to open the keystore (=wallet)

administer key management 
set keystore open identified by MyWalletPW_12 container=ALL;

Now we get STATUS=OPEN_NO_MASTER_KEY, as the wallet is open, but we still have no TDE master encryption keys in it.

Step 6: Create a master encryption key for all PDBs

If we check the v$encryption_keys at this moment, we will see that there are no keys yet (no value in the KEY_ID column).

set lines 300
col name for a10
col key_id for a60
select p.con_id, p.name, p.open_mode, ek.key_id
from v$pdbs p left outer join v$encryption_keys ek on (ek.con_id = p.con_id)
order by p.con_id;

Create a master encryption key per PDB by executing the following command.

administer key management 
set key identified by MyWalletPW_12 with backup container=ALL;

Now, the STATUS changed to OPEN, and we have our key for the PDB.

Step 7: Create an encrypted tablespace

You can encrypt existing tablespaces now, or create new encrypted ones.

alter system set db_create_file_dest = '/u01/app/oracle/oradata/ORCL/';

alter session set container=ORCLPDB;

create tablespace enctbs encryption using 'AES128' default storage(encrypt);

select t.name, e.encryptedts, e.encryptionalg 
from v$encrypted_tablespaces e join v$tablespace t on (e.ts# = t.ts#)
order by t.name;

Step 8: Create a table and insert data

create table enctable (id number) tablespace enctbs;
insert into enctable values (1);
commit;

select * from enctable;

Step 9: Restart the database instance

Now, let’ see what happens after the database instance is getting restarted, for whatever reason.

alter session set container=CDB$ROOT;
shutdown immediate;
startup;
alter session set container=ORCLPDB;
select * from enctable;

Ohhh! Let’s check

Indeed! After the restart of the database instance, the wallet is closed. You are not able to query the data now unless you open the wallet first.

alter session set container=cdb$root;

administer key management
set keystore open identified by MyWalletPW_12 container=ALL;

alter session set container=ORCLPDB;
select * from enctable;

Step 10: Create an auto-login Keystore (cwallet.sso)

To avoid the situation in step 9, we will create an auto-login wallet (cwallet.sso) from the password wallet (ewallet.p12) that gets opened automatically after the database instance restart.

alter session set container=cdb$root;
administer key management create [local] auto_login keystore
from keystore '$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde'
identified by MyWalletPW_12;

ls -l $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde

By adding the keyword “local” you can create a LOCAL auto-login wallet, which can only be used on the same machine that it was created on.

Step 11: Close the password Keystore and open the auto-login Keystore

At this moment the WALLET_TYPE still indicates PASSWORD.

We have to close the password wallet and open the autologin wallet. We can do this by restart the database instance, or by executing the following command.

administer key management set keystore close identified by MyWalletPW_12 container=ALL;

By querying v$encryption_wallet, the auto-login wallet will open automatically. After each startup, the wallet is opened automatically and there is no need to enter any password to open the wallet.

Step 12: Create a PDB clone

When cloning a PDB, the wallet password is needed.

create pluggable database clonepdb from ORCLPDB;

create pluggable database clonepdb from ORCLPDB 
keystore identified by MyWalletPW_12;

Step 13: Create Secure External Password Store (SEPS)

By saving the TDE wallet password in a Secure External Password Store (SEPS), we will be able to create a PDB clone without specifying the wallet password in the SQL command.

mkdir $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde_seps

alter system set EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION = '$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde_seps' scope=spfile;
shutdown immediate;
startup;

administer key management ADD SECRET 'MyWalletPW_12' for client 'TDE_WALLET'
using TAG 'TDE keystore password'
to [local] auto_login keystore '$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde_seps';

ls -ltr $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde_seps

Step 14: Create a new PDB

To create a new PDB (clone from PDB$SEED):

create pluggable database PDBNEW admin user newpdbadmin identified by NewAdPW_12;
alter pluggable database PDBNEW open;
alter session set container=PDBNEW;
administer key management set key force keystore identified by external store with backup;

Step 15: Create a local PDB clone using the external store

Now, create the PDB by using the following command

create pluggable database clonepdb2 from ORCLPDB 
keystore identified by external store;

Step 16: Create a remote PDB clone using the external store

Remote Cloning works the same way.

create pluggable database REMOTE1 from PDB1@DBLINK_TO_SOURCE 
keystore identified by MyWalletPW_12;

create pluggable database REMOTE2 from PDB1@DBLINK_TO_SOURCE 
keystore identified by external store;

Step 17: Check and create the master encryption keys in the cloned PDBs

If you check the newly created PDBs, you’ll see that they don’t have any master encryption keys yet.

SELECT p.con_id, p.name, p.open_mode, ek.key_id --, ek.creation_time, ek.KEY_USE
FROM v$pdbs p left outer join V$ENCRYPTION_KEYS ek on (ek.con_id = p.con_id)
order by p.con_id;

Open the PDBs, and create the master encryption key for each one. In the following example for CLONEPDB2.

alter pluggable database all open;
alter session set container=CLONEPDB2;
administer key management set key force keystore identified by external store with backup;

The PDB CLONEPDB2 has it’s own master encryption key now.

Note: if the source PDB already has a master encryption key and this is imported to the cloned PDB, you’d do a re-key operation anyway and create a new key in the cloned PDB by executing the same command above.

Check Oracle documentation before trying anything in a production environment.