batzman
asked on
Implementing column masking using Virtual Private database
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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;
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;
https://www.experts-exchange.com/articles/Database/Oracle/Flexible-VPD-structures-for-Oracle.html