What is Oracle Data Redaction?
Oracle Data Redaction is a technology that allows you to mask (redact) data on the database. In this way, you can mask the data in your tables as you wish and prevent data breaches.
Oracle Data Redaction does not physically mask data like Transparent Database Encryption. Encryption is done according to the rules prepared based on the “DBMS_REDACT” package, and on a user specific basis. In other words, your data is stored on Buffer Cache as raw as before. When a data is requested, the DMBS_REDACT package transmits the data in the methods you specify.
Why we should use Oracle Data Redaction?
Your app users don’t always need to see your sensitive data. For example, you may want the personal information on a customer card not be visible.
The information may need to be changed partially or completely. You can use Oracle Data Redaction features in these processes.
Oracle Data Redaction Methods
There are 4 different methods to perform Data Redaction as you see below.
- Full redaction.
- Partial redaction.
- Regular expressions.
- Random redaction.
- * No redaction
Full Redaction
All data in the affected column is affected. The default value of Full Redaction is 0. If the data type in the column is NUMBER, it is replaced with 0, if the data type is character, it is replaced with a space.
Partial Redaction
You can edit a certain part of the existing data in the affected column. For example, 8 digits from the beginning or end of a 16-digit card number can be changed with “*”.
Regular Expression based Data Redaction
Available for character data types only. With Regular Expressions, you can optionally edit e-mail names with different characters.
Random Redaction
Regardless of the data type of the data in the column, it is the method in which data is randomly displayed every time it is called. When data is called with this method, Data Redaction changes the data to Random, so the data always looks unique.
There are some important details in the usage of the most used Random Redaction.
- When using the Char data type, the returned data length will always be the same as the column’s character count. For example, if the column is CHAR (25), the output result provides a string of 25 random characters.
- When using the VARCHAR2 data type, the returned data length is the number of characters in the column. For example, if a column of data type VARCHAR (25) contains 10 characters of data, the result of Data Redaction is 10 Random characters.
- In the number data types, a positive number is returned randomly.
- In date-time data types, random dates are displayed.
Differences between Oracle Data Redaction and Oracle Virtual Pricate Database
- While you can edit data more functionally with Oracle Data Redaction, Oracle Virtual Private Database returns the data as NULL. When the values return NULL, it can cause problems for applications. With Oracle Data Redaction, you will not encounter such problems.
- Oracle Virtual Private Database is sensitive to static and dynamic content. Data Redaction is only sensitive to static content.
- Data Redaction allows only one policy to be defined on a table or view, while Oracle Virtual Private Database allows you to define many policies.
How does Oracle Data Redaction affect SYS and SYSTEM or default schemas?
- SYS and SYSTEM users have the EXEMPT REDACTION POLICY privilege by default. This privilege means that the Oracle Data Redaction policy is invalid for these users. In other words, they can always read the data unless otherwise stated.
- By default, users created do not have the EXEMPT REDACTION POLICY privilege. For example, the HR user is directly affected by this situation.
- The EXEMPT REDACTION POLICY role from users such as SYS and SYSTEM should not be revoked.
Limitations
- GROUP BY statement can not be used in columns that have implemented Oracle Data Redaction. This is because the column in the Select list is changed by Data Redaction. When GROUP BY is used for column with Data Redaction, error ORA-00979 is returned.
- The same conditions apply in DISTINCT as in the GROUP BY statement. When DISTINCT is used for column with Data Redaction, ORA-01791 error is returned.
- Data Redaction cannot be applied to a column with JSON data type. The ORA-28073 error is returned.
- GRAPHIC, LONG VARGRAPHIC, VARGRAPHIC, and TIME data types are not supported under any circumstances.
- XML types, Oracle Spatial types, Oracle Media types are not supported.
You may want to read below article to implement in Oracle 19c.
Oracle Data Redaction 19c Example
