[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     Last tested

rem             12.1.0.2

rem             11.2.0.4

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

        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,

        rpad('x',100)

from

        all_objects

where

        rownum <= 100 -- > comment to avoid wordpress format issue

;

insert into t2

        1+trunc((rownum-1)/5),

        rownum <= 500 -- > comment to avoid wordpress format issue

commit;

prompt  =================================

prompt  Parent/Child rowcounts for id = 1

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.

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

         5

SCOTT@test01p> delete from t1 where id = 1;

1 row deleted.    

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的审计.