How To Generate A New Master Encryption Key for the TDE

 
How To Generate A New Master Encryption Key for the TDE

The statements used to generate a new master key 

alter system set encryption key identified by "<password>"; -- when using a file

alter system set encryption key identified by usr:passwd; -- when using HSM(this is not available in 10gR2)

To check whether a new master key was generated dump the contents of the wallet before and after the operation:

[oracle@seclin4 wallet]$ orapki wallet display -wallet -pwd <password>

Oracle PKI Tool : Version 11.2.0.2.0 - Production

Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:

Subject: CN=oracle

User Certificates:

Oracle Secret Store entries:

ORACLE.SECURITY.DB.ENCRYPTION.AV8kySrjGU/rv4vxZLV9/kAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.AXUWrqkVHU9LvysE2PqARpwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAAAAAA

Trusted Certificates:

[oracle@seclin4 wallet]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 22 15:02:48 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> alter system set encryption key identified by "<password>";

System altered.

SQL> ! orapki wallet display -wallet . -pwd <password>

Oracle PKI Tool : Version 11.2.0.2.0 - Production

Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:

Subject: CN=oracle

User Certificates:

Oracle Secret Store entries:

ORACLE.SECURITY.DB.ENCRYPTION.AV8kySrjGU/rv4vxZLV9/kAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.AVdps8EplE9Svy/okCRsTNMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.AXUWrqkVHU9LvysE2PqARpwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA --> This is the new Master Key

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAAAAAA

Trusted Certificates:

 

When changing the master key it is recommended to :

1) Backup the database and the wallet file

2) Use the correct wallet password in the IDENTIFIED BY clause:

SQL> alter system set wallet open identified by "<password>";

System altered.

SQL> alter system set encryption key identified by "<password1>";

alter system set encryption key identified by "<password1>"

*

ERROR at line 1:

ORA-28353: failed to open wallet

SQL> alter system set wallet open identified by "<password>";

System altered.

The second command  failed  because a wrong password was used to access the wallet. The value specified via the IDENTIFIED BY is the password of the wallet and not the Master Key. Wallet's password can be changed using OWM.

3) Check the permissions of the wallet file. The owner of the Oracle binaries should be able to write the file. If the wallet is not accessible while changing the master key then the encrypted data may be lost.

Observations:

1) Do not use the same wallet for several databases. Try to configure the system so that each database will use it's own wallet.

 If the master keys are stored within a wallet file then running the above statement several times will increase the size of the ewallet.p12 file. On average every 100 master keys are consuming 26KB. Up to 10.2.0.3 the wallet file size is limited to 65KB. Any attempt to regenerate the master key after this limit has been passed will end up with the following error :

alter system set encryption key identified by "<password>"

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [ztsmstore failed],

[18446744073709550614], [], [], [], [], [], []

As of 10.2.0.4 a wallet can grow up to 4GB. Since 100 rekeys generate 26KB if we keep on rekeying every single day we can do it for a very long period( more than 500 years).

2) The auto login wallet ( the cwallet.sso file) is automatically updated with the latest keys:

SQL> alter system set encryption key identified by "<password>";

System altered.

SQL> ! ls -ltr

total 16

-rw-r--r-- 1 oracle oinstall 2365 Feb 22 15:11 ewallet.p12

-rw------- 1 oracle oinstall 2442 Feb 22 15:11 cwallet.sso

SQL> alter system set encryption key identified by "<password>";

System altered.

SQL> ! ls -ltr

total 16

-rw-r--r-- 1 oracle oinstall 2629 Feb 22 15:11 ewallet.p12

-rw------- 1 oracle oinstall 2706 Feb 22 15:11 cwallet.sso

SQL>

Database Recovery and Flashback Database on the TDE Wallet

 These operations don't have any impact on the TDE wallet. More precisely:

1. if performing a Database Media Recovery and within the recovery period there is a master key change, this operation will not be reflected in the database wallet. The wallet will be left unchanged and only the master keys existing in the wallet at recovery start will be present at recovery end. If a master key change has been done in the database within the recovery interval and the existing wallet does not include the master key generated at that time, the recovery will fail.

2. if performing a Flashback Database and a master key change has been done in the covered interval of time, the operation will not be reflected in the wallet, ie all the master keys in the wallet before the flashback will be present at command completion.

3. the direct consequence of the above is that database media recovery cannot be used to recover wallet losses, when there are older wallet copies, if the master key has been changed within the interval between the wallet backup and the wallet loss. The recovery can be performed in this case until the moment when the master key has been changed. In this scenario if the latest wallet is not available after a rekey operation and the database needs to be recovered until the moment of the rekey operation, make sure to also restore a controlfile that was backed up before the latest rekey operation, since the latest controlfile has info about an unavailable master key which will lead to ORA-28374 errors during recovery.

To prove the above information, the following test has been done:

Impact of database recovery on the oracle wallet.

1. create a database

2. set the database in archivelog and flashback mode

mkdir $ORACLE_BASE/admin/$ORACLE_SID/{wallet,archive,backup}

mkdir $ORACLE_BASE/admin/$ORACLE_SID/backup/wallet

SQL> sqlplus "/ as sysdba"

SQL> alter system set log_archive_dest_1='location=<directory>';

SQL> alter system set log_archive_format='archive_<SID>_%t_%s_%r.dbf' scope=spfile;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database flashback on;

SQL> alter database open;

3. enable TDE

SQL> alter system set encryption key identified by "<wallet password>";

4. check wallet contents

cd $ORACLE_BASE/admin/$ORACLE_SID/wallet

mkstore -wrl . -list

Enter wallet password:

Oracle Secret Store entries:

ORACLE.SECURITY.DB.ENCRYPTION.<key 1>

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.<key 2>

5. create an encrypted tablespace and an encrypted table and add some initial data:

SQL> create user <user> identified by <password>;

SQL> grant dba to <user>;

SQL> create table <user>.<table>(id number encrypt);

SQL> insert into <user>.<table> values(1);

SQL> commit;

SQL> create tablespace <tablespace name> datafile '<directory>/<datafile name>' size 10m

encryption using '3des168'

default storage(encrypt);

SQL> create table <user>.<table 1>(id number) tablespace <tablespace name>;

SQL> insert into <user>.<table 1> values(1);

SQL> commit;

5. take a database backup and a wallet backup

rman target /

RMAN> configure channel device type disk format '<backup location>';

RMAN> backup database plus archivelog;

SQL> alter system set wallet close identified by "<wallet password>";

cp $ORACLE_BASE/admin/$ORACLE_SID/wallet/* $ORACLE_BASE/admin/$ORACLE_SID/backup/wallet/ewallet.p12.1

6. recreate master key and add some more data

SQL> alter system set encryption key identified by "<wallet password>";

SQL> insert into <user>.<table> values(2);

SQL> commit;

SQL> insert into <user>.<table 1> values(2);

SQL> commit;

7. check wallet contents

mkstore -wrl . -list

Enter wallet password:

Oracle Secret Store entries:

ORACLE.SECURITY.DB.ENCRYPTION.<key 3>

ORACLE.SECURITY.DB.ENCRYPTION.<key 1>

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.<key 2>

8. make a new wallet backup, as ewallet.p12.2

cp $ORACLE_BASE/admin/$ORACLE_SID/wallet/* $ORACLE_BASE/admin/$ORACLE_SID/backup/wallet/ewallet.p12.2

9. remove the current database and the wallet.

SQL> shutdown immediate

rm <datfile created in step 5>

rm $ORACLE_BASE/admin/$ORACLE_SID/wallet/ewallet.p12

9. restore the backup and the first wallet and recover to present time.

cp $ORACLE_BASE/admin/$ORACLE_SID/backup/wallet/ewallet.p12.1 $ORACLE_BASE/admin/$ORACLE_SID/wallet/ewallet.p12

RMAN> restore database;

SQL> alter system set wallet open identified by "<wallet password>";

RMAN> recover database;

Fails with:

Starting recover at 03-JUN-11

using channel ORA_DISK_1

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 06/03/2011 07:14:31

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed

start

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 1: '<datafile name>'

ORA-28374: typed master key not found in wallet

As seen above, the database media recovery didn't affect the wallet.

10. restore the second wallet, including the latest master key:

SQL> alter system set wallet close identified by "<wallet password>";

cp $ORACLE_BASE/admin/$ORACLE_SID/backup/wallet/ewallet.p12.2 $ORACLE_BASE/admin/$ORACLE_SID/wallet/ewallet.p12

SQL> alter system set wallet open identified by "<wallet password>";

RMAN> recover database;

Starting recover at 03-JUN-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=192 device type=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:01

The solution, as seen above, is to use the latest wallet version.

11. check the table contents:

SQL> alter database open;

SQL> select * from <user>.<table>;

ID

----------

1

2

SQL> select * from <user>.<table 1>;

ID

----------

1

2