In a previous blog post, I provided a recipe for enforcing encryption for data-at-rest. However, it is important to remember that Transparent Data Encryption (TDE) protects your data from bad actors, who might have illegally gained access to the data files. Data exists unencrypted when loaded and accessed in the database. Malicious attacks can come from internal sources as well. When storing user confidential information, data stewards need to take greater responsibility in ensuring that data is only seen by staff who are authorized and require access to privileged information. That could mean creating tiered access and other protections like data redaction.
For example, an e-commerce website that handles credit card transactions, may require call centre staff to have access to the last four digits of a credit card number for verifying customers’ identity when support call are received. Or an insurance claims website that requires claimants to submit their health identity number. The application displays the partially or fully-masked ID, enough to suggest that the data has been captured, but insufficient for a passerby to steal a glance.
Oracle’s Data Redaction makes this possible, and with the latest release of Oracle Database 18c Express Edition, this technology is now acessible to developers for FREE! If you are new to Data Redaction, here’s a simple recipe to get started!
Create a simple table called
paymentinfo to store customers’ names and credit card information.
create table paymentinfo (
There are many sites and applications out there that help you generate mock data. Here are two examples:
Both sites help generate various types of data. For the purpose of demo, I generated a 100 rows of first names, last names and fictitious credit card numbers (any similarities is pure coincidence). Load the data.
insert into paymentinfo (first_name, last_name, ccard_number) values ('Brigham', 'Raftery', '3558886053942589');
Now query the data.
SQL> column first_name format a20
When working with Oracle Application Express (APEX) or any other programming languages, we would typically use database views to query data for display, e.g. Interactive Reports. We could attempt to “mask” the data with a query something like this:
create or replace force view vw_paymentinfo
Then query the data using the view.
SQL> select * from vw_paymentinfo where rownum <= 10;
Great! The credit card numbers are successfully masked. However, there are some key concerns with this approach. When implementing security for applications and system, I generally subscribe to a few “golden rules”.
- Avoid reinventing the wheel - the same is said with data encrypting methods. It’s always preferred that we apply commonly used, well-tested and accepted algorithms. There are many credit card options. Designing a comprehensive regular expression or string replacement scheme to cover all the available number formats is not easy. Why not rely on off-the-shelf solutions from companies that have invested significantly and has a lot of experience dealing with these issues, for handling these monumental and critical tasks.
- Separation of duties - if developers have no need to view the data of the applications they build, then perhaps they should not. Creating data security policies and enforcement should preferably be the responsibility of a separate person or team.
When Oracle Database 12c was released, the company restructured its Advanced Security Option offering. Secure database connections became a “feature” and a new technology, Data Redaction, was introduced and became part of the for-fee product. We, Oracle Database Developers, are very fortunate that this premium functionality is now available for free with the Oracle Database 18c XE.
With Data Redaction, I am relying on Oracle’s vast experiences implementing security and compliance with well-known, well-defined security requirements. It offers predefined redaction rules that not only mask credit card numbers, but national identification numbers, phone numbers, email addresses and even IP addresses.
Redaction policies are created at the “database level”, so that means they apply regardless of how you access the data. Running a query in SQL Developer, SQLcl or through an APEX application yields the exact same results.
As a best practice (separation of duties), Oracle encourages customers to use a different user/role for managing redaction policies. Let’s begin by creating a user
sec_admin for this task. We note the following requirements for creating such a user (from the docs):
- To create redaction policies, you must have the EXECUTE privilege on the DBMS_REDACT PL/SQL package.
- You do not need any privileges to access the underlying tables or views that will be protected by the policy.
Let’s do that. As
SYS user, execute the following commands:
create user sec_admin identified by "sec_admin";
Note that the user
sec_admin, would have rights to create and enforce data redaction policies, but will not have access to the data it is tasked to protect.
Next, create a data redaction policy for masking the data in column
Run the query again.
SQL> select * from paymentinfo where rownum <= 10;
Data Redaction policies are enforced regardless of how the data is accessed. In the previous examples, data was queried using SQLcl as the schema owner. Let’s try accessing the data using a simple Classic Report that runs the same query against the table
paymentinfo. Below is a short screen recording of data redaction in action.
Notice that the data is masked regardless of who accesses the application as the expresion condition is
In this next and final section, we will use Expressions to determine if the data should or should not be masked, depending on the user’s role in the application.
We begin by adding an Application Item the should be restricted and may not be set by the browser. Set the application state using a Post-Authentication procedure in the application’s current Authentication Scheme.
Next, update the
expression attribute for the
ccardnum_mask policy to use the Application Item’s session state.
Watch the screen recording of how the results of the Classic Report now differs between a User and an Administrator.
Even with this approach, a potential loophole remains. A malicious actor (developer) could alter the session state of this item or how it is set, and gain access to the data unprotected. Think about combining data redaction (using
SYS_XS_CONTEXT in the expression) with Oracle’s Real Application Security (RAS) and you’ll have a near air-tight solution. RAS incidentally, is also available to use in XE, and if you’d like to learn more, check out Dimitri Gielis’ blog post on enabling RAS in APEX.
To remove a policy, run the following procedure and specify the object’s schema, name and the policy name to drop.
Finally, through this article, I hope I have given you a preview of what the technology is capable of, and have sparked some interest There’s much to know and learn about Oracle Data Redaction, the full spectrum of what it offers and its limitations. You are highly encouraged to read the official documentation, test out various scenarios and consider using this technology, especially if you are a custodian of confidential data.