Hide your sensitive data from the specific database users or APEX users using Oracle VPD.

Recently, I had to give access to the database to specific users. They must see all tables, excluding particular columns with such data as salary, income, invoices, billing or customers' addresses, etc. All that is called "sensitive data."

I have used Oracle VPD (Virtual Private Database) for that. According to this Oracle documentation, if you are using Oracle XE 21c - VPD is free to use for you (check for other releases).

Added 26.03.2023

How to hide your data from specific Oracle APEX users.

Prerequisites

Oracle XE Database 21c

HR schema - stores sensitive data.

TEST schema - limited access user.

I have a table HR.EMPLOYEES with SALARY column ( it's somewhat sensitive data :) )

Now I'll grant access to this table to user TEST.

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

And the result is:

However, I don't want user TEST to see data from the SALARY column. That's easy.

Create a function to handle your VPD policies.

It must be created on a schema that 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;

Create a VPD policy to hide your 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 HR.EMPLOYEES.SALARY column.

Let's query the EMPLOYEES table once again (connect as TEST).

Now, TEST user can query EMPLOYEES table but can't see any salaries.

Control your VPD policies

You can always preview what vpd policies you have using a simple query.

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

Exclude users from VPD policies.

If, for some reason, you want VPD policy not to affect particular users, then use:

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

Hide your data from specific Oracle APEX users.

Update 26.03.2023

One of my colleagues asked me how to protect data from certain APEX users.

Here you go.

Just 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;

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

Show sensitive data to APEX users having specified APEX roles.

Create a role called e.g. "SENSITIVE_DATA' and assign only to some users.

Upgrade your code for 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;

If you want to read more about VPD or implement more complicated policies, check this documentation from Oracle.

Hope I helped :)

Rafal