删除审计表AUD$的相关测试

原创 Leo 2022-10-13

322

文档课题：删除审计表AUD$的相关测试.
数据库：oracle 11.2.0.4 64位
1、开启审计
sys@ORCL 2022-10-12 20:27:47> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/app/oracle/adm
                                             in/orcl/adump
audit_sys_operations                boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE
sys@ORCL 2022-10-12 20:34:31> alter system set audit_trail=db scope=spfile;

System altered.

sys@ORCL 2022-10-12 20:34:54> alter system set audit_sys_operations=true scope=spfile;

System altered.

sys@ORCL 2022-10-12 20:35:43> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL 2022-10-12 20:35:58> startup
ORACLE instance started.

Total System Global Area 2522038272 bytes
Fixed Size                  2255872 bytes
Variable Size            1291846656 bytes
Database Buffers         1207959552 bytes
Redo Buffers               19976192 bytes
Database mounted.
Database opened.
sys@ORCL 12-OCT-22> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/app/oracle/adm
                                                 in/orcl/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB
说明：审计级别为DB表示将审计信息记录在sys.aud$,只包含连接信息(LOGON,LOGOFF),但不包含以sysdba或sysoper连接的信息.
2、删除测试
2.1、审计记录
用system用户登陆登出数据库，产生审计记录.(注意时区问题)
sys@ORCL 2022-10-13 10:28:41> select sessionid,userid,userhost,terminal,logoff$time,ntimestamp# from aud$;

SESSIONID USERID     USERHOST                  TERMINAL        LOGOFF$TIME         NTIMESTAMP#
---------- ---------- ------------------------- --------------- ------------------- ------------------------------
    270194 SYSTEM     WorkGroup\NEWMACHINE      NEWMACHINE      2022-10-13 02:28:44   13-OCT-22 02.28.44.985592 AM
    270105 SYSTEM     WorkGroup\NEWMACHINE      NEWMACHINE      2022-10-13 02:25:24   13-OCT-22 02.25.24.415556 AM
270194 SYSTEM     WorkGroup\NEWMACHINE      NEWMACHINE                          13-OCT-22 02.25.42.413254 AM
2.2、创建Job
创建存储过程.
> create or replace procedure clear_aud (in_days in number)
     as
       begin
        delete sys.aud$ where ntimestamp# < sysdate-in_days;
        exception
             when others then
                  null;
        end;
    /

Procedure created.
创建删除1分钟前的审计记录，且每3秒执行一次.
> variable aud_job number;
> begin
            dbms_job.submit(:aud_job,'clear_aud(1/1440);',sysdate,'sysdate+3/(24*60*60)');
            commit;
        end;
    /

PL/SQL procedure successfully completed.
1分钟后查询.
sys@ORCL 2022-10-13 10:10:10> select sessionid,userid,userhost,terminal,logoff$time,ntimestamp# from aud$;

no rows selected
说明：1分钟后aud$表的记录被自动删除.
3、测试broken
现测试broken为Y时，job不会运行.
产生审计记录.
C:\Users\Administrator>sqlplus system/oracle@192.168.133.216:1521/orcl

SQL*Plus: Release 19.0.0.0.0 - Production on 星期四 10月 13 10:14:06 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@ORCL 2022-10-13 10:12:16> select sessionid,userid,userhost,terminal,logoff$time,ntimestamp# from aud$

SESSIONID USERID     USERHOST                  TERMINAL        LOGOFF$TIME         NTIMESTAMP#
---------- ---------- ------------------------- --------------- ------------------- ------------------------------
270065 SYSTEM     WorkGroup\NEWMACHINE      NEWMACHINE                          13-OCT-22 02.14.06.055262 AM

sys@ORCL 2022-10-13 10:23:26> select job,log_user,priv_user,broken,interval,what from dba_jobs where job=23

       JOB LOG_USER   PRIV_USER B INTERVAL             WHAT
---------- ---------- ---------- - -------------------- --------------------
        23 SYS        SYS        N sysdate+3/(24*60*60) clear_aud(1/1440);
sys@ORCL 2022-10-13 10:23:28> exec dbms_job.broken(23,true);

PL/SQL procedure successfully completed.

sys@ORCL 2022-10-13 10:24:26> select job,log_user,priv_user,broken,interval,what from dba_jobs where job=23;

       JOB LOG_USER   PRIV_USER B INTERVAL             WHAT
---------- ---------- ---------- - -------------------- --------------------
        23 SYS        SYS        Y sysdate+3/(24*60*60)   clear_aud(1/1440);
C:\Users\Administrator>sqlplus system/oracle@192.168.133.216:1521/orcl

SQL*Plus: Release 19.0.0.0.0 - Production on 星期四 10月 13 10:25:29 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
sys@ORCL 2022-10-13 10:28:41> select sessionid,userid,userhost,terminal,logoff$time,ntimestamp# from aud$

SESSIONID USERID     USERHOST                  TERMINAL        LOGOFF$TIME         NTIMESTAMP#
---------- ---------- ------------------------- --------------- ------------------- ------------------------------
    270194 SYSTEM     WorkGroup\NEWMACHINE      NEWMACHINE      2022-10-13 02:28:44 13-OCT-22 02.28.44.985592 AM
    270105 SYSTEM     WorkGroup\NEWMACHINE      NEWMACHINE      2022-10-13 02:25:24   13-OCT-22 02.25.24.415556 AM
270194 SYSTEM     WorkGroup\NEWMACHINE      NEWMACHINE                          13-OCT-22 02.25.42.413254 AM
小结：当Job的Broken被设置为Y时，该Job不会被运行.
sys@ORCL 2022-10-13 10:28:47> exec dbms_job.broken(23,false);

PL/SQL procedure successfully completed.

sys@ORCL 2022-10-13 10:30:31> select job,log_user,priv_user,broken,interval,what from dba_jobs where job=23;

       JOB LOG_USER   PRIV_USER B INTERVAL             WHAT
---------- ---------- ---------- - -------------------- --------------------
        23 SYS        SYS        N sysdate+3/(24*60*60) clear_aud(1/1440);

sys@ORCL 2022-10-13 10:30:39> select sessionid,userid,userhost,terminal,logoff$time,ntimestamp# from aud$;

SESSIONID USERID     USERHOST                  TERMINAL        LOGOFF$TIME         NTIMESTAMP#
---------- ---------- ------------------------- --------------- ------------------- ------------------------------
    270194 SYSTEM     WorkGroup\NEWMACHINE      NEWMACHINE      2022-10-13 02:28:44   13-OCT-22 02.28.44.985592 AM
    270105 SYSTEM     WorkGroup\NEWMACHINE      NEWMACHINE      2022-10-13 02:25:24   13-OCT-22 02.25.24.415556 AM
270194 SYSTEM     WorkGroup\NEWMACHINE      NEWMACHINE                          13-OCT-22 02.25.42.413254 AM
小结：当Job的Broken重新被设置为N时，从实验结果来看，该Job也不会被运行.重建存储过程以及Job删除审计记录.
4、测试noaudit
检查哪种审计记录较多.
SQL> select action_name,count(*) from dba_audit_trail group by action_name;
说明:一般是LOGON和LOGOFF类型的审计最多,取消该类审计.
SQL> noaudit session whenever successful;
测试.
C:\Users\Administrator>sqlplus system/oracle@192.168.133.216:1521/orcl

SQL*Plus: Release 19.0.0.0.0 - Production on 星期四 10月 13 10:47:13 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@ORCL 2022-10-13 10:48:15> select sessionid,userid,userhost,terminal,logoff$time,ntimestamp# from aud$;

no rows selected

小结：system用户的登陆不再记录到aud$表中.

5、测试audit
sys@ORCL 2022-10-13 11:24:33> audit session whenever successful;
Audit succeeded.
测试.
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
C:\Users\Administrator>sqlplus system/oracle@192.168.133.216:1521/orcl

SQL*Plus: Release 19.0.0.0.0 - Production on 星期四 10月 13 11:30:13 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
sys@ORCL 2022-10-13 11:30:14> select sessionid,userid,userhost,terminal,logoff$time,ntimestamp# from aud$

SESSIONID USERID          USERHOST                  TERMINAL        LOGOFF$TIME         NTIMESTAMP#
---------- --------------- ------------------------- --------------- ------------------- ------------------------------
    280007 SYSTEM          WorkGroup\NEWMACHINE      NEWMACHINE      2022-10-13 03:30:17 13-OCT-22 03.30.17.408109 AM
    270196 SYSTEM          WorkGroup\NEWMACHINE      NEWMACHINE      2022-10-13 02:47:10   13-OCT-22 02.47.10.508068 AM
280007 SYSTEM          WorkGroup\NEWMACHINE      NEWMACHINE                          13-OCT-22 03.30.12.619736 AM

小结：恢复audit后，system用户的登陆登出被记录到aud$审计表中.

相关网址：http://blog.itpub.net/31490526/viewspace-2795411/

补充说明：通过DBA_AUDIT_TRAIL视图查出的时间无时区误差.

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

删除审计表AUD$的相关测试

1、开启审计

2、删除测试

2.1、审计记录

2.2、创建Job

3、测试broken

4、测试noaudit

5、测试audit

评论