I need some help with Oracle's RLS feature. I have never used it before but did some research online about it, however i'm running into a problem.
Here are the steps that I have taken:
1- Create the function:
create or replace function
table_access_policy
(obj_schema varchar2, obj_name varchar2) return varchar2
is
v_project varchar2(2000);
begin
v_project:= '(select project from users where user_name = (select sys_context(''userenv'',''session_user'') from dual))';
return 'project = ' || v_project;
end;
2- Add the security policy
Begin
DBMS_RLS.ADD_POLICY ('db1', 'data', 'access_policy', 'admin', 'table_access_policy', 'SELECT' );
End;
3- Run a simple select query on the object
select * from db1.data
When I do step 3 I get the following error:
ORA-28113: policy predicate has error
28113. 00000 – "policy predicate has error"
*Cause: Policy function generates invalid predicate.
*Action: Review the trace file for detailed error information.
Error at Line: 5 Column: 14
I don't know what could be wrong with the security function. I successfully used it manually on the db1 schema as follows:
select * from data
where project = (select project from users where user_name = (select sys_context('userenv','session_user') from dual))
Any thoughts??
Best Answer
Because your policy function is on the admin schema then you should qualify the select from the users table with its schema:
In addition, you should check the trace file as it recommends as that will show you the exact SQL which is run as well as the real error message.