You can use Oracle Data Redaction with other Oracle features and products, such as Oracle Virtual Private Database or Oracle Enterprise Manager Data Masking and Subsetting Pack.
Topics:
Although you will use Oracle Data Redaction primarily for redacting the displayed results of application queries, you should understand of how it affects DML and DDL operations, especially if you have users who issue ad-hoc SQL against tables with redacted columns.
Note the following:
Oracle Data Redaction treats the RETURNING INTO clause of a DML statement as a query, even though the result is not displayed. The result that is sent to the buffer is what would have been displayed had the RETURNING INTO clause been run as an ordinary SQL query, rather than as part of a DML statement. If your application performs further processing on the buffer that contains the RETURNING INTO value, then consider changing the application because it may not expect to find a redacted value in the buffer.
If a redacted column appears as the source in a DML or DDL operation, then Oracle Data Redaction considers this as an attempt to circumvent the policy and prevents it with an ORA-28081: Insufficient privileges - the command references a redacted object error unless you have the EXEMPT REDACTION POLICY system privilege. Internally, Oracle Data Pump issues these kinds of operations, so you may also need to grant the EXEMPT REDACTION POLICY system privilege to a user if they need to perform schema-level exports of tables that have redacted columns.
You can use Oracle Data Redaction with nested functions, inline views, and the WHERE clause in SELECT statements.
Oracle Data Redaction policies work as follows:
Nested functions are redacted innermost. For example, in SELECT SUM(AVG(TO_NUMBER(((X))) FROM HR.EMPLOYEES WHERE ..., the TO_NUMBER function is redacted first, followed by AVG, and then last the SUM function.
Inline views are redacted outermost. For example, in SELECT XYZ … AS SELECT A… AS SELECT B… AS SELECT C…, SELECT XYZ is redacted first, followed by AS SELECT A, then AS SELECT B, and so on. AS SELECT C is redacted last.
The WHERE clause is never redacted. Data Redaction redacts only data in the column SELECT list.
Because Oracle Data Redaction dynamically modifies the value of each row in a column, certain SQL queries that use aggregate functions cannot take full advantage of database optimizations that presume the row values to be static.
In the case of SQL queries that call aggregate functions, it may be possible to notice performance overhead due to redaction.
An object type is a user-defined type that make it possible to model real-world entities such as customer accounts and purchase orders as objects in a database.
You cannot redact object types. This is because Database Redaction cannot handle all of the possible ways that object types can be configured, because they are user defined. You can find the type that an object uses by querying the OBJECT_NAME and OBJECT_TYPE columns of the ALL_OBJECTS data dictionary view.
You cannot use XML generation functions on columns that have Oracle Data Redaction policies defined on them.
Oracle XML DB Developer's Guide describes the kinds of SQL functions to which this restriction applies. This restriction applies irrespective of whether the Oracle Data Redaction policy has been enabled or disabled, or is active for the querying user.
You cannot redact editioned views.
In addition to not being able to redact editioned views, you cannot use a redacted column in the definition of any editioned view. You can find information about editions by querying the DBA_EDITIONS data dictionary view.
In a multitenant environment, Oracle Data Redaction policies apply only to the objects within the current pluggable database (PDB).
You cannot create a Data Redaction policy for a multitenant container database (CDB). This is because the objects for which you create Data Redaction policies typically reside in a PDB. You can find all the PDBs in a CDB by querying the DBA_PDBS data dictionary view.
Oracle Virtual Private Database policies are unaffected by Oracle Data Redaction because the Virtual Private Database inline view, which contains the Virtual Private Database predicate, acts on actual values.
Oracle Data Redaction differs from Oracle Virtual Private Database in the following ways:
Oracle Data Redaction provides more redacting features than Oracle Virtual Private Database, which only supports NULL redacting. Many applications cannot use NULL redacting, so Data Redaction is a good solution for these applications.
Oracle Virtual Private Database policies can be static, dynamic, and context sensitive, whereas Data Redaction policies only allow static and context-sensitive policy expressions.
Data Redaction permits only one policy to be defined on a table or view, whereas you can define multiple Virtual Private Database policies on an object.
Data Redaction is when application users try to access an object that is protected by a Data Redaction policy using a synonym, but (unlike Oracle Virtual Private Database) Data Redaction does not support the creation of policies directly on the synonyms themselves.
Oracle Data Redaction differs from Oracle Database Real Application Security in that Real Application Security provides a comprehensive authorization framework for application security.
Column security within Real Application Security is based on application privileges that are defined by applications using the Real Application Security framework.
See Also:
Oracle Database Fusion Security Guide for information about how you can protect table columns with custom application privileges
You can use Oracle Data Redaction in an Oracle Database Vault environment.
For example, if there is an Oracle Database Vault realm around an object, a user who does not belong to the authorized list of realm owners or participants cannot see the object data, regardless of whether the user was granted the EXEMPT REDACTION POLICY privilege. If the user attempts a DML or DDL statement on the data, error messages result.
When you use Oracle Data Redaction with Oracle Data Pump, you must consider the impact the DATAPUMP_EXP_FULL_DATABASE role has, the ramifications of exporting objects that contain Data Redaction policies, and exporting data using the EXPDP access_method parameter.
Topics:
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 DATAPUMP_IMP_FULL_DATABASE.
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.
You can export objects that have already had Oracle Data Redaction policies defined on them.
Topics:
If you want to use Oracle Data Pump to export objects that have Oracle Data Redaction policies defined on them, you first must find the type names used by Oracle Data Pump for the various data redaction data dictionary metadata types.
After you find these types, you should use these types as parameters for the INCLUDE directive to the IMPDP utility, to selectively export only metadata of these specific types to the dump file.
To find type names, 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
You can export only the data dictionary metadata that is related to data redaction policies and full redaction settings.
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 and full redaction settings, enter an EXPDP utility command similar to the following:
expdp system/password \
full=y \
COMPRESSION=NONE \
content=metadata_only \
INCLUDE=RADM_FPTM,RADM_POLICY\
directory=my_directory \
job_name=my_job_name \
dumpfile=my_data_redaction_policy_metadata.dmp
See Also:
Oracle Database Utilities for detailed information about the INCLUDE parameter of the EXPDP utility
Oracle Database Utilities for detailed information about metadata filters
You can use Oracle Data Pump to 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.
See Also:
Oracle Database Utilities for more information about using Data Pump Export.
If you want to use Oracle Data Pump to import data into Oracle Data Redaction-protected objects, then be careful about how you use Oracle Data Pump, so that you do not unintentionally drop the data redaction policies protecting those objects.
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.
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.
See Also:
Oracle Database Utilities for more information about using Data Pump Export
Oracle Enterprise Manager Data Masking and Subsetting Pack enables you to create a development or test copy of the production database, by taking the data in the production database, masking this data in bulk, and then putting the resulting masked data in the development or test copy.
You can still apply Data Redaction policies to the non-production database, in order to redact columns that contain data that was already masked by Oracle Enterprise Manager Data Masking and Subsetting Pack.
Remember that Oracle Enterprise Manager Data Masking and Subsetting Pack is used to mask data sets when you want to move the data to development and test environments. Data Redaction is mainly designed for redacting at runtime for production applications in a consistent fashion across multiple applications, without having to make application code changes.
See Also:
Oracle Data Masking and Subsetting User's Guide for more information about data masking and subsetting