Oracle Data Redaction can be used with other Oracle features, but some Oracle features may have restrictions with regard to Oracle Data Redaction.
15.1 Oracle Data Redaction General Usage Guidelines
It is important to understand usage guidelines for using Oracle Data Redaction.
Do not include any redacted columns in a SQL expression that is used in a
GROUP BYclause in a SQL statement. Oracle does not support this, and raises an
ORA-00979: not a GROUP BY expressionerror. This happens because internally the expression in the
SELECTlist must be modified by Data Redaction, but this causes it to no longer be found when it comes time to process the
GROUP BYclause (which is currently not updated by Data Redaction) leading to this unintended error message.
Do not include any redacted columns in a SQL expression that is used in both the
ORDER BYclause in a SQL statement. Oracle does not support this, and raises an error:
ORA-01791: not a SELECTed expression. This happens because internally the expression in the
SELECTlist must be modified by Data Redaction, but this causes it to no longer be found when it comes time to process the
GROUP BYclause, leading to this unintended error message.
ORA-28094: SQL construct not supported by data redactionerror is raised if a query involves a
UNIONof redacted columns and each branch of the
UNIONdoes not have the same redaction policy. To avoid the
ORA-28094error, ensure that the query has the following properties:
When a column in the
UNIONhas a redaction policy, the corresponding column in each branch of the
UNIONmust use a redaction policy with the same values for all of its properties:
Function parameters or
It can be a different redaction policy, but all these properties must be the same.
In an inline view, a
UNIONcannot have a subquery.
CREATE VIEWdefinition or an inline view, there cannot be any SQL expression that involves a redacted column.
Oracle does not support the following:
- Adding a Data Redaction policy on a virtual column
- Adding a Data Redaction policy on the base column of a virtual column
- Adding a virtual column whose base column has a Data Redaction policy defined on it
- Creating a functional index on a column with Data Redaction policy
CREATE VIEWdefinition or an inline view, there cannot be any SQL expressions (such as
max, and so on) that involve a redacted column.
15.2 Oracle Data Redaction and DML and DDL Operations
Oracle Data Redaction affects DML and DDL operations, especially for users who issue ad-hoc SQL against tables with redacted columns.
Note the following:
Oracle Data Redaction treats the
RETURNING INTOclause 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 INTOclause 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 INTOvalue, 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 objecterror unless you have the
EXEMPT REDACTION POLICYsystem privilege. Internally, Oracle Data Pump issues these kinds of operations, so you may also need to grant the
EXEMPT REDACTION POLICYsystem privilege to a user if they need to perform schema-level exports of tables that have redacted columns.
15.3 Oracle Data Redaction and Nested Functions, Inline Views, and the WHERE Clause
You can use Oracle Data Redaction with nested functions, inline views, and the
WHERE clause in
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_NUMBERfunction is redacted first, followed by
AVG, and then last the
Inline views are redacted outermost. For example, in
SELECT XYZ … AS SELECT A… AS SELECT B… AS SELECT C…,
SELECT XYZis redacted first, followed by
AS SELECT A, then
AS SELECT B, and so on.
AS SELECT Cis redacted last.
The WHERE clause is never redacted. Data Redaction redacts only data in the column
15.4 Oracle Data Redaction and Queries on Columns Protected by Data Redaction Policies
Queries that include the
ORDER BY clause on columns that are protected by Oracle Data Redaction policies may return 0 rows.
This happens because redaction preserves the semantics of the query. For example, a query such as
SELECT * table_name WHERE sensitive_column LIKE 'value'; would likely result in 0 rows returned, because the redacted value would not match the
'value' value entered in the
WHERE clause. To work around this issue, rewrite the query to include an inline view so that the semantic layer can find the column. For example, instead of the following query:
SELECT DISTINCT sensitive_column FROM table_name ORDER BY sensitive_column;
Write the query as follows:
SELECT sensitive_column FROM (SELECT DISTINCT sensitive_column FROM table_name ORDER BY sensitive_column);
15.5 Oracle Data Redaction and Database Links
Do not create Oracle Data Redaction policies on database views that reference database links.
You can find information about existing database links by querying the
DBA_DB_LINKS data dictionary view.
Oracle Database Administrator’s Guide for detailed information about database links
15.6 Oracle Data Redaction and Aggregate Functions
Aggregate functions can affect performance overhead on Oracle Data Redaction policies.
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.
15.7 Oracle Data Redaction and Object Types
You can use object types to model real-world entities such as customer accounts.
An object type is a user-defined type. 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_TYPE columns of the
ALL_OBJECTS data dictionary view.
15.8 Oracle Data Redaction and XML Generation
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.
15.9 Oracle Data Redaction and Editions
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.
15.10 Oracle Data Redaction in a Multitenant Environment
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. If you have the
SYSDBA privilege, then you can list all the PDBs in a CDB by running the
SHOW PDBS command.
As with the CDB root, you cannot create Data Redaction policies in an application root.
15.11 Oracle Data Redaction and Oracle Virtual Private Database
Oracle Data Redaction does not affect Oracle Virtual Private Database policies because the VPD inline view, which contains the VPD 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
NULLredacting. Many applications cannot use
NULLredacting, 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.
15.12 Oracle Data Redaction and Oracle Database Real Application Security
Oracle Data Redaction differs from Oracle Database Real Application Security because of how security is implemented for applications.
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.
Oracle Database Real Application Security Administrator's and Developer's Guide for information about how you can protect table columns with custom application privileges
15.13 Oracle Data Redaction and Oracle Database Vault
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.
15.14 Oracle Data Redaction and Oracle Data Pump
Oracle Data Pump export operations can affect objects that have Oracle Data Redaction policies.
15.14.1 Oracle Data Pump Security Model for Oracle Data Redaction
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
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.
15.14.2 Export of Objects That Have Oracle Data Redaction Policies Defined
You can export objects that have already had Oracle Data Redaction policies defined on them.
184.108.40.206 Finding Type Names Used by Oracle Data Pump
You must find the type names Oracle Data Pump uses before exporting objects that have Oracle Data Redaction policies defined on these objects.
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
SELECT OBJECT_PATH FROM DATABASE_EXPORT_OBJECTS WHERE OBJECT_PATH LIKE 'RADM_%';
Output similar to the following appears:
OBJECT_PATH ------------ RADM_FPTM RADM_POLICY
220.127.116.11 Exporting Only the Data Dictionary Metadata Related to Data Redaction Policies
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
EXPDPutility 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
18.104.22.168 Importing Objects Using the INCLUDE Parameter in IMPDP
You can import objects using Oracle Database Pump.
To import the objects, include these names in the
INCLUDEparameter in the
IMPDPutility command, based on the output from querying the
OBJECT_PATHcolumn in the
15.14.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
external_table. The underlying problem could be a permissions problem, for example:
ORA-28081: Insufficient privileges - the command references a redacted object.
Oracle Database Utilities for more information about using Data Pump Export.
15.14.4 Import of Data into Objects Protected by Oracle Data Redaction
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.
If you use the
CONTENT=METADATA_ONLYoption in the
IMPDPcommand, then the Data Redaction policies on the destination tables are dropped. You must recreate the Data Redaction policies.
If you use
IMPDPcommand, then the Data Redaction polices on the destination tables are not dropped.
Oracle Database Utilities for more information about using Data Pump Export
15.15 Oracle Data Redaction and Data Masking and Subsetting Pack
Oracle Enterprise Manager Data Masking and Subsetting Pack can be used to create a development or test copy of a production database.
To accomplish this, you can mask this data in bulk, and then put 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.
15.16 Oracle Data Redaction and JSON
is json constraints on table columns.
However, you cannot create an Oracle Data Redaction policy on a table column that has the
is json constraint. If you attempt to do so, an
ORA-28073 - The column column_name has an unsupported datatype error is raised. As a workaround solution, Oracle recommends that you create a relational view that uses the
JSON_TABLE row source operator on top of the JSON object, and then apply the Data Redaction policy to this view.
See Oracle Database SQL Language Reference for more information about