| Virtual Private Database [message #206670] |
Fri, 01 December 2006 01:02  |
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
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
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
is
where_clause varchar2(200);
begin
where_clause:='fisrt_name=sys_context(' 'USERENV' ',' 'SESSION_USER' ')';
return where_clause;
end;
2-Then i had added the policy function
begin
dbms_rls.add_policy(object_schema=>'HR',object_name=>'TEST',
policy_name=>'test_policy',function_schema=>'SYS',
policy_function=>'policy_func',sec_relevant_cols=>'SALARY');
end;
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  |
nmacdannald
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 |
orasupport
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 |
 |
Michel Cadot
Messages: 68641
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.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
