Re: Row Level RLS function: Can the table be used in the function?

On May 17, 2:22 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <Craiger..._at_gmail.com> a écrit dans le message de news: 1179351323.629809.64..._at_e65g2000hsc.googlegroups.com...
> |I need to write an RLS function to restrict access to a table based on
> | the average of one of it's fields. Consider an employee table, EMP, in
> | which there is a salary field.
> |
> | CREATE OR REPLACE FUNCTION rls_sal (
> | p_schema_name IN VARCHAR2,
> | p_object_name IN VARCHAR2
> | )
> | RETURN VARCHAR2
> | IS
> | l_return_val VARCHAR2(2000);
> | l_avg_sal NUMBER;
> | BEGIN
> | select avg(sal) into l_avg_sal from emp;
> | l_return_val := 'SAL > ' || l_avg_sal;
> | RETURN l_return_val;
> | END rls_sal;
> | /
> |
> | When I run "SELECT * FROM EMP" sqlplus "hangs", apparently forever. It
> | seems like I have sent the system into an infinite loop. I seems
> | plausible as the function could be firing the RLS policy recursively
> | from within the function? What is the best approach/workaround?
> |
> | I anxiously await your response..
> |
> | Thanks!!
> | Craig
> |
>
> The owner of the rls function must have the EXEMPT ACCESS POLICY
> privilege (only SYS by default).
>
> Regards
> Michel Cadot

Wow, thanks a ton! I was running all of this from the same schema. Your response put me on the right track. I was able to put the policy function in another schema, give that schema EXEMPT ACCESS POLICY, and all is well.

Thanks!!
Craig Received on Thu May 17 2007 - 11:26:27 CDT