CODEWITHSUNDEEP
sqloracle-database
AFJ
AFJ

Reputation: 159

Oracle RLS/VPD with for loop in policy function

This is a followup to an older question about Oracle's row level security feature originally discussed here

I needed to modify the because the original code would return more than one results if the user was attached to multiple projects. So now I need to have multiple where conditions (ie where project = project_1 or project = project_2) passed to the security policy for this to work. To do this I tried modifying the code by using a for loop and it is not working...

--create function
create or replace function
   table_access_policy
   (obj_schema varchar2, obj_name varchar2) return varchar2

is                

v_project_temp varchar2(9000);

begin              

v_project_temp:= 'declare v_project varchar2(9000);

begin
v_project:= ''project = '';

for c in (select admin.access_list.project from admin.access_list where upper(admin.access_list.user_id) = SYS_CONTEXT (''USERENV'', ''SESSION_USER'')) 

loop

v_project := v_project || c.project_sn || '' or project = '' ;

end loop;

v_project := rtrim(v_project, '' or project = '');

end;';

return v_project_temp;

end;

The function saves/runs without any errors, but the policy itself throws an error when it's called. Is there a better way to do this?

Upvotes: 1

Views: 388

Answers (1)

John Doyle
John Doyle

Reputation: 7803

Instead of putting the PL/SQL in a string you should run it and build up the v_project string to return. Such as:

--create function
create or replace function
   table_access_policy
   (obj_schema varchar2, obj_name varchar2) return varchar2    
is
  v_project varchar2(9000);
begin              
  v_project:= 'project = ';
  for c in (select admin.access_list.project from admin.access_list where upper(admin.access_list.user_id) = SYS_CONTEXT ('USERENV', 'SESSION_USER')) 
  loop
    v_project := v_project ||''''|| c.project_sn ||''''|||| ' or project = ' ;
  end loop;    
  v_project := rtrim(v_project, ' or project = ');    
  return v_project;    
end;

Ultimately the value that appears in v_project will go straight after a where in an SQL statement such as:

select * from data;

will become

select * from date where <v_project>;

So only something that follows a where should go in v_project.

Upvotes: 2

Related Questions