CODEWITHSUNDEEP
sqloracle-database
AFJ
AFJ

Reputation: 159

Predicate Error with Oracle VPD/RLS

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??

Upvotes: 1

Views: 6266

Answers (2)

John Doyle
John Doyle

Reputation: 7803

Because your policy function is on the admin schema then you should qualify the select from the users table with its schema:

v_project:= '(select project from db1.users where user_name = (select sys_context(''userenv'',''session_user'') from dual))';

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.

Upvotes: 2

Luke Woodward
Luke Woodward

Reputation: 64969

I don't have a copy of Oracle Enterprise Edition to hand (VPD is Enterprise-edition only), so I can only guess at a few possible causes to the problem:

  • Is the table on which you wish to apply the policy db1.data?
  • Is the function table_access_policy under the admin schema?
  • Have all necessary users have been granted EXECUTE privileges on the table_access_policy function?

I don't think the issue is case-sensitivity of the arguments, because Oracle's own examples use lower-case object names such as hr.

Upvotes: 0

Related Questions