Information Security and Regulatory Compliance are among the top priorities of CEOs and CIOs today. PCI DSS (Payment Card Industry Data Security Standard) is one of the popular Information Security standards that, when implemented, protects cardholder data against theft and fraud. Complying with PCI DSS provides security and protection in handling of Payment Card data (at rest, in transit and while processing).
Oracle Data Redaction
There are two traditional approaches to meet the Redaction requirements:
| Traditional Approach | Challenge / Pitfall |
| The Developer writes and incorporates data masking code in the application | 1. Makes applications complex 2. Code manageability issues 3. Possible inconsistencies in presenting the data by different applications (depending on application-specific needs) |
| The DBAs create a View and create a public synonym that points to the view instead of providing direct query access to the base table. The DBAs can grant required privileges to the requested users. | The only option for changing data while using the view is to use triggers that update the tables behind the scenes. This makes application development complex and prone to errors. This is not a recommended approach and not suitable for production environments. |
Let’s look at how Oracle’s Data Redaction feature (part of Advanced Security option in Oracle 12c and 11.2.0.8) can be used to mask Payment Card data from being displayed based on different factors (User, Role, IP Address, Host, Application User, etc.). The actual data is not changed, it is just a way to mask the sensitive data when displayed.
How does Oracle Data Redaction work behind the scenes?
Oracle Data Redaction does on-the-fly Redaction of sensitive data in query results before display. It assists with consistent Redaction of data across the applications accessing the same data.
Factors to be considered when implementing Oracle Data Redaction:
- Data that should be redacted
- Different scenarios when the Redaction should take place
- Redaction method that is most suitable for the identified data (scenario)
A Redaction Policy is created and enforced with the above parameters based on the need.
Enabling Redaction Policy in a Database
The package used to create protection rules is called DBMS_REDACT. The package includes five procedures to manage the rules and an additional procedure to change the default value for full Redaction policy.
| Redaction Package Name | Description |
| DBMS_REDACT.ADD_POLICY | Adds Data Redaction policy to a table or view |
| DBMS_REDACT.ALTER_POLICY | Allows changes to existing policies |
| DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES | Changes the default returned value for full Redaction. You must restart the database to take effect. |
| DBMS_REDACT.ENABLE_POLICY | Enables Redaction policy |
| DBMS_REDACT.DISABLE_POLICY | Disables an existing policy |
| DBMS_REDACT.DROP_POLICY | Drops an existing policy |
Here are the different methods of column level Redaction.
- Full Redaction: The data or the value of the column is protected but the type of value returned depends on the column datatype. Returned value for numeric columns is 0 (zero) and returned value for character data is a space (blank). This setting can be changed at the database level.
- Partial Redaction: Only specific part of the data is changed. For example, the first 12 digits of the credit card number can be replaced by asterisks.
- Regular Expressions: Can use regular expressions to search for patterns of data that must be protected in a column.
- Random Redaction: Random values are returned which means the displayed data is different each time the query is executed.
- No Redaction: This method allows testing the inner workings of Redaction policies without disturbing the results of current running queries. This is commonly used during testing phase of Redaction policies that will finally find their way to production environments.
Data Redaction can be used for the following data types:
NUMBER, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, BLOB, CLOB, and NCLOB.
Adding Redaction Policy using SQL * Plus
For the purpose of our discussion, let’s try to mask data from CREDIT_LIMIT column.
Here is how CREDIT_LIMIT from CUSTOMERS table is displayed before implementing the Redaction policy:
Adding the new Redaction policy “CUSTOMER_TEST” on CUSTOMERS tables from OE schema:
Adding the “CREDIT_LIMIT” column with FULL Redaction method to the “CUSTOMER_TEST” policy:
Here is how the CREDIT_LIMIT column values are displayed after implementing the Redaction policy. Displays 0 (zero) as CREDIT_LIMIT is of Numeric data type:
Oracle dictionary views to obtain metadata about Redaction policies:
1. REDACTION_POLICIES
2. REDACTION_COLUMNS
3. REDACTION_VALUES_FOR_TYPE_FULL
We can use the following parameters of the expdp utility to export the Data Redaction metadata:
content=METADATA_ONLY
include=RADM_FPTM,RADM_POLICY
Implications of Enabling Redaction Policies
- Admin user can see the redacted data. Additional users can be added (to view the redacted data) by using the EXPRESSION parameter of the Redaction policy.
- Users should have the EXEMPT REDACTION POLICY system privilege to export the redacted objects. The following exception is thrown if the user (without EXEMPT REDACTION POLICY system privilege) tries to export the redacted data.
ORA-28081: Insufficient privileges – the command references a redacted object.
- Implementing the Oracle Redaction alone does not meet the PCI DSS standards. It is only one of the quick ways to mask the sensitive data from display.