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.

No alt text provided for this image

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;
No alt text provided for this image

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;
No alt text provided for this image

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

When using the WALLET_ROOT database parameter, the TDE wallet MUST be stored in a subdirectory named “tde”. The following command will create the password-protected keystore, which is the ewallet.p12 file.

mkdir $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde
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
No alt text provided for this image

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.

No alt text provided for this image

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

administer key management 
set keystore open identified by MyWalletPW_12 container=ALL;
No alt text provided for this image

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;
No alt text provided for this image

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;
No alt text provided for this image

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;
No alt text provided for this image

Step 8: Create a table and insert data

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

select * from enctable;
No alt text provided for this image

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;
No alt text provided for this image

Ohhh! Let’s check

No alt text provided for this image

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;
No alt text provided for this image

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
No alt text provided for this image

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.

No alt text provided for this image

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;
No alt text provided for this image

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;
No alt text provided for this image

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
No alt text provided for this image

Step 14: 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;
No alt text provided for this image

Step 15: 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;
No alt text provided for this image

Step 16: 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;
No alt text provided for this image

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;
No alt text provided for this image

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.

Would you like to get notified when the next post is published?