How to setup Transparent Data Encryption (TDE) in Oracle 12c for Non-Pluggable database
TDE was introduced as of 10gR2 ( 10.2.0.1 ). In 12c TDE provides a completely different interface to manage the wallet and the master keys it contains. This post is not intended to be a complete guide for managing TDE on 12c, it just provides the steps needed to quickly set up TDE on a 12c non-pluggable database.
1. Set the ENCRYPTION_WALLET_LOCATION in $ORACLE_HOME/network/admin/sqlnet.ora. For example:
$ cat /u01/app/oracle/product/12.1.0.1/db_1/network/admin/sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/home/oracle/tde12c)))
2. Create the keystore (wallet):
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
---------- ------------------------- ------------------- -------------------- --------- --------- ----------
FILE /home/oracle/tde12c/ NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED 0
SQL> administer key management create keystore '/home/oracle/tde12c/' identified by "[password]";
keystore altered.
3. Open the keystore:
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
---------- ------------------------- --------- -------------- --------- --------- ----------
FILE /home/oracle/tde12c/ CLOSED UNKNOWN SINGLE UNDEFINED 0
SQL> administer key management set keystore open identified by "<password>";
keystore altered.</password>
The status “OPEN_NO_MASTER_KEY” means the master key has not been created yet in the keystore:
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
----------- ------------------------- -------------------- -------------- --------- --------- ----------
FILE /home/oracle/tde12c/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
4. Create the master key:
SQL> administer key management create key identified by "<password>" with backup;
keystore altered.</password>
5. In order to create an encrypted table/tablespace the master key should be activated:
SQL> select key_id from v$encryption_keys;
KEY_ID
-----------------------------------------------------
ATj0+xSyzU9mvwKP2JevKFAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL> administer key management use key 'ATj0+xSyzU9mvwKP2JevKFAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' identified by "[password]" with backup;
keystore altered.
SQL> select key_id,activation_time from v$encryption_keys;
KEY_ID ACTIVATION_TIME
-----------------------------------------------------------------------------------------
ATj0+xSyzU9mvwKP2JevKFAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 24-JAN-15 09.46.16.766501 PM +00:00
After the master key is activated the status of the wallet changes to “OPEN”:
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------------ ---------- -------------------- --------- --------- ----------
FILE /home/oracle/tde12c/ OPEN PASSWORD SINGLE YES 0
The attempt to create an encrypted table/tablespace without activating the master key fails:
SQL> create table [username].[tablename](id number encrypt using 'AES256');
create table [username].[tablename]id number encrypt using 'AES256')
*
ERROR at line 1:
ORA-28361: master key not yet set
SQL> create tablespace ENC datafile '/u01/app/oracle/oradata/db12ctde/enc.dbf' size 10M encryption using 'AES256' default storage(encrypt);
create tablespace ENC datafile '/u01/app/oracle/oradata/db12ctde/enc.dbf' size 10M encryption using 'AES256' default storage(encrypt)
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
Now the TDE has been enabled in the database and we can proceed to create encrypted objects. The next example shows different actions we can perform to manage the Keystore in 12c:
6. Re-key the master key:
SQL> administer key management set key identified by "[password]" with backup;
keystore altered.
SQL> select key_id,activation_time from v$encryption_keys;
KEY_ID ACTIVATION_TIME
----------------------------------------------------------------------------------------
AXuxEQtql08yv4InUP6HkdcAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 24-JAN-15 10.25.41.144778 PM +00:00
ATj0+xSyzU9mvwKP2JevKFAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 24-JAN-15 09.46.16.766501 PM +00:00
7. Change the keystore password:
SQL> administer key management alter keystore password identified by "[password]" set "[new password]" with backup using 'change';
keystore altered.
8. Backup the keystore:
SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'tde12c' IDENTIFIED BY "<new password="">" to '/home/oracle';
keystore altered.</new>
9. Create an auto_login keystore:
SQL> administer key management create auto_login keystore from keystore '/home/oracle/wallet12c/tde/' identified by "[new password]";
keystore altered.
10. Close the keystore:
SQL> administer key management set keystore close identified by "[new password]";
keystore altered.
11. Merge two keystores:
SQL> administer key management merge keystore '/home/oracle/tde' identified by [password] into existing keystore '/home/oracle/wallet12c/tde/' identified by [new password] with backup;
keystore altered.
The merge operation has to be performed with both keystores closed otherwise master key activation will fail with:
ORA-46649: master key with the specified identifier does not exist
- When using a shared software keystore location ( wallet) all the keystore operations will be performed on one instance and they will be propagated automatically and transparently to all the other nodes. All the nodes must have the correct ENCRYPTION_WALLET_LOCATION parameter set beforehand.
- When using a non-shared wallet all the keystore operations must be performed on one node and then the resulted wallet must be copied to all the other nodes. Furthermore, any key rotation must be followed by a manual synchronization of the wallets with the other nodes ( i.e. the wallet file containing the newly generated key will be copied to all the other nodes).