Encryption in Oracle 11g - Oracle 11g

In addition to the COMPRESSION parameter,the encryption optiontremendously enhances the Data Pump tool,particularly in the face of today’s ever-changing compliance regulations.Because of an ever-increasing theft of sensitive data, more companies are required to encrypt data in motion and data at rest.

Oracle Database 11g Data Pump complies with regulatory requirements by providing the ENCRYPTION parameter to specify varying levels of encryption algorithms to satisfy data-at-rest requirements. In addition to the ENCRYPTION parameter, ENCRYPTION_ PASSWORD and ENCRYPTION_ALGORITHM parameters are available to further secure your data.

In the coming section, we will demonstrate how these parameters can be effectively implemented to further secure your export dump files.

Specifying Encryption

The ENCRYPTION parameter has the following options:

encryption = {all | data_only | encrypted_columns_only | metadata_only | none}

The default value for the ENCRYPTION parameter depends upon thecombination of encryption related parameters that are used.To enableencryption, you must specify either the ENCRYPTION orENCRYPTION_PASSWORD parameter, or both.

The only option that may need explanation is the ENCRYPTED_COLUMNS_ONLY value, which specifies that only encrypted columns are written to the dump file set in encrypted format.

The ENCRYPTION parameter needs to be coupled with the ENCRYPTION_ALGORITHM parameter.The default value for the ENCRYPTION ALGORITHM parameter is AES128-bit encryption.

The ENCRYPTION_ALGORITHM parameter specifies which cryptographicalgorithm should be used to perform the encryption.Oracle supports three algorithms:

  • AES128
  • AES192
  • AES256

The ENCRYPTION_ALGORITHM parameter must be used in conjunction with either the ENCRYPTION or ENCRYPTION_PASSWORD parameter.

Choosing an Encryption Mode

The encryption mode specifies the type of security to apply when encryption and decryption operations are performed. There are three ENCRYPTION_MODE options:

  • dual
  • password
  • transparent

Using the transparent encryption mode is equivalent to performing a TDE-enabled export.If Oracle Wallet is open,no intervention is required by the DBAs to perform a transparent encrypted export.

If the ENCRYPTION_PASSWORD parameter is specified and Oracle Wallet is open, Oracle uses the dual mode for encryption. If Oracle Wallet is not available on the target database, you can use the ENCRYPTION_PASSWORD parameter that was specified at the time of export to decrypt the data during import.

The following example shows how to perform a TDE-enabled Data Pump export:

expdp dumpfile=oratmp:tde.dmp tables=rodba.docs parallel=2 encryption_mode=transparent encryption=all userid="'/ as sysdba'"

Let’s look at the password encryption mode.This option indicates that the password has to be provided to perform an import.To be able to import a password-encrypted export dump,the password must be provided during the import operation.

The following Data Pump export uses the password ENCRYPTION_MODE with the default encryption algorithm of AES128-bit encryption:

expdp dumpfile=dbadir:encrypted.dmp schemas=rodba userid=rodba/oracle123 encryption_mode=password encryption_password=oracle123

Using password mode is by far the easiest way to start performing encrypted Data Pump exports. Let’s add a higher level of cryptography to the equation:

expdp dumpfile=dbadir:encrypted.dmp.compressed tables=rodba.docs userid=rodba/oracle123 encryption_mode=password encryption_password=oracle123 ENCRYPTION_ALGORITHM=aes256

By specifying the encryption algorithm of AES256, the highest level of encryption is set for the export dump file.The dual encryption mode can be used when you need to restore to an alternate server on which the Oracle Wallet file is not accessible.

The dual mode provides flexibility of using either TDE or the password option during the export/import operation.To perform dual Data Pump exports, you can invoke the expdp utility with the following parameters:

expdp dumpfile=oratmp:tde.dualdmp tables=rodba.docs parallel=2 encryption_mode=dual encryption=all userid="'/ as sysdba'" encryption_password=11g

Please be aware that you are required to specify a password when exporting in either dual or password mode.You can specify a password to encrypt column data, metadata, or table data in the export dump file if an encryption password was supplied exporting the data.

The ENCRYPTION_PASSWORD parameter has several restrictions. First, the ENCRYPTION_PASSWORD parameter is not valid for export dump files created with the transparent encryption mode.

In addition, the ENCRYPTION_PASSWORD parameter works only with Oracle Database 11g Enterprise Edition.Data Pump exports over the network of user-defined external tables with encrypted columns are not supported if theENCRYPTION_PASSWORD parameter is supplied with the ENCRYPTED_COLUMNS_ONLY parameter.Lastly,the encryption attributes for all columns must match between the exported table and the target table.

Let’s see how we can enforce a password to be supplied during an import operation.Here we take an export of the DOCUMENTS table using the ENCRYPTION_PASSWORD parameter:

expdp expdp dumpfile=dbadir: documents.dmp tables=documents
userid=sh/sh123456 encryption_password=oracle123
reuse_dumpfiles=y encryption_mode=password
Export: Release 11.1.0.6.0 - Production on Sunday, 23 September, 2007 18:17:50
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SH"."SYS_EXPORT_TABLE_01": dumpfile=dbadir:documents.dmp tables=documents
userid=sh/******** encryption_password=******** reuse_dumpfiles=y
encryption_mode=password
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SH"."DOCUMENTS" 5.929 KB 3 rows
Master table "SH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
***********************************************
Dump file set for SH.SYS_EXPORT_TABLE_01 is:
/home/oracle/documents.dmp
Job "SH"."SYS_EXPORT_TABLE_01" successfully completed at 18:19:03

After a successful export with the ENCRYPTION_PASSWORD parameter oforacle123,we confirm that an import cannot occur without specifying a password.The following example produces an ORA-39174 error since the password is not provided during the import operation:

impdp dumpfile=dbadir:documents. dmp tables=documents
userid=sh/sh123456
Import: Release 11.1.0.6.0 - Production on Monday, 03 September, 2007 7:12:35
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39174: Encryption password must be supplied.

As you can see next, if the correct password is supplied (the original password used to create the export dump), we are able to successfully import the documents table:

impdp dumpfile=dbadir:documents. dmp tables=documents
userid=sh/sh123456 encryption_password=oracle123
Import: Release 11.1.0.6.0 - Production on Sunday, 23 September, 2007 18:20:09
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SH"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SH"."SYS_IMPORT_TABLE_01": dumpfile=dbadir:documents.dmp tables=documents
userid=sh/******** encryption_password=********
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."DOCUMENTS" 5.929 KB 3 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SH"."SYS_IMPORT_TABLE_01" successfully completed at 18:20:21

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics