Oracle Database Discussions

We have the need of limiting the number of rows retrieved from a table to some users.

We have tried to set this by adding a policy that limits the number of rows. Here is the code:


create or replace function
usu01_access_policy
(obj_schema varchar2, obj_name varchar2) return varchar2
is
d_predicate varchar2(2000);
begin
if user = 'PEPE01' then
d_predicate:=
'rownum < 11';
else
d_predicate:=
'1 = 1';
end if;
return d_predicate;
end;

This code is working ok when querying the table like this:

select * from usu01;

the number of rows retrieved is 10.

But when we define a 'where' clause, the query only applies the clause to the 10 rows retrieved before, getting only the rows of the first 10 with the 'where' condition=true.

Is there a way to limit the number of rows retrieved from a table, but applying first the 'where' clause to the whole table and later the 'rownum < limit' condition?

Thanks