You can use VPD to restrict the number of rows updated or deleted. The process for doing so is convoluted.
When you apply a VPD policy to a delete, a statement such as:
delete t
Becomes:
delete (select * from (select * from t) where <VPD policy clause>);
Therefore a policy applying a "rownum < :x" check becomes:
delete (select *
from (select * from t)
where rownum <= 100);
ORA-01732: data manipulation operation not legal on this view
Oh dear! To get around this, first you must find the rows of interest using a subquery. Then join those back to the main query, like so:
delete (select *
from (select * from t)
where rowid in (select rowid from t where rownum <= 100));
100 rows deleted.The complete policy to apply this would be:
create table t (x not null) as
select rownum x
from dual
connect by level <= 500;
create or replace function restrict_deletes (
schema_var in varchar2,
table_var in varchar2
) return varchar2
is
where_clause varchar2 (100);
begin
where_clause := 'rowid in (select rowid from t where rownum <= 100)';
return where_clause;
end restrict_deletes;
/
begin
dbms_rls.add_policy (
object_schema => 'chris',
object_name => 't',
policy_name => 'delete_policy',
function_schema => 'chris',
policy_function => 'restrict_deletes',
statement_types => 'update, delete'
);
end;
/
select count(*) from t;
COUNT(*)
----------
500
delete t;
100 rows deleted.
select count(*) from t;
COUNT(*)
----------
400
delete t
where rownum <= 50;
50 rows deleted.
select count(*) from t;
COUNT(*)
----------
350There's a problem with this approach however. The subquery filtering is applied
before your where clause. Therefore this may mean no rows are removed!
In the case below we would expect the delete to remove 50 rows. The first 100 identified by
select rowid from t where rownum <= 100
have x values < 400 however. Therefore the delete does nothing!
select count(*) from t
where x > 400;
COUNT(*)
----------
100
delete t
where x > 400
and rownum <= 50;
0 rows deleted.
select count(*) from t
where x > 400;
COUNT(*)
----------
100If the where clause for your deletes/updates is fixed and known, you can include it in the restrict_deletes function. If not you'll need to figure out a process to generate the appropriate where clause in the (select rowid from t...) subquery you create in the function.