Implementing column masking using Virtual Private database

batzman
batzman used Ask the Experts™
on
I have a task that requires me to do the following (running Oracle 10.2.0.3)

Allow a limited number of users select privilige from a number of tables and mask the data in certain columns where they cannot see the data. I understand that this can be done using VPD.
I have never used this tool. Does anyone have an example of how I would implement something like this for certain users and give me some insight on how to maintain it as i add or remove users from this priviledge? It could be as many as 10 users and as many as 100 tables affected.

Is there another suggestion/example on how I would do this.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
There is an article here on EE that includes a full VPD setup DDL.

http://www.experts-exchange.com/articles/Database/Oracle/Flexible-VPD-structures-for-Oracle.html
VPD  basically consists of following things.

1) A function ( a PLSQL Function that retruns predicate).
2) A policy itself . (Ie attaching the above function to any table).
 So  "which user?" etc are handled in PLSQL FUnction itself.

Pls find attachement. A small example, to mask SAL column of SCOTT.EMP from everybody other than SCOTT.
VPD column masking example
EE-VPD-COLUMN.txt
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I understand your solution. Could you show me how I would do this say to disallow 3 users from seeing the data in certain columns. User a, b & C for example. I will accept your solution if you could explain how I would do that.

Author

Commented:
n/a
You have already accepted my solution!!!!.


 Please change the following lines in the uploaded function code:
 
 if user <> 'SCOTT' then
         v_pred:='1=2';
       else
        v_pred:='1=1';
        end if;
       return v_pred;
       
       
       TO
       
 
   if user = 'a' or user = 'b' or user ='c' then
           v_pred:='1=2';
         else
          v_pred:='1=1';
          end if;
       return v_pred;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial