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 BY clause in a SQL statement. Oracle does not support this, and raises an ORA-00979: not a GROUP BY expression error. This happens because internally the expression in the SELECT list must be modified by Data Redaction, but this causes it to no longer be found when it comes time to process the GROUP BY clause (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 DISTINCT clause and ORDER BY clause 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 SELECT list must be modified by Data Redaction, but this causes it to no longer be found when it comes time to process the GROUP BY clause, leading to this unintended error message.

• An ORA-28094: SQL construct not supported by data redaction error is raised if a query involves a UNION of redacted columns and each branch of the UNION does not have the same redaction policy. Although Oracle Database does not support complex SQL that involves redacted columns, this error is not raised if the user who is executing a query with complex SQL has the EXEMPT REDACTION POLICY system privilege. This error is only raised for users who are not exempt from the redaction policy, and they cannot execute complex SQL that involves redacted columns, whereas users who are exempt from the policy are allowed to.

  To avoid the ORA-28094 error, ensure that the query has the following properties:

  • When a column in the UNION has a redaction policy, the corresponding column in each branch of the UNION must use a redaction policy with the same values for all of its properties:

    • Function type

    • Function parameters or REGEXP parameters

    • Policy expression

    • Enable flag

    It can be a different redaction policy, but all these properties must be the same.

  • In an inline view, a UNION cannot have a subquery.

  • In a CREATE VIEW definition or an inline view, there cannot be any SQL expression that involves a redacted column.

• An ORA-28093: operation on column %s is not supported by data redaction error is raised if a user issues a query that involves XML syntax and if the user does not have the EXEMPT REDACTION POLICY system privilege. The ORA-28093 is raised only for users who are not exempt from the redaction policy, and they cannot use XML syntax that involves redacted columns, whereas, user who are exempt from the redaction policy are allowed to.

• 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

• In a CREATE VIEW definition or an inline view, there cannot be any SQL expressions (such as concat, sum, trim, min, max, and so on) that involve a redacted column.

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 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.

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.

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_NAME and OBJECT_TYPE columns of the ALL_OBJECTS data dictionary view.

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. 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.

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 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 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.

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.

Oracle Data Pump export operations can affect objects that have Oracle Data Redaction policies.

• Oracle Data Pump Security Model for Oracle Data Redaction
  The DATAPUMP_EXP_FULL_DATABASE role includes the powerful EXEMPT REDACTION POLICY system privilege.
• 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.
• 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.
• 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.

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.