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

<Craigers01_at_gmail.com> a écrit dans le message de news: 1179351323.629809.64080_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 Received on Thu May 17 2007 - 01:22:59 CDT