Transparent Data Encryption

Encrypt the sensitive data in the database using transparent data encrption , when an insert happens on column which is protected, the database transparently encrypts it and stores it in the column and similarly the database decrypts when selected

Specify Wallet location in sqlnet.ora

ENCRYPTION_WALLET_LOCATION =

(SOURCE=

(METHOD=file)

(METHOD_DATA=

(DIRECTORY=G:\oracle11g\wallet)))

Create the wallet and set the password to access it.

alter system set encryption key authenticated by "wallet";

Open the wallet must be opened explicitly for operation, Whenever database is opened the wallet also need to be opened using the same password

alter system set encryption wallet open authenticated by "wallet";

Close the wallet using

alter system set encryption wallet close;

Use the below commands to encrypt sal column on emp table

alter table emp modify(sal encrypt);

For indexing a column which has been encrypted, we need to remove salt from the column.

SQL> create index idx_emp on emp(sal);

create index idx_emp on emp(sal)

*ERROR at line 1:ORA-28338: cannot encrypt indexed column(s) with salt

SQL> alter table emp modify sal encrypt no salt;
Table altered.

SQL> create index idx_emp on emp(sal);
Index created.

To decrypt the column Use

SQL> alter table emp modify(sal decrypt);
Table altered.

Exporting Encrypted data

To export data from emp with encrypted sal column, the data in the resulting dump file will be in clear text even the encrypted column data with exception in the logfile

>expdp scott/tiger dumpfile=scott.dmp logfile=scott.log directory=dumpdir
Export: Release 11.1.0.6.0 - Production on Tuesday, 03 June, 2008 21:55:27
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** dumpfile=scott.dmp logfile=scott.log directory=dumpdirEstimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 192 KBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA. . exported "SCOTT"."DEPT" 5.937 KB 4 rows. . exported "SCOTT"."EMP" 8.609 KB 15 rows. . exported "SCOTT"."SALGRADE" 5.867 KB 5 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsORA-39173: Encrypted data has been stored unencrypted in dump file set.Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: G:\ORACLE11G\BACKUP\SCOTT.DMPJob "SCOTT"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 21:56:53

Use ENCRPTION_PASSWORD parameter in expdp to export with the data encryption and while import the same password needs to be provided

>expdp scott/tiger dumpfile=scott.dmp logfile=scott.log directory=dumpdir encryption_password=mywallet
Export: Release 11.1.0.6.0 - Production on Tuesday, 03 June, 2008 22:10:57
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** dumpfile=scott.dmp logfile=scott.log directory=dumpdir encryption_password=********Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 192 KBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA. . exported "SCOTT"."DEPT" 5.945 KB 4 rows. . exported "SCOTT"."EMP" 8.617 KB 15 rows. . exported "SCOTT"."SALGRADE" 5.875 KB 5 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsMaster table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: G:\ORACLE11G\BACKUP\SCOTT.DMPJob "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:12:09