Transparent Data Encryption in 11g

In this article I would discuss how to implement Transparent Data Encryption (TDE) in 11g. TDE is used to encrypt data at table/column level or tablespace level. This ensures that encrypted data is accessible only by using encryption key that is stored in either Wallet or Hardware Security Module.

For RAC setups, encryption key needs to be stored/copies on all the RAC nodes. In dataguard setup, encryption key should also be copied on dataguard sites.

Following are the steps to implement TDE.

1. Defining Keystore

Keystore is the location where master encryption key is stored and we use SQLNET.ORA file to point to the location of keystore. If we have multiple databases, each database must have its own keystore to store the master encryption key. Edit sqlnet.ora file and add the keystore location. Directory structure mentioned here must already exist.

ENCRYPTION_WALLET_LOCATION=             (SOURCE=             (METHOD=FILE)(METHOD_DATA=(DIRECTORY C:\app\salmqure\testwallet\salman11 ))             )

If we want to define keystore for multiple databases, we can use entries similar to the following

ENCRYPTION_WALLET_LOCATION=             (SOURCE=             (METHOD=FILE)(METHOD_DATA=(DIRECTORY= C:\app\salmqure\testwallet\%$ORACLE_SID%))             )

Directory with same name as %ORACLE_SID% must already exist. For Unix based platforms, use $ORACLE_SID. For example, if we have 2 databases (MYDB1 and MYDB2) on same database server and both need implementation of TDE, we will add above entry in sqlnet.ora, and then create following 2 directories as keystore for each database.

C:\app\salmqure\testwallet\mydb1 C:\app\salmqure\testwallet\mydb2

For RAC, add this entry in sqlnet.ora on all RAC nodes.
For dataguard, add this entry in all standby sqlnet.ora files.

2. Creating Wallet and Generate Master Encryption Key

For RAC, stop all instances except one and perform this step on the available instance.

alter system set encryption key identified by "salman123";

After execution of above statement, we will see a file ewallet.p12 created under the keystore directory specified above in the sqlnet.ora file. This is the wallet we have just created that can be opened using the password we specified in the command.

We can also check generated encryption key as follows (my database name is salman11)

C:\>orapki wallet display -wallet C:\app\salmqure\testwallet\salman11 Oracle PKI Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Enter wallet password: <provide wallet password here> Requested Certificates: Subject:        CN=oracle User Certificates: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.AS7vjyRV/U90vyHywbmJtAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY Trusted Certificates:

If we have RAC, we need to copy this ewallet.p12 file on all the nodes at the location specified in sqlnet.ora. After copying, we can start all instances.
For dataguard, copy this file on all standby hosts.

3. Opening the Wallet Before Accessing Encrypted Data

Wallet needs to be open to read/write data in the column (for column based TDE) or access the data in encrypted tablespace. If wallet is not open, we will receive ORA-28365: wallet is not open error while accessing the encrypted data. While creating a table with encrypted column or creating an encrypted tablespace, wallet needs to be open. Let’s create a table with encrypted column and then also create an encrypted tablespace.

SQL>  select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS ------------------ file C:\app\salmqure\testwallet\salman11 CLOSED SQL> alter system set encryption wallet open identified by "salman123"; System altered. SQL>  select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER -------------------------------------------------------------------------------- STATUS ------------------ file C:\app\salmqure\testwallet\salman11 OPEN -- Creating a table with encrypted column SQL> conn salman/salman Connected. SQL> create table test_tde (id number,   2  name varchar2(200),   3  salary number encrypt); Table created. -- Creating an encrypted tablespace SQL> create tablespace tdetest_tablespace datafile 'c:\tdetest01.dbf' size 100m encryption using '3DES168' default storage (encrypt); Tablespace created.

4. Enable Autologin

We can enable auto log for the wallet so that it remains open otherwise we would need to open wallet every time database starts. Also note that if database instance crashes, or media failure happens, the recovery (instance recovery or media recovery) needs to recover encrypted data and that requires wallet to be open, and if wallet is not open, database cannot go beyond MOUNT state.

orapki wallet create -wallet C:\app\salmqure\testwallet\salman11\ewallet.p12 -auto_login

Above command would create a file cwallet.sso under keystore directory which will be used for auto open of the wallet with every database startup.
For RAC, execute this step on all the nodes individually to create local cwallet.sso files.
For dataguard, perform this step on each standby host.

Following example shows that wallet is open automatically after restart of the database.

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area  801701888 bytes Fixed Size                  2259480 bytes Variable Size             260048360 bytes Database Buffers          532676608 bytes Redo Buffers                6717440 bytes Database mounted. Database opened. SQL> select * from v$encryption_wallet; WRL_TYPE -------------------- WRL_PARAMETER ---------------------------------------------------------------------------- STATUS ------------------ file C:\app\salmqure\testwallet\salman11 OPEN

5. Take backups of ewallet.p12 and cwallet.sso files.

ewallet.p12 file is very important and if we lose it, there is no we can access our encrypted data. cwallet.sso file is for auto login and can be recreated if we lose it.
We can also set permissions on these file so that no one can modify or delete these files.
Don’t backup ewallet.p12 at the same location where your RMAN backup is taken as it will be a security threat, because if our backup is stolen, the key to access the stolen backup is also stolen along with the secured data.

Rotating Master Encryption Key

We can do a master key rotation on regular basis for a better security. Rotating process generates new encryption key. Steps are as follows

For RAC, stop all instances except one and execute following steps on the running instance.

1. Rotate Master Key

Use following command to rotate the key. Use current wallet password in the command.

SQL> alter system set encryption key identified by "salman123";

Following command shows new encryption key added

C:\>orapki wallet display -wallet C:\app\salmqure\testwallet\salman11\ewallet.p12 Oracle PKI Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Enter wallet password: <Enter wallet password> Requested Certificates: Subject:        CN=oracle User Certificates: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.AS7vjyRV/U90vyHywbmJtAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.AWwnVqRVik/LvzZ9sL5WRxIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY Trusted Certificates:

2. Execute close wallet command

This will cause the wallet to close. cwallet.sso (auto login) file needs to re-open and closing wallet at this point would cause cwallet.sso to re-open automatically when next time any encrypted data will be accessed.

SQL> alter system set encryption wallet close identified by "salman123"

Run a query on eny encrypted table check everything is working fine until this point.

3. Change Wallet Password

C:\>orapki wallet change_pwd -wallet C:\app\salmqure\testwallet\salman11\ewallet.p12 Oracle PKI Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved. Enter wallet password: <Enter Current Password> New password: Enter wallet password:<Enter New Password>

4. Close the wallet

Close the wallet so that it can be re-opened after the password has been reset

SQL>alter system set encryption wallet close;

Run a query on any encrypted table to check everything is working fine until this point.

5. Take backup of ewallet.p12

Take backup of ewallet.p12 file. In case of RAC, delete old ewallet.p12 files from other nodes and copy this file on all other nodes. For dataguard, copy this file on all standby hosts also. 

6. Enable auto-login

In case of RAC, Auto-login needs to be enabled again on all other nodes be executing following command. Delete current cwallet.sso files before executing this command. For dataguard, execute this on all standby hosts.

orapki wallet create -wallet C:\app\salmqure\testwallet\salman11\ewallet.p12 -auto_login

Related Articles

• Configuring Transparent Data Encryption (TDE) in 12c

TDE Related Error Messages

• ORA-46630
• ORA-46633
• ORA-46658
• ORA-28417
• ORA-28354
• ORA-46661
• ORA-28365