Troubleshoot: data masking without changing the applications

April 1, 2025 No Comments

Due to a new privacy policy, one of my Clients was about to start modifying its applications so that some data would be hidden under specific conditions.

The information is recorded in databases distributed throughout the country and periodically consolidated in a centralized database, which is queried through a Web application.

They were considering alternatives that included periodically updating the columns to delete their contents, while working on reconfiguring the data replication routines to eventually stop transferring that particular data.

They were about to start working on it when luckily they asked me if there was a better way to achieve this goal, and were pleased when I told them that it could be accomplished without changing anything and in less than 30 minutes!

Let’s see how that magic is achieved thanks to column masking with Oracle VPD.

The problem

By means of a Web application the information of the centralized database can be consulted, but due to legal issues it is necessary to hide certain data for certain rows.

In order to comply with the Confidentiality Agreement, we will replace the real scenario by one in which we will take as a basis the following query, made to the SH schema tables, which comes as part of the Oracle examples.

What is needed is to hide the sales data: TIME_ID, QUANTITY_SOLD and AMOUNT_SOLD, but only if the product corresponds to the Golf category, which for our result is the third row.

Copy

SELECT prod_name 
  FROM sh.products 
 WHERE prod_category = 'Golf';

PROD_NAME
-------------------------------------
Right-Handed Graphite Shaft Iron Set
Lithium Electric Golf Caddy           <-----

The solution

Oracle Virtual Private Database is a feature that allows you to limit access at the row level and also at the column level.

When accessing a table configured with VPD, a dynamic WHERE is created to identify the rows to which restrictions should be applied or not, using a function that returns the condition to be evaluated.

So, the first step is to create this function, as we see below:

Copy

CREATE OR REPLACE FUNCTION sh.vpd_condition (
  object_schema IN VARCHAR2, 
  object_name   IN VARCHAR2)
RETURN VARCHAR2 AS
  condition VARCHAR2 (100);
BEGIN
  condition := 'prod_id NOT IN ( SELECT prod_id FROM sh.products WHERE prod_category = ''Golf'' )';
  RETURN (condition);
END vpd_condition;
/

In line 7 we can see how the condition is generated, which in a few words can be translated into: show the data without restrictions if the product code is not in the list of products of the Golf category.

The next step is to create the policy that associates the table with the function, using a call to DBMS_RLS.ADD_POLICY.

Copy

BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema         => 'SH',
    object_name           => 'SALES',
    policy_name           => 'HIDE_COLUMNS',
    function_schema       => 'SH',
    policy_function       => 'VPD_CONDITION',
    policy_type           => DBMS_RLS.SHARED_STATIC,
    sec_relevant_cols     => 'QUANTITY_SOLD,AMOUNT_SOLD,TIME_ID',
    sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/

In line 9 we see how the columns we want to hide are listed.

There is nothing more to do, we have all set up, so let’s proceed to repeat the query to see what happens.

Excellent! Now it is no longer possible to see the data of the restricted columns, but only for the rows that have a product of the Golf category, for all other cases they are fully visible.

With this result we closed the case and my Customer was fully satisfied: in a few minutes his problem was solved, and he did not have to make any changes to his applications!

To complement the above, I recommend you to read the note: