TDE - Transparent Data Encryption

Encrypt sensitive data transparently without writing a single line of code.

It's your organization's worst nightmare: Someone has stolen backup tapes of your database. Sure, you built a secure system, encrypted the most sensitive assets, and built a firewall around the database servers. But the thief took the easy approach: He took the backup tapes, ostensibly to restore your database on a different server, start the database on it, and then browse the data at his leisure. Protecting the database data from such theft is not just good practice; it's a requirement for compliance with most laws, regulations, and guidelines. How can you protect your database from this vulnerability?

- Encrypt the sensitive data and store encryption key in a separate location called wallet.
- Without the keys stolen data is worthless.
- Define a column as encrypted.
- When user insert the data, the database transparently encrypts it and stores in the column, similarly when users select the column the database automatically decrypts it.
- Backups and archive logs are also in encrypted format.
- If the data on the disk is stolen, it can't be retrieved without master key, which is in the wallet not part of the stolen data.
- Even if the wallet is stolen, the master key can't be retrieved from it without the wallet password.

How does it work :
1. Specify wallet location in sqlnet.ora D:\oracle\product\10.2.0\admin\rock and place following lines:
ENCRYPTION_WALLET_LOCATION =(SOURCE= (METHOD=file) (METHOD_DATA= (DIRECTORY=D:\oracle\product\10.2.0\admin\rock)))

2. Create the wallet
Now you must create the wallet and set the password to access it. To do this, issue the following as a SYS user with the ALTER SYSTEM privilege:
SQL> alter system set encryption key authenticated by "soni";
System altered.

This command
-Creates the wallet in the location specified in Step 1
-Sets the password of the wallet as "soni"
-Opens the wallet for TDE to store and retrieve the master key


- Restart the database

Every time you open the database, you'll have to open the wallet using the same password as follows:
SQL> alter system set encryption wallet open authenticated by "soni";
System altered.

You can close the wallet like this:
alter system set encryption wallet close;

The wallet must be open for TDE to work. If the wallet is closed, you can access all nonencrypted columns, but not encrypted columns.

On a regular schema, suppose you have a table of account holders as follows:

SQL> conn hr/hr@rock
Connected.


SQL>
create table accounts
(
ACC_NO NUMBER(2),
ACC_NAME VARCHAR2(30),
SSN VARCHAR2(9)
)
/

Currently, the table has all data in clear text. You want to convert the column SSN, which holds the Social Security Number, to be stored as encrypted. You can issue

alter table accounts modify (ssn encrypt);


SQL> desc accounts;

Name Null? Type

-------------------------- -------- ----------------------------
ACC_NO NUMBER(2)
ACC_NAME VARCHAR2(30)
SSN VARCHAR2(9) ENCRYPT

This statement does two things:
-It creates an encryption key for the table. If you change another column in the same table to use the encrypted format, the same table key will be used.
-It converts all values in the column to encrypted format.


Using Data Pump with TDE
By default, if you use the Data Pump export utility (EXPDP) to export data from a table with encrypted columns, the data in the resulting dump file will be in clear text, even the encrypted column data. The following command exports the ACCOUNTS table—with its encrypted columns—and returns a warning:

SQL> conn sys/rock@rock as sysdba
Connected.
SQL>

SQL> create or replace directory expdp as 'D:\expdp_dir';
Directory created.
SQL>

SQL> grant read, write on directory expdp to hr;
Grant succeeded.
SQL>

H:\>expdp hr/hr@rock directory=expdp tables=accounts
Export: Release 10.2.0.1.0 - Production on Wednesday, 20 May, 2009 9:27:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@rock directory=expdp tables=accountsEstimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE. . exported "HR"."ACCOUNTS" 5.585 KB 1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is: D:\EXPDP_DIR\EXPDAT.DMP
Job "HR"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 09:27:48
H:\>

This is just a warning, not an error; the rows will still be exported.
To protect your encrypted column data in the data pump dump files, you can password-protect your dump file when exporting the table. This password, specified as an ENCRYPTION_PASSWORD parameter in the EXPDP command, applies to this export process only; this is not the password of the wallet.


SQL> grant read, write on directory expdp to soni;
Grant succeeded.


H:\>expdp hr/hr@rock directory=expdp encryption_password=soni tables=accounts

H:\>impdp hr/hr@rock directory=expdp encryption_password=soni tables=accounts


-----------------------------------------------------------------------------------------------
for more details check the link below :

http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html