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.
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.
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.
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.