Using encryption with data pump

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.

Getting ready

All steps will be performed on the database HACKDB.

How to do it...

  1. Create a directory /security/datapump for dumps and change its ownership to the user oracle:
    mkdir –p /backup/datapump
chown oracle:oinstall /backup/datapump
  2. Connect as the user 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.
  3. Export the schema 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~]
  4. Export the 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~]
  5. Export the 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~]
  6. Import data using the first dump 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~]
  7. Because this dump was made by using the encryption mode 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~]
  8. Dumps made using 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~]

How it works...

The encryption of dumps is controlled by the ENCRYPTION parameter. The possible values for this parameter are:

  • ENCRYPTED_COLUMNS_ONLY: The encrypted columns are exported to the dump file set in encrypted format
  • DATA_ONLY: All data is exported in encrypted format
  • METADATA_ONLY: All metadata is exported in encrypted format
  • ALL: All data and metadata is exported to the dump file set in encrypted format
  • NONE: Nothing is encrypted

The ENCRYPTION_MODE parameter controls the mode of encryption and may have the following values:

  • DUAL: This encrypts the dump using the wallet and the password provided by the 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.
  • TRANSPARENT: This encrypts the dump using the master key within the wallet. The source database must have the same master key.

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.

Note

While you import the data from a dump created in transparent mode, you have to ensure that your encryption wallet is opened at the destination database and contains the same encryption key.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.202.240