4

Some records are getting removed from a table and we want to identify sql statements which are removing records from a table, so that we can check find the program which is causing the problem.

I have written the following but the sql 

create or replace 
trigger find_del_abc
before delete on abc 
for each row 
declare 
    temp_audsid integer; 
    temp_sql_id VARCHAR2(13); 
    temp_prev_sql_id   VARCHAR2(13);  

begin 

  If deleting then 

     select sql_id, prev_sql_id, audsid into temp_sql_id, temp_prev_sql_id, temp_audsid from v$session where audsid = SYS_CONTEXT('USERENV','sessionid');

     insert into delete_abc_session 
     select * from v$session where audsid = temp_audsid; 

     insert into my_sql
     select sql_id, sql_fulltext from v$sqlarea where sql_id in (temp_sql_id, temp_prev_sql_id); 

  End If; 


end;

But I don't see the 'delete from abc' sql in my_sql. Am I doing something wrong?

Is there any other way to capture the sql_id, prev_sql_id, audsid of the sql statement which invoked the trigger (inside the trigger block).

Thanks in advance for any help in this regard.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Sam
  • 41
  • 2
  • You could identify the user and more if you enabled auditing on that table. No code to write either. Another crazy thought, revoke DELETE privs on that table from everyone? – thatjeffsmith Jul 12 '19 at 19:16
  • you could also write that trigger to basically PREVENT the delete from happening in the first place, raise an exception and log the details of the session into your table - then wait to see who comes to you complaining – thatjeffsmith Jul 12 '19 at 19:17
  • and remove `If deleting then` which's redundant for a delete only trigger. – Barbaros Özhan Jul 12 '19 at 19:30
  • As a side note, Oracle has a special product called `Audit Vault` to trace the DML statements. – Barbaros Özhan Jul 12 '19 at 19:34

1 Answers1

3

Is there any other way to capture the sql_id, prev_sql_id, audsid of the sql statement which invoked the trigger (inside the trigger block).

Not easily.

Well, AUDSID is easy: use the expression SYS_CONTEXT('USERENV','SESSIONID')

Getting the SQL_ID, as far as I know, is impossible, but it is possible, though difficult, to get the SQL text.

To do so, you would need to create a Fine-Grained Auditing (FGA) policy on your table. Inside an FGA policy handler, you have access to SYS_CONTEXT('USERENV','CURRENT_SQL'). If your policy handler saved that off somewhere, your trigger could have access to it.

Unfortunately, your trigger needs to be an AFTER trigger, because a BEFORE trigger will execute before the FGA policy.

Here is a quick example of the idea:

Create a test table

--DROP TABLE matt1;
CREATE TABLE matt1 ( a number );

Create a fine-grained auditing policy handler to save the last SQL

CREATE OR REPLACE PACKAGE xxcust_record_last_sql_pkg AS
  -- TODO: you probably would want to store a collection of last SQL by table name
  l_last_sql VARCHAR2(32000);
  PROCEDURE record_last_sql (object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2);
  FUNCTION get_last_sql RETURN VARCHAR2;
END xxcust_record_last_sql_pkg;
/

CREATE OR REPLACE PACKAGE BODY xxcust_record_last_sql_pkg AS

  PROCEDURE record_last_sql (object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2) IS
  BEGIN
    xxcust_record_last_sql_pkg.l_last_sql := SUBSTR(SYS_CONTEXT ('userenv', 'CURRENT_SQL'),1,32000);
--    raise_application_error(-20001,'SQL = ' || xxcust_record_last_sql_pkg.l_last_sql);
  END record_last_sql;

  FUNCTION get_last_sql RETURN VARCHAR2 IS
  BEGIN
    RETURN xxcust_record_last_sql_pkg.l_last_sql;
  END get_last_sql;

END xxcust_record_last_sql_pkg;
/

Register the FGA policy so that the handler is invoked on any DML of our table

--EXEC DBMS_FGA.drop_policy (user, 'MATT1', 'MATT_TEST_POLICY');

BEGIN
   DBMS_FGA.add_policy (
          object_schema     => user,
          object_name       => 'MATT1',
          policy_name       => 'MATT_TEST_POLICY',
          audit_condition   => '1=1',
          audit_column      => null,
          handler_schema    => user,
          handler_module    => 'XXCUST_RECORD_LAST_SQL_PKG.RECORD_LAST_SQL',
          statement_Types   => 'INSERT,UPDATE,DELETE',
          enable            => TRUE);
END;
/

Create an AFTER INSERT trigger on our table to test the concept

--drop trigger matt1_ari1;

create or replace trigger matt1_ari1 after insert on matt1 for each row
begin
    raise_application_error(-20001, 'Invoking SQL was: ' || substr(xxcust_record_last_sql_pkg.get_last_sql,1,4000));
end;
/

Test it all out

insert into matt1 (a) select 7*rownum from dual connect by rownum <= 5;

Error starting at line : 54 in command - insert into matt1 (a) select
7*rownum from dual connect by rownum <= 5 Error report - ORA-20001:
Invoking SQL was: insert into matt1 (a) select 7*rownum from dual
connect by rownum <= 5 ORA-06512: at "APPS.MATT1_ARI1", line 4
ORA-04088: error during execution of trigger 'APPS.MATT1_ARI1'

Caveat

I am assuming that you have a legitimate reason to want to do this. One reason this is hard is that there aren't common use cases for this. There is auditing and security to control access to tables. In fact, I would bet that proper use of the fine-grained auditing feature by itself (i.e., no custom trigger on the table) would be a better way to do whatever you are trying to do.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59