CODEWITHSUNDEEP
sqloracle-databasedata-maskingvpd
Venzie
Venzie

Reputation: 1

My question is how to create a VPD in Oracle with SQL that will also mask data

I am trying to create a VPD in Oracle using SQL statements. The purpose of this problem is so an employee could ONLY view records for employees in the same department while masking their coworker's salaries as NULL.

The code for the table being used is as follows

create table Employee
(
    ID number primary key,
    DEPT varchar2(25),
    SALARY number(8,2),
    NAME varchar2(25)
);

I am unsure what the best way to go about doing this would be.... would it be to create a package and use an application context. I believe getting the table to only display those in same "DEPT" I understand but unsure how to mask the data of those with same DEPT but different ID.

Upvotes: 0

Views: 267

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

Native RLS will get you close but not totally there. Using "sec_relevant_cols" will give you the option between

  • only seeing the rows that match your predicate, but all values are present
  • seeing all the rows, but masking values that do not match your predicate

whereas (if I'm reading correctly) you want to see only predicate matching rows AND mask out some values as well.

You could achieve this with a two-step method

  1. Your context contains two keys (say) DEPT and YOUR_ID

  2. The RLS policy is "where dept = sys_context(ctx,'DEPT')"

  3. You have a view EMP to which that policy is applied, being

     select 
   id, 
   dept, 
   name,
   case when id = sys_context(ctx,'YOUR_ID') then sal else null end sal
 from EMP_TABLE

Upvotes: 0

Related Questions