Oracle data masking: hide information from users with an easy-to-use VPD

Contents

Sometimes you need to provide your users access to certain parts of data, but hide other parts from them. Here’s how to use Oracle VPD to take care of such data masking in Oracle Database and in Oracle APEX.

Recently, during one of my projects, I had to provide access to the database to specific users. The client wanted these users to see all the tables, but excluding some particular columns with such sensitive data as salary, income, invoices, billing or customers’ addresses, etc. – so-called “sensitive data.”

I decided to use Oracle VPD (Virtual Private Database) for that since – according to the Oracle documentation – if you are using Oracle Database XE 21c, VPD is free (if you use other releases, I advise you to verify this for yourself). In this article, I’ll show you how to use it for data masking, i.e. hiding data from your Oracle Database users, and – as a bonus – from Oracle APEX users.

Prerequisites

Here are the things you’ll need to recreate the example I show in this article:

Oracle XE Database 21c
HR schema – stores sensitive data
TEST schema – limited access user
HR.EMPLOYEES table with a SALARY column

Granting access to the column

Before I do anything else, I need to grant the user access to the table. In my case, the user is called TEST, so the command looks like this:

--connect as HR
GRANT SELECT ON EMPLOYEES TO TEST;

The result looks like this:

Restricting access to the column

However, as I’ve specified previously, I don’t want the user TEST to see data from the SALARY column. How to do that? You need to follow a few simple steps.

1. Create a function to handle your VPD policies.

The first thing is creating a function to handle the VPD policies. It must be created on a schema that the user TEST cannot access (I use ADMIN for Oracle Cloud).

--connect as ADMIN (as I'm using Oracle Cloud)
create or replace function f_vpd_column_masking(
   p_schema varchar2,
   p_obj    varchar2
) return varchar2 as
   l_database_user      varchar2(255) DEFAULT USER;
   l_predicate varchar2(100);
begin
   if l_database_user in ('TEST') then
      l_predicate := '1=2';
   end if;

   return l_predicate;
end f_vpd_column_masking;

2. Create a VPD policy to hide your data

Next, you need to create a VPD policy that’ll allow you to hide the data.

--execute as ADMIN
begin
   dbms_rls.add_policy
        (object_schema         => 'HR',
         object_name           => 'EMPLOYEES',
         policy_name           => 'EMPLOYEES_SELECT',
         function_schema       => 'ADMIN',
         policy_function       => 'f_vpd_column_masking',
         statement_types       => 'SELECT',
         sec_relevant_cols     => 'SALARY',  
         sec_relevant_cols_opt => dbms_rls.ALL_ROWS);
end;
/
--clean up
--EXEC DBMS_RLS.DROP_POLICY('HR','EMPLOYEES','EMPLOYEES_SELECT');

This will prevent users listed in my function from seeing data from the HR.EMPLOYEES.SALARY column.

Now, let’s query the EMPLOYEES table once again (connect as TEST).

Everything seems to work as intended: the TEST user can query the EMPLOYEES table but can’t see any salaries.

Control your VPD policies

It’s worth noting you can always preview what VPD policies you have using the simple query you see below.

--run as ADMIN (SYS)
select *
  from all_policies
 where object_owner = 'HR';

Exclude users from VPD policies

But what if, for some reason, you want the VPD policy not to affect particular users? You can use this query to achieve this.

--run as ADMIN or SYS
grant EXEMPT ACCESS POLICY to YOUR_USER;

Hiding data from specific Oracle APEX users

One of my colleagues asked me how to protect data from certain APEX users. It’s quite easy – you just need to update your VPD policy function.

--connect as ADMIN (as I'm using Oracle Cloud)
create or replace function f_vpd_column_masking(
   p_schema varchar2,
   p_obj    varchar2
) return varchar2 as
   l_database_user      varchar2(255) DEFAULT USER;
   l_predicate varchar2(100);
begin
   if l_database_user in ('TEST') then
      l_predicate := '1=2';
   end if;

/* added to protect fromm specific APEX users 
If your logged APEX user is SANDBOX_LTD he will see NULL's in salary column 
*/
   if SYS_CONTEXT('APEX$SESSION', 'APP_USER') = 'SANDBOX_LTD' then
      l_predicate := '1=2';
   end if;
/*Modify it however you want, e.g. exclude only users not having certain authorization_scheme or role.*/

   return l_predicate;
end f_vpd_column_masking;

Now, APEX user SANDBOX can see all data in the EMPLOYEES table, but user SANDBOX_LTD can only see NULLs instead of values from the salary column.

Show sensitive data to APEX users having specified APEX roles

To show sensitive data to APEX users with specific roles, you’ll need to create a role – for example, SENSITIVE_DATA – and then assign it only to some users.

Then, you’ll have to upgrade your code for the VPD policy function.

--connect as ADMIN (as I'm using Oracle Cloud)
create or replace function f_vpd_column_masking(
   p_schema varchar2,
   p_obj    varchar2
) return varchar2 as
   l_database_user      varchar2(255) DEFAULT USER;
   l_apex_user APEX_WORKSPACE_APEX_USERS.user_name%TYPE;
   l_predicate varchar2(100);
   l_sensitive_data_access boolean :=false;
begin
    --get apex user
    l_apex_user := SYS_CONTEXT('APEX$SESSION', 'APP_USER');

 --if database user is SANDBOX_LTD or apex user is SANDBOX_LTD then VPD policy hides data
   if l_database_user in ('SANDBOX_LTD') or l_apex_user = 'SANDBOX_LTD' then
      l_predicate := '1=2';
   end if;

    --is user not has role SENSITIVE_DATA then data is hidden
      l_sensitive_data_access := APEX_ACL.HAS_USER_ROLE (
                    p_application_id  => v('APP_ID'),
                    p_user_name       => l_apex_user,
                    p_role_static_id  => 'SENSITIVE_DATA' );

    IF NOT l_sensitive_data_access then
      l_predicate := '1=2';
    END IF;


   return l_predicate;
end f_vpd_column_masking;

Conclusion

And that’s it. As you can see, hiding specific data from your Oracle DB or Oracle APEX users is pretty easy – you just need a few simple queries to get the job done. If you want to read more about VPD or implement more complicated policies, check this documentation on data access control from Oracle. If you have any questions, reach out to me at rgrzegorczyk@pretius.com. You can also check out my other articles on the Pretius blog: