Troubleshoot: data masking without changing the applications
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.
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:
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.
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:
| 414230.1 | How To Restrict The Access To Specific Columns Without Creating Views |