software world

Purpose

This module describes how you can use Transparent Database Encrption to encrypt data stored on disk..

Time to Complete

30 minutes

Topics

This module will discuss the following topics:

	Overview
	Scenario
	Preparing the Database for Encryption
	Creating a Table with an Encrypted Column
	Making Changes to Encrypted Data
	Adding and Applying a VPD Policy
	Testing the Policy
	Using LogMiner to View Redo Logs
	Recreating Your Table Without the Encrypted Column
	Reviewing the Redo Logs Again
	Cleanup
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, 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.

Overview

Oracle Database 10g Release 2 Transparent Database Encryption simplifies encryption of sensitive personal information such as credit card numbers and social security numbers. Transparent Data Encryption eliminates the need to embed encryption routines in existing applications and dramatically lowers the cost and complexity of encryption. With a few simple commands, sensitive application data can be encrypted.

Automatic Encryption Of Sensitive Information

Most encryption solutions require specific calls to encryption functions within the application code. This is expensive because it typically requires extensive understanding of an application as well as the abity to write and maintain software. In general, most organizations don't have the time or expertise to modify existing applications to make calls to encryption routines. Oracle Transparent Data Encryption addresses the encryption problem by deeply embedding encryption in the Oracle database.

Application logic performed through SQL will continue to work without modification. In other words, applications can use the same syntax to insert data into an application table and the Oracle database will automatically encrypt the data before writing the information to disk. Subsequent select operations will have the data transparently decrypted so the application will continue to work normally. This is important because existing applications generally expect to see application data unencrypted. Displaying encrypted data may, at a minimum, confuse the application user and may even break an existing application.

Setting The Encryption Key

Oracle Transparent Data Encryption provides the key management infrastructure necessary for implementing encryption. Encryption works by passing clear text data along with a secret, known as the key, into an encryption program. The encryption program encrypts the clear text data using the supplied key and returns the data encrypted. Historically the burden of creating and maintaining the secret or key has been on the application. Oracle Transparent Data Encryption solves this problem by automatically generating a master key for the entire database. Upon starting up the Oracle database, an administrator must open an object known as an Oracle Wallet using password separate from the system or DBA password. The administrator then initializes the database master key. The master key is automatically generated.

Performance

Encryption typically creates problems for existing application indexes because the index data isn't encrypted. Oracle Transparent Data Encryption encrypts the index value associated with a given application table. This means that equality searches within an application will see little to no decrease in performance. For example, assume an index exists on application PERSON ID and the application executes the following statement:

SQL> Select rating from credit where person id = '23590';

The Oracle database will use the existing application index even though the PERSON ID information is encrypted in the database.

Back to Topic List

Scenario

In this tutorial, you will create a table that contains an encrypted column. You will create an index on the encrypted column and grant access to the column for a particular user. This user will then make a change to the data. You will then create a function which contains proper access control to the encrypted data and then apply the function through a VPD policy.

Transparent Data Encryption encrypts data right before it is written to disk and de-crypts it when it is read. It's transparent to all applications that use the SQL layer. Hence there is no easy way to verify that data has truly been encrypted. Since Oracle LogMiner records what is written to disk, you can access the information.

Back to Topic List

Preparing the Database for Encryption

In this section, you will update your sqlnet.ora, create an encrypted wallet (ewallet.p12), open the wallet, and create the master key for TDE. Perform the following:

1.

You need to update your sqlnet.ora file to include an entry for ENCRYPTED_WALLET_LOCATION. Open a terminal window and enter the following commands: cd $ORACLE_HOME/network/admin gedit sqlnet.ora Add the following entry to the end of the file: ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/app/oracle/product/10.2.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. From your terminal window, enter the following commands: cd /home/oracle/wkdir sqlplus /nolog @tde00_dbsetup connect / as sysdba alter system set key identified by "welcome1"; The above alter command does the following: If no encrypted wallet is present in the directory specified, the encrypted wallet is created (ewallet.p12), the wallet is opened, and the master key for TDE is created/recreated. If the encrypted wallet is present in the directory specified, the walled is opened, and the master key for TDE is created/recreated. 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. Posts By (MR.KAKAR)