TDE Key Store Management in Oracle Database

Once a keystore is created, it cannot be deleted. It is important to test thoroughly before creating one in a production environment.

Steps to Set Up a Keystore:

sqlplus / as sysdba

Create the Keystore: Create a keystore by setting a password.

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY tde_key#$03;

A new folder called tde is created under the wallet_root directory:

cd /opt/app/oracle/wallet/

$ ls

tde

cd tde/

$ ls

ewallet.p12

Open the Keystore: Open the keystore with the password:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY tde_key#$03;

Set the Master Key: Set the master key with a backup.

ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'master key' IDENTIFIED BY tde_key#$03 WITH BACKUP USING 'masterbackup';

This creates a backup file:

ewallet.p12

ewallet_2019080809241127_masterbackup.p12

Create Auto-login for Keystore: Generate an auto-login keystore.

ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY tde_key#$03;

Query to confirm wallet status:

SELECT WRL_TYPE, STATUS, WALLET_TYPE, WALLET_ORDER, FULLY_BACKED_UP, CON_ID, WRL_PARAMETER FROM V$ENCRYPTION_WALLET;

Database Restart: After restarting the database, the wallet type should change to AUTOLOGIN.

Moving the Master Key:

To delete the keystore, you must move the active master key to another location:

Identify the Key ID:

SELECT KEY_ID, CREATION_TIME, ACTIVATION_TIME, TAG FROM V$ENCRYPTION_KEYS;

Move the Master Key:

ADMINISTER KEY MANAGEMENT MOVE KEYS

TO NEW KEYSTORE '/home/oracle'

IDENTIFIED BY test

FROM FORCE KEYSTORE IDENTIFIED BY tde_key#$03

WITH IDENTIFIER IN 'ASxb82RXzk+5v+YrGLS1IYEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'

WITH BACKUP;

Change from Auto-login to Local Auto-login:

Local auto-login adds security by restricting access to the machine on which it was created.

Close the Keystore:

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;

Backup the Auto-login File:

mv cwallet.sso cwallet.sso.bak

Open the Password Keystore:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY tde_key#$03;

Create a Local Auto-login Keystore:

ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY tde_key#$03;

Backup Keystore:

Backup Command:

ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'backup_tag' FORCE KEYSTORE IDENTIFIED BY tde_key#$03;

Backup to Specific Location:

ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'backup_tag' FORCE KEYSTORE IDENTIFIED BY tde_key#$03 TO '/home/oracle';

Create Master Key for Later Use:

Create a New Master Key:

ADMINISTER KEY MANAGEMENT CREATE KEY USING TAG 'new key for later' FORCE KEYSTORE IDENTIFIED BY tde_key#$03 WITH BACKUP USING 'later key';

Activating a Master Key Created Earlier:

Identify the Key to Activate:

SELECT KEY_ID, CREATION_TIME, ACTIVATION_TIME, TAG FROM V$ENCRYPTION_KEYS;

Activate the Master Key:

ADMINISTER KEY MANAGEMENT USE KEY 'AUBz/7910k8mvzRinUjJI8sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' USING TAG 'later key activates' FORCE KEYSTORE IDENTIFIED BY tde_key#$03 WITH BACKUP USING 'later key activates';

Rekeying the Master Key:

Check Tablespace Status: Ensure tablespaces are not undergoing online rekeying.

SELECT TS#, ENCRYPTIONALG, STATUS FROM V$ENCRYPTED_TABLESPACES;

Set a New Master Key:

ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'new key' FORCE KEYSTORE IDENTIFIED BY tde_key#$03 WITH BACKUP USING 'new key backup';

Export and Import Master Keys:

Exporting:

Export a Master Key:

ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "exported key" TO '/home/oracle/exportedkey.p12' IDENTIFIED BY tde_key#$03 WITH IDENTIFIER IN 'AW/NN5gqQk//vwjnb0ibS9oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "my_secret" TO '/u02/oradata/wallets/devdb02.exp' IDENTIFIED BY tde_key#$03;

Importing:

Import the Master Key:

ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "exported key" FROM '/home/oracle/exportedkey.p12' IDENTIFIED BY tde_key#$03 WITH BACKUP;

Merging Keystores:

Keystores can be merged to consolidate or migrate keys:

Merge Command:

ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '/home/oracle/Public' IDENTIFIED BY test INTO EXISTING KEYSTORE '/opt/app/oracle/wallet/tde' IDENTIFIED BY tde_key#$03 WITH BACKUP;

Change Keystore Password

To change the password of the Oracle TDE keystore, follow these steps:

Specify the Old Password and New Password

Use the following command to change the keystore password by providing both the old password and the new password:

ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD FORCE KEYSTORE

IDENTIFIED BY key#st0r403 --old password

SET tde_key#$03 -- new password

WITH BACKUP USING 'pwd_change';

key#st0r403 is the old password.

tde_key#$03 is the new password.

A backup is created using the identifier 'pwd_change'.

Auto-login File Update

After successfully changing the password, the auto-login file (cwallet.sso) and other related keystore files are automatically updated with the new password.

Example of the updated files:

-rw-------. 1 oracle asmadmin 14091 Aug 13 09:29 ewallet_2019081309291420_pwd_change.p12

-rw-------. 1 oracle oinstall 14091 Aug 13 09:29 ewallet.p12

-rw-------. 1 oracle asmadmin 14136 Aug 13 09:29 cwallet.sso

The cwallet.sso file allows automatic opening of the keystore, thus avoiding the need to manually provide the keystore password during every instance startup.