sql - 如何在该触发器中找到已调用触发器的 sql 语句的 audsid、sql_id

Question

一些记录正在从表中删除，我们想要识别从表中删除记录的 sql 语句，以便我们可以检查找到导致问题的程序。

我写了以下但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;

但是我在 mysql 中没有看到“从 abc 中删除”sql。
难道我做错了什么？

有没有其他方法可以捕获调用触发器的 sql 语句的 sql_id、prev_sql_id、audsid(在触发器块内)。

预先感谢您在这方面的任何帮助。

Answer 1

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).

不容易。

嗯，AUDSID 很简单:使用表达式 SYS_CONTEXT('USERENV','SESSIONID')
据我所知，获取 SQL_ID 是不可能的，但是获取 SQL 文本是可能的，尽管很困难。

为此，您需要在表上创建细粒度审计 (FGA) 策略。在 FGA 策略处理程序中，您可以访问 SYS_CONTEXT('USERENV','CURRENT_SQL') .如果您的策略处理程序将其保存在某处，则您的触发器可以访问它。

不幸的是，您的触发器必须是 AFTER 触发器，因为 BEFORE 触发器将在 FGA 策略之前执行。

这是这个想法的一个快速示例:

创建测试表

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

创建一个细粒度的审计策略处理程序来保存最后的 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;
/

注册 FGA 策略，以便在我们表的任何 DML 上调用处理程序

--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;
/

在我们的表上创建一个 AFTER INSERT 触发器来测试这个概念

--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;
/

测试一下

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'

警告

我假设您有正当理由想要这样做。这很难的一个原因是没有常见的用例。有审计和安全来控制对表的访问。事实上，我敢打赌，正确使用细粒度审计功能本身(即表上没有自定义触发器)将是做任何您想做的事情的更好方法。