Transparent Data Encryption (TDE) – EXPDP and IMPDP – 10gR2 and 11gR2

It had been long time i had played with TDE, so thought of experimenting few things, as its the best way to learn 😉 .

As most of us know,TDE enables to encrypt individual table columns or a tablespace.Prior to 11g, only table column level was supported.A user with “create table” privilege can choose one or more columns in a table to be encrypted.The database automatically encrypts the data when being inserted and decrypts the data when the user select the column.The data in the datafiles is in encrypted format, so if someone tries to look directly into the datafile using some kind of OS tool the data would be encrypted preventing viewing the information/data.

I found few difference while using “EXPDP” and “IMPDP” on the tables having encrypted column using TDE on 10gR2 and 11gR2 which i thought of writing.

1. In 10gR2 we can’t use “sys as sysdba” to export the table having encrypted columns.

D:\>expdp dumpfile=DATA_PUMP_DIR:tde_test.dmp logfile=DATA_PUMP_DIR:tde_test.log tables=tde.cust encryption_password=anand123;

Export: Release 10.2.0.4.0 - Production on Wednesday, 09 March, 2011 0:45:22

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  sys/******** AS SYSDBA dumpfile=DATA_PUMP_DIR:tde_test.dmp logfile=DATA_PUMP_DIR:tde_test.log tables=tde.cust encryption_password=********
Estimate 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
ORA-31693: Table data object "TDE"."CUST" failed to load/unload and is being skipped due to error:
ORA-28336: cannot encrypt SYS owned objects
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  D:\ORACLE\PRODUCT\10.2.0\ADMIN\MATRIX\DPDUMP\TDE_TEST.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 00:46:20

A trace file is generated which contains the below details –

ORA-00604: error occurred at recursive SQL level 3
ORA-28336: cannot encrypt SYS owned objects
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_2413A8" ("CUST_NAME" VARCHAR2(20),"CARD_NO" VARCHAR2(20) ENCRYPT  ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950912 ) NOPARALLEL
Current SQL statement for this session:
CREATE TABLE "ET$008300050001" 
   (	"CUST_NAME", 
	"CARD_NO" ENCRYPT USING 'AES128' IDENTIFIED BY "anand123;" 
   ) ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_DATAPUMP  DEFAULT DIRECTORY DATA_PUMP_DIR ACCESS PARAMETERS (DEBUG =0 DATAPUMP INTERNAL TABLE  "TDE"."CUST"  JOB ( "SYS","SYS_EXPORT_TABLE_01",1) WORKERID 1 PARALLEL 1 VERSION COMPATIBLE) LOCATION ('bogus.dat') )  PARALLEL 1 REJECT LIMIT UNLIMITED
    AS SELECT /*+ PARALLEL(KU$,1) */ "CUST_NAME", "CARD_NO"
    FROM RELATIONAL("TDE"."CUST" ) KU$ 

While exporting the table, SYS user tries to create a global temporary table with encryption on the column and it fails, as SYS owned objects cannot be encrypted, which is a limitation of TDE.

Even in 11gR2, we can’t encrypt SYS owned objects.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> create table test (a number,b number encrypt no salt);
create table test (a number,b number encrypt no salt)
                            *
ERROR at line 1:
ORA-28336: cannot encrypt SYS owned objects

But, seems like from 11gR2 (as i haven’t tested on 11gR1, so might be in 11gR1 also) no more global temporary table is created and expdp completed successfully.

D:\scripts>expdp dumpfile=DATA_PUMP_DIR:ted_11gR2_test.dmp logfile=DATA_PUMP_DIR:ted_11gR2_test.log tables=tde.cust encryption_password=anand123

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 15:48:47 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  sys/******** AS SYSDBA dumpfile=DATA_PUMP_DIR:ted_11gR2_test.dmp logfile=DATA_PUMP_DIR:ted_11gR2_test.log tables=tde.cust encryption_password=********
Estimate 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 "TDE"."CUST"                                5.445 KB       1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  D:\ORACLE\APP\ADMIN\MATRIX\DPDUMP\TED_11GR2_TEST.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 15:49:09

2.The way encryption_password works, has changed from 11gR1.In 10gR2 you must provide the same password as given for encryption_password during export for successful import, but from 11gR1 things have changed a little.Lets check it out!!!

10gR2
========

D:\>expdp dumpfile=DATA_PUMP_DIR:tde_test.dmp logfile=DATA_PUMP_DIR:tde_test.log tables=tde.cust encryption_password=anand123;

Export: Release 10.2.0.4.0 - Production on Wednesday, 09 March, 2011 1:06:33

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=DATA_PUMP_DIR:tde_test.dmp logfile=DATA_PUMP_DIR:tde_test.log tables=tde.cust encryption_password=********
Estimate 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 "TDE"."CUST"                                5.289 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  D:\ORACLE\PRODUCT\10.2.0\ADMIN\MATRIX\DPDUMP\TDE_TEST.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 01:07:20

In the above export of TDE.CUST table i have used “anand123;” as the encryption password which re-encrypts encrypted table columns so that they are not written as clear text in the dump file set.Now during import the password that is specified must be the same one that was specified on the export operation.If not, you will end up with “ORA-39176: Encryption password is incorrect.”

D:\>impdp dumpfile=DATA_PUMP_DIR:tde_test.dmp logfile=DATA_PUMP_DIR:tde_test_imp.log tables=tde.cust encryption_password=anand

Import: Release 10.2.0.4.0 - Production on Wednesday, 09 March, 2011 1:08:00

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39176: Encryption password is incorrect.

And in case you don’t provide the encryption_password during import, you end up with “ORA-39174: Encryption password must be supplied.”

What happens if encryption_password parameter is not provided during impdp in 11g release.

11gR2
==========

D:\scripts>expdp dumpfile=DATA_PUMP_DIR:ted_11gR2_test.dmp logfile=DATA_PUMP_DIR:ted_11gR2_test.log tables=tde.cust encryption_password=anand123

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 15:48:47 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  sys/******** AS SYSDBA dumpfile=DATA_PUMP_DIR:ted_11gR2_test.dmp logfile=DATA_PUMP_DIR:ted_11gR2_test.log tables=tde.cust encryption_password=********
Estimate 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 "TDE"."CUST"                                5.445 KB       1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  D:\ORACLE\APP\ADMIN\MATRIX\DPDUMP\TED_11GR2_TEST.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 15:49:09

Without encryption_password parameter –

D:\scripts>impdp dumpfile=DATA_PUMP_DIR:ted_11gR2_test.dmp logfile=DATA_PUMP_DIR:ted_11gR2_test_imp.log full=y

Import: Release 11.2.0.1.0 - Production on Wed Mar 9 15:50:58 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  sys/******** AS SYSDBA dumpfile=DATA_PUMP_DIR:ted_11gR2_test.dmp logfile=DATA_PUMP_DIR:ted_11gR2_test_imp.log full=y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TDE"."CUST"                                5.445 KB       1 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 15:51:06

Interesting isn’t it.Is it a bug???Hmmmmm….

No, its not a bug.Its new feature of 11g release.From 11g release we have parameter “ENCRYPTION_MODE”

From Oracle Doc

ENCRYPTION_MODE

Default: The default mode depends on which other encryption-related parameters are used. If only the ENCRYPTION parameter is specified and the Oracle encryption wallet is open, then the default mode is TRANSPARENT. If only the ENCRYPTION parameter is specified and the wallet is closed, then an error is returned.

If the ENCRYPTION_PASSWORD parameter is specified and the wallet is open, then the default is DUAL. If the ENCRYPTION_PASSWORD parameter is specified and the wallet is closed, then the default is PASSWORD.

DUAL mode creates a dump file set that can later be imported either transparently or by specifying a password that was used when the dual-mode encrypted dump file set was created. When you later import the dump file set created in DUAL mode, you can use either the wallet or the password that was specified with the ENCRYPTION_PASSWORD parameter. DUAL mode is best suited for cases in which the dump file set will be imported on-site using the wallet, but which may also need to be imported offsite where the wallet is not available.

PASSWORD mode requires that you provide a password when creating encrypted dump file sets. You will need to provide the same password when you import the dump file set. PASSWORD mode requires that you also specify the ENCRYPTION_PASSWORD parameter. The PASSWORD mode is best suited for cases in which the dump file set will be imported into a different or remote database, but which must remain secure in transit.

TRANSPARENT mode allows an encrypted dump file set to be created without any intervention from a database administrator (DBA), provided the required wallet is available. Therefore, the ENCRYPTION_PASSWORD parameter is not required, and will in fact, cause an error if it is used in TRANSPARENT mode.

So, as the default for encryption_mode is “DUAL”, the import was successful without even encryption_password parameter.

Take export using encryption_mode=password

D:\scripts>
D:\scripts>expdp dumpfile=DATA_PUMP_DIR:tde_test_11gR2.dmp logfile=DATA_PUMP_DIR:tde_test_11gR2.log tables=tde.cust encryption_password=anand123 encryption_mode=password

Export: Release 11.2.0.2.0 - Production on Fri Mar 11 00:33:35 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  sys/******** AS SYSDBA dumpfile=DATA_PUMP_DIR:tde_test_11gR2.dmp logfile=DATA_PUMP_DIR:tde_test_11gR2.log tables=tde.cust encryption_password=******** encryption_mode=password
Estimate 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 "TDE"."CUST"                                5.445 KB       1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  D:\ORACLE\APP\ADMIN\MATRIX\DPDUMP\TDE_TEST_11GR2.DMP
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:33:59

Lets try to import without encryption_password parameter

D:\scripts>impdp dumpfile=DATA_PUMP_DIR:tde_test_11gR2.dmp logfile=DATA_PUMP_DIR:tde_test_11gR2_imp.log full=y

Import: Release 11.2.0.2.0 - Production on Fri Mar 11 00:34:42 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39174: Encryption password must be supplied.

So, we got the error :).To do a successful import now,correct encryption_password value is must.

D:\scripts>impdp dumpfile=DATA_PUMP_DIR:tde_test_11gR2.dmp logfile=DATA_PUMP_DIR:tde_test_11gR2_imp.log full=y encryption_password=anand123

Import: Release 11.2.0.2.0 - Production on Fri Mar 11 00:34:59 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  sys/******** AS SYSDBA dumpfile=DATA_PUMP_DIR:tde_test_11gR2.dmp logfile=DATA_PUMP_DIR:tde_test_11gR2_imp.log full=y encryption_password=********
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TDE"."CUST"                                5.445 KB       1 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 00:35:11

Enjoy 🙂

Reference :-
http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm#SUTIL843

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s