we wanna record user delete operation on certain table via trigger, rather than oracle audit(
coz oracle audit was set to NONE after db installation finished, and the rdbms is service for 7*24h). it soulds easy, but we got into trouble.
here's the demo:
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)
it is funny that
we wanna capture delete operation, but got insert. maybe my SQL statement in trigger TRI_TEST01_DEL_LOG is not appropriate.
let's try it again via sqlplus rather than trigger:
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.1the 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?
My first (and probably obvious) question is: Why do you not want to use AUDIT? That is *why* it is there.
Fine grained auditing can be used and you can override the database auditing setting,
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>