Table or full database dumps can also be a major source of information theft in case it is not protected. Oracle also provides encryption options for data pump exports using TDE or passwords. In this recipe we will generate dumps by exporting the HR
schema using different encryption options. Next, we will import each dump by remapping the tablespace USERS
to the tablespace ENCRYPTED_TBS
, and using related options.
/security/datapump
for dumps and change its ownership to the user oracle
:mkdir –p /backup/datapump chown oracle:oinstall /backup/datapump
system
and create an oracle directory mapped to the /backup/datapump
directory by executing the following statement:SQL> create directory encrypted_dumps as '/storage/datapump'; Directory created.
HR
by using the all
option and the encryption mode transparent
as follows:[oracle@nodeorcl1 ~]expdp dumpfile=encrypted_dumps:hr_encdump_transparent.dmp logfile=encrypted_dumps:hr_encdump_transparent.log schemas=HR encryption=all encryption_mode=transparent Export: Release 11.2.0.3.0 - Production on Thu Aug 30 16:19:29 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning and Oracle Label Security options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=encrypted_dumps:hr_encdump_transparent.dmp logfile=encrypted_dumps:hr_encdump_transparent.lo g schemas=HR encryption=all encryption_mode=transparent …………………………………………………………………………………………………………………………………………………… Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** [oracle@nodeorcl1~]
HR
schema by using the all
option for encryption and "ty745))+!>rto"
as the encryption password:[oracle@nodeorcl1~] expdp dumpfile=encrypted_dumps:hr_encpdump_password.dmp logfile=encrypted_dumps:hr_encdump_password.log schemas=HR encryption=all encryption_password="ty745OO+!>rto" Export: Release 11.2.0.3.0 - Production on Thu Aug 30 16:46:25 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning and Oracle Label Security options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=encrypted_dumps:hr_encpdump_password.dmp logfile=encrypted_dumps:hr_encdump_password.log schemas=HR encryption=all encryption_password=******** .......................................................... [oracle@nodeorcl1~]
HR
schema by using the all
option for encryption and use "ty745))+!>rto"
as the encryption password. For encryption mode use dual
mode and change the encryption algorithm to AES256:[oracle@nodeorcl1~] expdp dumpfile=encrypted_dumps:hr_encdump_dualmode.dmp logfile=encrypted_dumps:hr_encdump_dualmode.log schemas=HR encryption=all encryption_password="ty745OO+!>rto" encryption_mode=dual encryption_algorithm=AES256 Export: Release 11.2.0.3.0 - Production on Thu Aug 30 16:07:59 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning and Oracle Label Security options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=encrypted_dumps:hr_encdump_dualmode.dmp logfile=encrypted_dumps:hr_encdump_dualmode.log schemas=HR encryption=all encryption_password=******** encryption_mode=dual encryption_algorithm=AES256 ............................................................................................................................................................. Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** .................................................. [oracle@nodeorcl1~]
hr_encdump_transparent.dmp
, remap the tablespace users to encrypted_tbs
, and replace all tables:[oracle@nodeorcl1~] impdp dumpfile=encrypted_dumps:hr_encdump_transparent.dmp logfile=encrypted_dumps:import_hr_encdump_transparent.log remap_tablespace=USER:ENCRYPTED_TBS table_exists_action=replace Import: Release 11.2.0.3.0 - Production on Thu Aug 30 16:41:11 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning and Oracle Label Security options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded ....................................................................................................................................................................... [oracle@nodeorcl1~]
password
, it cannot be imported unless a decryption password is given:[oracle@nodeorcl1~] impdp dumpfile=encrypted_dumps:hr_encpdump_password.dmp logfile=encrypted_dumps:import_hr_encdump_password.log remap_tablespace=users:encrypt ed_tbs table_exists_action=replace encryption_password="ty745OO+!>rto" Import: Release 11.2.0.3.0 - Production on Thu Aug 30 17:06:10 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning and Oracle Label Security options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=encrypted_dumps:hr_encpdump_password.dmp logfile=encrypted_dumps:import_hr_encdump_password.log remap_tablespace=users:encrypted_tbs table_exists_action=replace encryption_password=******** ................................................................................................................................................ [oracle@nodeorcl1~]
dual
mode will first check for the encryption key within the wallet used for encrypting the exported dump (the wallet must be in open state). Import hr_encdump_dualmode.dmp
made with the encryption mode dual
, with the encryption wallet open:[oracle@nodeorcl1~] impdp dumpfile=encrypted_dumps:hr_encdump_dualmode.dmp logfile=encrypted_dumps:import_hr_encdump_dualmode.log remap_tablespace=users:encrypted_tbs table_exists_action=replace Import: Release 11.2.0.3.0 - Production on Thu Aug 30 17:10:39 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: system Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning and Oracle Label Security options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded. [oracle@nodeorcl1~]
The encryption of dumps is controlled by the ENCRYPTION
parameter. The possible values for this parameter are:
The ENCRYPTION_MODE
parameter controls the mode of encryption and may have the following values:
ENCRYPTION_PASSWORD
parameter. While importing the data, if you share the same master key as the source database then the password is not mandatory and the dump can be imported using master key decryption.This mode can be combined with ENCRYPTION_PASSWORD
. At import the password will be mandatory. In this way the data is encrypted using the password provided and the destination database might have another encryption master key.
18.116.15.161