I have an ancient application environment called XAL.

It has own data dictionary, own programming language, own development and runtime environment (kernel). From its own data dictionary the kernel auto-creates tables and indexes in Oracle. From its own programming language the kernel auto-generates SQL that runtime is executed on the database.

The XAL data dictionary has the concept of temporary tables, but the kernel creates normal tables, just with a DATASET column that runtime is populated with a unique session identifier so each session has own "slice" of data in these "temporary" tables. (We've changed these tables to GTTs, but the XAL kernel still believes they are normal tables.)

So the developers of the XAL kernel (which we cannot change) has implemented clean-up code whenever a session ends. The clean-up code loops over the temporary tables and issue a "DELETE FROM {table} WHERE DATASET={session identifier}" to remove the session "slice" of data in the "temporary" tables.

This is acceptable. Since we changed the tables to GTTs, these deletes do no harm anyway.

But... We've hit a bug in this kernel (and it won't be fixed), so once in a while (maybe a couple times a year), the clean-up code mistakenly deletes from a real table (some random real table, seldom the same one) instead of the temporary table. We have had cases where suddenly we had no employees or all prices went missing :-(

We are monitoring v$sql for when it happens:

select *
from (
   select distinct
   parsing_schema_name,
   substr(sql_text,13,instr(sql_text,' ',13)-13) table_name
   from v$sql
   where executions > 0
   and sql_fulltext like 'DELETE FROM % WHERE DATASET=:255 AND ROWNUM<=5000'
) bad
join dba_tables t
   on t.owner = 'XAL_SUPERVISOR'
   and t.table_name = bad.table_name
   and t.temporary != 'Y'
/

The kernel clean-up code deletes 5000 rows at a time, committing and looping until SQL%ROWCOUNT is less than 5000. That way we can identify that the text above is SQL generated by that clean-up code and not SQL from somewhere else. We then check if this delete happened to a non-temporary table.

When the monitor discovers it has happened, we can use flashback to get the data back.


But is there instead a way to block these particular DELETE statements instead so data won't be deleted?

Can advanced query rewrite somehow be made to work for DELETE rather than just SELECT?

Can some FGAC block a particular sql_id? (I might be able to isolate the about 900 individual DELETE statements that can happen.)

Other ideas?


Thanks in advance
Kim Berg Hansen

create table t (id not null, dataset not null) as
  select rownum, 'dataset' from dual connect by level <= 10000;

create or replace function restrict_deletes ( 
  schema_var in varchar2,
  table_var  in varchar2
) return varchar2
is
  where_clause varchar2 (100);
begin

  if upper(sys_context('USERENV', 'CURRENT_SQL')) like 
       '%ROWNUM<=5000%' then
    where_clause := '1=0';
  else
    where_clause := '1=1';
  end if;
  
  return where_clause;
  
end restrict_deletes;
/
show errors

set serveroutput on

var v255 varchar2(10);
exec :v255 := 'dataset';

PRO Policy not applied, should delete 5,000 rows 
delete t WHERE DATASET=:v255 AND ROWNUM<=5000;

5,000 rows deleted.

begin
  dbms_rls.add_policy (
    object_schema    => 'chris',
    object_name      => 't',
    policy_name      => 'delete_policy',
    function_schema  => 'chris',
    policy_function  => 'restrict_deletes',
    statement_types  => 'delete'
   );
 end;
/

PRO Policy applied, delete 0 rows
delete t WHERE DATASET=:v255 AND ROWNUM<=5000;

0 rows deleted.

PRO "normal" deletes still allowed
delete t ;

5,000 rows deleted.