Using Transparent Database Encryption in Oracle Database 11g

This tutorial describes how you can use Transparent Database Encryption to encrypt data stored on disk using Enterprise Manager Database Control in Oracle Database 11g.

30 minutes

This tutorial covers the following topics:

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Transparent data encryption enables you to encrypt individual table columns or an entire tablespace. When a user inserts data into an encrypted column, transparent data encryption automatically encrypts the data. When users select the column, the data is automatically decrypted. After the selection, the data is reencrypted.

Transparent data encryption helps protect data stored on media in the event that the storage media or data file gets stolen, because it stores the encryption keys in a security module (that is, a wallet) external to the database. Protecting data from this type of theft is required for most compliance regulations. The benefit to using transparent data encryption is that it requires little coding and is quick and easy to implement.

In Oracle Database 11g, you can use Enterprise Manager to manage transparent data encryption.

Before you perform this tutorial, you should:

1.

Install the database by completing the Installing Oracle Database 11g Release 2 on Linux tutorial.

Back to Topic List

In this section, you change the location of the wallet and open it with Enterprise Manager Database Control. Perform the following steps:

1.

Open your browser and enter the following URL.

https://<hostname>:1158/em

Login as the system user.

 

2.

Select the Server tab.

 

3.

Under Security, click Transparent Data Encryption.

 

4.

It is recommended that the wallet is never stored in the same backup as the database as anyone with database backup and the associated wallet could launch a brute force attack on the wallet password to gain access to the protected data. In line with this we will set the location for the wallet to a directory that will not be backed up in a normal backup of the database or it's associated binaries. Click Change.

 

5.

Since the directory that you want to change to doesn't exist yet, you need to create it. Open a terminal window and execute the following commands to create the directory. Also take note of the directory location:

pwd
mkdir wallets
chmod 750 wallets

ls -ld wallets




Note: In a production implementation you would most likely locate your wallets in a non-user directory assigned by your systems administrator.

 

6.

Back inside Enterprise Manager. Enter your Oracle software owner Username and Password and click Login.

 

7.

Enter the location of the directory you created earlier into the Encryption Wallet Directory field and click OK.

 

8.

The change has been made but it is not immediately picked up by Enbterprise Manager. To quickly force the change log out of Enterprise Manager and log back in again. Click Logout.

 

9.

Click Login.

 

10.

Login as system again.

 

11.

Select the Server tab.

 

12.

Under Security, click Transparent Data Encryption.

 

13.

Notice that the directory name has changed. Enter a strong password that protects the wallet and click OK. Optionally, this password can be known only to a Security DBA, enforcing separation of duty. In any event, the password must be remembered (or securely noted) as it cannot be recovered if it is lost.

 

14.

Your wallet has been created and is now open.

 

Back to Topic List

In this tutorial, you encrypt a column in the OE.CUSTOMERS table. Perform the following steps:

1.

Scroll to the bottom of your Enterprise Manager page and click Tables in the Related Links section.

 

2.

Enter OE in the Schema field and click Go.

 

3.

Select the radio button in front of CUSTOMERS and click Edit.

 

4.

You can specify a different encryption algorithm and the key seed to be used for all encrypted columns in this table. Click Encryption Options.

 

5.

Review the options and click Continue.

 

6.

Select the checkbox in the Encryption column for CREDIT_LIMIT and click Apply.

 

7.

A job was submitted to encrypt the column. Click the link to the job.

 

8.

The job succeeded. Click the Database breadcrumb.

 

Back to Topic List

In this section, you create a tablespace that is encrypted. Perform the following steps:

1.

From the Server tab in Enterprise Manager Database Control, under Storage, select Tablespaces.

 

2.

Click Create.

 

3.

Enter TDE for the name of the tablespace and click Add under Datafiles.

 

4.

Enter TDE for the File Name and click Continue.

 

5.

Tablespace encryption protects all the objects in a tablespace by storing data in encrypted format on disk. An Oracle wallet must exist and needs to be in open state. Click Encryption Options.

 

6.

The wallet is open. Select your preferred encryption algorithm and click Continue.

 

7.

Select the Encryption checkbox and click OK.

 

8.

Your tablespace was created successfully. Select the TDE link from the list of Tablespaces.

 

9.

Notice that the Encryption option is set to YES. Click the Database breadcrumb.

 

Back to Topic List

In this section, you create a table in the encrypted tablespace, create an index on one of the columns in the table and then access the data in that column to see what execution plan is used. Perform the following steps:

1.

Open a terminal window and execute the following commands to create a copy of the customers table in your newly created encrypted tablespace:

sqlplus / as sysdba

CREATE TABLE oe.customers_tde TABLESPACE tde
AS SELECT * FROM oe.customers;

 

2.

Execute the following command to create an index on the newly created tablespace. The index will also reside in the encrypted tablespace.

CREATE INDEX oe.customers_tde_idx ON oe.customers_tde(date_of_birth)
TABLESPACE tde;

 

3.

We will now query the encrypted table using a search condition that will use the encrypted index. Execute the following commands to process the query and display the associated execution plan:

set linesize 120

set autotrace on

SELECT cust_last_name, date_of_birth FROM oe.customers_tde
WHERE date_of_birth > '04-FEB-59'
AND date_of_birth < '06-FEB-59';

Note that the query transparently used the encrypted table and index as it would if the tablespace was not encrypted.

 

4.

Exit sqlplus and navigate to the directory containing your encrypted tablespace.

 

5.

The original (unencrypted) customers table is located in the EXAMPLE tablespace. Anyone with access to database files can circumvent database security by interrogating the database files directly. Execute the following Linux command to parse the datafile and return some information about Mr Pacino.

strings example01.dbf | grep -A 10 -B 10 Pacino |  head -20 

 

6.

Now look for Pacino in the encrypted tablespace. Note that nothing is returned. Use the following command:

strings TDE | grep Pacino

 

7.

Execute the following command to see if any human readable information exists in the TDE datafile:

strings TDE | head -20

The output confirms that the file in encrypted.

 

Back to Topic List

The database master key can be regenerated if it has been compromised. This procedure only regenerates the master keys used for encrypted columns; master keys for encrypted tablespaces cannot be re-keyed. If you need to regenerate the master key for an encrypted tablespace, you need to create a new encrypted tablespace and move all the content from the original encrypted tablespace to the new encrypted tablespace. Perform the following steps:

1.

From the Server tab in Enterprise Manager Database Control, under Security, select Transparent Data Encryption. .

 

2.

Click the + in front of Advanced Options.

 

3.

Under Regenerate Master Database Key, click Regenerate. Enter the same strong password you used when initially creating the wallet; both the old and new master keys are not related to the wallet password at all; click OK.

 

4.

Your Master Database Key was regenerated.

 

Back to Topic List

Perform the following steps to remove the objects and settings you created in this tutorial:

1.

From a terminal window execute the following commands:

sqlplus / as sysdba

ALTER TABLE oe.customers MODIFY (credit_limit DECRYPT);

DROP TABLESPACE tde INCLUDING CONTENTS AND DATAFILES;

exit

.

 

2.

Back in the Enterprise Manager Transparent Data Encryption page, select the Close Wallet checkbox and click OK.

.

 

3.

The wallet is now closed and Transparent Data Encryption is disabled.

.

 

4.

Back in your terminal window, execute the following series of commands to remove the wallet and the directory that contained it:

cd /home/oracle/wallets

rm ewallet.p12

cd ..

rmdir wallets

.

 

Back to Topic List

In this tutorial, you learned how to:

Prepare the database for encryption
Encrypt a column in an existing table
Create and use an encrypted tablespace
Regenerate the database master key

Back to Topic List

Move your mouse over this icon to hide all screenshots