Monthly Archives: February 2016

TDE safety net

2 Replies

You run your Oracle database with Transparent Data Encryption (TDE) and you store your Master Encryption Keys in a software keystore (wallet)?
Sure, you backup the keystore to a safe location and you are confident that in a desaster scenario you will be able to recover and open the keystore from backup. Maybe you are just a bit overconfident… or I’m paranoid. Anyway, here’s the safety net to TDE if you can’t open the keystore or any of its backups.

Whenever you create (or rekey) a Master Encryption Key you can extract all the information from the software keystore. Make sure you have a safe terminal session and nobody is watching over your shoulder (lock yourself in a room with your security officer). Write the key information on a piece of paper, envelope it and securely put it in a safe.

As you probably know from secure external password stores Oracle stores the information in a keystore as key-value pairs. This is also true for all the key information. Unitl 12c we used the “orapki” utility for keystore management. Starting with 12c you should not use that utility anymore as all operations should be performed using “ADMINISTER KEY MANAGEMENT” SQL commands.
“orapki” would not be useful in this context anyway as it won’t allow you do display the value for a given key. But, there’s still the good old “mkstore” utility which is mainly used for managing secure external password stores. This little tool allows you to display the value for a given key.
So, we (ab)use “mkstore” to extract all the Master Encryption Key information:

# read the keystore password from the command prompt
read -s -p "Keystore password: " keypass

# 1) get all TDE related key entries from the keystore
# 2) print the key-value pairs for each key entry
for secret in $(echo ${keypass} \
  | mkstore -wrl ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde -list \
  | grep '^ORACLE.SECURITY');
do
  echo ${keypass} \
     | mkstore -wrl ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde -viewEntry ${secret} \
     | grep '^ORACLE.SECURITY'
done

Note: even though Oracle probably didn’t think of this as a use case for “mkstore” I don’t see anything why this operation should not be supported.

Here’s a sample output of above script anotated with some comments:

# this is the actual Master Encryption Key
ORACLE.SECURITY.DB.ENCRYPTION.AfUlOfRdpE/VvwpTbMGlxlIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA = AEMAASAAPoVGwpf57OTqOnRzOLr5ElAOOO7JDfZm7Gf1GtEIBwQDEADaxjuiRvu9qTEz0HBp6vp7BQcAeHQBHw0iPA==

# This matches column KEY_ID in V$ENCRYPTION_KEYS
# It holds the ID of the currently active Master Encryption KEY
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AfUlOfRdpE/VvwpTbMGlxlIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

# I'm not quite sure, I think this matches column WALLET_TYPE in V$ENCRYPTION_WALLET
ORACLE.SECURITY.ID.ENCRYPTION. = PASSWORD

# This matches column FULLY_BACKED_UP in V$ENCRYPTION_WALLET
# "KB" probably stands for "Key Backup"
ORACLE.SECURITY.KB.ENCRYPTION. = ORACLE.KEY.STORE.NOT.BACKED.UP

# This is the rest of the information in V$ENCRYPTION_KEYS encoded in some format
# "KM" probably stands for "Key Metadata" (or something like that)
ORACLE.SECURITY.KM.ENCRYPTION.AfUlOfRdpE/VvwpTbMGlxlIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA = 019C0001000D7874011F0C223C2CE54D28153C000D7874011F0C232837D0ECD8153C0000000000000003001E53595300000000000000000000000000000000000000000000000000000000330090D128C43600000004001E44455631000000000000000000000000000000000000000000000000000000000001FFFFFFFF00000004001E444556310000000000000000000000000000000000000000000000000000000100000008001E43444224524F4F5400000000000000000000000000000000000000000000000000010010286F66C41A66075DE0531F38A8C0351F0000000000000003001E5359530000000000000000000000000000000000000000000000000000000090D128C43600000004001E44455631000000000000000000000000000000000000000000000000000000000001FFFFFFFF00000004001E444556310000000000000000000000000000000000000000000000000000000100000008001E43444224524F4F5400000000000000000000000000000000000000000000000000010010286F66C41A66075DE0531F38A8C0351F00000001

# This matches column TAG in V$ENCRYPTION_KEYS (the tag specified in the ADMINISTER KEY MANAGEMENT command)
# "KT" probably stands for "Key Tag"
ORACLE.SECURITY.KT.ENCRYPTION.AfUlOfRdpE/VvwpTbMGlxlIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA = DEV1_MK_ACT

 
Recover (re-create) the keystore
Now, in case we lose the keystore and all its backups we can re-create the keystore with all its necessary content. Just get the notes from the safe.

Create a new, empty keystore

administer key management
  create keystore '<ORACLE_BASE>/admin/<ORACLE_SID>/wallet/tde'
  identified by "<KEYSTORE-PASSWORD>"
;

Insert the key information using “mkstore”

# Example with actual values from above sample output
read -s -p "Keystore password: " keypass
echo ${keypass} | mkstore -wrl ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde \
  -createEntry ORACLE.SECURITY.DB.ENCRYPTION.AfUlOfRdpE/VvwpTbMGlxlIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AEMAASAAPoVGwpf57OTqOnRzOLr5ElAOOO7JDfZm7Gf1GtEIBwQDEADaxjuiRvu9qTEz0HBp6vp7BQcAeHQBHw0iPA==

echo ${keypass} | mkstore -wrl ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde \
  -createEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY AfUlOfRdpE/VvwpTbMGlxlIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

echo ${keypass} | mkstore -wrl ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde \
  -createEntry ORACLE.SECURITY.ID.ENCRYPTION. PASSWORD

echo ${keypass} | mkstore -wrl ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde \
  -createEntry ORACLE.SECURITY.KB.ENCRYPTION. ORACLE.KEY.STORE.NOT.BACKED.UP

echo ${keypass} | mkstore -wrl ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde \
  -createEntry ORACLE.SECURITY.KM.ENCRYPTION.AfUlOfRdpE/VvwpTbMGlxlIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 019C0001000D7874011F0C223C2CE54D28153C000D7874011F0C232837D0ECD8153C0000000000000003001E53595300000000000000000000000000000000000000000000000000000000330090D128C43600000004001E44455631000000000000000000000000000000000000000000000000000000000001FFFFFFFF00000004001E444556310000000000000000000000000000000000000000000000000000000100000008001E43444224524F4F5400000000000000000000000000000000000000000000000000010010286F66C41A66075DE0531F38A8C0351F0000000000000003001E5359530000000000000000000000000000000000000000000000000000000090D128C43600000004001E44455631000000000000000000000000000000000000000000000000000000000001FFFFFFFF00000004001E444556310000000000000000000000000000000000000000000000000000000100000008001E43444224524F4F5400000000000000000000000000000000000000000000000000010010286F66C41A66075DE0531F38A8C0351F00000001

echo ${keypass} | mkstore -wrl ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde \
  -createEntry ORACLE.SECURITY.KT.ENCRYPTION.AfUlOfRdpE/VvwpTbMGlxlIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA DEV1_MK_ACT

Open the keystore in the database and you can access your TDE encrypted data again.

administer key management
  set keystore open
  identified by "<KEYSTORE-PASSWORD>";

 
Some thoughts
From a security perspective I think the Master Encryption Keys should not be extractable by printing it in the terminal session. This is not secure. On the other hand you probably should be using a HSM anyway.
TDE can be scary as you might possibly loose all the data in the encrypted tablespaces. In the odd case you use software key stores the Master Encryption Key extract can be a live saver. Just get your security officer involved to discuss how to handle the plain-text key information in a secure manner (and maybe now you’ll get a HSM).