Oracle Data Redaction Policy and Object Transfers with Oracle Data Pump

4.4 Oracle Data Redaction Policy and Object Transfers with Oracle Data Pump

Objects with Data Redaction policies can affect Data Pump operations.

4.4.1 Oracle Data Pump Security Model for Oracle Data Redaction

The DATAPUMP_EXP_FULL_DATABASE role includes the powerful EXEMPT REDACTION POLICY system privilege.

Remember that by default the DBA role is granted the DATAPUMP_EXP_FULL_DATABASE role as well as the DATAPUMP_IMP_FULL_DATABASE role.

This enables users who were granted these roles to be exempt from Data Redaction policies. This means that, when you export objects with Data Redaction policies defined on them, the actual data in the protected tables is copied to the Data Pump target system without being redacted. Users with these roles, including users who were granted the DBA role, are able to see the actual data in the target system.

However, by default, all of the Data Redaction policies associated with any tables and views in the Data Pump source system are also included in the export and import operation (along with the objects themselves) and applied to the objects in the target system, so the data is still redacted when users query the objects in the target system.

Related Topics

Exemption of Users from Oracle Data Redaction Policies

4.4.2 Export and Import of Objects That Have Oracle Data Redaction Policies Defined

You can export objects that have already had Oracle Data Redaction policies defined on them.

4.4.2.1 Finding Object Types Used by Oracle Data Pump

You must find the object types that Oracle Data Pump uses before exporting objects that have Oracle Data Redaction policies defined on them.

After you find these object types, you should use these object types as parameters for the INCLUDE directive to the EXPDP utility, to selectively export only metadata of these specific object types to the dump file.

To find the object types, query the DATABASE_EXPORT_OBJECTS view.

For example:

SELECT OBJECT_PATH
FROM DATABASE_EXPORT_OBJECTS
WHERE OBJECT_PATH LIKE 'RADM_%';

Output similar to the following appears:

OBJECT_PATH
------------
RADM_FPTM
RADM_POLICY
RADM_POLICY_EXPR

4.4.2.2 Exporting Only the Data Dictionary Metadata Related to Data Redaction Policies

You can export only the data dictionary metadata using Oracle Database Pump metadata filters.

This kind of Data Pump export could, for example, be used if you must use the same set of Data Redaction policies and settings across development, test, and production databases. Because the flag content=metadata_only is specified, the dump file does not contain any actual data.

To export only the data dictionary metadata related to data redaction policies, full redaction settings, and policy expressions, include RADM_POLICY, RADM_FPTM, and RADM_POLICY_EXPR in the EXPDP utility command similar to the following:
```
expdp system/password \
full=y \
COMPRESSION=NONE \
content=metadata_only \
INCLUDE=RADM_FPTM,RADM_POLICY,RADM_POLICY_EXPR \
directory=my_directory \
job_name=my_job_name \
dumpfile=my_data_redaction_policy_metadata.dmp
```

Related Topics

Oracle Database Utilities

4.4.2.3 Importing Only the Data Dictionary Metadata Using the INCLUDE Parameter in IMPDP

You can import only the data dictionary metadata using Oracle Database Pump metadata filters.

To import only the data dictionary metadata related to data redaction policies, full redaction settings, and policy expressions, include RADM_POLICY, RADM_FPTM, and RADM_POLICY_EXPR in the IMPDP utility command similar to the following:
```
impdp system/password \
full=y \
content=metadata_only \
INCLUDE=RADM_FPTM,RADM_POLICY,RADM_POLICY_EXPR \
directory=my_directory \
job_name=my_job_name \
dumpfile=my_data_redaction_policy_metadata.dmp
```

4.4.3 Export of Data Using the EXPDP Utility access_method Parameter

Oracle Data Pump can export data from a schema that contains an object that has a Data Redaction policy.

If you are using Oracle Data Pump to perform full database export operations using the Data Pump default settings (direct_path), and if you receive error messages that you do not understand, then use this section to repeat the operation in such a way as to better understand the error.

If you try to use the Oracle Data Pump Export (EXPDP) utility with the access_method parameter set to direct_path to export data from a schema that contains an object that has a Data Redaction policy defined on it, then the following error message may appear and the export operation fails:

ORA-31696: unable to export/import TABLE_DATA:"schema.table" using client specified DIRECT_PATH method

This problem only occurs when you perform a schema-level export as a user who was not granted the EXP_FULL_DATABASE role. It does not occur during a full database export, which requires the EXP_FULL_DATABASE role. The EXP_FULL_DATABASE role includes the EXEMPT REDACTION POLICY system privilege, which bypasses Data Redaction policies.

To find the underlying problem, try the EXPDP invocation again, but do not set the access_method parameter to direct_path. Instead, use either automatic or external_table. The underlying problem could be a permissions problem, for example:

ORA-28081: Insufficient privileges - the command references a redacted object.

Related Topics

Oracle Database Utilities

4.4.4 Import of Data into Objects Protected by Oracle Data Redaction

During import operations into Oracle Data Redaction-protected objects, be aware of issues that may occur.

Inadvertent Drop of Oracle Data Redaction Policies

During an import operation, be careful that you do not inadvertently drop data redaction policies that protect imported data.

Consider a scenario in which the source tables that were exported using the Oracle Data Pump Export (EXPDP) utility do not have Oracle Data Redaction polices. However, the destination tables to which the data is to be imported by using Oracle Data Pump Import (IMPDP) have Oracle Data Redaction policies.

During the Data Pump import operation, the status of the Data Redaction policies on the objects being imported depends on the CONTENT option of IMPDP command.

If you use the CONTENT=ALL or CONTENT=METADATA_ONLY option in the IMPDP command, then the Data Redaction policies on the destination tables are dropped. You must recreate the Data Redaction policies.
If you use CONTENT=DATA_ONLY in the IMPDP command, then the Data Redaction polices on the destination tables are not dropped.

Data Redaction Policy Not Being Imported

During an import operation, you could get an ORA-28069: A Data Redaction Policy Already Exists On This Object error.

Consider a scenario in which the source tables that were exported using the Oracle Data Pump Export (EXPDP) utility have Oracle Data Redaction policies. The destination tables to which the data is being imported by using Oracle Data Pump (IMPDP) also have Oracle Data Redaction policies.

During the Data Pump import operation, you could get the ORA-28069 error. To avoid this error, you must import only the data using the CONTENT=DATA_ONLY parameter. Alternatively, you can drop the redaction policies on the target objects and then import all the data.

Related Topics

Oracle Database Utilities