[20180525]丢失审计.txt

[20180525]丢失审计.txt

https://jonathanlewis.wordpress.com/2018/05/24/missing-audit/

---//重复测试:
1.环境:

SCOTT@test01p> @ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

rem
rem     Script:         del_cascade_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2004
rem
rem     Last tested
rem             12.1.0.2
rem             11.2.0.4
rem
 
drop table t2 purge;
drop table t1 purge;
 
create table t1 (
        id              number(6),
        v1              varchar2(10),
        padding         varchar2(100),
        constraint t1_pk
                primary key (id)
);
 
 
create table t2 (
        id_par          number(6),
        id_ch           number(6),
        v1              varchar2(10),
        padding         varchar2(100),
        constraint t2_pk
                primary key (id_par,id_ch),
        constraint t2_fk_t1
                foreign key (id_par) references t1
                on delete cascade
);
 
 
insert into t1
select
        rownum,
        rownum,
        rpad('x',100)
from
        all_objects
where
        rownum <= 100 -- > comment to avoid wordpress format issue
;
 
 
insert into t2
select
        1+trunc((rownum-1)/5),
        rownum,
        rownum,
        rpad('x',100)
from
        all_objects
where
        rownum <= 500 -- > comment to avoid wordpress format issue
;
 
commit;
 
prompt  =================================
prompt  Parent/Child rowcounts for id = 1
prompt  =================================
 
select count(*) from t1 where id = 1;
select count(*) from t2 where id_par = 1;
 
column now new_value m_now
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') now from dual;
 
audit delete on t2 by access;
audit delete on t1 by access;
 
prompt  =======================================================
prompt  If you allow the cascade (keep the t2 delete commented)
prompt  then the cascade deletion is not audited.
prompt  =======================================================
 
-- delete from t2 where id_par = 1;
delete from t1 where id = 1;
 
noaudit delete on t1;
noaudit delete on t2;
 
column obj_name format a32
 
select  action_name, obj_name
from    user_audit_trail
where   timestamp >= to_date('&m_now','dd-mon-yyyy hh24:mi:ss')
;
--//测试结果如下:

SCOTT@test01p> select count(*) from t1 where id = 1;
  COUNT(*)
----------
         1

SCOTT@test01p> select count(*) from t2 where id_par = 1;
  COUNT(*)
----------
         5

column now new_value m_now
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') now from dual;
 
audit delete on t2 by access;
audit delete on t1 by access;
    
SCOTT@test01p> delete from t1 where id = 1;
1 row deleted.    

noaudit delete on t1;
noaudit delete on t2;
 
column obj_name format a32
 
SCOTT@test01p> select  action_name, obj_name from    user_audit_trail where   timestamp >= to_date('&m_now','dd-mon-yyyy hh24:mi:ss') ;
ACTION_NAME          OBJ_NAME
-------------------- --------------------
DELETE               T1

--//仅仅看到删除T1的审计.
SCOTT@test01p> select count(*) from t1 where id = 1;
  COUNT(*)
----------
         0

SCOTT@test01p> select count(*) from t2 where id_par = 1;
  COUNT(*)
----------
         0