Oracle: How to Mask Sensitive Data with the Views — Quickly and Free

Sometimes you can suddenly identify data that must be urgently hidden from everybody in the system that is up and running with a variety of components, services, and processes.

For example, you have found out that the system has clients’ e-mails accessible in one or multiple applications, including mobile, desktop, web applications, and reporting services. Now, the development team faces the task: to urgently disable access to viewing these data. You are aware that the applications accessing these data make up an unknown set. And some applications/services work with the table directly, while others interact with it through procedures/batches.

How to solve the task quickly without breaking anything? Of course, there are great Oracle products (Data Redaction, Virtual Private Database) that solve this task in a comprehensive reliable manner and offer a variety of other benefits. However these are paid options, and you need specific skills to implement them. What I suggest is to take the simple way of solving the task fast and free of charge.

The gist of the solution is to create the View with the same name as that of the table, whose data we need to protect. At the same time, we will rename the source table so that no one could refer to it anymore.

The difficulty many people trip over is that, even though the View solves the data sampling task easily enough, as soon as we try to insert or modify data through the View, we will get the ORA-01733: virtual column not allowed here error. It means that there are fields we modify in the view. This issue is easy to bypass with triggers. I will use the following example to show how to do it.

For this experiment, let’s use the free Oracle Autonomous Database available for everybody. It’s convenient for experiments.

Data Masking Steps:

1. Create a test table and fill it with data
2. Create the view with data masking
3. Enable the data insert and update through the View
4. Rename the table and the view

Step 1. Create the table and fill it with data

Create a simple table named Cards that consists of two columns — the identifier and the varchar column that stores the PAN of the credit card.

Insert several rows:

Check, whether the data are accessible:

Good, we can see exactly what we just inserted.

Step 2. Create the view with data masking

Now let’s create the view that queries our table with data masking using the REGEXP_REPLACE regular expression function:

Check that the returned output is what we expect:

Now, this is what we need — the data are masked.

Step 3. Enable the data insert and update through the View

So far the View doesn’t allow to insert of new data. Have a look:

We get the error ORA-01733: virtual column not allowed here

That’s because the field is changed when we create the View.

We can make sure of it by checking USER_UPDATABLE_COLUMNS:

It’s not actually a problem since we can bypass this restriction with the simple trigger mechanism.

Let’s create the trigger that will be executed when attempting to make an insert in the View. In the trigger itself we will make actions directly with the source table:

Now let’s try to insert the value in our View:

There is no error. Let’s check if the row has been added.

Here it is, with card_id = 6

Now let’s check that the source table also has the data and that they are not masked:

Great!

Let’s repeat this magic with data update through the View.

Without creating the trigger Оracle will threaten us with the same error:

Yes, here it is:

ORA-01733: virtual column not allowed here

But we already know what to do and just create the trigger:

Let’s try to update the data once again:

No errors. Let’s check if the data were updated:

Now we have made sure that we can both read from the View and get the masked data, and insert and update data through the View.

Step 4. Rename the table and the View

Now what we have to do is to rename the View so that its name would be the same as that of the source table, and to give any other name to the table. We will also have to recreate the triggers, and in the end, recompile the invalidated objects. As we rename the table, all references (foreign keys) to it will retain.

Drop the triggers, and then create them again with the right references to the table:

Change the table name from cards to cards_tbl:

The objects that worked with this table have become invalid. We need to recreate the View so that all objects would become valid.

Change the View name from cards_v to cards:

Recreate the triggers:

Now we can recompile the objects that became invalid:

Let’s check if everything is up and running:

The code runs as expected.

Conclusion

In this article, we have handled how to replace the table with the view adding data masking. We have also learned how to bypass Oracle’s restriction of data modification through the View. This trick is helpful when we need to mask sensitive data without major fixes.

I’d love to hear your thoughts on the above in the comments and feel free to connect with me on LinkedIn.