标签：审计 10 13 AUD 删除 ORCL 2022 NEWMACHINE aud
文档课题：删除审计表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/[email protected]: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/[email protected]: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/[email protected]: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/[email protected]: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视图查出的时间无时区误差.
标签：审计,
10,
13,
AUD,
删除,
ORCL,
2022,
NEWMACHINE,
aud
From： https://blog.51cto.com/u_12991611/5754582