Where is my wallet – loosing the encryption master key in 11g db, compatibility in 12c

A while ago I configured an encrypted column in a table for a customer in database 11g. To achieve this, a wallet had to be created outside the database. The main characteristic of this concept is that your data in the database is safe for unauthorized acces, so the wallet is seperated from the databases. All the advantages of this concept can be read in the Oracle documentation  here.

As I am relatively new to these security concepts (not very much used), I was just curious what will happen when loosing this wallet at the operation system level. And, as database 12c has arrived, if this wallet and the encrypted data can be transported to a 12c-database (the level  of backward compatibility). This is the more challenging, while 12c is not using wallets anymore, but keystores (for who is keeping his keys in a wallet…). I think it’s usefull to know if wallets actually work in 12c. Only then you can decide what effort has to be made in preparing the change to keystores before any upgrade to 12c.

The steps I took:

1. Created a wallet and encrypt a column of a table in 11g.

2. Messing around on o.s.-level: deleted the wallet(s), restored the wallet, tried to recover the wallet.

3. Used this wallet in a newly created 12c database, and imported the table.
An overview of the concept of encryption:

 

 

1. Creating a wallet

Created a wallet and encrypted a column of a table in 11g.

This is well documented in the advanced security guide, but roughly I took the following steps:

Created a directory on the Operating System level, edited the sqlnet.ora,  and created a wallet with autologin-feature with the o.s.-command orapki.

At last created a simple table, and encrypt one column of it. No RAC involved, so don’t have to copy files to the other instance (or put it on a shared device).

So now two files exists in the wallet-directory:

– cwallet.sso (the autologin wallet)

– ewallet.p12 (the wallet with de TDE – Transparant Data Encryption – master key in it)

To check if the data really is encrypted and correct configured:

SQL> select owner, table_name,column_name,encryption_alg from dba_encryption_columns.

 

2. Messing around on o.s.-level

Deleted the file cwallet.sso (the autologin wallet) on the o.s.-level.

Result:

- SQL> select * from TABTDE; –> no problem reading the data, as expected, it’s just the auto-login wallet.

- Shutdown , startup database: no problem with starting the database

- SQL> select * from .TABTDE; ---> ORA-28365: wallet is not open.

- SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “<password>”;

- SQL> select * from TABTDE;  --> works again.

So the cwallet.sso is indeed just for the autologin, no key in it.

Deleted the ewallet.p12 too.

Result:

- SQL> select * from TABTDE;  --> No problem in reading the data, key is read from the database (but when will I find out I’ve lost my wallet?).

- SQL> Shutdown , startup database: --> No problem to startup. No errors in alert-file also.

- SQL> select * from TABTDE; –> This gives ORA-28365: wallet is not open

And….. the wallet can’t be opened. –> ORA-28353: failed to open wallet……Oops.

This is a ticking timebomb. The DBA only finds out there’s a problem when the database is bounced and the users starting to use the application. And that may take a while.

Don’t want to be the DBA who’s bouncing the database in the middle of the night and discovering this.

 

Regenerate the wallet

Is is possible to recreate the wallet?

# orapki wallet create -wallet /home/oracle/wallets/<dbname>  -auto_login

Enter password: <same password as initial> –> wallet has been created.

SQL> select * from TABTDE; -->  ORA-28362: master key not found, tried to match the new key to the table:

SQL>alter table TABTDE rekey;  --> ORA-28361: master key not yet set. O.k, I'll set the master key:

SQL>alter system set encryption key authenticated by “<password new wallet>”; –> ORA-28362: master key not found.

SQL>alter system set wallet close; –> ORA-28390: auto login wallet not open but encryption wallet may be open.

Shutdown, restart of the database: doesn’t help.

Hmm… doesn’t seem to be the best idea. After some poking around with changing passwords of the wallet and that kind of stuff, read a clarifying note 1342875.1 about the master key in my Oracle Support and they stated in the end: “As a consequence this is also the reason why wallets should not ever be deleted or moved . The newly generated wallet will contain a new master key which cannot be used for the decryption of the existing encrypted data.”

So backup the wallets is of vital importance.

Restoring the wallet, just copy the old wallets to the right place

just copied both the wallets to the right place. Is it working?

- SQL> select * from TABTDE; -->  ORA-28362: master key not found. Couldn’t open the wallet either.

But probably it wasn’t synchronized between memory and wallet, so a reboot should help.

Shutdown/startup of the database then.

- SQL> select * from TABTDE; --> no problem anymore, data can be read again.

3. Used the 11g-wallet in 12c, and import the table in 12c

Exported the table with expdp in 11g. Noticed an ora-39173 error:  “encrypted data has been stored unencrypted in dump file set”. It’s no error, works as designed. The data is unencrypted. But.. be aware, the create table command with the encrypted column has also been saved in the dump file. So in the import it’s trying to create a table with an encrypted column in it!

Took the same steps as in 11g to create a wallet (sqlnet.ora), but used the wallet from 11g, and imported the dump file (spoiler alert: don’t do this in this order in real life!). The table has been created with the encrypted column in it and imported the unencrypted data in de encrypted column!

Does it work: Select * from testtde.tabtde:  working! Is it encrypted: yes (select * from dba_encrypted_columns).

But.. is this a wallet or a keystore:

SQL> Select * from v$encryption_wallet:

- Status = open

- Wallet_type = unknown

The type of keystore that was created:

SQL> SELECT KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS;

- UNDEFINED

More alarming: can’t open or close the wallet the old way anymore, and the new way ‘ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY “..”;  gives the error ‘ORA-28417 password-based keystore is not open.’

I reached a state where I don’t want to be: a wallet/keystore twilight zone. So I stopped.

Just a few points I am now more aware of: keep track of your wallet in 11g , en don’t put your keys in a wallet at 12c, but in the keystore. And when upgrading from 11g to 12c, pay special attention to the encrypted data and the wallets.

For now I’m just curious how the migration to 12c handles this….

 

Sources:

FAQ: http://www.oracle.com/technetwork/database/security/tde-faq-093689.html

Advanced security guide: http://docs.oracle.com/cd/E18283_01/network.112/e10746/asotrans.htm

Doc id 1342875.1: The Master Key : What happens when the master key is used, accessed and reset in TDE ?