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.

 

1.

Install Oracle Database 11g

In this section, you update your sqlnet.ora, create an encrypted wallet (ewallet.p12), open the wallet, and create the master key for TDE. You will also verify that TDE is enabled and the wallet is open in Enterprise Manager Database Control. Perform the following steps:

1.

Open your sqlnet.ora file located in the $ORACLE_HOME\network\admin directory in an editor.

You need to update your sqlnet.ora file to include an entry for ENCRYPTED_WALLET_LOCATION. Add the following command:

ENCRYPTION_WALLET_LOCATION= 
(SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=<oracle_home>)))

where <oracle_home> is the directory location of your oracle home (for example, /u01/app/oracle/product/11.1.0/db_1)

Save your changes and close the file.

Note: Any directory can be picked for the encrypted wallet, but the path should not point to the standard obfuscated wallet (cwallet.sso) created during DB installation.

 

2.

Next, you need to open the wallet and create the master encryption key. Open a SQL*Plus session and execute the following commands:

connect / as sysdba
alter system set key identified by "welcome1";  

The above alter command does the following:

Note: only users with the 'alter system' privilege can create a master key or open the wallet.

The master key should only be created once, unless you want to re-encrypt your data with a new encryption key !!!

For later sessions, you do not want to use the command given above; you need the wallet to be open (it has been closed when you shut down your database), but you don't want to create a new master key. Then the command is:

alter system set wallet open identified by "welcome1"; 

The master encryption key is necessary because each table has its own encryption key. These column keys are stored in the database. Since the wallet can only store a limited number of keys and is not very scalable, the column keys are encrypted with the master key. This way, you can have as many column keys as needed, with only a small number of master keys stored in the wallet (including retired keys, that you may need one day to decrypt data from an old backup-tape). By default, the command above generates a key using the Advanced Encryption Standard with 192 bits (AES192). 3DES could also be used, or a smaller or bigger number of bits for the AES encryption.

 

3.

To verify that Transparent Data Encryption is enabled from within Enterprise Manager Database Control, open your browser and enter the following URL.

https://<hostname>:1158/em

Login as the system user.

 

4.

Select the Server tab.

 

5.

Under Security, click Transparent Data Encryption.

 

6.

The wallet is open and Transparent Data Encryption is enabled.

 

Back to Topic List

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

1.

Under Related Links, click Tables.

 

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 OBE for the name of the tablespace and click Add under Datafiles.

 

4.

Enter OBE 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.

Review the options and click Continue.

 

7.

Select the Encryption checkbox and click OK.

 

8.

Your tablespace was created successfully. Select the OBE 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 SQLDeveloper. On Linux, open a terminal window and execute the following commands:

cd $ORACLE_HOME/sqldeveloper/sqldeveloper/bin
./sqldeveloper

If you receive a message asking you if you want to migrate your previous connections, click No.

 

2.

Create a Connection. Right-click Connections and select New Connection.

 

3.

Enter OE for the Connection Name, Username and Password. Enter orcl for the SID and click Test.

 

4.

Your test was successful. Click Connect.

 

5.

You first want to create a copy of the CUSTOMERS table. From the SQL Worksheet, enter the following command and click Execute.

CREATE TABLE customers_obe AS SELECT * FROM customers 

 

6.

Now you can move the table into the OBE tablespace (that is encrypted). Expand Tables and right-click CUSTOMERS_OBE and select Storage then Move Tablespace.

 

7.

Select the OBE tablespace from the list and click Apply.

 

8.

The table was moved to the OBE tablespace successfully. Click OK.

 

9.

Now you can create an index on the DATE_OF_BIRTH column. Enter the following command in the SQL Worksheet area and click Execute.

CREATE INDEX customers_obe_idx ON customers_obe(date_of_birth) 

 

10.

You can now select some data from the DATE_OF_BIRTH column. Enter the following command in the SQL Worksheet area and click Execute.

SELECT cust_last_name, date_of_birth FROM customers_obe
WHERE date_of_birth > '04-FEB-59'
AND date_of_birth < '06-FEB-59'

The data is selected. So what explain plan did it use. Click the Explain Plan icon.

 

11.

Notice that it used the index and did an index(range scan) to retrieve the data rather than a full table scan.

 

Back to Topic List

The database master key can be regenerated if it has been compromised. 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 welcome1 for the Encryption Wallet Password and click OK.

 

4.

Your Master Database Key was regenerated.

 

Back to Topic List

You can disable Transparent Data Encryption by closing the wallet. Perform the following steps:

1.

Click the Close Wallet checkbox and click OK.

 

2.

The wallet was closed. To verify that Transparent Data Encryption is disabled, you will select data from the encrypted column you created earlier. Click the Database breadcrumb.

 

3.

Scroll to the bottom of the page, under Related Links, click SQL Worksheet.

 

4.

Enter the following command in the SQL Command area and click Execute.

 

5.

Since the column is encrypted, the wallet needs to be open for the data to be queried. Leave this window open and switch back to Enterprise Manager.

 

Back to Topic List

To reopen the wallet, perform the following steps:

1.

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

 

2.

Under Enable Transparent Data Encryption, enter your password in the Encryption Wallet Password field and click OK.

 

3.

You wallet is now open.

 

4.

Switch back to SQL Worksheet and reexecute the query. Notice this time, the column data appears.

 

Back to Topic List

In this tutorial, you learned how to:

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

Back to Topic List

Move your mouse over this icon to hide all screenshots