如何通过触发器记录删除操作 (而不是oracle审计)

我们想通过触发器在某些表上记录用户删除操作，而不是oracle审计 (coz oracle audit was set to NONE after db installation finished, and the rdbms is service for 7*24h)。这很容易，但是我们遇到了麻烦。

这是演示:

SQL> CREATE TABLE TEST01 AS SELECT 10 AS A FROM DUAL;

Table created.

SQL> CREATE TABLE TEST_01_DEL_LOG(    
  2           LOG_TABLE VARCHAR2(100) NOT NULL,   
  3           LOG_DML   VARCHAR2(20),
  4           SQL_ID    VARCHAR2(13),
  5           SQL_TEXT  VARCHAR2(1000),
  6           OSUSER   VARCHAR2(20),
  7           MACHINE  VARCHAR2(64),
  8           PROGRAM  VARCHAR2(48),
  9           LOG_DATE  DATE,   
 10           LOG_USER  VARCHAR2(15),
 11           LOG_TERMINAL varchar2(100),
 12           LOG_IP_ADDRESS varchar2(20)   
 13           ); 

Table created.

SQL> CREATE OR REPLACE TRIGGER TRI_TEST01_DEL_LOG
  2  after DELETE ON TEST01 FOR EACH ROW
  3  BEGIN
  4  INSERT INTO TEST_01_DEL_LOG
  5    select 'TEST01' table_name,
  6             'DELETE' FIELD,
  7             se.sql_id,
  8             sq.sql_text,
  9             se.OSUSER,
 10             se.MACHINE,
 11             se.PROGRAM,
 12             SYSDATE,
 13             USER,
 14             SYS_CONTEXT('USERENV','TERMINAL'),
 15             SYS_CONTEXT('USERENV','IP_ADDRESS')
 16             from v$sql sq, v$session se
 17        where se.audsid=(select userenv('SESSIONID') from dual) 
 18        and  se.PREV_HASH_VALUE = sq.hash_value
 19        and  se.prev_sql_addr = sq.address;
 20  END;
 21  /

Trigger created.

SQL> truncate table TEST_01_DEL_LOG;

Table truncated.

SQL> insert into TEST01 values(100);

1 row created.

SQL> insert into TEST01 values(200);

1 row created.

SQL> delete from TEST01 where a=100;

1 row deleted.

SQL> insert into TEST01 values(300);

1 row created.

SQL> insert into TEST01 values(400);

1 row created.

SQL> delete from TEST01 where a=300;

1 row deleted.

SQL> select sql_text from TEST_01_DEL_LOG;

SQL_TEXT
--------------------------------------------------------------------------------
insert into TEST01 values(200)
insert into TEST01 values(400)

有趣的是we wanna capture delete operation, but got insert。也许我在触发器TRI_TEST01_DEL_LOG中的SQL语句不合适。
让我们通过sqlplus而不是触发器再试一次:

SQL> col SQL_TEXT for a40
SQL> col SYS_CONTEXT('USERENV','IP_ADDRESS') for a12
SQL> truncate table TEST_01_DEL_LOG;

Table truncated.

SQL> insert into test01 values(100);

1 row created.

SQL> insert into test01 values(200);

1 row created.

SQL> delete from test01 where a=100;

1 row deleted.

  select -- 'TEST01' table_name,
  2           --  'DELETE' FIELD,
  3           --  se.sql_id,
  4             sq.sql_text,
  5           --  se.osuser,
  6           --  se.machine,
  7           --  se.program,
  8           --  SYSDATE,
  9           --  USER,
 10           --  SYS_CONTEXT('USERENV','TERMINAL'),
 11             SYS_CONTEXT('USERENV','IP_ADDRESS')
 12             from v$sql sq, v$session se
 13        where se.audsid=(select userenv('SESSIONID') from dual) 
 14        and  se.PREV_HASH_VALUE = sq.hash_value
 15        and  se.prev_sql_addr = sq.address;

SQL_TEXT                                 SYS_CONTEXT(
---------------------------------------- ------------
delete from test01 where a=100           127.0.0.1

the delete operation was queried out, but not logged via trigger!
this demo makes me feel confused. suppose that we wanna record delete operation via tigger, what shall we do?

我的第一个 (可能也是显而易见的) 问题是: 为什么你不想使用审计？这就是为什么它在那里。

可以使用细粒度审计，您可以覆盖数据库审计设置，

SQL> create table my_tab as select * from scott.emp;

Table created.

SQL> BEGIN
  2    DBMS_FGA.add_policy(
  3      object_schema   => user,
  4      object_name     => 'MY_TAB',
  5      policy_name     => 'MY_TAB_DEL',
  6      audit_condition => null,
  7      statement_types => 'DELETE',
  8      enable          => TRUE,
  9      audit_trail     => DBMS_FGA.DB + DBMS_FGA.EXTENDED);
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> delete from my_tab where empno = 7364;

0 rows deleted.

SQL> delete from my_tab where empno = 7654;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select lsqltext
  2  from sys.fga_log$;

LSQLTEXT
--------------------------------------------------------------------------------
delete from my_tab where empno = 7364
delete from my_tab where empno = 7654

SQL>