ENCRYPTION_PASSWORD

Default: There is no default; the value is user-provided.

Purpose

Specifies a password for encrypting encrypted column data, metadata, or table data in the export dumpfile. This prevents unauthorized access to an encrypted dump file set.

Note:

Data Pump encryption functionality changed as of Oracle Database 11g release 1 (11.1). Before release 11.1, the ENCRYPTION_PASSWORD parameter applied only to encrypted columns. However, as of release 11.1, the new ENCRYPTION parameter provides options for encrypting other types of data. This means that if you now specify ENCRYPTION_PASSWORD without also specifying ENCRYPTION and a specific option, then all data written to the dump file will be encrypted (equivalent to specifying ENCRYPTION=ALL). If you want to re-encrypt only encrypted columns, then you must now specify ENCRYPTION=ENCRYPTED_COLUMNS_ONLY in addition to ENCRYPTION_PASSWORD.

Syntax and Description

ENCRYPTION_PASSWORD = password

The password value that is supplied specifies a key for re-encrypting encrypted table columns, metadata, or table data so that they are not written as clear text in the dump file set. If the export operation involves encrypted table columns, but an encryption password is not supplied, then the encrypted columns are written to the dump file set as clear text and a warning is issued.

The password that you enter is echoed to the screen. If you do not want the password shown on the screen as you enter it, then use the ENCRYPTION_PWD_PROMPT parameter.

The maximum length allowed for an encryption password depends on the option specified on the ENCRYPTION parameter. If ENCRYPTION=ENCRYPTED_COLUMNS_ONLY is specified, then the maximum length of the encryption password is 30 bytes. If the ENCRYPTION parameter is specified as ALL, DATA_ONLY, or METADATA_ONLY, or if the default is used, then the maximum length of the encryption password is 128 bytes.

For export operations, this parameter is required if the ENCRYPTION_MODE parameter is set to either PASSWORD or DUAL.

Note:

There is no connection or dependency between the key specified with the Data Pump ENCRYPTION_PASSWORD parameter and the key specified with the ENCRYPT keyword when the table with encrypted columns was initially created. For example, suppose a table is created as follows, with an encrypted column whose key is xyz:

CREATE TABLE emp (col1 VARCHAR2(256) ENCRYPT IDENTIFIED BY "xyz");

When you export the emp table, you can supply any arbitrary value for ENCRYPTION_PASSWORD. It does not have to be xyz.

Restrictions

  • This parameter is valid only in the Enterprise Edition of Oracle Database 11g or later.

  • The ENCRYPTION_PASSWORD parameter is required for the transport of encrypted tablespaces and tablespaces containing tables with encrypted columns in a full transportable export.

  • Data Pump encryption features require that the Oracle Advanced Security option be enabled. See Oracle Database Licensing Information for information about licensing requirements for the Oracle Advanced Security option.

  • If ENCRYPTION_PASSWORD is specified but ENCRYPTION_MODE is not specified, then it is not necessary to have Oracle Advanced Security Transparent Data Encryption enabled since ENCRYPTION_MODE will default to PASSWORD.

  • The ENCRYPTION_PASSWORD parameter is not valid if the requested encryption mode is TRANSPARENT.

  • To use the ENCRYPTION_PASSWORD parameter if ENCRYPTION_MODE is set to DUAL, you must have Oracle Advanced Security Transparent Data Encryption (TDE) enabled. See Oracle Database Advanced Security Guide for more information about TDE.

  • For network exports, the ENCRYPTION_PASSWORD parameter in conjunction with ENCRYPTION=ENCRYPTED_COLUMNS_ONLY is not supported with user-defined external tables that have encrypted columns. The table will be skipped and an error message will be displayed, but the job will continue.

  • Encryption attributes for all columns must match between the exported table definition and the target table. For example, suppose you have a table, EMP, and one of its columns is named EMPNO. Both of the following situations would result in an error because the encryption attribute for the EMP column in the source table would not match the encryption attribute for the EMP column in the target table:

    • The EMP table is exported with the EMPNO column being encrypted, but before importing the table you remove the encryption attribute from the EMPNO column.

    • The EMP table is exported without the EMPNO column being encrypted, but before importing the table you enable encryption on the EMPNO column.

Example

In the following example, an encryption password, 123456, is assigned to the dump file, dpcd2be1.dmp.

> expdp hr TABLES=employee_s_encrypt DIRECTORY=dpump_dir1
DUMPFILE=dpcd2be1.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY 
ENCRYPTION_PASSWORD=123456

Encrypted columns in the employee_s_encrypt table will not be written as clear text in the dpcd2be1.dmp dump file. Note that to subsequently import the dpcd2be1.dmp file created by this example, you will need to supply the same encryption password.