Home » RDBMS Server » Security » Virtual Private Database
Virtual Private Database [message #206670] Fri, 01 December 2006 01:02 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
I am testing Virtual Private database
i want to restrict access test table in hr schema
----------- -------------------- ----------
201 ABC 13000
202 XYZ 6000
203 USER3 6500
204 USER4 10000
205 USER5 12000

For Example when user abc query the test table
he can't see other users record

1-i have created a functtion in hr schema by using the following script
create or replace function policy_funct(owner varchar2,objname varchar2)
return varchar2
where_clause varchar2(200);
where_clause:='fisrt_name=sys_context(' 'USERENV' ',' 'SESSION_USER' ')';
return where_clause;

2-Then i had added the policy function

3-Then i connect as ABC user and try to execute the following query
select * from hr.test
ERROR at line 1:
ORA-28110: policy function or package HR.POLICY_FUNC has error

Policy Function is valid when i checked its status

Can anybody help me in this regard?
thanx in advance

Re: Virtual Private Database [message #223170 is a reply to message #206670] Wed, 07 March 2007 20:09 Go to previous messageGo to next message
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Create a view and use a synonym where the view does not include the columns that are restricted. Name the synonym so users do not know they are not seeing the entire table.
Re: Virtual Private Database [message #235124 is a reply to message #206670] Fri, 04 May 2007 02:30 Go to previous messageGo to next message
Messages: 11
Registered: May 2007
Junior Member
First check the typo in your funtion:
where_clause:='****fisrt_name*****=sys_context(' 'USERENV' ',' 'SESSION_USER' ')';

if its fine then...
try the following..

Grant EXECUTE to "policy_funct()" to user ABC and try.
Also need to have SELECT permission on the HR.TEST table

[Updated on: Fri, 04 May 2007 02:36]

Report message to a moderator

Re: Virtual Private Database [message #235126 is a reply to message #235124] Fri, 04 May 2007 02:33 Go to previous message
Michel Cadot
Messages: 68311
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't read the thread and don't know if your answer is accurate but I don't think he is waiting for an answer 6 months later.

Previous Topic: Scenario and a Questions
Next Topic: to GRANT Privilege
Goto Forum:

Current Time: Wed Nov 30 10:13:40 CST 2022